(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)
- Consider the advisor relation shown in the schema diagram in Figure 2.9, withs_id as the primary key of advisor. Suppose a student can have more than oneadvisor. Then, would s_id still be a primary key of the advisor relation? If not,what should the primary key of advisor be?arrow_forwardIn what three scenarios should a surrogate key be generated in place of a relation's primary key?arrow_forwardFor 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_forward
- Consider the given ER Diagram for Notown database in Figure 2. You have been tasked to recommend that Notown use a relational database system to store company data. Show the Structured Query Language (SQL) statements for creating relations corresponding to the entity sets and relationship sets in your design. Identify any constraints in the ER Diagram that you are unable to capture in the SQL statements and briefly explain why you could not express them.arrow_forwardExplain three scenarios in which a surrogate key for a relation's primary key should be generated.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_forward
- Three instances in which a surrogate key should be constructed for the relation's primary keyarrow_forwardTransform the diagram to a relational schema that shows referential integrity constraintsarrow_forwardWhat is the connection between a relation's primary key and the functional relationships between all of its attributes?arrow_forward
- 2. For the following questions, please identify the candidate keys of the given relations. You need to show the process to justify your answers. a) The given relation is R1(ABCDE). The given dependencies are A B B-E C-D b) The given relation is R2(ABCDE). The given dependencies are AB→BCDE BC ACE D-Earrow_forwardID name Courses M semester Teaches N ID name Professors Convert the above ER diagram to relations in the normal manner, and then identify which of the following is a relation schema. O A. Courses (ID, Name) O B. Teaches (ID, ID, semester) OC. Professors(ID, name, semester) O D. Teaches (ID, semester)arrow_forwardDescribe three scenarios when a surrogate key should replace a relation's primary key.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