Topic 7 - Database systems

A Flat File

A flat file is a file that stores all its data in one file.

This table/file stores data on owners and their dogs. It is known as unnormalised data.

If an Owner has more than one dog, you can see all of the owners details are repeated. This is the same with Breed and their Origin.

Over time, this causes the file to become very large causing redundant and duplicate data. There is also a chance the data can become inconsistent. If you think of DofB, this can be written in different formats e.g. 11/12/12 or 11th November 2012 etc

In order to fix this, we create a relational database also known as normalised data.

Solution - A Relational Database

Owners

We pull out data that keeps getting repeated

i.e. OWNERS and PETS

Dogs

We now have a relationship between Owner and Dog. ONE Owner can have MANY Dogs but a DOG can only have ONE Owner. This means we have made a relational database where an owner links to a dog. We only store the owners details once.

The Owner Table has a unique field, OwnerID. This is a primary key which uniquely identifies each owner i.e. so if two owners have the same name, you can identify them.

The primary field in the Owner table also gets put in the Dog table and this is called a foreign key. A foreign key is a key that is a primary key in another table that establishes a link between the two.

One to Many Relationship

Relationships

Here you can see how it would work. In the Owner Table, there is a primary key, this links to the Dog table. I click on one Owner and I can see all the dogs the owner has. The Benefit is, each Owner is only stored once.

Benefits, Features and Security of a Relational Database

Associated features to store data.

• Relational database:

o One-to-one

o One-to-many

• Data is normalised

• Uses a primary key in each table which is a unique identifier

• Uses a foreign key, which is a primary key from another table, forming a link between the tables

Advantages of using a relational database systems

• Avoids data duplication

• Minimised data inconsistency

• Easier to change data and data formats

• Data can be added and removed easily

• Easier to maintain security and easier to back-up data

• Easier to restore / recover from disasters

• Carry out queries / searches and user can sort data

• Can setup forms for data entry and user can generate reports e.g. year tutor generating a percentage attendance report for each form class in the year group.

• Perform calculations using calculated fields e.g. calculate a pupils age based on their Date of Birth

• Can ensure the reasonableness of data using validation e.g. range checks, length checks, format checks, dropdown lists etc.

• Can ensure data consistency using verification e.g. proof reading/visual checks, double entry method such as entering passwords twice when setting up an account.

Security

• User can have different views - Different users can see different parts of the system, the areas that are relevant to them.

• Standard clerical procedures

• Passwords for access - Having different passwords allows different levels of access

• Write-protect mechanisms. - Different users can see and use different areas of the database. For example admin staff can see data (Read) but also change data (Write). Teachers can only see data (Read) but not change anything. This maintains the security of the data and avoids the users making errors with the data accidently.

Page updated

Report abuse