Modern Database Management
13th Edition
ISBN: 9780134773650
Author: Hoffer
Publisher: PEARSON
expand_more
expand_more
format_list_bulleted
Question
Chapter 4, Problem 4.44PAE
Program Plan Intro
Creating relations from EER Diagram.
Program Plan Intro
Functional dependency of each relation.
Program Plan Intro
Convert relations into 3NF
Program Plan Intro
An integrity constraint that would ensure that no property is rented twice during the same time interval.
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solution
Students have asked these similar questions
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.
Ņ 5. Table 4-5 shows a shipping manifest. Your assignment is as follows: a. Draw a relational schema and diagram the functional dependencies in the relation. b. In what normal form is this relation? + c. Decompose MANIFEST into a set of 3NF relations. d. Draw a relational schema for your 3NF relations and show the referential integrity constraints. TABLE 4-5 Shipping Manifest 00-0001 Boston Brazil 39 Shipment ID: Origin: Destination: Ship Number: Item Number 3223 3297 Type BM BM Description Concrete Form Steel Beam Shipment Date: Expected Arrival: Captain: Weight 500 87 Quantity 100 2,000 Shipment Total: 01/10/2010 01/14/2010 002-15 Henry Moore TOTALWEIGHT 50,000 174,000 224,000
Using the STUDENT table structure shown in Table P6.4, do the following:a. Write the relational schema and draw its dependency diagram. Identify all dependencies, including all transitive dependencies.b. Write the relational schema and draw the dependency diagram to meet the 3NF requirements to the greatest practical extent possible. If you believe that practical considerations dictate using a 2NF structure, explain why your decision to retain 2NF is appropriate. If necessary, add or modify attributes to create appropriate determinants and to adhere to the naming conventions.c. Using the results of Problem 4, draw the Crow's Foot ERD.Note: Although the completed student hours (STU_HOURS) do determine the student classification (STU_ClASS), this dependency is not as obvious as you might initially assume it to be. For example, a student is considered a junior if the student has completed between 61 and 90 credit hours.To upload and submit your assignment, click the Choose File button…
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
Similar questions
- Using the STUDENT table structure shown in Table P6.4, do the following:a. Write the relational schema and draw its dependency diagram. Identify all dependencies, including all transitive dependencies.b. Write the relational schema and draw the dependency diagram to meet the 3NF requirements to the greatest practical extent possible. If you believe that practical considerations dictate using a 2NF structure, explain why your decision to retain 2NF is appropriate. If necessary, add or modify attributes to create appropriate determinants and to adhere to the naming conventions.c. Using the results of Problem 4, draw the Crow's Foot ERD.Note: Although the completed student hours (STU_HOURS) do determine the student classification (STU_ClASS), this dependency is not as obvious as you might initially assume it to be. For example, a student is considered a junior if the student has completed between 61 and 90 credit hours.arrow_forwardUsing the STUDENT table structure shown in Table 1, do the following:a. Write the relational schema and draw its dependency diagram. Identify alldependencies, including all transitive dependencies. [5 marks]b. Write the relational schema and draw the dependency diagram to meet the3NF requirements to the greatest practical extent possible. If you believe thatpractical considerations dictate using a 2NF structure, explain why yourdecision to retain 2NF is appropriate. If necessary, add or modify attributesto create appropriate determinants and to adhere to the naming conventions.[10 marks]c. Using the results of Problem b, draw the Crow’s Foot ERD. [5 marks]TABLE 1: STUDENT INFOATTRIBUTENAMESAMPLEVALUESAMPLEVALUESAMPLEVALUESAMPLEVALUESAMPLEVALUESTU_NUM 211343 200128 199876 198648 223456STU_LNAME Stephanos Smith Jones Ortiz McKulskiSTU_MAJOR Accounting Accounting Marketing Marketing StatisticsDEPT_CODE ACCT ACCT MKTG MKTG MATHDEPT_NAME Accounting Accounting Marketing Marketing Mathematicsarrow_forward4-53. Figure 4-40 shows an EER diagram for a university dining service organization that provides dining services to a major university. 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.arrow_forward
- For each of the following relations, indicate the normal form for that relation. If the relation is not in third normal form, decompose it into 3NF relations. Functional dependencies are shown where appropriate. a) CLASS(CourseNo, SectionNo) b) CLASS(CourseNo, SectionNo, Room) c) CLASS(CourseNo, SectionNo, Room, Capacity) d) CLASS(CourseNo, SectionNo, CourseName, Room, Capacity)arrow_forwardUsing the INVOICE table structure shown in Table P6.3, do the following: a. Write the relational schema, draw its dependency diagram, and identify all dependencies, including all partial and transitive dependencies. You can assume that the table does not contain repeating groups and that an invoice number references more than one product. (Hint: This table uses a composite primary key.)b. Remove all partial dependencies, write the relational schema, and draw the new dependency diagrams. Identify the normal forms for each table structure you created.Note: You can assume that any given product is supplied by a single vendor, but a vendor can supply many products. Therefore, it is proper to conclude that the following dependency exists:PROD_NUM S PROD_lABEl, PROD_PRICE, vEND_CODE, vEND_NAME(Hint: Your actions should produce three dependency diagrams.)c. Remove all transitive dependencies, write the relational schema, and draw the new dependency diagrams. Also identify the normal forms for…arrow_forwardUsing the INVOICE table structure shown in table P6.3, do the following a. Write the relational schema, draw its dependency diagram, and identify all dependencies, including all partial and transitive dependencies. You can assume that the table does not contain repeating groups and that an invoice number references more than one product. (Hint: This table uses a composite primary key.) b.Remove all partial dependencies, write the relational schema, and draw the new dependency diagrams. Identify the normal forms for each table structure you created. Note You can assume that any given product is supplied by a single vendor, but a vendor can supply many products. Therefore, it is proper to conclude that the following dependency exists: PROD_NUM -+ PROD_LABEL, PROD_PRICE,VEND_CODE, VEND_NAME ( Hint Your actions should produce three dependency diagrams.) c. Remove all transitive dependencies, write the relational schema, and draw the new dependency diagrams. Also, identify the normal…arrow_forward
- Create ERD of a hospital with these requirements "and then" write relation schema (underline primary key and connect foreign key with arrow): (use Chen Notation) 1. Hospital have many department, each department have it's own ID, name, and head of department. 2. Hospital need to collect Patient data included : ID, Name, Sexuality, Age, Address, Health Insurance, Telnum 3, Patient may have data stored about people they can contact, should be more than 1 people they can contact 4. Doctor should have : DocID, Name, Sex, TelNum, Specialty, Years of service, Salary, Qualification 5. 1 doctor can be assigned to only 1 department 6. some doctor may not have their assigned patient 7. patient can be assigned to more than 1 doctor for treatment 8. each treatment shall be stored (date, detail, next appointment) Specification for the diagram: - describe what should be describe and what can be describe inside the diagram - also explain others that can be describe inside the diagram briefly (don't…arrow_forwarda. Provide the relation (relational notation) that represents the user view for the school advising system.b. Transform the user view into the third normal form. Show the intermediate steps in the normalization process, i.e. 1NF, 2NF, 3NF.arrow_forwardUsing the invoice table stucture do the following:a.Write the relational schema ,draw its dependency diagram,and identy all dependencies,including all partial and transitive dependencies.You can assume that the table does not contain repeating groups and that an invoice number references more than one product.b.Remove all partial dependencies,write the relational schema,and draw the new dependency diagrams.Identify the normal forms for each table structure you created.c.Remove all transitive dependencies,write the relational schema,and draw the new dependency diagrams.Also identify the normal forms for each table structure you created.d.Draw the Crow's Foot ERD.arrow_forward
- 1.Consider the relation schema given in below fig. Design and draw an ER diagram that capture the information of this schema.(Analyse)(Apr/May 2017)Employee(empno,name,office,age)Books(isbn,title,authors,publisher)Loan(empno,isbn,date)Write the following queries in relational algebra and SQL.Find the names of employees who have borrowed a book published by McGraw-Hill.Find the names of employees who have borrowed all books published by McGraw-Hill.arrow_forwarda) Consider the following EER diagram. Map it to the relational model. Specify the relational schema in DBDL notation. b) Convert the following relational schema to BCNF (FD1, FD2 and FD3 are functional dependencies. CourseOffering(coId, courseId, semesterId, year, campus, courseName) Primary key (coId) Alternate Key (courseId, semesterId, year, campus) FD1: coId courseId, semesterId,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
arrow_back_ios
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
A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr
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
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr