The answersDownload the PDF
Worksheet

A11.1 Data models and entity relationships

Databases and big data · A level · OCR H446 1.3.2, AQA 7517 4.10.1, Eduqas A500QS 2.5 · about 55 min

BugBotLab
NameClassDate

What this lesson is about

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

Questions 6 marks in all

  1. [1 mark]In a data model, what is an entity?

    1. AA category of object, person, event or thing about which data is recorded
    2. BA single property, such as a robot's colour
    3. CA line joining two tables
    4. DOne row of a table

  2. [1 mark]A team owns many robots, and each robot belongs to exactly one team. What is the degree of the relationship from Team to Robot?

    1. AOne-to-many
    2. BMany-to-one
    3. COne-to-one
    4. DMany-to-many

  3. [1 mark]In an E-R diagram of that relationship, where is the crow's foot drawn?

    1. AAt the Robot end
    2. BAt the Team end
    3. CAt both ends
    4. DAt neither end

  4. [1 mark]How is a many-to-many relationship between Student and Robot implemented in a relational database?

    1. AWith a link entity, such as Booking, holding both keys, giving two one-to-many relationships
    2. BBy storing a list of robot IDs in one Student attribute
    3. CBy merging Student and Robot into one table
    4. DIt cannot be stored at all

  5. [1 mark]In the entity description Robot (RobotID, Name, Colour, TeamID), which attribute should be underlined?

  6. [1 mark]A sample of bookings shows no student has booked more than one robot. What can you conclude?

    1. ANothing certain: a sample can show a side is many, but only the requirements can say it is one
    2. BThe relationship is one-to-one
    3. CThe relationship is one-to-many
    4. DStudents cannot book robots

The 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): 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")]

Plan your program here, then type it in and press Run.

QR code
Do it on the robot
www.bugbotlab.com/learn/a11-1-entity-relationship-modelling/
The simulator checks it and tells you when it passes. Nothing to install, no account.

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?