1. Read the following tables and answer the following questions:
Customers
Customer ID
|
Name
|
Address
|
Phone
|
Email
|
9087
|
John Doe
|
204 University Ave.
|
987-098-9087
|
[email protected]
|
2098
|
Bill Lawrence
|
123 Jones St
|
717-387-3387
|
Business
|
2398
|
Laura Smith
|
0900 West Blvd.
|
901-234-4567
|
Information
|
Transactions
Customer ID
|
Account Number
|
Date of Last Transaction
|
9087
|
375
|
01/31/98
|
2098
|
123
|
03/09/97
|
2398
|
375
|
09/21/97
|
2098
|
375
|
12/31/97
|
2398
|
123
|
02/01/98
|
Accounts
Account Number
|
Balance
|
Account Type
|
375
|
234.45
|
Checking
|
123
|
056.90
|
Savings
|
|
|
|
§ Who owns the account with account number 123?
§ Can you insert a row with 3456 Customer ID, 456 as Account Number, and 09/20/97 as Data of Last Transaction into table
Transactions? Why or why not?
§ List how many records in
Transaction
table corresponds to each record in
Customers
table, and how many records in
Transactions
table corresponds to each record in
Accounts
table;
§ What is the mapping cardinality between customers and accounts?
§ Which column(s) is a primary key in each table?
§ Which columns are foreign keys?
2. Create a database with the above three tables in Question 6 using Microsoft Access. When you create these tables, make sure :
§ You have chosen right data type and field width for each column and each table has a primary key
§ Foreign keys are established.
§ Understand what the referential integrity constraints are.
§ Write some example queries for question 6.