ERDs & Data Dictionaries (SQA National 5 Computing Science): Revision Note

What is a database?

• A Database is a structured, persistent collection of data

• It allows easy storage, retrieval, and management of information

• Electronic databases offer a number of key benefits:

  • Easier to add, delete, modify and update data

  • Data can be backed up and copied easier

  • Multiple users, from multiple locations, can access the same database at the same time

• There are two main types of database

  • Flat file

  • Relational

• When creating a relational database, the design stage involves two key tools:

  1. Entity-relationship diagrams (ERDs) — to show how data is organised and linked

  2. Data dictionaries — to describe the details of each table and attribute

• Together, they help ensure the database is accurate, efficient and easy to implement

Entity-relationship diagrams (ERDs)

What is an entity-relationship diagram?

• An ERD is a diagram that shows:

  • The entities (tables) that will be in the database

  • The attributes (fields) for each entity

  • The relationships between entities

Entities and attributes

• An entity is something that data is stored about, such as Customer, Order, or Product

• Each entity has attributes, which are the pieces of data stored in that table

Attribute data types

• Each attribute must have a data type that defines what kind of data can be stored.

Attribute size

• The size controls how much data can be entered in a field

  • Example: Text(50) allows up to 50 characters

• It helps prevent errors and limits storage use

Relationships

• Relationships show how data in one table links to another

  • One-to-one (1:1) — uncommon

  • One-to-many (1:∞) — most common

  • Many-to-many (∞:∞) — must be resolved using a link table

Example

• One Customer can make many Orders, but each Order belongs to only one Customer

• This means:

  • The CustomerID is the primary key in the Customer table

  • The same CustomerID appears as a foreign key in the Order table

What is a primary key?

• A primary key is a unique field that identifies each record in a table

• No two records can share the same primary key value

• For example:

  • orderID is the primary key for the Order table

  • customerID is the primary key for the Customer table

• Each primary key allows the database to locate, update, or delete a specific record

What is a foreign key?

• A foreign key is a field in one table that refers to the primary key in another table

• It’s used to create a relationship between two entities

• For example:

  • In the Order table, customerID is a foreign key that links each order back to the customer who made it in the Customer table

• This connection enforces referential integrity, ensuring that every order relates to a real, valid customer

Data dictionary

What is a data dictionary?

• A data dictionary gives detailed information about each table in the database

• It lists all entities, their attributes, and how the data should be stored