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
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
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'.
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
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
- 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: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_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_forward
- Alert dont submit AI genertaed answer.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_forward(a) What are the significant functional dependencies in this relation? (b) What is/are the candidate key(s) of this relation? (c) is this relation in 1NF, 2NF, 3NF, BCNF? And why?arrow_forward
- PLZ 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_forwardTransform the diagram to a relational schema that shows referential integrity constraints (see the following figure for an example of such a schema – underlined items are primary keys, dotted underlines refer to foreign keys, while the arrows indicate direction from foreign to primary)arrow_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
- 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 whyarrow_forwardWhat are three advantages of using a surrogate key rather than the main key to access a relation instead?arrow_forwardTransform the diagram to a relational schema that shows referential integrity constraintsarrow_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