HOMEWORK # 1 - Basic Terminology, Keys, Integrity Rules

(Solutions to this homework assignment must be typed.)
Suppose you are working with a database that contains the tables shown:
Table name: AIRCRAFT

AC_NUM

MOD_CODE
AC_TTAF
AC_TTEL
AC_TTER
AC_100HRS
AC_YTDHRS
AC_SINCE100
2085V PA28-181 1785.3 1785.3 0.0 11/14/94 400.2 50.7
1998Y C-172 4118.2 1422.0 0.0 10/05/94 521.9 87.4
2873R C-152 3512.7 1948.5 0.0 10/23/94 497.8 72.1
1550P C-172 3002.5 1119.2 0.0 11/05/94 600.5 89.3
5827Y PA28-200 2259.9 385.1 0.0 10/29/94 114.9 23.8
2284W C-152 3984.4 87.3 0.0 09/12/94 508.7 91.9
1085Q C-152 4029.1 114.9 0.0 10/03/94 278.9 34.5

Table name: MODEL
MOD_CODE MOD_MANUF MOD_SEATS MOD_CHG
Be76 Beechcraft 4 $125.75
C-152 Cessna 2 $42.50
C-172 Cessna 4 $48.00
PA28-181 Piper 4 $54.00
PA28-200 Piper 4 $62.50

Table name: RENT
RENT_NUM RENT_DATE AC_NUM RENT_HRS CUST_NUM
4891 12/12/94 2873R 1.1 1018
489212/12/94 1550P 2.7 1024
489312/12/94 2284W 0.9 1015
489412/12/94 2873R 2.4 1016
489512/12/94 1550P 1.6 1017
4896 12/13/94 2805V 4.9 1020
4897 12/13/94 1085Q 1.2 1017
4898 12/13/94 1550P 2.0 1016
4899 12/13/94 2284W 1.5 1025
4900 12/13/94 1550P 1.3 1019
4901 12/13/94 5827Y 2.3 1021
4902 12/14/94 1998Y 0.8 1016
4903 12/14/94 2085V 1.7 1015
4904 12/14/94 2873R 1.5 1019
4905 12/14/94 1085Q 1.2 1016

Table name: CUSTOMER
CUST_NUM CUST_QUAL CUST_CHKDATE CUST_LIMIT CUST_BAL CUST_LNAME CUST_FNAME
1015 Student $1200.00 $456.74 Robertson Steven
1016Student 08/21/94 $1500.00 $450.38 Drapfna Anna
1017 Pvt/SEL 12/09/93 $1800.00 $0.0 Francisco Mark
1018 Student $1200.00 $0.0 Robertson John
1019 Student

$1200.00

$205.57 Wilson George
1020 Pvt/SEL 11/09/94 $2000.00 $0.0 Smith LouAnne
1021 Pvt/Inst/SEL 03/09/94 $2000.00 $1219.94 Rosten

Peter

1022 Pvt/SEL 12/09/93 $1500.00 $956.72 Darmanov Elise
1023 Pvt/SEL 10/09/93 $1500.00 $205.71 Lopez Jorge
1024 ATP/MEL 06/09/93 $2500.00 $0.0 Smith William
1025 Pvt/Instr/SEL 04/09/94 $2000.00 $0.0 Treynor Helen
1026 Pvt/SEL 07/09/94 $1200.00 $0.0 Ernbaum Joseph
1027 Comm/Instr/SMEL 10/09/93 $1200.00 $0.0 Williamson George
1028 Student $1200.00 $0.0 Smithson Anne
1029 Comm/Instr/SEL 03/09/93 $2000.00 $864.38 Rakoff William

Table name: CUSTOMER (continued)
CUST_INT CUST_ADDRESS CUST_CITY CUST_STATE CUST_ZIP CUST_AREACODE CUST_PHONE
T 1235 East Barner Road Murfreesboro TN 37456 615 899-1234
G

987 Traverse Lane

Nashville TN 21778 615 456-2315
Box 1245B Smyrna TN 34567 615 904-9981
H 145 East Main Str.Murfreesboro TN 37456 615 891-3245
D 985 Meadow song Dr. Murfreesboro TN 37456 615 899-0095
E 2314 Lake Shore Rd. Lavergne TN 38894 615 915-9982
M Box 231 Murfreesboro TN 37456 615 893-6667
H 2316 Unicorn Circle Murfreesboro TN 37456 615 899-1292
W 769 Kelly Drive Nashville TN 21772 615 450-0093
Box 1234H Murfreesboro TN 37456 615 899-2667
K 983 Auto Circle Murfreesboro TN 37456 615 899-4528
D 218 GreatView Lane Smyrna TN 34567 615 904-6670
B 3425 Rowland Dr. Bowling Green KY 32119 901 324-0893
J 453 Meadow Song Murfreesboro TN 37457 615 891-3346
A 2769 Airport Rd. Nashville TN 21772 615 456-8891

Assignment:

Given the components of this database, answer the following questions.

  1. How many entity sets does this database contain?
  2. How many entities are contained in RENT table?
  3. How many attributes does the CUSTOMER table contain?
  4. How many fields does the AIRCRAFT table contain?
  5. Identify the primary and foreign keys for each of the following tables. If there is no foreign key, enter NONE under the foreign key heading. Note: if a table has a composite primary key, identify all of its components.
    TABLEPRIMARY KEYFOREIGN KEY
    AIRCRAFT
    RENT

    CUSTOMER

    MODEL
  6. For each of the tables in this database, indicate (by answering YES or NO) whether or not it displays referential and entity integrity. Use NA for "Not Applicable".
    TABLE REFERENTIAL INTEGRITY ENTITY INTEGRITY
    AIRCRAFT
    RENT
    CUSTOMER
    MODEL
  7. Suggest a minimal secondary key for the CUSTOMER table. ( A minimal secondary key is one that successfully finds the requested data while using the minimum number of attributes.)
Return to home page
Amita Goyal Chin, Ph.D.