Database - How To Movies
 
1. Setting up and creating tables and fields 2. Formatting Fields. 3. Creating Primary Key and Relationships 4. Searching and Creating Reports 5. Editing Reports
 
Home DatabaseUsing Information Internet NAB/Coursework Revision Legal Responsibilities

Database

Keys


In order to establish the relationships between the tables in the database, each entry, or record, in a table must be able to be uniquely identified by a key. A key is a field, or set of fields, the values of which uniquely identify a record. A field is equivalent to an attribute of an entity. In any table, there may be more than one field, or set of fields, which can uniquely identify each record – these are called candidate keys. The candidate key that is chosen to be used is called the primary key.


In our DVD rentals example, Member Number is a candidate key for the MEMBER entity, because each member has a unique number.  For example, the number 1056 appears in only one member record. The key is identified by underlining it, as shown:


MEMBER(Member Number, Name, Telephone Number)

The extra field (Member Number) which is added to the DVD RENTAL table is called a foreign key. A foreign key is a field that is not a primary key in its own table, but is a primary key in another table.

In this example, Member Number is a foreign key in DVD RENTAL, because it is the primary key in MEMBER.

Here is the data model:



MEMBER(Member Number, Name, Telephone Number)

DVD RENTAL(DVD Code, Title, Cost, Date Out, Date Due, *Member
Number)


The asterisk (*) before Member Number in the DVD RENTAL entity indicates that it is a foreign key.

 

: Entity-relationship diagram for the video shop data model

You can use the data model to check that the E-R diagram is correct.

In this case, Member Number is a foreign key in the DVD RENTAL entity, so this entity is at the ‘many’ end of a one-to-many  relationship.

Read the relationship out. If you read the relationship back to yourself, you can check to see if it’s correct. In this case, reading from top to bottom, ‘each Member can rent many DVDs’. This is correct. Reading from bottom to top, ‘each DVD can be rented by only one Member’. This is also true.


It is not always obvious which entities are required in a data model. A process called normalisation is used to determine these.

In fact, the data model above is not fully complete. If the database records each time a DVD is rented, then a DVD may be rented more than once (on different dates).

From data model to database


Once the entities, attributes and relationships in the data model have been defined, a database can be created.

Each entity in the data model becomes a table in the database. Each attribute of an entity becomes a field in a table in the database.


 

 



 

Intermediate II Home
Database | Using Information | Internet | NAB/Coursework| Revision
Log into Glow | School Website | Contact Us