Database Systems: Design, Implementation, & Management
Database Systems: Design, Implementation, & Management
12th Edition
ISBN: 9781305627482
Author: Carlos Coronel, Steven Morris
Publisher: Cengage Learning
bartleby

Concept explainers

Question
Book Icon
Chapter 3, Problem 27P
Program Plan Intro

Synonyms:

When more than one name is assigned to same attribute, the attribute names are referred as “synonyms’. It exists when the same attribute has more than one name.

Example:

Suppose in table STUDENT, one of the attribute names is “STU_NUM” which displays the student registration number. Also, another attribute is “STU_ID” which also displays the student registration number. Then the attribute names are named as “Synonyms”.

Primary Key:

A Primary Key in a database table is a field in the table that uniquely identifies every row or record present in the database table.

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 Relationship:

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 relationship. It is also represented as1: M relationship. This is the opposite of many to one relationship.

One to One Relationship:

When one record in a database table is associated with one record in another table, the relationship between the two tables is referred as one to one relationship. It is also represented as1: 1relationship.

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
Check Mark

Explanation of Solution

Given database tables:

Table Name: CHARTER

CHAR_TRIP CHAR_DATE CHAR_PILOT CHAR_COPILOT AC_NUMBER CHAR_DESTINATION CHAR_DISTANCE CHAR_HOURS_FLOWN CHA_HOURS_WAIT CHAR_FUEL_GALLONS CHAR_OIL_QTS CUS_CODE
10001 05-Feb-18 104 2289L ATL 936.0 5.1 2.2 354.1 1 10011
10002 05-Feb-18 101 2778V BNA 320.0 1.6 0.0 72.6 0 10016
10003 05-Feb-18 105 109 4278Y GNV 1574.0 7.8 0.0 339.8 2 10014
10004 06-Feb-18 106 1484P STL 472.0 2.9 4.9 97.2 1 10019
10005 06-Feb-18 101 2289L ATL 1023.0 5.7 3.5 397.7 2 10011
10006 06-Feb-18 109 4278Y STL 472.0 2.6 5.2 117.1 0 10017
10007 06-Feb-18 104 105 2778V GNV 1574.0 7.9 0.0 348.4 2 10012
10008 07-Feb-18 106 1484P TYS 644.0 4.1 0.0 140.6 1 10014
10009 07-Feb-18 105 2289L GNV 1574.0 6.6 23.4 459.9 0 10017
10010 07-Feb-18 109 4278Y ATL 998.0 6.2 3.2 279.7 0 10016
10011 07-Feb-18 101 104 1484P BNA 352.0 1.9 5.3 66.4 1 10012
10012 08-Feb-18 101 2289L MOB 884.0 4.8 4.2 215.1 0 10010
10013 08-Feb-18 105 4278Y TYS 644.0 3.9 4.5 174.3 1 10011
10014 09-Feb-18 106 4278V ATL 936.0 6.1 2.1 302.6 0 10017
10015 09-Feb-18 104 101 2289L GNV 1645.0 6.7 0.0 459.5 2 10016
10016 09-Feb-18 109 105 2778V MQY 312.0 1.5 0.0 67.2 0 10011
10017 10-Feb-18 101 1484P STL 508.0 3.1 0.0 105.5 0 10014
10018 10-Feb-18 105 104 4278Y TYS 644.0 3.8 4.5 167.4 0 10017

Table Name: AIRCRAFT

AC_NUMBER MODE-CODE AC_TTAF AC_TTEL AC_TTER
1484P PA23-250 1833.1 1833.1 101.8
2289L C-90A 4243.8 768.9 1123.4
2778V PA31-350 7992.9 1513.1 789.5
4278Y PA31-350 2147.3 622.1 243.2

Table Name: MODEL

MOD_CODE MOD_MANUFACTER MOD_NAME MOD_SEATS MOD_CHG_MILE
B200 Beechcraft Super KingAir 10 1.93
C-90A Beechcraft KingAir 8 2.67
PA23-250 Piper Aztec 6 1.93
PA31-350 Piper Navajao Chiettan 10 2.35

Table Name: PILOT

EMP_NUM PIL_LICENSE PIL_RATINGS PIL_MED_TYPE PIL_MED_DATE PIL_PTI35_DATE
101 ATP ATP/SEL/MEL/Instr/CFII 1 20-Jan-18 11-Jan-18
104 ATP ATP/SEL/MEL/Instr 1 18-Dec-17 17-Jan-18
105 COM COMM/SEL/MEL/Instr/CFI 2 05-Jan-18 02-Jan-18
106 COM COMM/SEL/MEL/Instr 2 10-Dec-17 02-Feb-18
109 COM ATP/SEL/MEL/SES/Instr/CFII 1 22-Jan-18 15-Jan-18

