3. Translate the E-R dlagram into a relational model stating all tables, their primary keys, attributesand foreign keys(18 points] Underline the Primary Kevs. Cirele the Foreign KeysBranchHasStaftNuonrclass1.1branchNo (PK)addressstreotcitypontcodeutatfo (amepoatonalery11Aanages(Optionat, And)ry On1..1ManagerSalosPersonrietSecretaryFultimelomanentParTateTemponarmgrStartDatobonussalesAmaCarAllowanaesaaryScaleNadayAowanontyoingSpeedJob roiesubclassesContract of employmertsubcases

Given :- An Entity Relation diagram is mention in the above given question Need to translate the…

Answered: E-R dlagram into a relational model…

Engineering

Computer Science

E-R dlagram into a relational model ysf18 ointe

Database Systems: Design, Implementation, & Management

11th Edition

ISBN:9781285196145

Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Chapter10: Transaction Management And Concurrency Control

Section: Chapter Questions

Problem 1P

See similar textbooks

Similar questions

Please help me in this problem of Relational databases: Consider the relation schema R = {A, B, C, D, E, F, G} and the following set Σ of FDs:• C → BG• AE → F• CF → EA• A → DF1. What are the candidate keys of R? Justify your answer (i.e., include the main steps used for findingthe candidate keys). 2. Find a minimal cover of Σ and include the main steps used for finding a minimal cover. 3. Demonstrate why R, given Σ, does not satisfy 3NF, and then identify a 3NF decomposition for R.You need to include the main steps used for identifying the 3NF decomposition
Define in SQL-like syntax a view of the engineering database V(ENO, ENAME, PNO, RESP), where the duration is 24. Is view V updatable? Assume that relations EMP and ASG are horizontally fragmented based on access frequencies as follows: Site 1 Site 2 Site 3 EMP1 EMP2 ASG1 ASG2 Where EMP1 = ?TITLE ≠“Engineer.”(EMP) EMP2 = ?TITLE =“Engineer.”(EMP) ASG1 = ?0 < DUR < 36(ASG) ASG2 = ?DUR ≥ 36(ASG) At which site(s) should the definition of V be stored without being fully replicated, to increase locality of reference?
Provide a normalized relational logical schema for the same database (N.B. It is not mandatory to report the restructured E-R diagram) Define referential integrity constraints for 2 relationships of your choice among those defined in the conceptual schema solution: CUSTOMER(FiscalCode, Name, Surname, Address, LoyaltyCard*, Type) INTOLERANCES(Intolerances, FiscalCode) TICKET(CodS, CodF, Date, FisclaCode, Price) SEAT(CodS, CodF, Class) FLIGHT(CodF, Departure, Arrival, Departure_date, Arrival_date, Airplane_type) Integrity constraints: SEAT(CodF) REFERENCES FLIGHT(CodF) TICKET(CodS, CodF) REFERENCES SEAT(CodS, CodF) TICKET(FiscalCode) REFERENCES CUSTOMER(FiscalCode) i provided the solution but i dont understand where it came from. when do we and when do we not consider the black circles in the diagram and if we do for what and on what basis do we consider them? what is type? what are the references and how do i understand which ones i should take and not? please explain every…
4. (a) Why is normalization important in database design? Consider the universal relation R = {A, B, C, D, E, F, G, H, I, J} and the set of functional dependencies F = { AB → C, A → DE, B → F, F → GH, D → IJ }.What is the key for R? Decompose R into 2NF (Second Normal Form) and then 3NF (Third Normal Form) relations. (b) Consider a relation schema R = (X, Y, Z) on which the following functional dependencies hold: { XY → Z, Z → X }. Show that the schema R is not in BCNF (Boyce-Codd Normal Form) but it is in 3NF (Third Normal Form).
Please answer the following relational database question asap!! Consider the following relation schema R= {P, Q, S, T} and a set of functional dependencies Σ = {PQ->S, S->PQ, P->T}. Is R in the BCNF form with respect to Σ? If not, determine a BCNF decomposition for R and check if this BCNF decomposition is dependency-preserving.
Give a relational database schema for the following: EMPLOYEE (Fname, Minit, Lname, SSN, Bdate, Address, Sex, Salary, Super_ssn, Dno) PK= Ssn FK= Super_ssn DEPARTMENT ( Dnamc, Dnumbcr, Mgr_ssn, Mgr_start_datc) PK= Dnumbcr FK= Dno DEPT_LOCATIONS (Dnumber, Dlocation) PK= Dnumber FK= Dnumbcr PROJECT (Pname, Pnumber, Plocation, Dnum) PK= Pnumber FK= Pno WORKS_ON (Essn, Pno, Hours) PK= Pno FK= ssn DEPENDENT (Essn, Dependent_name, Sex, Bdate, Relationship) PK= Essn FK= Ssn
Consider given database schema and write triggers for each statement. Student ( regNo, sName, sCity, sCGPA, sAge) Student_BACKUP( regNo, sName, sCity, sCGPA, sAge) Course ( cCode, cName, credit_hours) Enrollment (regNo, cCode, semester,quality_points,Grade) Enrollment_BACKUP(regNo, cCode, semester,quality_points,Grade) 1- Whenever a record is inserted in student table, system must check following constraints in inserted value. a. sAge between 18 and 25 b. sCGPA between 0.0 and 4.0 if any of constraints are violated, system must delete record inserted by user. 2- Write a mechanism which should not allow user to update Grade in Enrollment table. Other columns of Enrollment table can be updated. 3- Write a mechanism which should copy those record to “Enrollment_BACKUP” whose GRADE is ‘F’, whenever a record is inserted in Enrollment Table. 4- Write a mechanism which stores all deleted record to Student_BACKUP table whenever a record is deleted from Student table. 5- Write a…
Consider given database schema and write triggers for each statement. Student ( regNo, sName, sCity, sCGPA, sAge) Student_BACKUP( regNo, sName, sCity, sCGPA, sAge) Course ( cCode, cName, credit_hours)Enrollment (regNo, cCode, semester,quality_points,Grade)Enrollment_BACKUP(regNo, cCode, semester,quality_points,Grade) 1- Whenever a record is inserted in student table, system must check following constraints in inserted value.a. sAge between 18 and 25b. sCGPA between 0.0 and 4.0if any of constraints are violated, system must delete record inserted by user. 2- Write a mechanism which should not allow user to update Grade in Enrollment table.Other columns of Enrollment table can be updated. 3- Write a mechanism which should copy those record to “Enrollment_BACKUP” whose GRADE is ‘F’, whenever a record is inserted in Enrollment Table. 4- Write a mechanism which stores all deleted record to Student_BACKUP table whenever a record is deleted from Student table. 5-…
1) How many candidate keys exist? List them and explain why that is. 2) Find a BCNF decomposition of PLANET. 3) Compute {S,A}⁺ 4)I s the solution to 4 dependency preserving? Justify your response.
Please solve nd tell me te answer of relational database. With explaination. Consider the relation schema Booking={Airline, Date, Destination, FlightNo, PassengerName, Origin,PassportNo, Seat, Terminal} and the following set Σ of FDs:• Airline → Terminal• FlightNo → Airline, Origin, Destination• PassportNo → PassengerName• FlightNo, PassportNo, Date → Seat• FlightNo, Date, Seat → PassportNoWhich, for brevity, we can alias the attributes and restate as Booking={A, D, E, F, N, O, P, S, T} and Σ:• A → T• F → AOE• P → N• FPD → S• FDS → P1. Is the above relation schema Booking in BCNF? If not, identify a BCNF decomposition for Bookingand check if your BCNF decomposition is dependency-preserving. You need to include the main stepsused for identifying the BCNF decomposition. 2. Consider another set Σ1 of FDs on Bookings:• Airline → Terminal• FlightNo → Airline, Origin, Destination• PassportNo → PassengerName• FlightNo, PassportNo, Date → Terminal, Seat• FlightNo, Date, Seat → PassportNo,…
Given the following relation(s) and the functional dependencies, what is the highest normal form the data model complies to? Note: Primary keys are indicated in bold. TaxiRide (jobID, jobDateTime, driverID, driverName, taxiID, clientID, clientName, jobPickUpAddress)jobID -> jobDateTime, driverID, driverName, taxiID, clientID, clientName, jobPickUpAddressjobDateTime, driverID -> jobID, driverName, taxiID, clientID, clientName, jobPickUpAddressjobDateTime, taxiID -> jobID, driverID, driverName, clientID, clientName, jobPickUpAddressjobDateTime, clientID -> jobID, driverID, driverName, taxiID, clientName, jobPickUpAddressdriverID -> driverName, taxiIDclientID -> clientName A)1NF B)2NF C)3NF
Provide a normalized relational logical schema for the same database (NB It is not mandatory to report the restructured ER diagram) Define referential integrity constraints for 2 relationships of your choice among those defined in the conceptual schema Normalized logical relational schema EMPLOYEE( CodE, NameE, Qualification*, Type)SEASIDE_RESORT(CodS, NameS, WebPage*)TIME (SDate)WORK_AT (CodE, CodS, SDate, EndDate, JobRole)BEACH_SEAT(CodS, CodBS, sea_distance, CodPR)PRICE_RANGE(CodPR, Amount) Referential integrities 1) RELATIONSHIP IN BEACH_SEAT(CodS) REFERENCES SEASIDE_RESORT(CodS) 2) RELAZIONE WORK_AT WORK_AT(CodE) REFERENCES EMPLOYEE(CodE)WORK_AT(CodS) REFERENCES SEASIDE_RESORT(CodS)WORK_AT(SDate) REFERENCES TIME(SDate)

SEE MORE QUESTIONS

Recommended textbooks for you

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

SEE MORE TEXTBOOKS