In the Fox Lake case at the start of Chapter 12, Chris was able to use Mike’s computer in the middle of the night, when no one was around. And, Fox Lake is about to discover that Jason, Chris’s accomplice, stole more than $2,500 worth of equipment. Jason will be living in another state by the time this is discovered.

Suppose that Fox Lake has asked you to develop an Access database that tracks keys, equipment, and user accounts that have been issued to employees and contractors. Assume your database has four tables: Person, Equipment, Key, and Account. Person has data about employees and contractors, Equipment has data about equipment that has been allocated to individuals in the Person table, Key has data about keys that have been allocated to individuals in Person, and Account has data about accounts that have been created for individuals in Person. (This last table is needed because Fox Lake is about to start a policy that every user of every computer has his or her own account for using that computer. Thus, an account name is an asset, just like a building key or a paddle.)

a. Using your knowledge, experience, and intuition design the Person, Equipment, Key, and Account tables. Name and describe the columns of each table and indicate which columns are the primary key.

b. Specify the maximum cardinality of the relationships between:

(1) Person and Equipment

(2) Person and Key

(3) Person and Account

c. Modify your design in part a to include the foreign keys necessary to support the relationships you specified in part b.

d. Create your database in Access and fill it with sample data.

e. Create a form for adding a new person and the equipment assigned to that person.

f. Create a form for allocating a key to an existing person.

g. Create a report suitable for use when an employee quits Fox Lake. Your report should include all resources that need to be recovered as well as computer accounts that need to be removed.

h. Create a parameterized query that accepts a person’s name and generates the report in part g for that person.