# INFO1003

Data Modelling Exercises

1. Data Modelling Concept Questions
1. What are the possible constraints in (a) participation (b) cardinality, and how are they represented? 2. What is the difference between a primary key, a foreign key and a composite key 3. From the combination of participation and cardinality, what are some of the representation types? 4. From the perspective of the Project entity, what is the participation and constraint as it relates to the Employees entity? How would you read this ERD altogether?

2. Data Modelling Practical Exercise
Exercise 1: Domain (~10 min)
(a) Set up the database for the Domain real estate company by first constructing an ERD. There exists an entity called PROPERTY, representing the property for sale by the agency. Each time a potential buyer makes a purchase offer on a property, the agency records the offering price, and the name of the person making the offer. Represent the PROPERTY entity and its attributes using multi-valued attributes. You must also create a primary key for every entity (even though the question may not tell you want the primary key is – always try to do this when creating ERDs). (b) Assume the agency now decides to also keep data about buyers and potential buyers, including their name, phone number and address. Augment your answer to accommodate this change.

Exercise 2: SMH (~10 min)
You are required to update Sydney Morning Herald’s database in order to keep track of their reporters and the stories they investigate. The attributes of REPORTER include: name; address; and birthday. SMH of course has numerous stories they pursue. Attributes of STORY include StoryName, and StartDate. Each reporter maybe be assigned to one or more stories, or, may not be assigned a story at all. A story must have at least one reporter assigned to it and may have any number of reporters assigned to it. A reporter’s billing rate varies by story (and consequently, which page of the newspaper it lands on). SMH has requested to be able to record the applicable billing rate (BillingRate) for each reporter when assigned to a particular story. Develop the ERD for the SMH database.

INFO1003 | FOUNDATIONS OF INFORMATION TECHNOLOGY

1

Data Modelling Exercises

Exercise 3 – Sydney Uni (~15 min)
The University of Sydney has asked you to design a database which outlines a student’s semester workload. Each student studies at least one subject. The lecturer of this subject will record the SID, name, unikey, email and degree of each student. There may be more than one lecturer per subject. The system needs to also keep track of the multiple clubs and societies a student may be a part of. As part of the workload of a subject, there may be lectures and tutorials. It is required that tutors for that subject record the attendance of students at each tutorial. Each different tutorial is identified by the tutor, time, day and room number. Within a tutorial, a student may participate in activities such as giving a presentation, work on their project, or just work on tutorial work. So that the lecturers and tutors are contactable, their emails and office number must be given to the student. Draw the corresponding ERD. You must utilise at least one associative entity.

INFO1003 | FOUNDATIONS OF INFORMATION TECHNOLOGY

2