Concept explainers
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
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.
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
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:
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?
Chapter 3 Solutions
EP DATABASE SYSTEMS-MINDTAP
- What problems can arise during view integration or merging relations?arrow_forwardE4.8a Depict full key functional dependencies, partial functional dependencies (if any), and transitive functional dependencies (if any) in the LANGUAGE SCHOOL Table. Use Visio.arrow_forwardCreate a view of the Join of Deposit and Withdraw transactions to Bank Branch UNION with the join of Bill Payment and Debit Purchase, or Return transactions to Merchant (i.e., all transactions with appropriate reference name.). BranchNbr stores RefNbr of either 'D', OR 'W' transactions and MerchantNbr stores RefNbr of either 'B', 'P' or 'R' transactions. GIVEN THE BELOW: --THIS CREATES THE TRANSACTION TABLE: CREATE TABLE Transaction ( TxNbr INT PRIMARY KEY, AccountNbr INT, TxTypeCode VARCHAR(250), TxDate DATE, TxTime TIMESTAMP, TxAmount FLOAT, RefNbr INT, FOREIGN KEY (AccountNbr)REFERENCES Account(AccountNbr), FOREIGN KEY (TxTypeCode) REFERENCES TxType(TxTypeCode)); --THIS CREATES THE BANK BRANCH TABLE:CREATE TABLE BankBranch ( BranchNbr INT PRIMARY KEY, BranchName VARCHAR(250)); --THIS CREATES THE MERCHANT TABLE:CREATE TABLE Merchant ( MerchantNbr INT PRIMARY KEY, MerchantName VARCHAR(250)); -- Insert test data…arrow_forward
- Task 5: ' The Driver Relationship team realized that there is no need for DRIVER_DRIVING_LICENSE_CHECKED field in the active drivers table. Since all the drivers are already active in InstantRide, their driving licenses should be checked regularly. The team wanted you to remove the field from the table. ANSWER IN MYSQL PLEASEarrow_forwardWrite the DDL CREATE statement that can be used to create the following table based on the given schema: Class (class_code, course_code, class_section, class_room ) Note: The example of the data record of the Course table is (10012, "ACCT-211",1, "KLR 225") class_code is the primary key of the Course table course_code is the foreign key from the Course tablearrow_forwardProblem 22 Insert the following customer into the CUST_MYSQL table, allowing the AUTO_INCREMENT attribute set up in Problem 20.a and Problem 20.b to generate the customer number automatically: CUST_LNAME CUST_FNAME CUST_BALANCE Powers Ruth 500 This is what problem 20.a said, and got right: Using MySQL, create a table named CUST_MYSQL with the same fields as in Problem 16, except, use the AUTO_INCREMENT feature for the CUST_NUM field. This is what problem 20.b said, and got right: Using MySQL, alter the table to populate the CUST_NUM field, beginning the increment with 2000.arrow_forward
- Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage LearningDatabase 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 LearningProgramming with Microsoft Visual Basic 2017Computer ScienceISBN:9781337102124Author:Diane ZakPublisher:Cengage Learning