Database design

Entity relationship diagrams

In entity relationship diagrams the tables are drawn with lines connecting them to show relationships.

They can either be simple, just showing the table name:

Example of a simple entity relationship diagram from BBC Bitesize
Example of a simple entity relationship diagram from BBC Bitesize

Or more complex, listing the attributes within the table:

Example of a more complex entity relationship diagram from BBC Bitesize
Example of a more complex entity relationship diagram from BBC Bitesize

In either case, the important part of the design is the line connecting the tables. This represents the type of relationship between them.

Entity occurrence diagrams

An entity-occurrence diagram shows the relationship between occurrences of entities. In other words specific entries in a table rather than the table as a whole.

Each table is shown as a tall oval. Each occurrence of an entity is show by a dot and a description.

Example of an entity occurrence diagram from BBC Bitesize
Example of an entity occurrence diagram from BBC Bitesize

One-to-one relationship

A one-to-one relation occurs when an entity in one table is only ever related to a single entity in another table and vice versa. It can be represented in either of the following ways:

Example of one-to-one shown as an entity relationship diagram
Example of one-to-one shown as an entity relationship diagram
Example of one-to-one shown as an entity occurrence diagram
Example of one-to-one shown as an entity occurrence diagram

One-to-many relationship

A one-to-many relation occurs when an entity in one table can be related to multiple entries in another table. It can be represented in either of the following ways:

Example of one-to-many shown as an entity relationship diagram
Example of one-to-many shown as an entity relationship diagram
Example of one-to-many shown as an entity occurrence diagram
Example of one-to-many shown as an entity occurrence diagram

Many-to-many relationship

A many-to-many relation occurs when an entities in one table can be related to multiple entries in another table and vice versa. It can be represented in either of the following ways:

Example of many-to-many shown as an entity relationship diagram
Example of many-to-many shown as an entity relationship diagram
Example of many-to-many shown as an entity occurrence diagram
Example of many-to-many shown as an entity occurrence diagram

Data dictionaries

Data dictionaries are used to define the structure of a database.

Data dictionaries contain the following:

  • The name of each entity.
  • The name of each attributes for each entity.
  • The data type of each attribute.
  • The size of each attribute.
  • The attributes that will be used as keys.
  • Any validation of attributes.
Example data dictionary
Example data dictionary

Validation of attributes

In an RDBMS you can specify that an attribute needs to meet certain criteria to be considered valid. Invalid data will not be accepted and so the insert or update command will be ignored.

The criteria can be:

  • Presence. Has any data been provided e.g. leaving a field blank.
  • Length. Limiting the input to a certain number of characters.
  • Restricted choice. Only accepting inputs that are certain vales.
  • Restricted range. Only accepting inputs between certain values.

Query design

It is important to take time to design any queries that may need to be implemented later.

At the design stage at SCQF level 6, it is necessary to state the following:

  • fields
  • tables
  • search criteria
  • sort order
  • calculations
  • grouping

Shorthand can be used for the terms Ascending (ASC) and Descending (DESC).

Only fields and tables are mandatory, the others depend on the query.

Example

Here is an example query to find the details of the customers for the vehicle with an example number plate of “AB1234CD”.

Example query design
Example query design

Next: Database normalisation

Report a Glow concern
Cookie policy  Privacy policy