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
Concept explainers
Question
Chapter 4, Problem 4.34PAE
Program Plan Intro
(a)
Conversion of the given table in 1NF.
Program Plan Intro
(b)
List of the functional dependencies in the PART SUPPLIER and fund the candidate key.
Program Plan Intro
(c)
Identification of insert anomaly, delete anomaly and modification anomaly for the relation PART SUPPLIER.
Program Plan Intro
(d)
Drawing of a relational schema for PART SUPPLIER with functional dependencies.
Program Plan Intro
(e)
To identify:
A normal form in the relation.
Program Plan Intro
(f)
Development of 3 NF relations from PART SUPPLIER.
Program Plan Intro
(g)
To show: The 3 NF relations using Microsoft Visio.
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solution
Students have asked these similar questions
Consider two relations Order and Product with the schema Order (Oid, Odate, Pid, qty) and
Product (Pid, manufacturer_id, unit_price).
i) Can the Oid attribute in the relation Order be a candidate key of the relation? Justify
your answer.
ii) Can the Pid attribute in the relation Product be a candidate key of the relation? Justify your answer.
iii) Find the candidate key for the relation Product.
iv) Using the primary keys identified for the above relations write a SQL query that retrieves all tuples of the form (Oid, Odate, Pid, unit_price).
Question 6.
Consider the following relation:
Invoice(Order_ID, Order_Date, Customer_ID, Customer_Name,Customer_Address,Product_ID, Product_Desc, Price, Order_Quantity)
FD1 - Order_ID -> Order_Date, Customer_ID, Customer_Name, Customer_Address
FD2 - Customer_ID -> Customer_Name, Customer_Address
FD3 - Product_ID -> Product_Description, Price
FD4 - Order_ID, Product_ID -> Order_Quantity
What are the candidate keys of this relation?
Give reasons to explain if this is in 1NF or not. If not, decompose it to 1NF.
Give reasons to explain if this is in 2NF or not. If not, decompose it to 2NF.
Give reasons to explain if this is in 3NF or not. If not, decompose it to 3NF.
Use 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.
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
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
- Transform the given ER model into a set of 3NF relations. Use the format shown below for relations. FACULTY (FacID, LastName, FirstName, Rank, DeptID) FK DeptID à DEPT DEPT (DeptID, DeptName, DeptPhone) You must underline all components of each primary key. Denote each foreign key directly beneath the relation that contains the foreign key, as shown above. Do not use the graphical notation from the textbook. The notation shown here is the notation you will be required to use on exams. Be sure that all relations are in 3NF. ******HOW DO WE DRAW THE MULTI VALUED ATTRIBUTEarrow_forwardTransform the given ER model into a set of 3NF relations. Use the format shown below for relations. FACULTY (FacID, LastName, FirstName, Rank, DeptID) FK DeptID à DEPT DEPT (DeptID, DeptName, DeptPhone) You must underline all components of each primary key. Denote each foreign key directly beneath the relation that contains the foreign key, as shown above. Do not use the graphical notation from the textbook. The notation shown here is the notation you will be required to use on exams. Be sure that all relations are in 3NF.arrow_forwardAnswer the following questions based on the functional dependencies for a relation as provided below: Aircraft(AC_Num, AC_Model, AC_Seats, AC_Rent_Charge, Model_Code, Model_Manufacturer, Model_Seates, Model_RentCharge) Primary key: AC_Num Functional dependencies: AC_Num->AC_Model, AC_Seats, AC_Rent_Charge, Model_Code, Model_Manufacturer, Model_Seates, Model_RentCharge Model_Code-> Model_Manufacturer, Model_Seates, Model_RentCharge questions: Does this relation contain a ttransitive dependency? if so what it is? Create new relations to remove transitive dependency. Make sure to include primary and foreign keys for all relations.arrow_forward
- Write the Relation Model, using SQL, for the gig Relationship Set shown in this ER Model: The Band and Venue Entity Sets both have the conflicting name as their Primary Keys. Only the gig relation needs to be defined for this question.arrow_forwardPlease draw an relational ERD with multiplicities and all the attributes based on following description: ( Please identify the primary key in each entity.) An Adult Education Department runs various courses during the daytime and evenings, and at different times of the year. For example, ‘Spanish level 1’ is offered on Monday mornings, Monday evenings or Wednesday evenings, and runs over 25 weeks from October to March. On the other hand, ‘Introduction to Digging Up Your Ancestors’ only runs for 8 weeks, but is offered on Tuesday or Wednesday evenings from October to December, January to March, and April to June, with an optional field week in August. There is always a maximum number of places for each course offering, which is dependent on the individual teacher. For example, ‘Spanish level 1’ on Monday evenings may be limited to 20 places, but on Wednesday evenings the limit may be 25. Each course offering is only taken by one teacher, however teacher may take…arrow_forward1.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_forward
- Please answer this DBMS ques asap! Suppose that we have three relations R1={A, B, C}, R2={A, B, D} and R3={B, C, D} under the relational data model. The relation R1 has 5 tuples, the relation R2 has 6 tuples and the relation R3 has 7 tuples. There are no NULL values in these relations. For each of the following relational algebra expressions, list the minimum and maximum numbers of tuples in the result that the relational algebra expression may have. (1) πA,B(R1)(2) R2 × πB(R3)(3) πB(R2) ∪ πB(R3)(4) R1 ⋈ R2(5) ρX (R2) ⋈X.B≠Y.B ρY (R2) You can use the following format to organise your answers.(1) max=____ , min=____ ;(2) max=____ , min=____ ;(3) max=____ , min=____ ;(4) max=____ , min=____ ;(5) max=____ , min=____ ;arrow_forwardGiven the following relation vendor order (vendor no, order no, vendor name, qty supplied , price/unit) the second normal form relations arearrow_forwardConsider the following schema ,write the relational algebra queries for the following: Supplier(sid,name,address) Parts(pid,name,color) Catalog (sid,pid,cost) i)Find sid of supplier who supplies every part) ii)find the name of suppliers who supplies only red parts. iii) find the ID’s of suppliers who supplies only red parts. iv) find the sid of suppliers who supplies only red parts or are at 221 park street.arrow_forward
- For each table, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None in the space provided. Do the tables exhibit entity integrity and referential integrity? Answer yes or no and then explain your answer. In the case of Referential Integrity write NA (Not Applicable) if the table does not have a foreign key. iii. Describe the type(s) of relationship(s) between STORE and REGION Create the ERD to show the relationship between STORE and REGION.6 Describe the type(s) of relationship(s) between EMPLOYEE and STORE. (Hint: Each store employs many employees, one of whom manages the store.) Draw the ERD to show the relationships among EMPLOYEE, STORE and REGION.arrow_forwardproduce a list of relations indicating the primary key, columns and foreign keys. Consider the following attributes for tables in a relational model designed to keep track of information for a moving company that moves residential customers, usually from one home or apartment to another:customerID, customerName, customerCurrentAddress, customerCurrentPhone, customerNewAddress, customerNewPhone, pickupLocation, dropOffLocation, dateOfMove, startingTime, estimatedWeight, estimatedCost, truck#Assigned, driverName, driverLicNo, actualCost, amountOfDamages, truckCapacity, costOfTolls, tax, finalAmount, invoiceNumber, amountPaid, datePaid, checkNumber, amountDueAssume:- Although in most cases the pickupLocation is the customer’s old address and the dropOffLocation is the new address, there are exceptions, such as when furniture is moved to or from storage.-An estimate is provided before the move using a pre-printed invoice containing a unique invoice number. The actual cost is recorded on…arrow_forwardConvert the diagram into a set of relational tables. The tables must be in 3NF. All referential integrity and functional dependencies must be indicated accurately in EITHER of the following 2 ways: Draw lines for referential integrity and for functional dependencies as belows. 1. Show referential integrity as follows: Supplier(S#, Sname, City, Status); Part(P#, Pname, Color, Weight, City); SP(S#, P#, QTY) Foreign key (S#) References Supplier, Foreign key (P#) References Part; 2. Show functional dependencies as follows: B → C CD → Earrow_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
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