Data modelling works from a description of the requirements and aims to build a comprehensive description of the entities involved in the application and the relationships among these entities. The model constructed should ensure that all of the requirements can be met i.e. that all of the relevant data is represented and is structured in such a way that all of the operations mentioned in the requirements can be carried out.

The data model should give enough detail that a database schema can be derived from it without significant additional information being needed.

Possible data:

• people: authors, editors; organisations: publishers
• person's name, TFN, address
• books: published, unpublished
• book's title, edition, ISBN, ...
• publisher: ABN, name, address
• etc.

Possible relationships:

• author writes books (using pen-name)
• book is edited by editor
• etc.

Possible constraints:

• every person has exactly one, unique TFN
• every published work has exactly one, unique ISBN
• editors and authors must be distinct sets
• editor works for ≤ 1 publisher
• etc.

Operation: student enrols in a class.

Possible data:

• class information (day, time, location, quota, ...)
• room information (name, building, capacity, ...)
• course information (code, title, ...)
• etc.

Possible relationships:

• class enrolment (for classes in this course)
• course enrolment (for course containing class)
• etc.

Possible constraints:

• cannot enrol in more than one class for single course
• must be enrolled course before enrolling in classes
• etc.

Operation: student enrols in a course.

Possible data:

• course information (code, title, syllabus, pre-reqs, ...)
• course offering information (quota, lic, ...)
• etc.

Possible relationships:

• student enrolment (in previous/current courses)
• etc.

Possible constraints:

• cannot enrol in a full course
• cannot enrol if don't have pre-reqs
• cannot enrol in more than 24UC per semester
• etc.

Operation: system prints a student transcript.

Possible data:

• student information (name, id, ...)
• course information (code, title, UOC, ...)
• session information (year, code, dates, ...)
• etc.

Possible relationships:

• student enrolment (previous/current courses, marks/grades)
• course offering (course, session, ...)
• etc.

Possible constraints:

• none?

ER models for Researchers-WorksOn-Projects relationship.

1. the Time attribute is attached to the WorksOn relationship, which models the association between a researcher and a project that they work on ... thus, we are modelling the time that each researcher spends on each project that they are involved with

2. the Time attribute is attached to the Project entity, and so must be a measure of time associated with the project (e.g. the total time allocated to the project)

In both cases, the thick line from Project to WorksOn indicates that every project must have at least one person working on it. On the other hand, the thin line from Researcher to WorksOn suggests that not every researcher needs to be working on a project.

ER diagram for Supplier-Part scenario.

If the only information you needed on each cuisine was its name, either version would be satisfactory. However, if additional information was needed on each cuisine (e.g. country of origin or classic dish or heat-rating), then the second version would be essential, as it is the only one in which this additional information can be associated with the cuisine.

ER diagrams for Teachers-Teaches-Courses relationship.

1. Teachers may teach the same course in several semesters, and each must be recorded

   

   Note: this solution follows the idea used by some textbooks that relationships must be uniquely identified by the participating entities. The more normal view is that relationships are uniquely identified by the combination of participating entities and attributes, in which case the solution to the next part would be vaild here.

2. Teachers may teach the same course in several semesters, but only the current offering needs to be recorded (assume this in the following parts)

   

3. Every teacher must teach some course

   

4. Every teacher teaches exactly one course

   

5. Every teacher teaches exactly one course, and every course must be taught by some teacher

   

6. A course may be taught jointly by a team of teachers

   

ER diagrams for Person-Address-ElectricCompany scenario.

1. Address as attribute

   

2. Address as entity

   

   In the first example, a person was forced to be associated with exactly one address. In this example, we indicate this constraint via the arrow from LivesAt to Address and the heavy line from Person to LivesAt.

3. If the address is an attribute then it is not possible to relate electric companies directly to a home address, instead they can only be related to persons, and through them indirectly related to addresses. This does not quite capture the information that we wish to model. We wish to relate electric companies directly to home addresses, so we would model the latter as an entity rather than as an attribute. This additional information demands that we favour using an entity for modelling home addresses:

   

   The arrow from Address to ElectricCompany indicates that each home is supplied by at most one electric company.

ER diagram for Teams-Players-Fans.

ER design for trucking company.

ER design for University database, which uses the additional assumptions:

• every faculty has one dean; every school has one head
• every course has one convener; every class has one teacher
• every student must be studying one or more courses
• not all courses necessarily have classes (e.g. reading courses)
• every class is scheduled in one classroom and is associated with one course
• every class has one teacher

Assumptions:

• each person (staff or student) has an identifying number that uniquely identifies them
• each student undertakes only one degree at the University (at any one time)
• every faculty has one dean; every school has one head
• every course has one convener; every class has one teacher
• every student must be studying/attending one or more courses/classes
• every class is scheduled one classroom and is associated with one course

Some possible variations:

• degrees could be modelled as an entity (which might specify such information as title, years of study, ...); in this case, there would be a relationship between a student and a degree, rather than the degree being an attribute of the student class; note that this would also allow us to model the scenario where a student takes more than one degree

The design does not capture constraints such as:

• students only attend classes for courses they are enrolled in
• a lecturer can only be dean of a faculty containing his school

ER design for book publishing scenario:

Some of the assumptions made in developing this model:

• authors use only one pen-name for their entire career
  (if they used different pen-names for different books,
  the pen-name attribute would move to the Writes relationship)
• the only Books recorded are those which are edited and published
• both ISBN and (Title,Edition) are candidate keys for Books
• a particular edition is published by one Publisher

Some of the assumptions made in developing this model:

