(a)
The given diagram into a relational schema showing referential integrity constraint, the functional dependencies and transformation into 3NF.
(b)
Transformation of a given diagram into a relational schema showing referential integrity constraint and a diagram of the functional dependencies.
(c)
The given diagram into a relational schema showing referential integrity constraint, diagram the functional dependencies and transformation into 3NF if not in 3NF.
(d)
The given diagram into a relational schema showing referential integrity constraint and the diagram of functional dependencies and transformation into 3 NF.
(e)
Transform the given diagram into a relational schema showing referential integrity constraint, diagram the functional dependencies and transform into 3NF if not in 3NF.
Trending nowThis is a popular solution!
Chapter 4 Solutions
Modern Database Management (12th Edition)
- How does the primary key of a relation relate to the interdependencies of its characteristics from a functional standpoint?arrow_forwardIn what three scenarios should a surrogate key be generated in place of a relation's primary key?arrow_forwardPlease transform the following Entity-Relationship Schema (Diagram) into a Relational Schema. Please do not forget to specify the keys and constraints for each relation. (RESP_ENG: the engineer responsible for the project. WORKS_ON: the project an engineer works on). MANAGES SSNO FName PNo PROJECT EMPLOYEE LName PName |1 Address \ISA WORKS ON ENGINEER Speciality RESP ENGarrow_forward
- For developing our queries, we will be using the following relational schema: Student(SID, name, username, major) Course(cname, description, department, weight) Offering(OID, cname, year, term, duration) Enrolled(SID, OID, grade) A course relation is the generic description of a course which is offered by a department. The offering relation is the specific instance of a course being taught in a given term, and is the entity which will be associated with the student enrollment. Students are enrolled in these offerings and achieve a mark. Express each of following queries in Relational Algebra: | Write a relational algebra query which will list the cname and description of all the courses which are being offering in the Fall 2018 term. 2 Write a relational algebra query which will list the name of all the students who have earned 60% in both 'COSC2P12' and 'COSC2P13'.arrow_forwardDevelop an Entity Relationships Diagram (ERD) based on the relational schemas in the 3NF for the starting relational schema in un-normalized form presented below. UNF:Order(OrderID, OrderDate, CustID, CustName, CustPhone, CCNum, CCExpDate, CCBank, BnkContName, BnkContPhone, CustEmail, OrderIP, SiteRefFrom, ShipStreet, ShipCity, ShipSt, ShipZip, OrderLineNum, ItemID, ItemName, ItemDesc, ItemQtyOrdered,ItemListPrice, ItemSalePrice, ItemQtyShip, ShipCharge, Tax, TotalDue)arrow_forwardWhat is the relationship between the primary key of a relation and the functional dependencies among all attributes within that relation?arrow_forward
- Create a relational diagram for the database using crow’s foot notation. The ERD should include all the entities, relationships, and connectivity where applicable. 4 tables for the database: PATRON, BOOK_COPY, BOOK, and CHECKOUT. (Since a book may have multiple copies that may be purchased by the library at different time, it is better to have a BOOK_COPY table to avoid unnecessary data redundancy.) The data in the tables are as follows: PATRON table records a patron’s ID, name, address, phone number, and email address. BOOK table contains information such as author, title, publication date, subject, language, and a unique identifier (It can be the ISBN of the book) for each book. BOOK_COPY table records a unique identifier for each copy of a book, the date of purchase, and the identifier of the book from the BOOK table. CHECKOUT table records the date of check-out, patron’s ID, the identifier of the book copy…arrow_forwardWhat are three advantages of using a surrogate key rather than the main key to access a relation instead?arrow_forward
- Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage LearningDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage Learning