In the context of database application development (aka database engineering), what are the aims of data modelling?

Describe the similarities and differences between the following similarly-named concepts:

1. relationship in the entity-relationship data model

2. relation in the relational data model

What kind of data, relationships and constraints exist in this scenario?

• for each person, we need to record their tax file number (TFN), their real name, and their address
• everyone who earns money in Australia has a distinct tax file number
• authors write books, and may publish books using a ``pen-name'' (a name which appears as the author of the book and is different to their real name)
• editors ensure that books are written in a manner that is suitable for publication
• every editor works for just one publisher
• editors and authors have quite different skills; someone who is an editor cannot be an author, and vice versa
• a book may have several authors, just one author, or no authors (published anonymously)
• every book has one editor assigned to it, who liaises with the author(s) in getting the book ready for publication
• each book has a title, and an edition number (e.g. 1st, 2nd, 3rd)
• each published book is assigned a unique 13-digit number (its ISBN); different editions of the same book will have different ISBNs
• publishers are companies that publish (market/distribute) books
• each publisher is required to have a unique Australian business number (ABN)
• a publisher also has a name and address that need to be recorded
• a particular edition of a book is published by exactly one publisher

Consider some typical operations in the myUNSW system ...

• student enrols in a lab class
• student enrols in a course
• system prints a student transcript

For each of these operations:

1. identify what data items are required
2. consider relationships between these data items
3. consider constraints on the data and relationships

Researchers work on different research projects, and the connection between them can be modelled by a WorksOn relationship. Consider the following two different ER diagrams to represent this situation.

Describe the different semantics suggested by each of these diagrams.

Draw an ER diagram for the following application from the manufacturing industry:

• Each supplier has a unique name.
• More than one supplier can be located in the same city.
• Each part has a unique part number.
• Each part has a colour.
• A supplier can supply more than one part.
• A part can be supplied by more than one supplier.
• A supplier can supply a fixed quantity of each part.

The following two ER diagrams give alternative design choices for associating a person with their favourite types of food. Explain when you might choose to use the second rather than the first:

[Based on RG 2.2] Consider a relationship Teaches between teachers and courses. For each situation described below, give an ER diagram that accurately models that situation:

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

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

You may assume that the only attribute of interest for teachers is their staff number while for courses it is the course code (e.g. COMP3311). You may introduce any new attributes, entities and relationships that you think are necessary.

Assume there is a Person entity type. Each person has a home address. More than one person can live at the same home address.

1. Create two, different ER diagrams to depict Persons and their addresses, one with Address as an attribute, the other with Address as an entity.

2. Why would we choose one rather than the other?

3. Assume that we have a ElectricCompany entity type. Only one of these companies supplies power to each home address. Add that information to each ER diagram.

[Based on GUW 2.1.3] Give an ER design for a database recording information about teams, players, and their fans, including:

1. For each team, its name, its players, its captain (one of its players) and the colours of its uniform.

2. For each player, their name and team.

3. For each fan, their name, favourite teams, favourite players, and favourite colour.

A trucking company called Truckers is responsible for picking up shipments from the warehouses of a retail chain called Maze Brothers and delivering the shipments to individual retail store locations of Maze Brothers. Currently there are 6 warehouse locations and 45 Maze Brothers retail stores. A truck may carry several shipments during a single trip, which is identified by a Trip#, and delivers those shipments to multiple stores. Each shipment is identified by a Shipment# and includes data on shipment volume, weight, destination, etc. Trucks have different capacities for both the volumes they can hold and the weights they can carry. The Truckers company currently has 150 trucks, and a truck makes 3 to 4 trips each week. A database - to be used by both Truckers and Maze Brothers - is being designed to keep track of truck usage and deliveries and to help in scheduling trucks to provide timely deliveries to the stores.

Design an ER model for the above application. State all assumptions.

Give an ER design for a University administration database that records information about faculties, schools, lecturers, students, courses, classes, buildings, rooms, marks. The model needs to include:

1. for each faculty, its name, its schools and its dean

2. for each school, its name, the location of its school office, its head and its academic staff

