![Database Systems: Design, Implementation, & Management](https://www.bartleby.com/isbn_cover_images/9781285196145/9781285196145_largeCoverImage.gif)
Create the ERD. (Hint: Look at the table contents. You will discover that an AIRCRAFT can fly many CHARTER trips but that each CHARTER trip is flown by one AIRCRAFT, that a MODEL references many AIRCRAFT but that each AIRCRAFT references a single MODEL, and so on.)
![Check Mark](/static/check-mark.png)
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.
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.
CROW FOOT diagram to represent relationship between CHARTER, MODEL, AIRCRAFT, PILOT, EMPLOYEE and CUSTOMER:
The CROW FOOT diagram to represent relationship between CHARTER, MODEL, AIRCRAFT, PILOT, EMPLOYEE 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.
Want to see more full solutions like this?
Chapter 3 Solutions
Database Systems: Design, Implementation, & Management
- complete the docstring using the information provided:arrow_forwardImplement the following clauses on a database of your choice and produce results. (Writequery and add screen shot of your implementation)iv) Viewv) Group Byvi) Order Byvii) Like kindly answer the all parts i already posted 2 times this one question but there are are total 7 parts so kindly solve 4 prts.arrow_forward3.Rose Theater Company For this assignment you will modify the Rose Theater Company assignment created in chapter 2. Rose Theater company sells tickets to their productions. There are three seating options for purchase at Rose's theater; Orchestra seats, Center Stage seats, and Outer Stage seats. Orchestra seats cost $75 each, Center Stage seats cost $50 each, and Outer Stage seats cost $25 each. The theater company also gives a discount of 5% on tickets to the local theater group members. The user will enter the number of Orchestra, Center State, and/or Outer stage seats the customer wants to purchase along with whether the customer is a member of the local theater group. The program will calculate and display the income from each type of seat sale along with the discount, and total sale. Requirements Must include use of named constants Must include meaningful variable names Must include a main function with a call to the main function. Must include at least one value returning…arrow_forward
- Task 1: The InstantRide received some traffic violation tickets from the government. The Legal team of InstantRide requires the travel information of the respective drivers along with corresponding Driving License IDs to proceed further. In addition, the team wants to include the drivers without travel information in the system yet for the completion of driver list. Therefore, you need to return DRIVER_FIRST_NAME, DRIVER_LAST_NAME, DRIVER_DRIVING_LICENSE_ID, TRAVEL_START_TIME, TRAVEL_END_TIME information from the DRIVERS and TRAVELS data connected by LEFT JOIN. Task: Query all drivers with and without travel data. (SQL Database Test)arrow_forwardCreate a table of your courses for this semester with fictitious grades for Test 1, Test 2, and Test 3 with appropriate weighting. The next column should determine the grade using the 'if' function, with plus and minus. The grade cell should show a discrete color scheme with below C- in red; above B in green and, yellow for the rest. This should be done using conditional formatting of cells.arrow_forward2- selects the order line items (orderNumber, orderlinenumber) fromthe “orderdetails” table. Also, display the subtotal (quantityOrdered * priceEach) for eachline item and sorts the result set based on the subtotal descending .3- Display unique last names from employees table ordered by last name.4- Display customerNumber, customerName and creditLimit for getting the top five customerswho have the highest creditLimit.5- Find customer Name, customer country, sales representative employee number fromcustomers who do not have a sales representative.6- For each order status, display status and count of orders have this status.7- For Each order number in order details table, Display order number, sum of quantityordered (itemsCount) and sum of (priceEach * quantityOrdered) as total. With condition“total” greater than 1000.8- Display product code, product name and text description for product.9- Find customer number, customer name, order status and order number, for customerwith their orders…arrow_forward
- House Building [Code should be in python] You are to draw a House using the base primitives: points, lines, or triangles. You can use GL_POINTS, GL_LINES or GL_TRIANGLES for designing this house. A diagram has been provided as an example. You can modify the house design to your liking.arrow_forwardMember m = new BoardMember(); Is this statement legal or illegal and widening or narrowing conversion.arrow_forwardSQL: Create a SQL query that uses an uncorrelated subquery and no joins to display the descriptions for products provided by a vendor in area code 615. Remember, the main difference between a correlated subquery and uncorrelated subquery is that, in a correlated subquery, the inner query is dependent upon the outer query, while, with an uncorrelated query, the inner query does not depend upon the outer query. Instead, it runs just once. You should NOT use union, minus, or intersect in this query. The outer query will use the PRODUCT table and the inner query will use the VENDOR table [hint: you should have 9 records of output]. My code: select p_descript from product where (select v_areacode from vendor where v_areacode = '615' ; I am getting this error, problem is I can't see what is missing any assistance is appreciated. ORA-00936: missing expressionarrow_forward
- Please answer the following questions A . . Can prompt method take two input values from user? If yes/no, please explain. B . . Can we insert a Table inside a Form or can insert a Form inside a Table? In both scenarios please explain.arrow_forwardcalculate_new_balance Given a starting balance (a number), and a list of transaction tuples, calculate the final balance for an account. Transaction tuples are of the shape ("description", amount, "withdrawal") , or ("description", amount, "deposit"). The last entry in the tuple will be either "withdrawal" or "deposit". Every withdrawal decreases the balance of the account by the specified amount, and every deposit increases the balance. The return value is the new account balance, as a number. (which could be negative) Sample calls should look like: >>> calculate_new_balance(100, [("payday", 20, "deposit"), ("new shoes", 50, "withdrawal"), ("illicit winnings", 200, "deposit")])270>>> calculate_new_balance(100, [])100arrow_forwardTask 4: The InstantRide User Satisfaction team is a core team for InstantRide, and they focus on increasing the customer satisfaction. They want to learn the travel time for each ride in the system. You need to return the USER_ID, and the TRAVEL_TIME column which is calculated using the TIMEDIFF function on the TRAVEL_END_TIME and the TRAVEL_START_TIME.arrow_forward
- Np Ms Office 365/Excel 2016 I NtermedComputer ScienceISBN:9781337508841Author:CareyPublisher:CengageCOMPREHENSIVE MICROSOFT OFFICE 365 EXCEComputer ScienceISBN:9780357392676Author:FREUND, StevenPublisher:CENGAGE L