Conceptual data models and entity relationship modelling

Overview

Recall questions on entities and their occurrences, the three degrees of relationship, and resolving a many-to-many relationship with a link entity.

What you need to know

Answer each one before you reveal it.

Questions from this subtopic

Each one with the model answer underneath.

1. 1

   What is a data model, and what does producing one from a set of data requirements involve?

   Model answer

   A data model is a description of the data a system must hold, independent of how it will eventually be stored. Producing one from given requirements means reading the description of the scenario and identifying the entities — the distinct kinds of thing data must be kept about; the attributes of each entity — the individual items of data recorded about it; an identifier for each entity — the attribute or attributes whose values distinguish one occurrence from every other; and the relationships between the entities, together with the degree of each. The result is usually presented as an entity relationship diagram plus a set of entity descriptions.

2. 2

   What is an entity, and how does an entity differ from an occurrence of that entity?

   Model answer

   An entity is a category of thing about which data is stored — Student, Book, Booking. An occurrence, sometimes called an instance, is one particular member of that category — the student Aisha Khan, the copy of a book with accession number 41207. The entity is what becomes a table in the eventual database; the occurrence is what becomes one row in it. Entity names are written in the singular.

3. 3

   Describe how an entity relationship diagram is drawn.

   Model answer

   Each entity is drawn as a rectangle containing the entity name. A relationship between two entities is drawn as a straight line joining their rectangles, and the line is labelled with the nature of the relationship, read in a direction, for example 'a member borrows a copy'. The degree of the relationship is shown by the way each end of the line is marked: a plain single end means one, and a crow's foot — the line splitting into three short prongs where it meets the rectangle — means many. So a line with a plain end at Member and a crow's foot at Loan means one member has many loans.

4. 4

   Describe the three degrees of relationship and how each appears on an entity relationship diagram.

   Model answer

   One-to-one: each occurrence of the first entity is related to at most one occurrence of the second, and the other way round — for example Employee to CompanyCar where each employee has at most one car and each car is assigned to at most one employee. Drawn as a plain line with a single end at both entities. One-to-many: one occurrence of the first entity relates to many of the second, but each occurrence of the second relates to only one of the first — for example one Customer places many Orders. Drawn with a plain end at Customer and a crow's foot at Order. Many-to-many: occurrences on each side may relate to many on the other — for example Student to Course, since a student takes several courses and a course has several students. Drawn with a crow's foot at both ends.

5. 5

   Why can a many-to-many relationship not be implemented directly, and what is done about it?

   Model answer

   It cannot be implemented directly because there is nowhere to put the link. To connect the two tables one of them would have to hold a key value of the other, and with many on each side a single row would need to hold many such values, which breaks the requirement that each cell holds one atomic value. The relationship is therefore resolved by introducing a link entity between the two, which holds one occurrence for each pairing. The original many-to-many becomes two one-to-many relationships, one from each original entity into the link entity, and any data belonging to the pairing itself is stored there.

6. 6

   Resolve the many-to-many relationship between Student and Course, and describe the resulting diagram.

   Model answer

   Introduce a link entity Enrolment, holding one occurrence for each student taking each course. The diagram then has three rectangles: Student, Enrolment and Course, with Student joined to Enrolment and Course joined to Enrolment. Each of those two lines has a plain end at the original entity and a crow's foot at Enrolment, because one student has many enrolments and one course has many enrolments, while each enrolment concerns exactly one student and one course. The entity descriptions are Student(StudentID, Forename, Surname, TutorGroup), Course(CourseCode, CourseTitle, Level) and Enrolment(StudentID, CourseCode, DateEnrolled), with StudentID underlined in Student, CourseCode underlined in Course, and both StudentID and CourseCode underlined in Enrolment.

Other ways to work this

The same content, four other ways.

What is inside Levo

Six ways to work the same material.