A database is an organised store of data/information making it easy to store, access and retrieve information.

A flatfile database is one table used to store all of the information this can be suitable in simple scenarios but is inefficient in more complex scenarios.

Data Duplication

Data is repeated unnecessarily which wastes space in our storage device and increases the length of time it takes queries to run

Data Inconsistency

When we enter data multiple times we are more likely to make a mistake, such as mispelling a name or mistyping a date of birth.

Poor Data Integrity

Data that is inconsistent or erroneous is not reliable and can lead to poor decisions being made by an organisation.

Relational databases are a more efficient method of storing data. A relational database has multiple tables with each one having a primary key to uniquely identify each record in that table. The tables are linked together through relationships, this involves inserting primary keys from one table as foreign keys in other tables. A relational database is a much more powerful way of storing data as the relationships allow data to be retrieved from a number of tables using a single query and there is less data redundancy, less data inconsistency and improved data integrity.

• Each table must have a unique reference for each record which is called the Primary key. The primary key uniquely identifies each record in the table

• Replicating a Primary Key into other tables creates a Foreign key

• Foreign keys form the Relationships that link the tables together 

• A composite key is when two or more fields act as the primary key.

A relational database has more than one table of data, each table holds the information about a specific entity or object in a database. The tables are joined through primary and foreign keys. The key can join tables together in many different relationships, such as a one-to-one relationship, where each table contains one unique record; a one-to-many relationship, where one table might contain records about an individual and the other might have records about all that individual’s financial transactions. 

A record is all of the information about an object in a database.

A field is one piece of data which is held in the database.

In a student table we might have the fields:

Name:

Age:

Class:

A record might be.... 

Name: James

Age: 17

Class: 13C

Using a relational database a name and data type for each field can be specified. As well as data types, database systems allow you to apply further constraints such as specifying a maximum length or enforcing the uniqueness of a field. Constraints help control data integrity and prevent problems such as: • entering a Project Name (text) in a field where a Project Number is expected. 

• entering a Start Date in the last century.

 • having two projects with the same Project ID. 

• entering a number in the Date field. 

• entering a Pay Grade which does not exist. 

• Maintaining data integrity. 

The features which allow us to set field properties, link tables and set constraints help to increase the reliability of the data. 

Access Rights Most relational database systems provide access rights which can be assigned to different users depending on their role. Some of the operations that can be allowed or disallowed to a user are SELECT, INSERT, DELETE, ALTER, And CREATE. These rights correspond to the operations that can be performed using the Structured Query Language (SQL). When manipulating data in the database, SQL queries are formulated. Examples of manipulating data include storing new data, and selecting and altering existing data. We will learn more about SQL in a later topic. All databases will allow their owners to create, read, update and delete data. This is generally done through various forms of Structured Query Language. The language also allows most users to query and manipulate data, as well as protect it from unwanted updates, deletions or other potential errors 

The relational model is a standard. By adhering to the rules of the relational model you ensure that your data can be transferred between relational database systems relatively easily. 

Data Modelling Database design is about identifying the relationships between data and creating a logical design which is best suited to the data required for a system. 

Data modelling is used when designing relational databases. Within the process of data modelling, Entity Relationship (ER) diagrams are produced, and plans for the table design are constructed. The first step in designing a database is to identify and state what data needs to be stored. From the statement of data requirements a conceptual data model is produced. This describes how the data elements in the database are to be grouped. The terms used in building a picture of the data requirements are Entity, Attribute and Relationship. Following this, the ENTITY-RELATIONSHIP (ER) diagrams are produced. 

When creating a logical data model such as an ER Diagram entities and their relationships are identified with the keys. The attributes of each entity are also identified. A logical data model does not describe the physical structure of the real database. When creating a logical data model entities and their relationships are identified with the keys. The attributes of each entity are also identified. A logical data model does not describe the physical structure of the real database. 

A physical data model is derived from the logical data model and includes the specification of all tables and the columns (fields) inside them. The table specification includes the table name and the column specification includes the column name and data type. The physical data model contains the primary keys of each table and it specifies the relationship between the tables using foreign keys. The physical implementation will differ based on the actual database management system used.  A data dictionary is an example of a physical data model

When drawing an ER diagram: 

1. Create a box for each entity relevant to the model. 

2. Model the relationships between each entity by drawing lines to connect related entities. 

3. Label the relationships using verbs. 

4. Use Crows Foot Notation to illustrate the cardinality of relationships. 

5. Identify relevant attributes within important entities. 

6. Note that many to many relationships cannot be implemented in a relational database so these should be resolved prior to developing the physical data model. 

This will help define how different business concepts relate to one another, and created a solid conceptual foundation for designing a relational database to support the business requirements. When designing databases you should not have any ‘many to many’ relationships. They need to be redesigned so that they are ‘one to many’ or ‘many to one’. 

Consider the scenario: 

A company uses a database to store data about orders. 

• Customers can make multiple orders 

• Each order can refer to a number of books 

• A book has only one publisher 

Draw an entity relationship (ER) diagram for this system which does not contain a many to many relationship. Use the instructions outlined above to construct the diagram.