Assignment 3: Creating Tables and Relationships in MS Access

Goal: The purpose of this assignment is to give you practice with creating tables and relationships in MS Access from a given schema.

For this assignment, you are to create a database to keep track of your books collection and to track those books when you loan them out.

Name the database yourfirstname_yourlastname_access.mdb (example: serena_fenton_access.mdb)

The tables for this database are:
(Primary and Foreign Keys are in bold type)

A primary key is anything one attribute (or combination of attributes) that uniquely describes the entity.
Each table has one (fairly obvious) primary key. Two tables have foreign keys. One table is a junction table, which allows the joining of two tables with many to many relationships.

Relations and normalization:

• One author writes many books.
• One friends take the loan of many books at the same time
• One book can only be lent to one friend at one time (until it is returned)

Data - Enter at least:

• 10 authors
• 12 books
• 5 friends
• 5 loans

Considerations:

You will need to create the four tables and use the relations given to create the relationships table (in Access). Do NOT use the wizards to create your tables.

You will need to modify the data fields appropriately, and label them as required. We will be building forms and queries that will use the data field names, so create appropriate captions as needed.

Do not use the database wizard to create your tables or you will receive no credit for the assignment!

Component Specifications and Point Value :

(100 points possible)

Database Creation (20 pts)

Database contains four tables with the entities listed above
Entities are well named, as regards consistency and the use of spaces in a field name.

File has been compacted (10 pts)

Toolbar > Tools > Options > select the General tab> check Compact on Close

Table Specifications (20 pts:)

20 pts: All tables follow the specifications above

Primary Keys (PK) (20 pts:)

10 pts: Primary key(s) are assigned appropriately in each table.
10 pts: Primary key(s) are used as appropriate as Foreign Keys.

Relations (relationship window) (15 pts:)
15 pts: Relationships table fulfills the specifications above

Masks and Data Types (10 pts:)

6 pts: The phone number, zip code, isbn, and date fields in the above tables all have masks
3 pts: Captions are used appropriately
1 pts: short description (book) has appropriate field size (more than 255 characters).

Other (5 pts)

2 pts: Naming the database correctly (see above).
3 pts: zip your database and submit via Blackboard