Data models and entity relationships

Model the data before building anything: entities, attributes, entity descriptions and the degree of each relationship, drawn as an entity relationship diagram.

A11.1Databases and big dataA level55 min

At GCSE (F7.2) you split one big table of robot runs into two linked tables, and met primary and foreign keys. The tables were given to you. At A level you design them yourself: start from what the users of a system need, model the data on paper, and only then build the database. This module follows the robotics club's data all the way: from a model, to a normalised database queried in SQL, to a database shared by many users at once, and finally to data too big for one machine.

Conceptual data models

A data model describes the data a system must hold and how the items of data relate to each other. A conceptual data model says what data there is, not how it will be stored: it would be the same whether the club used a relational database, a spreadsheet or paper files. It is built from the requirements, the description of what the users need.

A conceptual model has three parts:

• An entity is a category of object, person, event or thing of interest to the system, about which data is recorded. Team, Robot, Run and Task are entities. One particular robot, Ada, is an instance of the entity Robot.
• An attribute is a property of an entity: a Robot has a name and a colour.
• A relationship is an association between two entities: a team owns robots.

Each entity needs an entity identifier: an attribute, or a set of attributes, whose value is different for every instance. It becomes the primary key when the model is built as tables.

Here are the club's requirements:

The club has several teams, each with a name. A team owns several robots, and every robot belongs to exactly one team. Each robot has a name and a colour. A robot makes many runs. A run is one attempt at one task, such as the wall or the square, and records the distance and the time. Students book robots for practice sessions: a student can book many robots, and a robot is booked by many students.

The nouns suggest the entities (team, robot, run, task, student). The descriptions of those nouns are the attributes. The verbs linking them (owns, makes, books) are the relationships.

Entity descriptions

An entity description writes an entity with its attributes in brackets, and the entity identifier underlined:

• Team (TeamID, TeamName)
• Robot (RobotID, Name, Colour, TeamID*)
• Run (RunID, RobotID*, TaskCode*, Cm, Seconds)
• Task (TaskCode, TaskName)

The asterisks mark foreign keys, the attributes that link one entity to another. Conventions for foreign keys vary; the underline on the identifier is the part to get right. Notice the foreign key always goes on the many side: each run records the one robot that made it, but a robot could not store a list of all its runs in one attribute.

The degree of a relationship

The degree of a relationship says how many instances of one entity can be linked to one instance of the other.

An entity relationship diagram (E-R diagram) draws each entity as a box and each relationship as a line. At the many end the line splits into a crow's foot; the one end is a plain line.

Read each line in both directions, and give the degree from the requirements, not from a guess. "A robot makes many runs" gives the crow's foot at Run; "a run is by one robot" gives the plain end at Robot.

Resolving a many-to-many relationship

A relational database cannot build a many-to-many relationship directly. To link Student and Robot, one of them would have to hold a list of the other in a single attribute, and attributes hold one value each. The fix is a link entity (also called a linking or junction table) between them, which turns the many-to-many into two one-to-many relationships:

• Booking (StudentID*, RobotID*, SessionDate)

The link entity's identifier combines the identifiers of both sides. Here the date is part of it too, because the same student may book the same robot on different days. An identifier made of more than one attribute is a composite key, the subject of the next lesson.

Checking a degree against data

Given some real bookings, a program can look for the evidence of "many": one value on one side paired with two or more on the other.

teams_robots = [("Hawks", "Ada"), ("Hawks", "Bolt"), ("Owls", "Cog")]

robots_of = {}
teams_of = {}
for team, robot in teams_robots:
    robots_of.setdefault(team, set()).add(robot)
    teams_of.setdefault(robot, set()).add(team)

for team in sorted(robots_of):
    print(team, "owns", sorted(robots_of[team]))
for robot in sorted(teams_of):
    print(robot, "belongs to", sorted(teams_of[robot]))

Run this in the simulator

Hawks owns two robots, so the robot side is many. No robot belongs to two teams, so this sample fits one team. But a sample can only prove that a side is many; it can never prove a side is one, because tomorrow's data might break it. The degree comes from the requirements ("every robot belongs to exactly one team"), and the data is only a check.

Task: the degree of a relationship

Each list below holds pairs (left, right) taken from the club's records. Write a function degree(pairs) that takes one list of 2-tuples and returns a string: "one-to-one", "one-to-many", "many-to-one" or "many-to-many".

• The right side is many if any left value appears with two or more different right values; otherwise it is one.
• The left side is many if any right value appears with two or more different left values; otherwise it is one.
• The string is the left side, then -to-, then the right side.

Call it on each list and print four lines, in this order, exactly like this (with the degree your function returns):

teams to robots: one-to-many
students to robots: many-to-many
robots to chargers: one-to-one
runs to robots: many-to-one

The robot does not move.

# the two lines every program starts with: the commands, then the robot
from bugbot import *
connect()

teams_robots = [("Hawks", "Ada"), ("Hawks", "Bolt"), ("Owls", "Cog"), ("Owls", "Dot")]
students_robots = [("Amir", "Ada"), ("Beth", "Ada"), ("Amir", "Cog"), ("Chen", "Bolt"), ("Beth", "Dot")]
robots_chargers = [("Ada", "C1"), ("Bolt", "C2"), ("Cog", "C3"), ("Dot", "C4")]
runs_robots = [(1, "Ada"), (2, "Ada"), (3, "Bolt"), (4, "Cog")]

Challenges

1. A school library lends books to students. Write the requirements as entity descriptions and draw the E-R diagram, resolving any many-to-many relationship.
2. Add a Sensor entity: a robot carries several sensors, and one sensor model is fitted to many robots. What link entity do you need, and what is its identifier?
3. Why would making Colour the identifier of Robot be a poor choice?