Table Name: EMPLOYEE

EMP_NUM EMP_TITLE EMP-LNAME EMP_FNAME EMP_INITIAL EMP_CODE EMP_HIRE_DATE
100 Mr. Kolrnycz George D 15-Jun-62 15-Mar-08
101 Ms. Lewis Rhonda G 19-Mar-85 25-Apr-06
102 Mr. Vandam Rhett   14-Nov-78 18-May-13
103 Ms. Jones Anne M 11-May-94 26-Jul-17
104 Mr. Lange John P 12-Jul-91 20-Aug-10
105 Mr. Williams Robert D 14-Mar-95 19-Jun-17
106 Mrs. Duzak Jeanine K 12-Feb-88 13-Mar-18
107 Mr. Deante George D 01-May-95 02-Jul-16
108 Mr. Wiesanbach Paul R 14-Feb-86 03-Jun-13
109 Ms. Travis Elizabeth K 18-Jun-81 14-Feb-16
110 Mrs. Genkazi Lieghla W 19-May-90 29-Jun-10

Table Name: EMPLOYEE

CUS_CODE CUS_LNAME CUS_FNAME CUS_INITIAL CUS_AREACODE CUS_PHONE CUS_BALANCE
10010 Ramas Alfred A 615 844-2573 0.00
10011 Dunne Leona K 713 894-1293 0.00
10012 Smith Kathy W 615 894-2285 896.54
10013 Owolski Paul F 615 894-2180 1285.19
10014 Orlando Myron 615 222-1672 673.21
10015 OBrian Amy B 713 442-3381 1014.86
10016 Brown James G 615 297-1228 0.00
10017 Williams George 615 290-2556 0.00
10018 Fariss Anne G 713 382-7185 0.00
10019 Smith Olette K 615 297-3809 453.98

PRIMARY KEY in the above tables:

For Table Name: CHARTER:

Primary Key: CHAR_TRIP

“CHAR_TRIP” acts as primary key of the table because the attribute “CHAR_TRIP” is a unique ID that is assigned to every individual trip by the charter plane. It also uniquely identifies every other row present in the database table.

For Table Name: AIRCRAFT:

Primary Key: AC_NUMBER

“AC_NUMBER” acts as primary key of the table because the attribute “AC_NUMBER” is a unique number that is assigned to every individual charter plane and is used to distinguish among them. It also uniquely identifies every other row present in the database table.

For Table Name: MODEL:

Primary Key: MOD_CODE

“MOD_CODE” acts as primary key of the table because the attribute “MOC_CODE” is a unique number that is assigned to every individual model of the charter plane and is used to distinguish models among them. It also uniquely identifies every other row present in the database table.

For Table Name: PILOT:

Primary Key: EMP_NUM

“EMP_NUM” acts as primary key of the table because the attribute “EMP_NUM” is a unique number that is assigned to every pilot that flies an aircraft. It also uniquely identifies every other row present in the database table.

For Table Name: EMPLOYEE:

Primary Key: EMP_NUM

“EMP_NUM” acts as primary key of the table because the attribute “EMP_NUM” is a unique number or ID that is assigned to every employee that works in the airline. It also uniquely identifies every other row present in the database table.

For Table Name: CUSTOMER:

Primary Key: CUS_CODE

“CUS_CODE” acts as primary key of the table because the attribute “CUS_CODE” is a unique code that is assigned to every customer that books a flight with the airline. It also uniquely identifies every other row present in the database table.

FOREIGN KEY in the above tables:

For Table Name: CHARTER:

Primary Key: CHAR_PILOT,CHAR_COPILOT,AC_NUMBER,CUS_CODE

“CHAR_PILOT” acts as foreign key of the table because the attribute “CHAR_PILOT” is also present in the table PILOT and it references PILOT and hence it forms a link between the two tables.

“CHAR_COPILOT” acts as foreign key of the table because the attribute “CHAR_COPILOT” is also present in the table PILOT and it references PILOT and hence it forms a link between the two tables.

“AC_NUMBER” acts as foreign key of the table because the attribute “AC_NUMBER” is also present in the table AIRCRAFT and it references AIRCRAFT and hence it forms a link between the two tables.

“CUS_CODE” acts as foreign key of the table because the attribute “CUS_CODE” is also present in the table CUSTOMER and it references CUSTOMER and hence it forms a link between the two tables.

For Table Name: AIRCRAFT:

Foreign Key: MOD_CODE

“MOD_CODE” acts as foreign key of the table because the attribute “MOD_CODE” is also present in the table MODEL and it references MODEL and hence it forms a link between the two tables.

“For Table Name: MODEL:

Foreign Key: None

There is no Foreign Key attribute present in the table because there is no attribute in the table except the primary key which is present in any other database table.

