IMS1805 Systems Analysis
Semester 2, 2005: Week 7 Tutorial Exercise
Entity-Relationship Modeling
Aims
This tutorial aims to give you some exercises which will help you look into and understand in more detail the issues involved in data modeling – what a data analyst does, why they do it, and how they do it.
Tutorial Tasks
Complete the following exercises. You might find it easier to get started by doing each exercise in groups of 3-4, but you can decide this with your tutor. Note that the important thing is not your ability to complete the analysis, but your ability to be able to make a start and to understand what the data model is trying to do and why that is a necessary part of system development. Compare your answers with other students/groups and discuss any differences in your answers and approach.
Note that although in each exercise you are heading towards the same end point – a data model - each problem has a different starting point: No. 1 starts with a list of data elements, No. 2 with some business documents, and No. 3 with a set of existing data tables. I have not done this to confuse you, but in the hope that the different approaches may help you to get a better understanding of the purpose of a data model and the way you can go about developing it. If you find one problem is very hard to get started with, try one of the others and maybe that starting point will be easier for you to understand.
You will probably not have the time to get through each exercise in the tutorial class; your aim should be to do at least one or two in detail in the tute, and you can do the others in your own time.
Note that each of these problems is easy enough that you should be able to make a start at it, but each also some difficult aspects which mean that you may not be able to finish it. Don’t get too despairing if your analysis gets a bit complicated – in fact if you find them all really easy, you are missing some important elements of the problem.
1. This problem is a re-visit to the problem described in last week’s tute exerciseon the needs for recording student results. An enthusiastic tutor who knows nothing about data modeling tries to set up a file in which to record the information needed by the lecturer. He creates an Access database and puts in a few data elements to give you the idea(I have shown it as columns of data items rather than rows, because I can’t fit a row across the page!)
Year / 2004 / 2004 / 2004
Semester / 1 / 1 / 1
Campus / Caulfield / Caulfield / Caulfield
Unit Code / IMS1805 / IMS1805 / IMS1805
Unit Name / Systems Analysis / Systems Analysis / Systems Analysis
Student ID / 123456578 / 87654321 / 18273645
Student first name / Fred / Douglas / Isaac
Student surname / Aarons / Adams / Asimov
Assignment 1 mark
Assignment 1 as % of final mark / 25 / 25 / 25
Assignment 2 mark
Assignment 2 as % of final mark / 25 / 25 / 25
Assignment 1 grade
Assignment 2 grade
Overall assignment mark
Overall assignment grade
Tute number / 1 / 3 / 4
Tute location / A2.12 / K2.08 / F4.25
Tute time / 10:00 / 12:00 / 10:00
Exam mark Q1 / 4
Exam mark Q2 / 3
Exam mark Q3 / 8
Exam mark Q4 / 5
Exam mark Q5 / 9
Exam mark Q6 / 7
Exam mark Q7 / 7
Exam mark Q8 / 8
Overall exam mark
Overall exam grade
Exam as % of final mark / 50 / 50 / 50
Final mark for unit
Final grade for unit
Student course code / 3323 / 3323 / 2330
Student course name / BIS / BIS / BComputing
Student e-mail address /
Enrolment type (full/part time) / F/t / F/t / F/t
On or off-campus student? / On / On / On
Has student applied for special consideration? / No / No / Yes
Tutor name / Ann / Martin / Manoj
This table is very complete in its coverage of all the data elements, but it does not do a very good job of distinguishing between the different types of data – ie the entities and their associated attributes. Can you:
(a) combine these data elements together into groups as attributes of different entities;
(b) identify the links which will be needed between the entities;
(c) draw the E-R model
2. Business documents are one of the best sources of information about data needs. Any good data modeler will have a careful look at all available business documents to get some idea of the sort of data which the system records.
Have a look at the attached business documents, and for each one, identify the data elements (attributes) shown on it, and try to start to identify the entities with which they would be associated. Note that you do not have enough information about the business to be able to go very far in deciding what the right entities, attributes and relationships should be, but you should be able to have a go at getting started.
Document (a) is a supermarket receipt from a transaction at the local supermarket.
Document (b) is a membership renewal form for the Royal Automobile Club of Victoria, which is a car club to which I belong. It offers services to car owners such as emergency help if your car breaks down (various levels of support are offered - roadside care, extra care and total care).
Document (c) is a bill from the water supply and drainage company which looks after providing these services to my area. I have shown both sides of the bill; do the data on the front first, because it is probably a bit easier to deal with than the data on the back.
3. The Commonwealth Games are on in Melbourne next year, and a group of Monash students are interested in keeping track of the results and in watching some of the events. One of the students decides to help the group by developing a simple database of the information about Games events. Unfortunately the student does not know anything about data analysis, so he develops the system in an Access database without first doing any entity relationship modeling. Following are the files he develops in Access, showing the first few records in each file.
(i) This table records when each event is on
Event / Day / TimeRoad race / Saturday / 7:30
4*100 metres relay / Sunday / 11:30
200 metres / Tuesday / 2:50
400 metres / Sunday / 3:50
(ii) This table records all the Australians competing, and how they went
Event / Australian competitor / Result400 metres / I Thorpe / First
400 metres / G Hackett / Second
Road race / R McEwen / 11
Trap shooting / N Vella / 3
(iii) This table records the winner of every event
Event /World record
/Winner
/Time
100 metres / 9.81 / K Collins / 9.85400 metres / 3:41.86 / I Thorpe / 3:46.25
Road race / C Bettini
Trap shooting / A Alipov
(iv) This table records how many medals have been wonby athletes of each country
Country /Gold
/Silver
/Bronze
/Total
Mauritius / 5 / 2 / 1 / 8Australia / 4 / 1 / 3 / 8
India / 4 / 1 / 0 / 5
New Zealand / 2 / 1 / 0 / 3
These tables do provide the means to record some information, but the developer has not thought carefully enough about the sort of data (entities, attributes, data types) which are required.
(a) What problems can you see both for people who try to enter data into the tables, and for users who are trying to find out things they need to know? (Hint: try adding some data for different events, OR try thinking up some of the things you might want to know and would not be able to get from the tables as they stand at present).
(b) use your answer to (a) to identify some of the entities and attributes which are missing, and some of the links between these entities which would be needed to meet likely user information needs
2(a) Supermarket receipt
2(b)RACV Membership renewal
2(c) Water Supply Bill (front)
2(c) Water Supply bill (back)