Design - continued .....

Normalisation

The database will be a relational schema, using primary keys. The aim of any relational schema is to have all the tables normalised. This would help minimise redundancy and minimise anomalies when inserting, deleting and updating. Although there are many types of normal form, Boyce-Codd Normal Form (BCNF) will be discussed in this report as it is a stricter form of normalisation than 3NF and was therefore used in the design of the database. Ideally, relational database design should achieve BCNF or 3NF for every relation schema.

Database Structure

To implement the desired system, a table was required holding the full text of each part of the website. Each field was stored as medium or long text depending on what part of the website it related to. This table did not require a separate primary key as there is only one row and consequently each field is a unique identifier and so the table is automatically in BCNF. The `text' table is illustrated below:

Text(top right, bottom right, contact us, about us, academics, offers, around world, asia, america, youth insurance, europe, uk, guides, picture)

The deals that were to be displayed in the main page required a new table. This is because there would be many different rows and would therefore leave the text table unnormalised due to the fact that there would be more than one row in the table. 'Deals' has primary key id, which auto increments in every insertion. This is used as a unique identifier, as none of the other fields in the table would be unique. The deals table is illustrated below:

Deals(id, title, description, price, url)

The functional dependencies demonstrate that the table is in BCNF, as all the superkeys are different:

id > title, description, price, url

title, description > id, price, url

The user is not obliged to enter their name and email when they give feedback so as to reduce any inconvenience they may have when filling out the form. Therefore the id would be a unique reference the whole table. The feedback table is illustrated below:

Feedback(id, name, email, feedback)

A table for the login form would be required with the user name as a primary key. The field 'admin' is a binary character which would be set to '1' if the user has administrative rights and set to `0' otherwise. The table is illustrated below:

Login(usr, first name, last name, password, admin)

Only the username and password are required for the login process. The password field is not a primary key as it may or may not be unique. The username however must be unique and consequently it is the primary key.