FIT2094 Databases
Normalisation and Logical Database Design - Clean Up Inc (CUI)
FACULTY OF INFORMATION TECHNOLOGY
Given the provided case study from assignment 1A, and additional forms/documents related to the case study, students will be asked to transform the information provided into a sound database design and implement it in Oracle. This task covers learning outcomes:
1. Apply the theories of the relational database model.
2. Develop a sound relational database design.
3. Implement a relational database based on a sound database
design.
Your task
This is an open book, group task (students will work in groups of two or three students with members selected randomly). The final output for this task will be a logical model implemented in the Oracle RDBMS
Value
20% of your total marks for the unit (includes individual Design Portfolio)
Due Date
Task Submission: Wed, 3rd May 2023, 4:30pm (AEST)/ 2:30pm (MYT)
Self and Group Evaluation (Feedback Fruit): Wed, 10th May 2023, 11:55 pm (AEST) / 9:55pm (MYT) - (no late submission permitted)
Submission
● Via Moodle Assignment Submission.
● FIT GitLab check ins will be used to assess history of development
Assessment Criteria
● Normalise the supplied case study documents and integrate the resultant relations into a logical model.
● Depict the data requirements expressed in the case study via a relational database logical model.
● Generate a schema which meets the case study data requirements from the logical model produced
● Consistent use of industry standard notation and convention
Late Penalties
● 10% deduction per calendar day or part thereof for up to one week
● Submissions more than 7 calendar days after the due date will receive a
mark of zero (0) and no assessment feedback will be provided.
● 0 marks for peer evaluation component (see marking guide) if the Self
and Group Evaluation is not completed by the due date (no late submission permitted)
Support Resources
See Moodle Assessment page
Feedback
Feedback will be provided on student work via:
● general cohort performance
● specific student feedback ten working days post submission
● a sample solution following assignment 1B marking
Page 1 of 14
INSTRUCTIONS
Please note that your group must not start the modelling task until each member individually has completed the week 6 Applied logical model for the property rental case study, pushed it to their private repo, and compared your answer with that provided in the sample solution to check their understanding.
This task continues the work you have started in assignment 1A 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 1A submission and marker feedback, you must ensure that your assignment 1A submission and the marker feedback remains confidential and is only seen by the members of your group and the unit teaching staff.
Assignment 1B's brief must be read in conjunction with the assignment 1A brief - i.e. your final model must encompass both sets of requirements. You may modify your assignment 1A conceptual model in any manner you wish as you work through assignment 1B, provided your final model meets both sets of requirements. Your assignment 1A conceptual model will not be submitted or assessed again, updates are only part of the group working towards your logical model.
In developing your final logical data model, composite attributes present on your conceptual model must be expanded into their component simple attributes, unless otherwise directed. If the supplementary material presented in this document does not guide you in deciding the components, you may make any reasonable decision on their simple component attributes.
FACULTY OF INFORMATION TECHNOLOGY
Page 2 of 14
FACULTY OF INFORMATION TECHNOLOGY
Further discussions with Clean Up Inc (CUI) have revealed the points listed below:
i. Local
○ ○ ○ ○ ○
ii. Road
○ ○ ○
Authorities are classified into the following fixed types:
Borough
City
District Council Shire
Town
surfaces are classified into the following fixed types:
Asphalt Concrete Unsealed
iii. Bins supplied to properties will be replaced if needed, the fixed reasons noted are:
○ Damaged by owner
○ Bin Failure (fair use eg. old age)
○ Damaged during pickup of waste ○ Stolen
iv. The types of waste collected by CUI currently include green waste, landfill, standard recycle and glass. The company would like to be able to easily add new types of waste for collection which suit particular local authorities needs.
v. Waste collections are made at one of the following fixed intervals:
○ Weekly
○ Fortnightly ○ Monthly
vi. The RFID codes used to identify bins are recorded as a 16 character hexadecimal number. For recording purposes it is important to be able identify what the actual charge was made for the supply of a bin to a property. This bin charge is determined by what contract the bin was supplied under. Note that this is not necessarily the bin charge under the current contract, it may be that the bin located at the property was supplied under a previous contract.
vii. Each truck driver employed by CUI is assigned a unique driver number. Drivers are approved by the company to drive individual trucks in the fleet. A given truck may be driven by many approved drivers. Each truck is identified by its Vehicle Identification Number (VIN). When a driver is approved to drive a particular truck the date of that approval is recorded. Note that there is no dependency between truck make and model (such details are not necessarily unique to a particular make/model).
viii. When a truck is making a pickup of a bin from a property on a particular date, if the bin is detected by the truck’s on board scales as being too heavy, the waste is not collected, and this bin collection is flagged as being overweight (Y). If the bin is not
Page 3 of 14
overweight, it is flagged as not overweight (N) and collected.
ix. A particular type of waste can only be collected once on any given date from a particular property. However a property may have different types of waste collected on the same date.
x. CUI have indicated that they wish all phone numbers in the system to be recorded as simple attributes.
Clean Up Inc have supplied the following two forms as samples of those which are used within their business. You should note that the data shown is incomplete and only representative of the type of data for each item.
(i) Driver Details:
FACULTY OF INFORMATION TECHNOLOGY
Page 4 of 14
(ii) Collection Report
FACULTY OF INFORMATION TECHNOLOGY
Page 5 of 14
FACULTY OF INFORMATION TECHNOLOGY
REMEMBER you must keep up to date with the Ed Assignment 1B forum where further clarifications may be posted (this forum is to be treated as your client).
Please be careful to ensure you do not publicly post anything which includes your reasoning, logic or any part of your work to this forum, doing so violates Monash plagiarism/collusion rules and has significant academic penalties. Use private posts or email your allocated tutor to raise such questions.
You are free to make assumptions if needed however they must align with the details here and in the assignment forums and must be clearly documented (see the required submission files). Other than surrogate keys, where appropriate, you must remember the design adage "All that is required has been included and all that has been included is required" i.e. you must not add features outside the requirements expressed in the brief.
Group Communication
Your group MUST make use of your private group channel in MS Teams for all group communication during this assignment which is not face to face. Microsoft Teams provides facilities to support group interaction including chat, group email, shared desktop, meetings, video/audio calling and shared files.
Activity in your private group channel is only visible to your group members and the teaching staff. It is important that you use Microsoft Teams for your group activities as it may be necessary for your marker to check the group members' contributions to the task and attendance at meetings - such a decision will be based on the activity in your private group channel ONLY.
Git Management
ENSURE your group name is shown on every page of any document you submit. If a document is a multipage document (such as the normalisation), please also make sure you include page numbers on every page.
GIT STORAGE
Your work for these tasks MUST be saved in your group local working directory (repo) in the Assignment 1B folder and regularly pushed to the FIT GitLab server to build a clear history of development of your model. Any submission with less than nine pushes to the FIT GitLab server will incur a grade penalty of 8 marks. Please note nine pushes is a minimum, in practice we would expect significantly more. This number of pushes must be evenly distributed amongst group members. All commits must include a meaningful commit message which clearly describes what the particular commit is about and must be correctly assigned to a valid GitLab author.
Groups must regularly check that their pushes have been successful by logging in to the web interface of the FIT GitLab server; you must not simply assume they are working. Before submission, via Moodle, you must log in to the web interface of the GitLab server and ensure your submission files are present on the GitLab server.
GIT automatically maintains a history of all files pushed to the server, you do not need to, and MUST not, add a version name to your various versions, please ensure you use the
Page 6 of 14
FACULTY OF INFORMATION TECHNOLOGY
same name for all versions of a particular file. Check Git File Versions video under week 3 block on Moodle if you need to clarify this.
Groups MUST NOT make use of REVERT or RESET when working on this assignment task, doing so will potentially cause serious errors to your remote repo. If you have problems in pushing to the remote group repo, you should move your current local group repo out of the way (to a new folder) and then reclone your group repo as discussed in the week 2 Applied notes (section 2.4).
Working on SQL Developer Models in your Group Repo
If multiple students work on a logical model at the same time, merging these changes can be quite difficult, since the files are not plain text files. For this reason, you are required to take a simple approach to working on the model - lock the remote repo when making changes.
Whenever a particular student wishes to work on the model, they should go to the Git Server web interface and check if the assignment 1B folder has been locked by another member of the group.
If it has, you must not carry out any work on the assignment task.
If it has not been locked, you can proceed to lock the folder by selecting "Lock":
Ensure you are in the correct folder when this lock is applied.
You will know the items are locked as each will have a lock icon attached to it:
If you hover over the padlock icon, you will be able to see who currently has the folder locked.
After locking the folder, you MUST do a pull (no changes must be made in your group local repo until the lock is in place AND this pull has been completed completed). When you have completed your work, and pushed it to Git, you should return to the Git web interface and unlock the folder:
It is our expectation that all members of the group will contribute to building the model, just one member must not complete it. In assessing your group's work, we will examine the commit log to ensure all members of the group have participated.
Page 7 of 14
Tasks to complete
1. Perform normalisation to 3NF for the data depicted in the two supplied sample documents.
The approach you are required to use is the same approach as shown in the normalisation applied class solution. The normalisation must be carried out form by form (i.e. one form at a time), beginning by representing the document you are working on as a single UNF relation and then moving through 1NF, 2NF and 3NF. No marks will be awarded if you use a different approach.
During normalisation, you must:
○ Not add surrogate keys.
○ Include all attributes (you must not remove any attribute as derivable)
○ Clearly show UNF, 1NF, 2NF and 3NF.
○ Clearly identify the Primary Key in all relations by underlining the PK
attribute/s.
○ Clearly identify all dependencies at the various normalisation stages (Partial
at 1NF, Transitive at 2NF and Full at 3NF). You should use the same notation as depicted in the normalisation sample solutions, for example:
attr1 -> attr2, attr3
If none exist you must note this by stating:
No partial dependencies present and/or
No transitive dependencies present
○ Carry out attribute synthesis.
The relation and attribute names used throughout your normalisation and those on
your subsequent logical model must be the same.
Your normalisation must be carried out in an MS Word document in your group’s private MS Teams channel so that a full development history is available. It does not need to be pushed to GitLab other than as listed in Submission Requirements.
2. Based on your group’s assignment 1A 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 design for the Clean Up Inc 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 not show data types.
○ 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). You may
add surrogate keys to multiple relations if you wish
○ All attributes must be commented in the database (i.e. 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 MUST include the legend as part of your model. Please edit the legend
panel to show your group name
○ Note that your GIT repository must clearly indicate your development history
with multiple commits/pushes as you work on your model.
FACULTY OF INFORMATION TECHNOLOGY
Page 8 of 14
FACULTY OF INFORMATION TECHNOLOGY
3. Generate the schema for the database in Oracle Data Modeler and use the schema to create the database in your Oracle account (this should be tested in your individual Oracle accounts - a group Oracle account is not available).
The only edit you are permitted to carry out to the generated schema file is to add header comment/s containing your details (group/members names) and the commands to spool/echo your run of the script. In generating your schema file ensure you:
○ Capturetheoutputoftherunofyourschemastatementsusingthespool command.
○ Ensureyourscriptincludesdroptablestatementsatthestartofthescript. ○ Nametheschemafileascui_schema.sql.
4. Maintain a Group Diary which records when the group met to discuss/work on the task, including the date, who was present and a brief statement of what occurred. This Group Diary must be maintained in Microsoft Teams as a shared document in your private group channel. It does not need to be pushed to GitLab other than as listed in Submission Requirements.
As part of submission of your assignment each group member will be required to provide confidential feedback on the group members performance/interactions.
Please note when working with your model ensure that you NEVER select any export options from the Data Modeller menu:
such actions can fill your Oracle account space and render it unusable.
Page 9 of 14
Submission Requirements
Assignment 1B: Due: Wed, 3rd May 2023, 4:30pm (AEST) / Wed, 3rd May 2023, 2:30pm (MYT)
The following files must be submitted to Moodle and must exist in your group FITGitLab server repo. The source files must exist in either your group's GitLab Repo or your group's
private
MS Teams channel:
● A pdf document showing your full normalisation of the sample Clean Up Inc documents showing all normal forms (UNF, 1NF, 2NF and 3NF). Name the file cui_normalisation.pdf
● A single page pdf file containing the final logical Model you created in Oracle Data Modeler. Name the file cui_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 cui_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 model otherwise your task 2 and 3 will not be able to be fully marked resulting in significant loss of marks. You MUST carefully check that your model is complete - ensure you 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. Please view the video on Moodle under week 6, "Preparing Files for Submission", which demonstrates this process.
● A schema file (CREATE TABLE statements) generated by Oracle Data Modeller. Name the file cui_schema.sql
● The output from SQL Developer spool command showing the tables have been created. Name the file cui_schema_output.txt
● A pdf document containing any assumptions you have made in developing the model or comments your marker should be aware of. If you have made no assumptions, submit the document with a single statement saying "No assumptions made". Name the file cui_assumptions.pdf
● A PDF copy of your group diary named as cui_##_Ass1B_diary.pdf (replace ## with your group number eg. cui_A01-Group01_Ass1B_diary.pdf)
These files must be submitted as individual files ie. you must upload to Moodle seven separate files as named above (the seven files must not be zipped into a single archive) before the assignment due date/time. The files only need to be submitted by one member of the group after the group has agreed that the submission is complete and ready to be graded.
FACULTY OF INFORMATION TECHNOLOGY
Page 10 of 14
FACULTY OF INFORMATION TECHNOLOGY
Late submission will incur penalties of 10 marks deduction per day or part thereof late. Submissions are not accepted beyond 7 days late.
Please note we cannot mark any work on the FITGitLab Server, you need to ensure that you submit correctly via Moodle since it is only in this process that you complete the required student declaration without which work cannot be assessed.
It is your responsibility to ENSURE that the files you submit are the correct files - we strongly recommend after uploading a submission, and prior to actually submitting in Moodle, that you download the submission and double-check its contents.
Your assignment MUST show a status of "Submitted for grading" before it will be marked.
If your submission shows a status of "Draft (not submitted)" it will not be assessed and will incur late penalties after the due date/time.
Interview
Groups may be required to attend an interview to explain their model and the approach used.
Page 11 of 14
Marking Guide
Submitted designs will be assessed against the optimal solution for this task - this optimal solution will be available as a sample solution after assignment 1B has been graded.
Assignment 1B Group submission content graded out of 80 marks as shown below:
FACULTY OF INFORMATION TECHNOLOGY
Marking Criteria
Items assessed
Normalise the supplied case study documents and integrate the resultant relations into a logical model. [24 marks]
Maximum 18 marks - Normalisation:
● Marks awarded for each correct normalisation step
● Marks awarded for correct attribute synthesis
● Mark penalty for additional attributes or surrogate
keys added during normalisation
Maximum 6 marks - Dependency diagrams:
● Marks awarded for each correct dependency depicted within normalisation
Mapping to logical model:
● Mark penalty for incorrect mapping of each relation to logical model
Depict the data requirements expressed in the case study via a relational database logical model.
[43 marks]
Maximum 10 marks - Relations:
● Marks awarded for each required relation and its attributes identified
● Mark penalty for extra relations included
● Marks penalty for placement of attribute in
incorrect relation
● Mark penalty for multivalued attributes included
Maximum 10 marks - Primary keys:
● Marks awarded for each correct assignment of a primary key
Maximum 10 marks - Relationships:
● Marks awarded for each required relationship identified
● Mark penalty for each incorrect minimum and maximum cardinality for each required relationship depicted
● Mark penalty for unnecessary relationships included
● Mark penalty for redundant relationships included
Maximum 4 marks - Surrogate key:
● Marks awarded for creation of at least one appropriate surrogate key
Page 12 of 14
FACULTY OF INFORMATION TECHNOLOGY
● Marks awarded for creation of unique index/s to protect natural key/s
Maximum 4 marks - Attribute data types:
● Marks awarded for each correctly identified Oracle data type
● Marks awarded for each null constraint correctly implemented based on business rules
Maximum 5 marks - Business Rules:
● Marks awarded for each correctly identified integrity requirement to implement case studies business rules
Generate a schema which meets the case study data requirements from the logical model produced
[8 marks]
Maximum 5 marks - Relational model generation:
● Marks awarded for correct generation of relational model from submitted logical model.
Maximum 3 marks - Schema generation:
● Marks awarded for correct generation of SQL schema file from submitted logical model
● Mark penalty for missing column comments
Consistent use of industry standard notation and convention
[5 marks]
Maximum 5 marks - Modelling standards:
● Marks awarded for application of Unit logical model notation convention
● Mark penalty for showing data types on logical model
● Mark penalty for missing model legend and/or relationship labels
Penalty Criteria
Penalty Applied
Limited or No push of model to FITGitLab server resulting in lack of development history
and/or
Pushes not distributed evenly amongst the group
If less than nine pushes showing a clear development history and/or uneven distribution of pushes amongst the group a grade deduction of 8 marks will be applied.
Incorrect Git author details used (see week 2 Applied notes Appendix)
If incorrectly identified pushes are made by any member of the group and/or non-informative Git Commit messages are used a grade deduction of 8 marks will be applied.
Page 13 of 14
Assignment 1B Individual Group Evaluation graded out of 10 marks as shown below:
FACULTY OF INFORMATION TECHNOLOGY
and/or
Blank or non-informative Git Commit message/s (such as "Push 4" - message must explain what the commit represents)
Peer Evaluation
Maximum 10 marks - Contribution and Participation in your group:
● Communication
● Project Management
● Quality of contribution
● Quantity of contribution
● Use of MS Teams within the group private
channel
● Support for the group's working environment as
assessed by self-evaluation and group members
(peer) evaluation via Feedback Fruits
● This component will be moderated, if necessary,
by your tutor based on any group issues/concerns which are not addressed
Final Assignment Mark Calculation
● 80 marks from the content of the group submission => maximum of 24 marks to unit grade
PLUS
● 10 marks from the individual members group evaluation (self-review, peer review and
tutor moderation) => maximum of 3 marks to unit grade
PLUS
● 10 marks from the Week 5 Design Portfolio task => maximum of 3 marks to unit
grade
Total:100 marks, recorded as a grade out of 30 towards your unit grade