P1 - Explain the features of a relational database

M1 - Explain referential integrity and the purpose of primary keys in building the relationships between tables

Relational Databases

Relational databases store data in different logical categories, this means that there are many tables within the database which will share primary and foreign keys to identify the tables and link them together. Relational databases are good when making an appointment because information does not have to be entered many times. Some things (such as a client’s DOB) won’t change and are unnecessarily being entered over and over again in other database types such as flat file or paper databases. Relational databases mean that this data does not need to be typed over and over again, this means that mistakes will be reduced, time will be saved and storage will be saved too. Another benefit of a relational database as a pose to flat file databases is improved security, this is because there are more tables to search for and information can be split between tables with different security.

The advantages summarised are:

The disadvantage of a relational database is that they can require a powerful system if there are a lot of tables with lots of relationships. Another issue with relational databases is that it can take a while to extract information from the data. The software can also be expensive and may not be as productive as expected. Relational databases are also harder to create and may need a specialist to design which again will cost money.

Entities

An entity is something data can be stored about; this could be a person/object or event. In a school database entities would be teachers, students etc. The entries in green are entities.

Teacher

Student

Exam session

Mr smith

Josh

1

Mrs potter

Jasmine

2

Mr Clark

Jack

3

Attributes

Entities have a series of attributes which describe properties of the entity. These are things about the person/object/event; for example name, age, location, hair colour etc. An attribute in the table below is Jim.

Primary Key

A primary key is a field in a table that is unique and allows you to identify every record in that table. Each table should have a primary key or a foreign key.

In this picture you can see that studentid is a primary key in the first table, it has a unique entry for that table. There could be duplicates in the other entities such as two people with the same name or course ID so they aren’t the primary key.

You can see that courseid is the primary key in the second table as it is unique in that table. http://rdbms.opengrass.net/2_Database%20Design/2.1_TermsOfReference/r/keyForeign.gif

Relationships

Each entity has a relationship with another entity; for example one teacher would have many students. This can be shown in the picture above, courseid is a primary key in one table and a foreign in another, the relationship is a many to one as there are many students doing one course.

Referential integrity

Referential integrity is checking the links between relationships and keys in a database; this means checking the primary and foreign key relationships within a database. Having referential integrity can help prevent records being deleted as it will give you a warning of what you are doing and that it could break references between tables. This is partly why it is so important to have referential integrity. Referential integrity also ensures that every table in a relational database can be linked with a primary or foreign key, sometimes you won’t be able to create new tables unless they have a foreign key linked to another table. A benefit of referential integrity is the rules that make it. There are both update and deletion referential integrity rules. If a field in one table is changed then it can also change in another table which is linked, this is called cascading update. Cascading deletion would be when a parent table is deleted all the children tables will be deleted too. Other rules include Restrict, set to null and set to default. When deleting the Restrict rule will prevent parent tables from being deleted if the child tables still exist. When updating the Restrict rule will prevent a primary key from being updated if it has values as a foreign key in another table. Set to null will set all foreign keys to null when deleted and set foreign keys to null when updating a table. Set to default will set the foreign key values to their default when deleted and will set them to their default when updating.

All of these rules help to maintain consistency in the database as it helps prevent mistakes when deleting and also saves time with rules such as cascade.