SS S06 C39 01

Advanced relational databases: 

Advanced relational databases

ER modelling: 

ER modelling ER modelling is the process of identifying the: entities needed nature of the relationships between them primary and foreign keys other ‘non-key’ attributes. Many relational databases consist of three tables (as with the Video database), although modelling 4 or 5 entities may be required.

Normalisation: 

Normalisation Normalisation is the process of ensuring that a relational database conforms to certain rules that ensure that the database is as efficient as possible. There are a number of rules and, when all of these have been followed, the database is said to be normalised. There are several layers of normalisation: For A level, you need to go up to third normal form (3NF). Normalisation can be used: to turn a flat file into a relational database to ensure that a relational database is set up as efficiently as possible.

First Normal Form (1NF): 

First Normal Form (1NF) Reduce entities to 1NF by removing repeating attributes. Ensure that every record is uniquely identifiable – this might mean adding a unique attribute such as an ID. Ensure every attribute is unique – there should only be one instance of an attribute in each entity, i.e. Video hired (1), Video Hired (2) will not work.

RENTAL version 1: 

RENTAL version 1 This table shows a first attempt at creating the database

RENTAL version 2: 

RENTAL version 2

Changes made to make it 1NF: 

Changes made to make it 1NF Rental ID has been added to uniquely identify every rental made. Repeated attributes Video Hired 1 to 3 have been replaced by a single attribute Video Hired. This uses the Video ID which has been created from a new entity that stores the video data. The Video ID is the primary key in the VIDEO entity and the foreign key in the RENTAL entity. Additional non-key attributes have been included in the VIDEO entity. Customer ID has also been added at this stage to ensure that each customer can be uniquely identified.

Second Normal Form (2NF): 

Second Normal Form (2NF) Remove attributes that are not wholly dependent on the primary key. The non-key attributes are all the other attributes not used as keys. These need to be in the correct entity. Any attribute that is not dependent on the primary key should be moved to another entity. In this case, the Customer ID is in the RENTAL entity is not dependent on the Rental ID so it should be moved.

Changes made to make it 2NF: 

Changes made to make it 2NF A new entity, CUSTOMER is created: CUSTOMER

Third Normal Form (3NF): 

Third Normal Form (3NF) Reduce 2NF by removing attributes that depend on the primary key of another entity. This means that every attribute in every entity is completely dependent on the primary key of that table. In this example, Customer Name and Address are still in RENTAL, although they are not dependent on the Rental ID. Therefore they should be removed.

Changes made to make it 3NF: 

Changes made to make it 3NF

Boyce-Codd Normal Form (BCNF): 

Boyce-Codd Normal Form (BCNF) There are further levels of normalisation. The truest form is called BCNF and is named after the people who created the relational database. It is similar to 3NF but uses candidate or compound keys. This means that all of the primary keys must be derived from the existing attributes within an entity. For example, you could not invent a Customer ID for the CUSTOMER table; you must combine attributes together to create a unique compound key. Customer Name and Date of Birth combined could be used although this may still not guarantee a unique key.