Modern Database Management
13th Edition
ISBN: 9780134773650
Author: Hoffer
Publisher: PEARSON
expand_more
expand_more
format_list_bulleted
Concept explainers
Videos
Textbook Question
Chapter 4, Problem 4.48PAE
Figure 4-38 includes an EER diagram for a medium-size software vendor. Transform the diagram into a relational schema that shows referential integrity constraints (see Figure 4-5 for an example of such a schema). In addition, verify that the resulting relations are in 3NF.
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solution
Students have asked these similar questions
Please 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
ENG
Transform the diagram to a relational schema that shows referential integrity constraints
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.
Chapter 4 Solutions
Modern Database Management
Ch. 4 - Define each of the following terms: determinant...Ch. 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 - Prob. 4.39PAECh. 4 - Transform Figure 2-15a, attribute version, to 3NF...Ch. 4 - The Public Safety office at Millennium College...Ch. 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
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.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)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_forwardUse the technique of normalization to validate the structure of your relational schema. Demonstrate that each of your relations is in third normal form (3NF) by displaying the functional dependencies between attributes in each relation. Note, if any of your relations are not in 3NF, this may indicate that your ER model is structurally incorrect or that you have introduced errors in the process of deriving relations from your model.arrow_forward
- What are three signs that a surrogate key should be generated for the primary key of a relation instead of using the main key?arrow_forwardIn what three scenarios should a surrogate key be generated in place of a relation's primary key?arrow_forwardExplain three conditions that suggest a surrogate key should be created for the primary key of a relation.arrow_forward
- 4-44. Figure 4-35 shows an EER diagram for Vacation Property Rentals. This organization rents preferred properties in several states. As shown in the figure, there are two basic types of properties: beach properties and mountain properties. Transform the EER diagram to a set of relations and develop a relational schema. Diagram the functional dependencies and determine the normal form for each relation. Convert all relations to third normal form, if necessary, and draw a revised relational schema. Suggest an integrity constraint that would ensure that no property is rented twice during the same time interval.arrow_forwardExplain three scenarios in which a surrogate key for a relation's primary key should be generated.arrow_forward2. What is the minimum level of normal form for a relation with only one non- prime attribute? 2NFarrow_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_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_forwardGiven the relations a(name, address, title) and b(name, address, salary), show how to express a natural full outer join b using the full outer-join operation with an on condition rather than using the natural join syntax. This can be done using the coalesce operation. Make sure that the result relation does not contain two copies of the attributes name and address and that the solution is correct even if some tuples in a and b have null values for attributes name or address.arrow_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
A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr
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
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
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
Enhanced Entity Relationship Model; Author: Data Science Center;https://www.youtube.com/watch?v=ocQUtXPumdQ;License: Standard YouTube License, CC-BY