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.37PAE
Program Plan Intro
(a)
To draw:
A relational schema and functional dependencies of the given relation.
Program Plan Intro
(b)
To identify:
The normal form of the relation.
Program Plan Intro
(c)
To decompose: given relation into 3NF relations.
Program Plan Intro
(d)
To draw:
A relational schema for 3NF relations and show referential integrity.
Program Plan Intro
(e)
To draw:
The 3NF relations using Microsoft Visio notation.
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
What is the most common method of specifying a primary key in a relational schema description?
Group of choice:
A. by enclosing the key attributes in parentheses
B. by stating the key attributes first in the relation definition
C. by writing key attributes in all capital letters
D. by italicizing the key attributes
E. by underlining the attributes that comprise the primary key
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
Convert the preceding ER diagram into a relational DB schema. (I want you to do this yourself, not using automated tools. You can use ERDPlus to construct the relational schema yourself, or you can do it yourself on paper and take a picture.) Indicate all primary keys correctly. Be sure to include arrows for foreign key relationships. Also make sure that all attributes in a single relation have different names.
ER Diagram:
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
- 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_forwardWrite a relational mode for each of the case below. You can create our own attributes and provide common attributes to determine the link between tables. 1. Draw the Crow’s Foot solution of the business rules given below: A driver may receive many (driving) assignments. Each (driving) assignment is made for a single driver. A truck may be driven in many (driving) assignments. Each (driving) assignment is made for a single truck 2. The Jonesburgh County Basketball Conference (JCBC) is an amateur basketball association. Each city in the county has one team as its representative. Each team has a maximum of 12 players and a minimum of 9 players. Each team also has up to three coaches (offensive, defensive and physical training coaches). During the season, each team plays two games (home and visitor) against each other team. a. Draw the Crow’s Foot ERD to represent the JCBC database. b. Identify the connectivity and cardinality of each of the relationship. 3. Using the Crow’s Foot notation,…arrow_forwardŅ 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,000arrow_forward
- produce 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_forwardWrite a relational mode for each of the case below. You can create our own attributes and provide common attributes to determine the link between tables. Draw the Crow’s Foot solution of the business rules given below: A driver may receive many (driving) assignments. Each (driving) assignment is made for a single driver. A truck may be driven in many (driving) assignments. Each (driving) assignment is made for a single truckarrow_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_forward
- Map the ER diagram below to a set of relations (i.e., relational schema). Underline the primary key attributes of each relation. Indicate referential integrity constraints using arrows. An arrow from an attribute A to an attribute B represents that A referencesarrow_forwardIn SQL, foreign key constraints can reference only the primary key attributes ofthe referenced relation or other attributes declared to be a superkey using theunique constraint. As a result, total participation constraints on a many-to-manyrelationship set (or on the “one” side of a one-to-many relationship set) cannotbe enforced on the relations created from the relationship set, using primarykey, foreign key, and not null constraints on the relations. Explain whyarrow_forwardWhat are three advantages of using a surrogate key rather than the main key to access a relation instead?arrow_forward
- In SQL, foreign key constraints can reference only the primary key attributes ofthe referenced relation or other attributes declared to be a superkey using theunique constraint. As a result, total participation constraints on a many-to-manyrelationship set (or on the “one” side of a one-to-many relationship set) cannotbe enforced on the relations created from the relationship set, using primarykey, foreign key, and not null constraints on the relations. Explain how to enforce total participation constraints using complexcheck constraints or assertions (see Section 4.4.8). (Unfortunately, thesefeatures are not supported on any widely used database currently.)arrow_forwardTransform the diagram to a relational schema that shows referential integrity constraintsarrow_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_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 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