Modern Database Management
13th Edition
ISBN: 9780134792293
Author: Hoffer
Publisher: PEARSON
expand_more
expand_more
format_list_bulleted
Concept explainers
Question
Chapter 4, Problem 4.42PAE
Program Plan Intro
Dependency diagram of the given data.
Program Plan Intro
Decomposing the given relation to third normal form
Program Plan Intro
ER diagram for the given set of relation from above.
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solution
Students have asked these similar questions
Please draw an Entity-Relationship diagram for the given below MOVIE database.
The data requirements are summarized as follows:
Each movie is identified by title and year of release. Each movie has a length in minutes. Each has a production company, and each is classified under one or more genres (such as horror, action, drama, and so forth). Each movie has one or more directors and one or more actors appear in it. Each movie also has a plot outline. Finally, each movie has zero or more quotable quotes, each of which is spoken by a particular actor appearing in the movie.
Actors are identified by name and date of birth and appear in one or more movies. Each actor has a role in the movie.
Directors are also identified by name and date of birth and direct one or more movies. It is possible for a director to act in a movie (including one that he or she may also direct).
Production companies are identified by name and each has an address. A production company produces one or more movies.
Regional Labs is a company that conducts research and development work on a contract basis
for other companies and organizations. Figure 2-33 shows data that Regional Labs collects
about projects and the employees assigned to them.
This data is stored in a relation (table) named PROJECT:
PROJECT (ProjectID, Employee Name, EmployeeSalary)
ProjectID
100-A
100-A
FIGURE 2-33
Sample Data for
Regional Labs
100-B
200-A
200-B
200-C
200-C
200-D
EmployeeName
Eric Jones
Donna Smith
Donna Smith
Eric Jones
Eric Jones
Eric Parks
Donna Smith
Eric Parks
Employee Salary
64,000.00
70,000.00
70,000.00
64,000.00
64,000.00
58,000.00
70,000.00
58,000.00
Assignment Brief:
Some of the most difficult decisions that you face as a database developer are what tables to
create and what columns to place in each table, as well as how to relate the tables that you
create. Normalization is the process of applying a series of rules to ensure that your database
achieves optimal structure. Normal forms are a progression of these rules. Each successive
normal form achieves a better database design than the previous form did. Based on your
understanding for the various normal form respond to the following problems and provide the
optimal answer/solution.
Problem#1: 1st Normal Form (1NF)
Consider the Students table, with the primary key underlined, and the following data:
Students:
Alpha
100111
Email
doe@usna.edu
Name
Courses
GradePoints
NN204, SI204,
IT221
SM223, EE301
SI204
John Doe
2,3,3
Matt Smith
092244
113221
smith@usna.edu
black@usna.edu
4,4
Melinda
3
Black
NN204, SI204,
IT221
090112
Tom Johnson
Johnson@usna.ed
4,2,3
u
a) Is the Students table…
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
- Need help in ER diagramarrow_forwardRegional Labs is a company that conducts research and development work on a contract basis for other companies and organizations. Figure 2-31 shows data that Regional Labs collects about projects and the employees assigned to them. This data is stored in a relation (table) named PROJECT: PROJECT (ProjectID, EmployeeName, EmployeeSalary) Assuming that all functional dependencies are apparent in this data, which of the following are true? ProjectID → EmployeeName ProjectID → EmployeeSalary (ProjectID, EmployeeName) → EmployeeSalary EmployeeName → EmployeeSalary EmployeeSalary → ProjectID EmployeeSalary → (ProjectID, EmployeeNamearrow_forwardRequirement analysis: Course evaluation must have a student, the students relation contains a unique student id, full name for each student, email address, phone number, address and date of birth. For each course, the courses contain course id, course name and credit. Each student can take many courses. For each Instructor, the instructors contain id, name and email. Each instructor can instruct many courses. For each section, the sections contain section id, year and semester. Students are able to evaluate the current courses. ER-diagram: Please complete the ER diagram using Microsoft word. Instructor ID Full name Email Student ID Full name Email Phone number Address Date of birth Course ID Course title Description Credit Section ID Year Semester Please Complete the diagram on MS word.arrow_forward
- Tasks: Considering the following E-R diagram, you need to translate it into a relational database and add some data to the tables. a) Write SQL statements to create the corresponding relations and capture as many of the participation and cardinality constraints as possible. Make sure that the data type and size for each attribute are appropriate. b) If some constraints in the diagram cannot be captured, explain why. c) Write SQL statements to insert at least two tuples for each table task name number ssn phone administrators O deptName dob Employees Caddresses salary staff's hourlySalary number M name Places N Orders M Contains N Items description P status number ID expShipDate qty deliveryDate name qtyInStock CunitCost actualShipDate salePrice email Street Customers city Addres statearrow_forwardQUESTION 2 Hillar Furniture Enterprise is a furniture store owned by Dr. Hillar Addo of AIT. The materials manager of this company maintains a list of suppliers for each of the materials items purchased by the company from outside vendors. The following table shows the essential data required for this application. Hillar Furniture Company Purchasing Data is shown below: Attribute Name Material_ID Material_ Name Unit_of_Measure Standard_Cost Vendor_ID Vendor_name Unit_price Terms_Code Terms Sample Value 3792 Hinges 3” locking Each GHC 5.00 V300 Apex Hardware GHC 4.75 1 COD a) Using the dependency diagram, identify the functional dependencies for this data. You may assume the following: Each material has one or more suppliers. Each supplier may have supply one or more items or may not supply many items. The unit price for a material may vary from one vendor to another The terms of a code for a supplier uniquely identifies the terms of a sale (e.g. code 2…arrow_forwardcreate an ER tablearrow_forward
- Task 1: ER diagram for give scenario Consider building a database for a shopping mall with requirements as follows: Each product of the shopping mall has a unique project No, and also has a name, a supplier name, a category, a purchase price and a selling price. Products need to be supplied from suppliers. Each product has one supplier, but a supplier may supply many products for the shopping mall. Each supplier has a supplier name and address. A purchase order for product from a supplier has a unique order No, and a date. The order specifies one or more products and their quantities required for a product. A sales order for product to customers has a unique order No, and a date. The order specifies one or more products and their quantities required for a product The shopping mall has warehouses. Each warehouse has a warehouse number, an address and a contact phone number. Each warehouse has shelves to store products. Each shelf has a shelf-number and a capacity. The shelf…arrow_forwardData Warehouse Fact Table: The Pine Valley Furniture Company is designing the fact table of a data warehouse to store its growing amount of transaction and product data. The total number of products sold by the company is 138. The company has a total of 98 retail stores. The company considers capturing monthly data over a period of 3 years. PVFC plans to use the data warehouse to track the sales of each product per store over the 3-year period. What is the estimated number of records to be stored in the fact table in PVFC's data warehouse? Enter your answer as a numeric integer in the nearby box.arrow_forwardRegional Labs is a company that conducts research and development work on a contract basis for other companies and organizations. Figure 2-31 shows data that Regional Labs collects about projects and the employees assigned to them. This data is stored in a relation (table) named PROJECTarrow_forward
- P2arrow_forwardIndicate the changes (using the shorthand representation) that you would need to make to the original KimTay Pet Supplies database design to support the following requirements. There is no relationship between customers and sales reps. When a customer places an order, any sales rep can process the order and create the invoice. On the invoice, you need to identify both the customer placing the order and the sales rep responsible for the invoice. Draw an E-R diagram for the new design.arrow_forwardIndicate the changes (using the shorthand representation) that you would need to make to the original KimTay Pet Supplies database design to support the following requirements. There is no relationship between customers and sales reps. When a customer places an order, any sales rep can process the order and create the invoice. On the invoice, you need to identify both the customer placing the order and the sales rep responsible for the invoice. Draw an E-R diagram for the new design.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
Np Ms Office 365/Excel 2016 I NtermedComputer ScienceISBN:9781337508841Author:CareyPublisher:Cengage
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 SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
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