Pearson eText for Modern Database Management -- Instant Access (Pearson+)
13th Edition
ISBN: 9780137305940
Author: Jeffrey Hoffer, Ramesh Venkataraman
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.
1. The BOOK CHECKOUT relation stores data about books checked out by students at a university. The functional dependencies are
given below for this relation.
a. Which normal form is this relation in and why?
b. Using the normalization steps develop a set of 3NF relations for this view. Describe the relational schema for the 3NF relations that
you developed. Be sure to list the referential integrity constraints
BOOK CHECKOUT (StudentID, StudentName, StudentPhoneNumer, ISBN, BookTitie, CheckOutDate, ReturnDate)
StudentID > StudentName, StudentPhoneNumer
ISBN -> BookTitle
StudentiD, ISBN ->CheckOutDate, ReturnDate
Using the STUDENT table structure shown in Table 1, 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 b, draw the Crow's Foot ERD.
TABLE 1: STUDENT INFO
ATTRIBUTE
SAMPLE
SAMPLE
SAMPLE
SAMPLE
SAMPLE
NAME
VALUE
VALUE
VALUE
VALUE
VALUE
STU_NUM
211343
200128
199876
198648
223456
STU LNAME
Stephanos
Smith.
Jones
Ortiz
McKulski
STU_MAJOR
Accounting Accounting Marketing Marketing
Statistics
DEPT CODE
ACCT
АССT
MKTG
MKTG
ΜΑΤΗ
DEPT NAΜΕ
Accounting Accounting Marketing Marketing
Mathematics…
Chapter 4 Solutions
Pearson eText for Modern Database Management -- Instant Access (Pearson+)
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
- QUESTION 1 Create relational schemas for the ER diagram given below. Indicate clearly the primary and foreign keys of each relation. Туре Address NoPersons Area No No 1 M House Has Roomarrow_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_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
- Using 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_forwardI got stuck on step one through four. I was wondering if you guys know how. each step says: Write the relational schema for the data in its current form. Identify all partial dependencies and transitive dependencies for the data in its current form. Write the relational schema to meet 3NF requirements to the greatest extent possible. If necessary, add or modify attributes to create appropriate determinants, create appropriate foreign keys, and to adhere to naming conventions. Analyze the given data and identify the business rules reflected in the data.arrow_forwardTransform the diagram to a relational schema that shows referential integrity constraintsarrow_forward
- Mapping: Create relational schemas for the ER diagram given below. Indicate clearly the primary and foreign keys of each relation. 2 Book-Chapter Title M N Role Written by Reviewed By Name M Author Narrow_forwardUsing 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…arrow_forwardUsing 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_forward
- a) Referring to the company database, which relations have the same set of attributes for their default superkey and primary key? b) Referring to the company database, which relations have a reasonable candidate key that is not its primary key? C) Referring to the company database, specify an update operation to the department relation that will violate a primary key constraint constraint. D) Referring to the company database, what constraints to the database would be violated if Smith were deleted from the employee relation? Include the relevant relations and attributes in your answer.arrow_forwardPLZ help with the following: Translate your ER diagram into a relational schema in the form of SQL DDLs. Choose appropriate data types for each attribute and include primary key and foreign key constraints, Check and Not Null constraints. Normalize the relations to 3NF.arrow_forwardUsing the STUDENT table structure in picture, do the following: 1. 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 any invoice number may reference more than one product. (Hint: This table uses a composite primary key.) 2. Remove all partial dependencies, write the relational schema, and draw the new dependency diagrams. Identify the normal forms for each table structure you created. 3. 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. 4. Draw the Crow’s Foot ERD.arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher: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:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning