Database Systems: Design, Implementation, & Management
12th Edition
ISBN: 9781305627482
Author: Carlos Coronel, Steven Morris
Publisher: Cengage Learning
expand_more
expand_more
format_list_bulleted
Question
Chapter 3, Problem 15P
Program Plan Intro
Primary Key:
A primary key in a
Example:
- Students in Universities are assigned a unique registration number.
- Therefore, in a STUDENT database table, the attribute “reg_no” acts as primary key.
Foreign Key:
Foreign key is a column in a relational database table which provides a relation between two tables. It provides a cross reference between tables by pointing to primary key of another table.
Example:
- In STUDENT database table, the attribute “reg_no” acts as primary key and in COURSE database table in which the student selects his or her course, the same “reg_no” acts as foreign key for the STUDENT table.
Many to One relationship:
When more than one record in a database table is associated with only one record in another table, the relationship between the two tables is referred as many to one relationship. It is also represented as M: 1 relationship.
One to Many relationships:
When one record in a database table is associated with more than one record in another table, the relationship between the two tables is referred as one to many relationships. It is also represented as1: M relationship. This is the opposite of many to one relationship.
CROW FOOT ERD:
The Crow Foot ERD is an Entity Relationship Diagram which is used to represent the cardinalities present in the basic ER diagram. It is used to represent the relationships present between two tuples or tables present in the database.
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
Normalize the below given Student Course Plan to First Normal Form, Second NormalForm and Third Normal Form.
Customer
Equipment
Loan
id
equipmentCode
name
id
name
H description
RentalRatePerDay
address
04 equipment Code e
contact
startDate
dateOfBirth
returnDate
occupation
DamageReport
reportId
damageType
(iii)
Create a view named CustomerSummary with details of each customer, the
number of equipment rented, the total amount of rental collected thus far and
the number of damages caused by customer and not due to wear and tear.
ID
Name
Address
Contact
DateOfBirth Occupation
Number of Equipment Rented Total Rent NumDamage
ST82J
Charles Toles
23 Haig Road
92142331 15-03-1976 Teacher
20.00
S823F
Damien Low
5 Still Road
88128833 05-07-1985 Teacher
1
16.00
1
3
sa56F Audrey Ng
10 Bedok Road
82138213 23-01-1988 Nurse
3
150.00
1
2 Jalan Eunos
8 Jalan Melayu
Ali Bin Mohd Hassan 10 Clomonti Road 91239123
4
S345G
Betsey Tan
98981212 12-05-1990 Cartoonist
4.00
99234J
Peter Lee
97589758 04-03-1991 Student
1
24.00
SATSZ
S575J Ravi S/O Ramasamy 12 Jurong Road
6
10-07-1997 IT Programmer
0.00
7
82223333…
Assignment:
a) Is this table in 1NF? Why or why not?
b) Describe and illustrate the process
of normalizing the data shown in this table,
from first to third normal form (1NF-3NF).
c) Identify the primary, alternate, and foreign keys
in the 3NF relations.
StaffBranch Table
staffNo
name
position
level salary
$1500
Tom Daniels
Manager
3
46000 B001
S0003
Sally Adams
Assistant
1
30000 B001
S0010
Mary Martinez
Manager
5
S3250
Robert Chin
Supervisor
1
50000 B002
32000 B002
S2250
Sally Stern
Manager
4
48000 B004
S0415
Art Peters
Manager
2
41000 B003
503-555-3618
503-555-2727
206-555-8836
206-555-6756
206-555-4112
hours supervisor
45 S0010
36.5 $2250
38 S0415
28 S0010
212-371-3000
37 S2250
branchNo branchAddress
8 Jefferson Way, Portland, OR 97201
8 Jefferson Way, Portland, OR 97201
City Center Plaza, Seattle, OR 98122
City Center Plaza, Seattle, OR 98122
16-14th Avenue, Seattle, OR 98128
14-18th Avenue, New York, NY 10012
telNo
Chapter 3 Solutions
Database Systems: Design, Implementation, & Management
Ch. 3 - What is the difference between a database and a...Ch. 3 - Prob. 2RQCh. 3 - Prob. 3RQCh. 3 - What are the requirements that two relations must...Ch. 3 - Which relational algebra operators can be applied...Ch. 3 - Explain why the data dictionary is sometimes...Ch. 3 - A database user manually notes that The file...Ch. 3 - Prob. 8RQCh. 3 - Prob. 9RQCh. 3 - Prob. 10RQ
Ch. 3 - Prob. 11RQCh. 3 - Prob. 12RQCh. 3 - Use Figure Q3.13 to answer Questions 1317. FIGURE...Ch. 3 - Create the table that results from applying a...Ch. 3 - Write the relational algebra formula to apply an...Ch. 3 - Create the table that results from applying an...Ch. 3 - Using the tables in Figure Q3.13, create the table...Ch. 3 - Prob. 18RQCh. 3 - Prob. 19RQCh. 3 - Prob. 20RQCh. 3 - Identify and describe the components of the table...Ch. 3 - Identify the primary keys. FIGURE Q3.22 THE...Ch. 3 - Identify the foreign keys. FIGURE Q3.22 THE...Ch. 3 - Create the ERM. FIGURE Q3.22 THE CH03_THEATER...Ch. 3 - Create the relational diagram to show the...Ch. 3 - Prob. 26RQCh. 3 - What would be the conceptual view of the INDEX...Ch. 3 - Prob. 1PCh. 3 - Prob. 2PCh. 3 - Do the tables exhibit referential integrity?...Ch. 3 - Describe the type(s) of relationship(s) between...Ch. 3 - Prob. 5PCh. 3 - Prob. 6PCh. 3 - Prob. 7PCh. 3 - Prob. 8PCh. 3 - Create the relational diagram to show the...Ch. 3 - Prob. 10PCh. 3 - Prob. 11PCh. 3 - Create the relational diagram to show the...Ch. 3 - Prob. 13PCh. 3 - Do the tables exhibit referential integrity?...Ch. 3 - Prob. 15PCh. 3 - Prob. 16PCh. 3 - For each table, identify the primary key and the...Ch. 3 - Prob. 18PCh. 3 - Do the tables exhibit referential integrity?...Ch. 3 - Identify the TRUCK tables candidate key(s). FIGURE...Ch. 3 - For each table, identify a superkey and a...Ch. 3 - Prob. 22PCh. 3 - Prob. 23PCh. 3 - Prob. 24PCh. 3 - Create the ERD. (Hint: Look at the table contents....Ch. 3 - Prob. 26PCh. 3 - Prob. 27PCh. 3 - Prob. 28PCh. 3 - Create the table that would result from applying...Ch. 3 - Create the table that would result from applying...Ch. 3 - Create the table that would result from applying a...
Knowledge Booster
Similar questions
- Modify the CHARTER table to add the attributes shown in the following table. TABLE P8.37 ATTRIBUTES AND VALUES FOR PROBLEM 37 ATTRIBUTE NAME ATTRIBUTE DESCRIPTION ATTRIBUTE TYPE CHAR_WAIT_CHG Waiting charge for each model (copied from the MODEL table) Numeric CHAR_FLT_CHG_HR Flight charge per mile for each model (copied from the MODEL table using the MOD_CHG_MILE attribute) Numeric CHAR_FLT_CHG Flight charge (calculated by CHAR_HOURS_FLOWN CHAR_FLT_CHG_HR) Numeric CHAR_TAX_CHG CHAR_FLT_CHG tax rate (8%) Numeric CHAR_TOT_CHG CHAR_FLT_CHG + CHAR_TAX_CHG Numeric CHAR_PYMT Amount paid by customer Numeric CHAR_BALANCE Balance remaining after payment Numericarrow_forwardCreate a table named REP_CONTRACTS containing the columns listed in the following chart A composite PRIMARY KEY constraint including the Rep_ID, Store_ID, and Quarter columns should be assigned. In addition, FOREIGN KEY constraints should be assigned to both the Rep_ID and Store_ID columns.arrow_forwardSelect the best answer from the given choices: Using the company schema, when creating the "works_on" table, the primary key should be specified as All of the answers are correct primary key (essn, pno) primary key (essn), primary key(pno) primary key (essn) and primary key(pno)arrow_forward
- Based on the ERD below, display all columns in the JOB table for those job descriptions that have both analysts and engineers within the description name. (Hint: You will use a “Like” phrase.)arrow_forwardplease use the picture to answer the following question Normalize below user view to the third normal form. Show all steps (UNF, 1NF, Dependencies, 2NF, 3NF).arrow_forwardList the name of employee who is working on a project with the budget that is below this project's divisional average project budget (use correlated subquery).arrow_forward
- Select all managers whose firstname starts with letter M and department they lead have less than 10 employees. Present manager and employee count in the result set. Order result set by employee count in descending order.arrow_forwardNormalize the following form into UNF 1NF, 2NF and 3NF. Show primary keys and foreign keys for all the normalized tables (from 3NF). Employee Task Record Form EmployeelD : 587 Employee Name: Smith Department ID: D1 DeparmentName : IT Task ID Task Name Duration Supervisor ID | Supervisor Name Emp_TaskJoin_Date Designing Drawing Reporting T1 70hrs S1 Khalfan 12/12/2019 T2 56hrs S2 Fatma 01/10/2020 T3 60hrs S3 Haitham 11/11/2020arrow_forwardPlease design a Task durations and a Dependencies table, and also calculate the Task durations and the Dependencies using the image below:arrow_forward
- All entity names, relationship names, and attribute definitions must be correct.arrow_forwardPlease answer my questionsarrow_forwardGarden Glory is a partnership that provides gardening and yard maintenance services to individuals and organizations. Garden Glory is owned by two partners. They employ two office administrators and a number of full- and part-time gardeners. Garden Glory will provide one-time garden services, but it specializes in ongoing service and maintenance. Many of its customers have multiple buildings, apartments, and rental houses that require gardening and lawn maintenance services. Create a sample list of owners and properties. Your list will be similar in structure to that in Figure 1-34 g, but it will concern owners and properties rather than owners and pets. Your list should include, at a minimum, owner name, phone, and billing address, as well as property name, type, and address. B. Describe modification problems that are likely to occur if Garden Glory attempts to maintain the list in a spreadsheet. C. Split the list into tables such that each has only one theme. Create appropriate…arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
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
Np Ms Office 365/Excel 2016 I NtermedComputer ScienceISBN:9781337508841Author:CareyPublisher:Cengage
A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology PtrCOMPREHENSIVE MICROSOFT OFFICE 365 EXCEComputer ScienceISBN:9780357392676Author:FREUND, StevenPublisher:CENGAGE L
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
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
COMPREHENSIVE MICROSOFT OFFICE 365 EXCE
Computer Science
ISBN:9780357392676
Author:FREUND, Steven
Publisher:CENGAGE L