Database

Implementation

Once the data model is completed, the database can be constructed. This involves three stages:

1.             set-up the tables
2.             populate the tables
3.             manipulate and present the data.

Let’s look at each of these stages in turn for the DVD rentals case study.

Setting up the tables

In order to set-up the tables in the database, you must decide:

1. Which tables are required?

The tables correspond directly to the entities in the data model. In this case, there will be two tables, Member and DVD Rental.

2. Which fields are required?

As before, the fields in each table are the attributes in each entity in the data model.

Table	Member	Table	DVD Rental
Fields	Member Number	Fields	DVD Code
	Name		Title
	Telephone Number		Cost
			Date Out
			Date Due
			Member Number

3. What are the properties of each field?

For each field in the database, you must consider the following.

(i) Its name

You should take care to choose sensible field names and make sure that your naming is consistent in each table. For example, if you choose to abbreviate Member Number to Member No. (rather than Member Num. or Member #), you should also abbreviate Telephone Number to Telephone No.

(ii) The data type

This may be one of the following.

Text, alphanumeric, or string	E.g. Smith, EH991AB, £14+VAT.
Numeric	Either integer (whole numbers) or real (floating point), e.g. 13, 3.14.
Currency	A special type of numeric field for monetary values, e.g. 12.00, 2.50, 0.15.
Date or time	Dates may be in dd/mm/yyyy format or ‘long date’ format. Times may be in hh:mm:ss format, or ‘long time’ format. E.g. 01/01/1990, 1 January 1990, 13:30:001:30 p.m.
Boolean	Yes or no. Named after George Boole, mathematician and logician, who created binary logic, e.g. AND, OR, NOT
Link	A reference to a file located outside the database.
Object	Data such as a picture or sound file.

(iii) Validation

Whether the field must have a value, or can be left blank (called a presence check).
Whether the value of the field is limited to certain values (called a restricted choice check), e.g. Mr/Mrs/Miss/Ms.
Numeric fields may be subject to a range check,

e.g. the ‘year’ for a secondary school pupil must be between 1 and 6.

It is often useful to record this information about tables and fields in a table called a data dictionary. This allows the database to be implemented using any database management system.

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