Modern Database Management (12th Edition)
12th Edition
ISBN: 9780133544619
Author: Jeffrey A. Hoffer, Ramesh Venkataraman, Heikki Topi
Publisher: PEARSON
expand_more
expand_more
format_list_bulleted
Question
Chapter 4, Problem 4.46PAE
Program Plan Intro
Transformation of given car racing league EER diagram into a relational schema.
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solution
Students have asked these similar questions
Develop 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)
Transform the diagram to a relational schema that shows referential integrity constraints
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'.
Chapter 4 Solutions
Modern Database Management (12th Edition)
Ch. 4 - Prob. 4.1RQCh. 4 - Prob. 4.2RQCh. 4 - Prob. 4.3RQCh. 4 - Describe the primary differences between the...Ch. 4 - Summarize six important properties of relations.Ch. 4 - Describe two properties that each candidate key...Ch. 4 - Describe the three types of anomalies that can...Ch. 4 - Demonstrate each of the anomaly types with an...Ch. 4 - Fill in the blanks in each of the following...Ch. 4 - What is a well-structured relation? Why are...
Ch. 4 - Prob. 4.11RQCh. 4 - Describe how the following components of an E-R...Ch. 4 - Prob. 4.13RQCh. 4 - Prob. 4.14RQCh. 4 - Briefly describe four typical problems that often...Ch. 4 - Prob. 4.16RQCh. 4 - Explain how each of the following types of...Ch. 4 - Prob. 4.18RQCh. 4 - Prob. 4.19RQCh. 4 - Prob. 4.20RQCh. 4 - Prob. 4.21RQCh. 4 - What is the relationship between the primary key...Ch. 4 - Prob. 4.23RQCh. 4 - Explain what can be done with primary keys to...Ch. 4 - Prob. 4.25RQCh. 4 - Explain three conditions that suggest a surrogate...Ch. 4 - Prob. 4.27RQCh. 4 - For each of the following E-R diagrams from...Ch. 4 - Prob. 4.29PAECh. 4 - Prob. 4.30PAECh. 4 - For your answers to the following Problems and...Ch. 4 - Figure 4-3212 shows a class list for Millennium...Ch. 4 - Prob. 4.33PAECh. 4 - Prob. 4.34PAECh. 4 - Prob. 4.35PAECh. 4 - Prob. 4.36PAECh. 4 - Prob. 4.37PAECh. 4 - Prob. 4.38PAECh. 4 - For your answers to the following Problems and...Ch. 4 - Transform Figure 2-15a, attribute version, to 3NF...Ch. 4 - Prob. 4.41PAECh. 4 - Prob. 4.42PAECh. 4 - Prob. 4.43PAECh. 4 - Prob. 4.44PAECh. 4 - For your answers to Problem and Exercise 3-33 from...Ch. 4 - Prob. 4.46PAECh. 4 - Prob. 4.47PAECh. 4 - Figure 4-38 includes an EER diagram for a...Ch. 4 - Prob. 4.49PAECh. 4 - Prob. 4.50PAECh. 4 - Prob. 4.51PAECh. 4 - Prob. 4.52PAECh. 4 - Figure 4-40 shows an EER diagram for a university...Ch. 4 - Explore the data included in Table 4-9. Assume...Ch. 4 - Prob. 4.55PAECh. 4 - Prob. 4.56PAECh. 4 - Prob. 4.57PAECh. 4 - Prob. 4.58PAE
Knowledge Booster
Similar questions
- Question 3 Normalize table 1 to a set of Third Normal Form (3NF) relations. Your answer should show all the three stages of normalization (1NF, 2NF and 3NF) by using the Database Design Language format (underline all primary keys, composite keys and use an * to indicate the foreign keys). State the component(s) that is/are removed from each Normal Form. Besides that, INF must be divided into repeating and non-repeating group relations. a) PatientID PatientName BloodID BloodType ExamDate DiseaselD DiseaseDesc HospitallD HospitalName 08/01/2020 D5001 08/01/2020 D3222 |11/11/2020 D5001 |11/11/2020 D7788 15/11/2020 D5001 12/12/2020 D3222 12/12/2020 D5001 Alice Hospital KL Hospital KL Hospital PJ Hospital SB Hospital SB Hospital KL Hospital PJ P7010 B001 B001 B001 A+ COVID-19 Н223 P7010 Alice A+ Diabetes H223 Alice Thomas P7010 A+ COVID-19 H881 P8888 B008 O- SARS H666 Penny Penny Julie P3000 B009 AB+ COVID-19 H666 P3000 B009 AB+ Diabetes H223 P4455 B008 0- COVID-19 H881 Table 1: Details of…arrow_forwardTransform the diagram to a relational schema that shows referential integrity constraints (see the following figure for an example of such a schema – underlined items are primary keys, dotted underlines refer to foreign keys, while the arrows indicate direction from foreign to primary)arrow_forwardTable 4-8 shows a portion of a shipment table for a large manufacturing company. Each shipment (identified by Shipment#) uniquely identifies the shipment Origin, Destination, and Distance. The shipment Origin and Destination pair also uniquely identifies the Distance. Question: a. Develop a diagram that shows the functional dependencies in the SHIPMENT relation. b. In what normal form is SHIPMENT? Why? c. Convert SHIPMENT to third normal form if necessary. Show the resulting table(s) with the sample data presented in SHIPMENT.arrow_forward
- Normalize Table 1 to a set of Third Normal Form (3NF) relations. Your answer should show all the three stages of normalization (1NF, 2NF and 3NF) by using the Database Design Language format (underline all primary keys, composite keys and use an * to indicate the foreign keys). State the action taken upon the table(s) for each Normal Form. For the INF in this case, only identify the Primary Key and keep the INF result in a single table. Then, split into new table(s) starting from a) 2NF onwards. Also, consider the following assumptions: - One ReservelD can have multiple venues' booking. - One venue can only be booked once for the same ReservelD on the same ReserveDate ReservelD ReserveDate StartTime EndTime VenuelD VenueTag 10:00 13:00 210101-01 3-Jan-21 210101-02 3-Jan-21 Hibiscus Lily VenueSize FeePerHour Purpose |150 15:00 v10 Small Discussion 20:00 20:00 21:00 V22 Medium 260 Forum 210101-02 3-Jan-21 13:00 V20 Petal Medium 260 Meeting 210101-02 3-Jan-21 17:00 V10 Hibiscus Small 150…arrow_forwardConsider the unnormalized relation R with six attributes ABCDEF and the following functional dependencies:AB ->CDEB -> FC -> Da) What is the key(s) for the relation?b) What is the normal form of this relation? Explain it.c) Decompose R into 3NF relations step by step if it is not in 3NF.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
- The use of composite attributes are avoided in relation schema when the relation schema conforms to –(a) 1NF(b) BCNF(c) 3NF(d) 4NFarrow_forwardConsider 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_forwardConsider 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_forward
- Given the following EER diagram, write down the corresponding relations using the method taught in conceptual database design. Also use the template taught to represent the relational schema. Agreement Policy usedfor 0..* 1.1 policyNo {PK} agreeNo{PK} agreeDate sumAssured currency 0. * {Optional, Or} insuredBy issueAge Permanent Term 1. * maxCoverAge noOfYears Insured Person personID {PK} {Mandatory, And} sex Life Critical Illness interestRate benefitarrow_forwardWhat are three signs that a surrogate key should be generated for the primary key of a relation instead of using the main key?arrow_forwardMultivalued dependencies are avoided in relation schema when the relation schema conforms to –(a) 1NF(b) BCNF(c) 3NF(d) 4NFarrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
Database 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
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
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