Relational Databases

Flat file and Relational Databases

A flat file database consists of asingle table of data

A relational database consists of 2 or more tables linked together by common attributes

Flat File

Database

Advantages Disadvantages
All records are stored in one place Duplication of data
Simple to understand Non-unique records
Easy to sort / filter data Difficult to change data format
Doesn't require specialist software Complex queries not possible
  Non-secure

Relational

Database

Advantages Disadvantages
No duplication of data Little more complex to setup
Removes inconsistent records Requires advanced planning
Simple to add /update /delete records 3NF is standard 5NF is best
Allows complex queries  
Improved security *NF = Normal Form a process of normalisation

Entities, Tables & Attributes

Relational databases depend on the idea of entities. An entity is simply something in the real world about which we store data. An entity can be anything, such as a customer, a pupil, an exam subject, a music track, an order, an invoice or an item of stock.

Each entity will have several attributes associated with it.

An attribute is something that describes the entity. For instance, think of a music track in your iTunes library. It has loads of attributes associated with it:

Song name Artist Album Year
Genre Time Size No. of Plays
No. of Skips Rating Bit Rate etc.

In a relational database, data about each entity is usually stored as a separate Table. The attributes in one table must all relate to one entity. Each attribute becomes a Field. The table made for an entity must have a primary key.

Primary Key

Care is taken to ensure that as far as possible, no data is duplicated. This avoids data redundancy. Data redundancy leads to a risk that incompatible updates will be made.