• every route must have some stops on it; the same stop may be used by several routes
• Info Sys people would probably suggest making Service into an entity
  (they don't seem to like n-way relationships where n > 2)
• we had to introduce a service id to make ScheduledService into an entity
  (we could have made it a weak entity tied to Route with key (number,direction,departureTime))
• we don't allow drivers to change part-way through a service

1. An attribute is a single atomic data item that forms part of a relation. In the "table view" of relations, an attribute would be a column.

2. A domain is a set of allowed values, typically associated with an attribute. Values are atomic and belong to a single data type (e.g. number, string, date). A given domain may have additional constraints defined on the data type (e.g. a person's age must be a positive integer and will be less than 150 ... although in this particular example, the upper bound is fuzzy).

3. A relation schema gives a name for the relation, names and domains for each attribute in the relation, and constraints on individual attributes and on the relation as a whole. For simplicty, and especially when talking about the relational model in the abstract, we might omit the domains and constraints and asssume that they can be inferred from the attribute names.

   E.g. full relation schema (expressed in an invented notation):

   Student(id:integer/key, name:string, degree:string, year:integer>1900)
   

   compared to an abstract relation schema

   Student(id, name, degree, year)
   

   Note that a common convention is to underline the key attributes.

4. A relational schema is a collection of relation schemas, along with additional constraints that define properties involving multiple relations (e.g. all banks must have at least one employee). Foreign key constraints also fit into this category of constraints.

5. A tuple is a collection of attribute values. In the SQL version of the relational model, some of the attribute values may be NULL; in the pure relational model, every attribute is required to have a defined value. All non-NULL values must belong to the domain corresponding to that attribute. They must also satisfy any other constraints defined in the schema.

6. A relation is a set of tuples based on a relational schema, where all tuples in the set ...

   • have the same structure as defined in the relational schema
   • satisfy all of the constraints defined in the relational schema.

7. A key is a set of attributes (often just a single attribute) that contains a distinct value for every tuple in the relation. Common single attribute keys are identifiers such as StudentID, TaxFileNumber, SocialSecurityNumber, etc. which are invented specifically for the purpose of distinguishing tuples. A multi-attribute key might be the combination of student and course in an enrolment table (i.e. you can only be enrolled in a given course once at a particular point in time)

8. A foreign key is a set of attributes (often just a single attribute) in one relation that contains the value of key attribute(s) in another relation. This is the relational model's mechanism by which one table can reference another. We say that the foreign key in table T references the corresponding primary key in table R. For a given tuple in T, the values of foreign key attributes must correspond to a primary key value that exists in the table R or be all NULL; this property is called referential integrity.

Relations are technically defined as sets, so there cannot be duplicates. They are not really "not allowed"; more like it does not make sense to talk about duplicate values in a set.

R(a1, a2, a3, a4)
S(b1, b2, b3)

R(1, a, b, c)  R(2, a, b, c)  R(1, x, y, z)

R(3, x, NULL, y)  R(NULL, x, y, z)

S(1, 2, x)  S(1, NULL, y)  S(2, 1, z)

• R(1, a, b, c) is legal
• R(2, a, b, c) is legal; duplicate non-key attribute values are allowed
• R(1, x, y, z) is not legal if R(1, a, b, c) exists; duplicate key value
• R(3, x, NULL, y) is legal; non-key attribute values may be MULL
• R(NULL, x, y, z) is not legal; key attribute values cannot be NULL (entity integrity)
• S(1, 2, x) is legal
• S(1, NULL, y) is not legal; key attribute values cannot be NULL (entity integrity)
• S(2, 1, z) is legal; has a different key to S(1, 2, x)

Person(zID, zPass, familyName, givenName, dateOfBirth, countryOfBirth, ...)
Student(zID, degreeCode, WAM, ...)
Staff(zID, office, phone, position, ...)
Course(cID, code, term, title, UOC, convenor)
Room(rID, code, name, building, capacity)
Enrolment(course, student, mark, grade)

• Person.zID ... primary key, 7-digit number (e.g. 5012345)
• Person.zPass ... encrypted string
• Person.familyName ... text string, typically < 50 chars
• Person.givenName ... text string, typically < 50 chars
• Person.dateOfBirth ... date (e.g. 12-Jan-1990)
• Person.countryOfBirth ... valid country name, or maybe a foreign key referencing a Country table
• Student.zID ... primary key, foreign key to Person.zID; same domain (7-digit nuber)
• Student.degreeCode ... 4-digit number (e.g. 3707, 3778)
• Student.WAM ... floating point number in range 0..100
• Staff.zID ... primary key, foreign key to Person.zID; same domain (7-digit nuber)
• Staff.office ... foreign key to Room.rID
• Staff.phone ... string looking like 9385-xxxx
• Staff.position ... string (e.g. Professor), or foreign key to a Position table
• Course.cID ... primary key, integer, maybe internally generated
• Course.code ... 8-char string (e.g. COMP3311)
• Course.term ... 4-char string (e.g. 20T3), or foreign key to a Term table
• Course.title ... text string
• Course.UOC ... integer (3,6,12,18)
• Course.convenor ... foreign key to Staff.zID
• Room.rID ... primary key, integer, maybe internally generated
• Room.code ... short sting (e.g. G03)
• Room.name ... text string (e.g. Ainsworth Theatre)
• Room.building ... text string (e.g. K17), or foreign key to Building relation
• Room.capacity ... integer (between 0 and 1500)
• Enrolment.course ... foreign key to Course.cID, part of primary key
• Enrolment.student ... foreign key to Student.zID, part of primary key
• Enrolment.mark ... floating point number in range 0..100
• Enrolment.grade ... two-char string (e.g. HD,DN,CR,PS,FL,WD,LE,...)