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.36PAE
Program Plan Intro
(a)
To draw a relational schema and functional dependencies of the given relation.
Program Plan Intro
(b)
To identify in which normal form the relation is.
Program Plan Intro
(c)
To decompose GRADE REPORT 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
Want to see the full answer?
Check out a sample textbook 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
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'.
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.
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
- 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.arrow_forward1. 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, ReturnDatearrow_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_forward
- What are three advantages of using a surrogate key rather than the main key to access a relation instead?arrow_forwardInstructions: Create a complete ERD in Crow's Foot notation that can be implemented in the relational model using the descriptions below. The attributes required: Activity's ID, Activity's Title, Activity's description, Child's ID, Child's firstname and lastname, Child's Birthday, Parent's ID, Parent's number, Parent's address. The business rules are: An activity may or may not have a child signed up An activity may have many children signed up A child must have an activity A child may have many activities A child must have a parent A parent must have a child A child may have many parents A parent may have many children To submit: Write your answer in a clean sheet of paper and upload a picture of it to dropboxarrow_forwardCreate an Entity-Relationship Model for the Queen Anne Curiosity Shop The first assignment for the class project is to create an entity-relationship model of the Queen Anne Curiosity Shop database. This assignment requires you to apply the knowledge gained from study of chapter 4 of the textbook. You will create the entity-relationship model using the ERDPlus database modeling tool. The Queen Anne Curiosity Shop database contains the following tables: CUSTOMER (CustomerID, LastName, FirstName, Address, City, State, ZIP, Phone, EmailAddress) EMPLOYEE (EmployeeID, LastName, FirstName, Phone, EmailAddress) VENDOR (VendorID, CompanyName, ContactLastName, ContactFirstName, Address, City, State, ZIP, Phone, Fax, EmailAddress) ITEM (ItemID, ItemDescription, PurchaseDate, ItemCost, ItemPrice, VendorID) SALE (SaleID, CustomerID, EmployeeID, SaleDate, SubTotal, Tax, Total) SALE_ITEM (SaleID, SaleItemID, ItemID, ItemPrice) Note that the above table descriptions follow the textbook convention:…arrow_forward
- What exactly is a Ternary Relationship, and how does it function?arrow_forwardConvert 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_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 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_forward
- Develop an Entity Relationships Diagram (ERD) based on the relational schemas in the 3NF for the starting relational schema in un-normalized form presented below. UNF:Order(OrderID, OrderDate, CustID, CustName, CustPhone, CCNum, CCExpDate, CCBank, BnkContName, BnkContPhone, CustEmail, OrderIP, SiteRefFrom, ShipStreet, ShipCity, ShipSt, ShipZip, OrderLineNum, ItemID, ItemName, ItemDesc, ItemQtyOrdered,ItemListPrice, ItemSalePrice, ItemQtyShip, ShipCharge, Tax, TotalDue)arrow_forwardYou are working with a database that stores information about suppliers, parts and projects. The Supply relation records instances of a Supplier supplying a Part for a Project.The schema for the database used in this question is as follows: (note that primary keys are shown underlined, foreign keys in bold).SUPPLIER (SNo, SupplierName, City)PART (PNo, PartName, Weight)PROJECT (JobNo, JobName, StartYear, Country)SUPPLY (SNo, PNo, JobNo, Quantity)Provide relational algebra (NOT SQL) queries to find the following information. Each question is worth 2 marks.NOTE:• You can use the symbols etc or the words ‘PROJECT’, ‘RESTRICT’ etc as you prefer. • You do not need to try to make efficient queries – just correct ones. • Where you use a join, always show the join condition.a. List the quantity of parts supplied on JobNo 4745.b. List the weight of the Part named “Left-handed screwdriver”.c. List the Project name and Part name of any Parts where fewer than 500 of the part has been supplied to a…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
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