FIT9132 Introduction to Databases
2017 Semester 2
Assignment 1 – Database Design – Run Monash (RM)
Run Monash (RM) is a running carnival which is held several times a year across various Monash campuses and external locations. Anyone can attend an RM Carnival. A carnival is run on a particular date, in a particular location and only lasts for one day. During a carnival a range of events are offered from the following list:
● Marathon 42.2 Km
● Half Marathon 21.1 Km
● 10 Km Run
● 5KmRun
● 3 Km Community Run/Walk
Not all events are run at each carnival. Each carnival is sponsored by a range of companies and organisations. Appendix A shows some of the details for the 2017 Summer Series Run Monash.
Competitors enter via the Run Monash web site. When a competitor initially registers for Run Monash, they are assigned a unique competitor number. Competitor’s numbers consist of a C followed by four digits eg C1122. Appendix B shows the online forms used by people who wish to register as Run Monash competitors.
After a competitor has registered for Run Monash, they will be sent details of each upcoming carnival and the range of events which will be offered. Competitors can log in to the Run Monash site and enter for a particular event within a carnival (see Appendix C). Every entry is assigned a unique entry number (e.g., 3021). Using official timing devices at the carnival, Run Monash records the entrants finishing time and their place within the event. These details are posted on the Run Monash result site the day following the carnival.
A major focus of the Run Monash Carnivals is to raise funds for various charities. When a competitor enters an event, they may nominate a charity for which they will raise funds (not all competitors will select a charity for each event they enter). Competitors who have entered an event can also form teams to support their training and run as a group. The first competitor to register a team for a given carnival is assigned as the Team Manager. This team manager can then add other competitors who have entered the carnival to the team. Teams are identified by a unique team name which the manager must select when they first create the team. Team names are unique within a carnival, however, a given team name may be reused by different competitors in a different carnival as teams are recreated for each carnival depending on which competitors have entered an event for the carnival. Run Monash wishes to record, as part of the stored data, how many members are on each team. Teams may also nominate a charity for which they will raise funds, although not all teams will do so. All charities for which funds can be raised must first be approved by Run Monash.
Page 1 of 9
As part of the entry process for a carnival, competitors can purchase a range of carnival specific merchandise such as running gear, drink bottles etc. Run Monash keep track of the current stock of each item of merchandise they make available. Appendix D shows an invoice from a competitor who has entered an event.
The successful operation of the carnivals depends on a group of volunteers who carry out the various functions leading up to the competition day and on the day itself. People who would like to volunteer for the Run Monash carnivals register on the Run Monash site. Each volunteer is assigned a unique volunteer id – their name, contact address and phone number are also recorded. Volunteer numbers consist of a V followed by three digits e.g., V123. After registering as a volunteer the Run Monash management team contacts volunteers and assigns them a particular role within a particular carnival e.g. timing official, race marshall, first aid, etc. The volunteers are paid a fixed stipend for the carnival they agree to work in (all volunteers regardless of role are paid the same stipend although this can vary across carnivals). Also, in recognition of their efforts, volunteers can purchase carnival merchandise at a reduced rate of 50% of the listed item cost. Appendix E shows an invoice from a volunteer.
Appendix A
Run Monash Summer Series
Carnival Date: February 15th 2017
Carnival Director: Mary Imparo
Carnival Location: The Tan, Botanic Gardens, Melbourne, Victoria Events Offered: 10 Km Run, 5 Km Run, 3 Km Community Run/Walk
Sponsor Name
Naming Sponsor
Amount of Sponsorship
Sponsor Contact
Sponsor Phone Contact
VegOut
Yes
$20,000
Mary Wie
03 99012323
Dept Sport and Recreation
No
$15,000
Peter Pierre
03 99090210
Page 2 of 9
Appendix B Competitor Registration Screens (note this process is made up of three screens – the three screens should be dealt with in one normalisation process using all data across the three screens). When the person entering their details clicks on “Register” on screen B3 they are assigned their RM competitor number. As a consequence you may add this number to your normalisation.
Screen B1:
Page 3 of 9
Screen B2:
Page 4 of 9
Screen B3:
Page 5 of 9
Appendix C Competitor Entry for a Carnival
Page 6 of 9
Appendix D Merchandise Sale
VegOut Run Monash Summer Series
Carnival Date: February 15th 2017
Carnival Location: The Tan, Botanic Gardens, Melbourne, Victoria
For: C1234 Name: Sansa Stark
Order Number: 408 Order Date: 1 Feb 2017
Merchandise
Item Code
Item Description
Cost
Qty
LineCost
WHR-G-MED
Women’s HR Tee Shirt – Green – Medium
$45.00
1
$45.00
EFM
Engraved Finishers Medal
$15.00
1
$15.00
Pickup or Delivery
Delivery
$10.00
Total Cost:
$70.00
Appendix E Merchandise Sale
VegOut Run Monash Summer Series
Carnival Date: February 15th 2017
Carnival Location: The Tan, Botanic Gardens, Melbourne, Victoria
For: V110 Name: Tyrion Lannister
Order Number: 512 Order Date: 10 Feb 2017
Merchandise
Item Code
Item Description
Cost
Qty
LineCost
MHR-R-SML
Men’s HR Tee Shirt – Red – Small
$20.00
2
$40.00
WHR-G-MED
Women’s HR Tee Shirt – Green – Medium
$22.50
1
$22.50
Pickup or Delivery
Pickup
$0.00
Total Cost:
$62.50
Page 7 of 9
TASKS
1. Using LucidChart, prior to completing task 2 (the normalisation), prepare an initial conceptual model (Entity Relationship Diagram) for Run Monash (RM). For this initial conceptual model, only include what you see as identifiers (keys) for each entity , do not include other attributes at this stage. Surrogate keys must not be added to this model. Participation and connectivity for all relationships must be shown on the diagram . (10 marks)
2. Perform normalisation to the 3NF for each set of data depicted in Appendix A – D. Consolidate all the found 3NF relations into a final set of relations.
During normalisation:
a. You must not add surrogate keys to the normalisation.
b. Clearly identify the Primary Key in all relations.
c. Clearly identify the partial and transitive dependencies (if they exist) in all 1NF
relations. You may use a dependency diagram or use your own notation (see the week 5 sample solution for a possible alternative representation). (30 marks)
3. Using your initial conceptual model and the result from your normalisation, prepare a logical level design for the RM database. The logical model must be drawn using the Oracle Data Modeler. The information engineering or Crow’s foot notation must be used in drawing the model. All attributes must be commen ted in the database. Sequences must be used to generate primary key and check clauses must be applied to attributes where appropriate. Be sure to include the legend as part of your model. (50 marks)
4. Generate the schema for the database in Oracle Data Modeler and use the schema to create the database in your Oracle account. The only edit you are permitted to carry out to the generated schema file is to add header comment/s containing your details (student name/id) and drop sequence commands. Capture the output of the schema statements using the spool command. Ensure your generated script includes drop table statements. Name the schema file as rm_schema.sql. (10 marks)
Page 8 of 9
Submission Requirements
Due: Friday 8-SEP-2017 11 PM (week 7)
The following files are to be submitted:
● ●
● ●
● ● ●
A pdf file containing your conceptual model. Name the file rm_conceptual.pdf
A pdf file containing your full normalisation for each form/document showing clearly UNF, 1NF, 2NF and 3NF, your dependency diagrams and your final synthesis of all 3NF relations. Name the file rm_normalisation.pdf.
A pdf file containing the final logical Model you created in Oracle Data Modeller. Name the file rm_logical.pdf.
A zip file containing your Oracle data modeler project (in zipping these files be sure you include the .dmd file and the folder of the same name). Name the file rm_OracleModel.zip. This model must be able to be opened by your marker and contain your full model otherwise your task 3 will not be marked . For this reason, you should carefully check that your model is complete – as a suggestion you should take your submission archive, copy it to a new temporary folder, extract your submission parts, extract your model and ensure it opens correctly before submission.
A schema file (CREATE TABLE statements) generated by Oracle Data Modeller. Name the file rm_schema.sql
The output from SQL Developer spool command showing the tables have been created. Name the file rm_schema_output.txt
A pdf document containing any assumptions you have made in developing the model or comments your marker should be aware of. Name the file rm_assumptions.pdf
Note that there are seven required files . These files must be zipped into a single zip file named a1-.zip e.g., a1-xyz123.zip before the assignment due date/time. Submit the a1-xyz123.zip to Moodle before the due date.
Late submission will incur penalties as outlined in the unit guide .
Page 9 of 9