For Table Name: PILOT:

Primary Key: EMP_NUM

“EMP_NUM” acts as foreign key of the table because the attribute “EMP_NUM” is also present in the table EMPLOYEE and it references EMPLOYEE and hence it forms a link between the two tables.

For Table Name: EMPLOYEE:

Foreign Key: None

There is no Foreign Key attribute present in the table because there is no attribute in the table except the primary key which is present in any other database table.

For Table Name: CUSTOMER:

Foreign Key: None

There is no Foreign Key attribute present in the table because there is no attribute in the table except the primary key which is present in any other database table.

Relationship among the tables:

A CUSTOMER requests many CHARTER trips and more than one CHARTER trip can be requested by a single customer. Hence, the relationship between CUSTOMER and CHARTER is one to many or 1: M.

An AIRCRAFT can fly many CHARTER trips but that each CHARTER trip is flown by one AIRCRAFT. Hence, the relationship between AIRCRAFT and CHARTER is one to many or 1: M.

Each AIRCRAFT references a single MODEL but a MODEL references many AIRCRAFT. Hence, the relationship between AIRCRAFT and MODEL is many to one or M: 1.

Many CHARTER trips are flown by a single PILOT and with a single COPILOT but a PILOT can fly only one charter trip at a time. Hence, the relationship between CHARTER and PILOT is many to one or M: 1.

All PILOTS are EMPLOYEES, but not all EMPLOYEES are PILOTS – some are mechanics, accountants, and so on.

There is an optional (default) 1:1 relationship between EMPLOYEE and PILOT. It can be represented that EMPLOYEE is the “parent” of PILOT.

Elimination of Homonyms:

In the above tables, there are two attributes that are homonyms. The attributes are CHAR_PILOTS and CHAR_COPILOTS.

The two homonyms attributes are eliminated by modifying the CHARTER table and deleting the CHAR_PILOTS and CHAR_COPILOTS attributes.

A new table CREW is added, which is a composite table and it acts as a link between the CHARTER and EMPLOYEE tables. One CHARTER requires many CREW members and hence there is a one to many relation between them. Many CREWS are employees but one EMPLOYEE can be a part of one crew and hence it represents  a many to one relationship between them.

CROW FOOT diagram to represent relationship between CHARTER, MODEL, AIRCRAFT, CREW, EMPLOYEE, PILOT and CUSTOMER:

The CROW FOOT diagram to represent relationship between CHARTER, MODEL, AIRCRAFT, CREW, EMPLOYEE, PILOT and CUSTOMER is shown below:

Database Systems: Design, Implementation, & Management, Chapter 3, Problem 27P

The above diagram represents the one to many relationship between CUSTOMER and CHARTER, one to many relationship between AIRCRAFT and CHARTER, many to one relationship between AIRCRAFT and MODEL, many to one relation between CHARTER and PILOT and an optional one to one relationship between PILOT and EMPLOYEE, one to many relationship between CHARTER and CREW, many to one relationship between CREW and EMPLOYEE.

Want to see more full solutions like this?

Subscribe now to access step-by-step solutions to millions of textbook problems written by subject matter experts!
Students have asked these similar questions
Show the structure view of the table to see the field name ,field properties and it's data type.
Task 2: The Car Maintenance team also wants to store the actual maintenance operations in the database. The team wants to start with a table to store CAR_ID (CHAR(5)), MAINTENANCE_TYPE_ID (CHAR(5)) and MAINTENANCE_DUE (DATE) date for the operation. Create a new table named MAINTENANCES. The PRIMARY_KEY should be the combination of the three fields. The CAR_ID and MAINTENANCE_TYPE_ID should be foreign keys to their original tables. Cascade update and cascade delete the foreign keys. Create a new table to store maintenance operations     Test Query DESCRIBE MAINTENANCES Expected Results Field Type Null Key Default Extra CAR_ID char(5) NO PRI NULL   MAINTENANCE_TYPE_ID char(5) NO PRI NULL   MAINTENANCE_DUE date NO PRI NULL
Task 2: The Car Maintenance team also wants to store the actual maintenance operations in the database. The team wants to start with a table to store CAR_ID (CHAR(5)), MAINTENANCE_TYPE_ID (CHAR(5)) and MAINTENANCE_DUE (DATE) date for the operation. Create a new table named MAINTENANCES. The PRIMARY_KEY should be the combination of the three fields. The CAR_ID and MAINTENACNE_TYPE_ID should be foreign keys to their original tables. Cascade update and cascade delete the foreign keys.   Answer in MYSQL please

Chapter 3 Solutions

Database Systems: Design, Implementation, & Management

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
Background pattern image
Computer Science
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
SEE MORE QUESTIONS
Recommended textbooks for you
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
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
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning