Help Constructing a Database

[Salaheldin]

Hi, so I have been given a practice question for a Database course that I am taking. But I just can't figure out what tables I need to create. I learn by example, so if someone can please help me out then I can try to piece together what to do. The question is as follows:

Read the following abstract description of a collection of data that describes a simple video lending store. From the elements provided, create an Entity Relationship Diagram (Part 3), clearly showing the tables, keys, columns and relationships of the database optimized to third normal form:
borrowers, borrower id, borrower’s name (first and last), address, amount owing (for late fees), video, video id, video’s title, distributor, run time, director, director id, director’s name (first and last), topics, topic id, topic description, video shelf location for topic, loans, date borrowed, date due, overdue indicator
With the following assumptions:
- The store can have many copies of a particular video
- A borrower can sign-out many videos at one time
- A borrower can sign-out only one copy of a particular video at one time
- A borrower can sign-out the same video at a future date
- A video has only one director
- A video falls under only one topic category (such as science fiction, fantasy, drama, etc)
- All videos of one topic category are put on one shelf location (e.g., horror is on shelf A100)
- You should require 5 tables
- Identify the primary key for a table by denoting a PK beside the field name
- Identify a foreign key by denoting it with a FK beside the field name

 

[Salaheldin]

I was able to create the tables, but I just don't know what relationships I am to make. In order for me to make a relationship does the corresponding table have to have a field name that is the same as the primary key in the other table?

 

[jdraw]

Many people find this tutorial very helpful in designing databases. Good luck.

http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip