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
- Covert the E-R diagram to relational schema.arrow_forwardQuestion 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_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
- Transform the following entity relationship diagram into a relational schema.arrow_forwardNormalize 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_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
- Review FamCation relations and determine if they are all in 3NF. If not, which relations do you need to normalize and how?arrow_forwardDesign a Relational Database by Mapping the following Entity-Relationship Diagram (ERD) into Relational Model. You must clearly determine the PK of each relation. Also, you should show the links between FKs and PKs in your answer. SName Owner SHIP MOVEMENT PName HISTORY HOME PORT SHIP PORT Latitude (0,") VISITS (0.") Timestamp Longitude N N N (1.1) IN TYPE ON 1 1 Date Time StartDate VISITS Type EndDate Tonnage SHIP TYPE Name STATE/COUNTRY Name SEA/OCEAN/LAKE Hull Continentarrow_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_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_forwardYou are working with a database that stores information about suppliers, parts and projects. The Supply relation records instances of a Supplier supplying a Part for a Project.The schema for the database used in this question is as follows: (note that primary keys are shown underlined, foreign keys in bold). SUPPLIER (SNo, SupplierName, City)PART (PNo, PartName, Weight)PROJECT (JobNo, JobName, StartYear, Country)SUPPLY (SNo, PNo, JobNo, Quantity) Provide relational algebra (NOT SQL) queries to find the following information.NOTE:• You can use the symbols σ, π, etc or the words ‘PROJECT’, ‘RESTRICT’ etc as you prefer.• You do not need to try to make efficient queries – just correct ones.• Where you use a join, always show the join condition.a. List the Project name and Part name of any Parts where fewer than 500 of the part has been supplied to a particular project.b. List the name of any Suppliers who have supplied Parts to the Project called “New Academic Building” or to the Project…arrow_forwardGiven 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_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