Assignment 2 – Database Design – Traffic Demerit System (TDS)
This task continues the work you have started in assignment 1 by refining/extending the model you developed and implementing it as a set of tables under your Monash Oracle database account. Since this is an ongoing development process based on your assignment 1 submission and marker feedback, you must ensure that your submission and the marker feedback remains confidential and is only seen by yourself and the FIT9132 teaching staff.
Assignment 2’s brief must be read in conjunction with the assignment 1 brief – ie your final model must encompass both sets of requirements.
You may modify your assignment 1 conceptual model in any manner you wish as you work through assignment 2, provided your final model meets both sets of requirements.
Further discussions with the local government authority have revealed that some demerits, particularly blood alcohol charges (BAC), are regarded as serious demerits and lead to an immediate licence cancellation. As well as recording the demerit details you have depicted in assignment 1, the authority needs to have recorded, for each demerit, if the demerit leads to an automatic cancellation and for those that do, the number of months for which the licence will be cancelled. When a licence is cancelled, its expiry date is set to the date of the offence which triggered the cancellation. Cancelled drivers need to apply to a court to get their licence back. If granted the driver will be reinstated under their old licence number.
The local government authority have provided three of the forms, depicted below, which they make use of showing some of the data they wish to record:
● a sample vehicle report,
● two sample driver reports, and
● a driver cancellation report.
Page 1 of 10
Sample Vehicle Report
* Note that the local government authority has indicated that it needs flexibility in the design to be able to add new, and remove current, vehicle types as circumstances change.
Page 2 of 10
Sample Driver Reports – Report A
* Note that the local government authority has indicated that it needs flexibility in the design to be able to add new, and remove current, licence types as circumstances change. The licence types match the same set of selections as vehicle types, since drivers are licenced for a vehicle type.
Page 3 of 10
Sample Driver Reports – Report B
* Note that the local government authority has indicated that it needs flexibility in the design to be able to add new, and remove current, licence types as circumstances change.The licence types match the same set of selections as vehicle types, since drivers are licenced for a vehicle type.
Page 4 of 10
Sample Cancellation Report
Task to complete:
2. Based on your assignment 1 conceptual model, your markers feedback, your reading of this case study and the normalisations you carried out in step 1 above, prepare a logical level designfor the Traffic Demerits System 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. Your logical model must notshow datatypes.
○ All relations depicted must be in 3NF
○ You are required to add at least one surrogate key to your design (you are free to select the most appropriate relation to make this change in)
○ All attributes must be commented in the database(ie. the comments must be part of the table structure, not simply comments in the schema file).
○ Check clauses/look up tables must be applied to attributes where appropriate.
○ You MUSTinclude the legend as part of your model.
○ Note that your GIT repository must clearly indicate your development history with multiple commits/pushes as you work on your model.
3. Generate the schema for the databasein Oracle Data Modeler and use the schema to create the database in your Oracle account. Theonlyedit you are permitted to carry out to the generated schema file is to add header comment/s containing your details (student name/id) and the commands to spool/echo your run of the script.
○ Capture the output of the schema statements using the spool command.
○ Ensure your script includes drop table statements at the start of the script.
○ Name the schema file as tds_schema.sql.
Page 6 of 10
The following files are to be submitted and must existin your FITGitLab server repo, along with the source documents from which they were generated:
● A single page pdf file containing the final logical Model you created in Oracle Data Modeller. Name the file tds_logical.pdf. This pdf must be created via File – Data Modeler – Print Diagram – To PDF File from within SQL Developer, do not use screen capture.
● 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 tds_oraclemodel.zip.
○ Part of the assessment of your submission will involve your marker extracting your model from this zip, opening it in SQL Developer Data Modeller, engineering to a new Relational model and from this your marker will generate a schema which will then be compared with your submitted schema (they must be the same for your schema to be accepted). For this reason your model must be able to be opened by your marker and contain your full modelotherwise your task 2 will not be able to be marked. For this reason, you MUSTcarefully check that your model is complete – ensure to take your submission archive, copy it to a new temporary folder, extract your submission parts, extract your model and ensure it opens correctlybeforesubmission.
● A schema file (CREATE TABLE statements) generated by Oracle Data Modeller. Name the file tds_schema.sql
● The output from SQL Developer spool command showing the tables have been created. Name the file tds_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 tds_assumptions.pdf
These files must be zipped into a single zip file named a2-
Page 7 of 10
Page 8 of 10