Concept explainers
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.
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.
Want to see more full solutions like this?
Chapter 3 Solutions
Database Systems: Design, Implementation, Management, Loose-leaf Version
- What's wrong with my pseudocode? // The calcDiscountPrice function accepts an item’s price and // the discount percentage as arguments. It uses those // values to calculate and return the discounted price. Function Real calcDiscountPrice(Real price, Real percentage) // Calculate the discount. Declare Real discount = price * percentage // Subtract the discount from the price. Declare Real discountPrice = price – discount // Return the discount price. Return discount End Functionarrow_forwardNeed help converting my pseudocode to python, AND have a flowchart showing everything!The code: Function getScore() // Prompt the user to enter a test score Display "Enter a test score as a percentage (0-100): " Input score // Return the score entered by the user Return scoreEnd Function Function getGPAPoint(Integer score) // Determine GPA point based on the score If score >= 90 Then Return 4.0 Else If score >= 80 Then Return 3.0 Else If score >= 70 Then Return 2.0 Else If score >= 60 Then Return 1.0 Else Return 0.0 End IfEnd Function Function getAverage() // Initialize variables to store the sum of scores and GPA points totalScore = 0 totalGPA = 0.0 // Loop to collect 5 test scores For i = 1 to 5 Do score = getScore() // Call getScore function to get a test score totalScore = totalScore + score // Add score to totalScore gpaPoint = getGPAPoint(score) // Convert…arrow_forwardWhere did I make an error in my pseudocode module???Code:Module main() Call raiseToPower(2, 1.5) End main Module raiseToPower(Real value, Integer power) Declare Real result Set result = value ^ power Display result End raiseToPowerarrow_forward
- I need help writing pseudocode for calculating class score average by putting in 5 test scores, and showing the average from all 5 inputs and the GPA score.Starting with 3 functions outside of a main module. The functions are getScore(), getGPAPoint(Integer score), and getAverage(). Function getscore is an input for a grade as a class percentage. Function getGPAPoint will calculate the score into a GPA point and return as a float (values of 90-100 as 4.0, 80-89 as 3.0, 70-79 as 2.0, 60-69 as 1.0, and anything below 60 as 0.) Function averageGPA will finally make a call to both previous functions when the user inputs numbers 5 times that then calculates the average (add up all the scores, divide by 5) and the average grade alongside displaying the average GPA. End result is a main module that makes a proper call to the averageGPA function and display its results. Need help with this!arrow_forwardPlease original work Why is integration between data collection and business analysis so important to success in an organization that uses business analytics? How can a company that is just starting to use business analytics set up its program for success right from the beginning? Please cite in text references and add weblinksarrow_forwardHow to make a 1 bit adder with CLA?arrow_forward
- I need help writing pseudocode for this function. The following pseudocode statement calls a function named half, which returns a value that is half that of the argument. (Assume both the result and number variables are Real.) Set result = half(number)arrow_forwardNeed help converting my pseudocode to python, AND have a flowchart showing everything!The code:Main Module Call InputModuleEnd Main Module Module InputModule // This module gets input from the user Declare Principal, AnnualRate, Years as Float Output "Enter the Principal amount (P): " Input Principal Output "Enter the Annual Interest Rate (in percentage, e.g., 5 for 5%): " Input AnnualRate Output "Enter the number of Years to repay the loan: " Input Years Call DisplayPayment(Principal, AnnualRate, Years)End Module Module DisplayPayment(Principal, AnnualRate, Years) // This module calculates and displays the monthly payment Declare R, N as Float Declare MonthlyPayment as Float Declare PowerFactor as Float // second local variable // Calculate monthly interest rate R and number of months N Set R = (AnnualRate / 100) / 12 Set N = Years * 12 // Calculate PowerFactor = (1 + R)^N Set PowerFactor = (1 + R) ^ N // Calculate…arrow_forwardWhats wrong with my pseudocode? Where did I make an error?Code: Module main() Declare Real mileage Call getMileage() Display “You’ve drive a total of “, mileage, “ miles End Module Module getMilage() Display “Enter your mileage: “ Input mileage End Modulearrow_forward
- I need help!! Writing a long pseudocode for a modular program that will display the monthly payment on a mortgage. P=Principal amount borrowed (loan)R=Rate of interest computed for each monthN=Number of months to pay back the loan or mortgageThe help I need is creating a module that you can input the principle, rate of percentage, and years to repay the loan, and another module "displaypayment" that accepts the 3 values and calculates the monthly payment needed for the rates. Lastly 2 local variables needed!Equation:Monthly Payment=[(R*(1+R)^N)/((1+R)^N-1)]*Parrow_forwardTwo pseudocode questions I need help with: How do I design a module called findSum that will display the sum of two integer passed by parameter, and a module called findArea that will display the area of a rectangle when passed 2 real values for the length and width of the rectangle?arrow_forwardFor the pseudocode module, what is displayed with the call findValue(1, 4, 2)?Module findValue(Integer a, Integer b, Integer c) Declare Integer value value = b + c - a Display valuearrow_forward
Programming with Microsoft Visual Basic 2017Computer ScienceISBN:9781337102124Author:Diane ZakPublisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
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 LearningNp Ms Office 365/Excel 2016 I NtermedComputer ScienceISBN:9781337508841Author:CareyPublisher:Cengage