3. for each lecturer, their names, bithdate, position, staff number, school, office, the courses they have convened, and the classes they have run

4. for each student, their names, birthdate, student number, degree enrolled in, courses studied, and marks for each course

5. for each course, its code, its name, the session it was offered, its lecturer(s), its students, its classes

6. for each class, what kind of class (lecture, tutorial, lab class, ...), its day and time (starting and finishing), who teaches it, which students attend it, where it's held

7. for each building, its name and map reference

8. for each room, its name, its capacity, type of room (office, lecture theatre, tutorial room, laboratory, ...) and the building where it is located

An assumption: staff and student numbers are unique over the union of the sets of staff and student numbers (i.e. each person has a unique identifying number within the University).

Another assumption: the lecturer who convenes a course would be called lecturer-in-charge at UNSW; lecturers typically teach classes in the courses they convene; they may also teach classes in other courses; a given class is only taught by one lecturer.

State all other assumptions.

Give an ER design to model the following scenario ...

• for each person, we need to record their tax file number (TFN), their real name, and their address
• everyone who earns money in Australia has a distinct tax file number
• authors write books, and may publish books using a ``pen-name'' (a name which appears as the author of the book and is different to their real name)
• editors ensure that books are written in a manner that is suitable for publication
• every editor works for just one publisher
• editors and authors have quite different skills; someone who is an editor cannot be an author, and vice versa
• a book may have several authors, just one author, or no authors (published anonymously)
• every book has one editor assigned to it, who liaises with the author(s) in getting the book ready for publication
• each book has a title, and an edition number (e.g. 1st, 2nd, 3rd)
• each published book is assigned a unique 13-digit number (its ISBN); different editions of the same book will have different ISBNs
• publishers are companies that publish (market/distribute) books
• each publisher is required to have a unique Australian business number (ABN)
• a publisher also has a name and address that need to be recorded
• a particular edition of a book is published by exactly one publisher

State all assumptions used in developing your data model.

Give an ER design to model the following scenario ...

• a driver has an employee id, a name and a birthday
• a bus has a make, model, registration number and capacity
  (e.g. a Volvo 425D bus which can carry 60 passengers, with registration MO-3235)
• a bus may also have features (e.g. air-conditioned, disabled access, video screens, etc.)
• a bus-stop (normally abbreviated to simply stop) is a defined place where a bus may stop to pick up or set down passengers
• each stop has a name, which is displayed on the timetable (e.g. ``Central Station'')
• each stop also has a location (street address) (e.g. ``North side of Eddy Avenue'')
• a route describes a sequence of one or more stops that a bus will follow
• each route has a number (e.g. route 372, from Coogee to Circular Quay)
• each route has a direction: ``inbound'' or ``outbound''
  (e.g. 372 Coogee to Circular Quay is ``inbound'', 372 Circular Quay to Coogee is ``outbound'')
• for each stop on a route, we note how long it should take to reach that stop from the first stop
• the time-to-reach the first stop on a route is zero
• stops may be used on several routes; some stops may not (currently) be used on any route
• a schedule specifies an instance of a route (e.g. the 372 departing Circular Quay at 10:05am)
• schedules are used to produce the timetables displayed on bus-stops
• a service denotes a specific bus running on a specific schedule on a particular day with a particular driver
• services are used internally by the bus company to keep track of bus/driver allocations
• the number of minutes that each bus service arrives late at its final stop needs to be recorded

State all assumptions used in developing your data model.

Describe each of the following core components of the relational model:

1. attribute
2. domain
3. relation schema
4. relational schema
5. tuple
6. relation
7. key
8. foreign key

Why are duplicate tuples not allowed in relations?

Consider the following simple relational schema:

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

which of the following tuples are not legal in this schema? Explain why the iillegal tuples are invalid.

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)

Consider the following relations which form a tiny part of the schema for the MyUNSW database:

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)

Identify all of the primary keys and foreign keys, and suggest suitable domains for each attribute. You can introduce new relations if you think would likely be used to represent objects not in the current tables. Discuss which attributes could have NULL values, and the circumstances under which this might occur.