Chapter 3, Problem 24P

Program Plan Intro

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.

Candidate Key:

A set of attributes (minimal) which can uniquely identify a record is known as candidate keys. A attribute which could’ve been a primary key but for was not chosen as primary key for some reason is a candidate key.

The value of candidate is not null for every record in database and in unique.

The candidate key can be of composite attributes and there can be more than one candidate key in a relation.

SUPER KEY:

A set of one or more columns or attributes to uniquely identify rows in a table is called super key.

A Super Key is a candidate key containing redundant attributes.

All candidate keys are super keys as candidate keys are derived from super keys.

Any primary key plus any attribute is a super key.

SECONDARY KEY:

Secondary keys are the set of attributes which are not selected as primary key but are considered to be candidate keys for the primary key of the table.

Numbers of attributes that constitute secondary key are arbitrary.

Secondary keys are also known as alternate keys.

After selecting the attributes from candidate key to form a primary key, the remaining attributes of candidate key are called secondary keys.

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.

Super key and Secondary Key in the above tables:

For Table Name: CHARTER:

Super Key:

CHAR_TRIP+CHAR_DATE: This combination of attributes can uniquely identify every other record present in the table.

Secondary Key:

CHAR_DATE+AC_NUMBER+CHAR_DESTINATION: This combination is an alternate key which will identify every other record present in the table is unique and it is less likely that one aircraft on same date will travel to the same destination twice.

For Table Name: AIRCRAFT:

Super Key:

AC_NUM+MOD_CODE: This combination of attributes can uniquely identify every other record present in the table.

Secondary Key:

MOD_CODE: This is an alternate key which will identify every other record present in the table and is unique.

For Table Name-MODEL:

Super Key:

MOD_CODE+MOD_NAME: This combination of attributes can uniquely identify every other record present in the table.

Secondary Key:

MOD_MANUFACTURER+MOD_NAME: This combination is an alternate key which will identify every other record present in the table and it is less likely that two models with same name and same manufacturer exist.

For Table Name-PILOT:

Super Key:

EMP_NUM+PIL_LICENSE: This combination of attributes can uniquely identify every other record present in the table.

Secondary Key:

PIL_LICENSE+PIL_MED_DATE: This combination is an alternate key which will identify every other record present in the table and it is less likely that two pilots with same license and same medical certificate exist.

For Table Name-EMPLOYEE:

Super Key:

EMP_NUM+EMP_DOB: This combination of attributes can uniquely identify every other record present in the table.

Secondary Key:

EMP_LNAME+EMP_FNAME+EMP_DOB: This combination is an alternate key which will identify every other record present in the table and it is less likely that two employees with same first name, same last name and same date of birth exist.

For Table Name-CUSTOMER:

Super Key:

CUS_CODE+CUS_LNAME: This combination of attributes can uniquely identify every other record present in the table.

Secondary Key:

CUS_LNAME+CUS_FNAME+CUS_PHONE: This combination is an alternate key which will identify every other record present in the table and it is less likely that two customers with same first name, same last name and same phone number exist.

Candidate keys present in the above table:

For table CHARTER:

Candidate Key: None

No practical candidate keys are possible. For example:

CHAR_DATE + CHAR_DESTINATION + AC_NUMBER + CHAR_PILOT + CHAR_COPILOT will not necessarily yield unique matches, because it is possible to fly an aircraft to the same destination twice on one date with the same pilot and copilot.

For table AIRCRAFT:

Candidate Key: None

No practical candidate keys are possible as no combination of attributes will yield unique matches, because it is possible to fly a same aircraft to the same destination twice on one date with the same pilot and copilot.

For table MODEL:

Candidate Key: None

No practical candidate keys are possible as no combination of attributes will yield unique matches, because it is possible to fly a same model of aircraft to the same destination twice on one date with the same pilot and copilot.

For table PILOT:

Candidate Key: None

No practical candidate keys are possible as no combination of attributes will yield unique matches, because it is possible to fly a same model of aircraft to the same destination twice on one date with the same pilot and copilot.

For table EMPLOYEE:

Candidate Key: EMP_LNAME + EMP_FNAME + EMP_INITIAL + EMP_DOB

The combinations of the above attributes will yield a unique outcome and hence is acceptable as candidate key.

For table CUSTOMER:

Candidate Key: CUS_LNAME + CUS_FNAME + CUS_INITIAL + CUS_PHONE

The combinations of the above attributes will yield a unique outcome and hence is acceptable as candidate key.