Open main menu

Wikibooks β

A-level Computing/AQA/Problem Solving, Programming, Operating Systems, Databases and Networking/Databases

< A-level Computing‎ | AQA‎ | Problem Solving, Programming, Operating Systems, Databases and Networking
From the Specification : Database
  • Conceptual data model
  • Entity Relationship modelling

Produce a data model from the given data requirements for a simple scenario involving two or three entities.

  • Database Design and Normalisation techniques

Be able to normalise relations to Third Normal Form.

From the Specification : Relational Databases

Explain the concept of a relational database.

Define the terms:

  • attribute,
  • primary key,
  • composite key,
  • foreign key,
  • referential integrity.
From the Specification : Querying a Database

Learn about Structured Query Language (SQL)

Use SQL to retrieve, update, insert and delete data from several tables of a relational database (see Teacher Resource Bank)

Data Definition Language (DDL)

Explain the term DDL. Use DDL to define a database (see Teacher Resource Bank for commands/statements).

Contents

Conceptual Data ModelEdit

Entity Relationship DiagramsEdit

An entity relationship diagram is used at the Analysis stage to help the analyst to model and represent the entities involved in the problem domain. These models can then be developed into designs for tables and relationships in a proposed database system.



RelationshipsEdit

One to One RelationshipEdit

Each instance of an entity can have one, and only one, related instance of another entity.

 
An example of a one to one entity relationship diagram.

Many to One RelationshipEdit

Each instance of an entity can have any number of related instances of another entity, however those instances are only have one related instance of the first entity.

 
An example of a many to one entity relationship

Many to Many RelationshipEdit

Each instance of an entity can have any number of related instances of another entity, and each of those instances can have many related instances of the first entity. This is to be avoided within proper database design.

 
an example of a many to many relationship entity relationship

Referential IntegrityEdit

Referential integrity means that for each new record added to an entity there must be a related field added to, or already existing in all related entities.

Foreign KeyEdit

Tuple sometimes called a record, a set of attribute values, (a row of a table)

Attribute a named column in a table

Primary Key a attribute that uniquely identifies a tuple

Relation a table

Relational Database a collection of tables

Composite Key where a collection of attributes uniquely identify a tuple rather than just one

Foreign Key An attribute in one table that is a Primary key in another table, often there to reference to said table later

Database Design and NormalisationEdit

0NF - Flat fileEdit

All of the data is held within a single table. This is to be avoided within proper database design.

1NF - 1st Normal FormEdit

A database is considered to be in first normal form if it passes the atomic data test, which means that all of the tables in the database are indexed with primary keys, and that the tables do not contain any repeating groups of attributes.

2NF - 2nd Normal FormEdit

To reach second normal form, a database must already be in first normal form. In addition to that, the tables must pass the partial key dependency test, which means that all of the attributes within a table are related to the table's primary key.

3NF - 3rd Normal FormEdit

To reach third normal form, a database must already be in second normal form. In addition to that, the tables must pass the non key dependency test, which means that only the attributes within a table that have a direct relationship with the primary key.

Further Normal FormsEdit

While there are further levels of normalisation, these are used only in very complex and specialised systems. Knowledge of them is not required for the course.

Relational DatabasesEdit