4.2C Miniproject - Part-1
Page 1 of 2
SIT103/SIT772: Database Fundamentals
4.2C: Mini-project Part 1
Overview
In this task and Task 7.2C, you will undertake a mini project to design and implement a database for an
organization from scratch, i.e., from understanding the organization’s data requirements to designing and
implementing a relational database for them. You are required to select a data-driven business
organization from any industry below.
1. School/College (e.g., Melbourne High School, Gordon TAFE)
2. Public Li
ary (e.g., Geelong Regional Li
ary, Burwood Li
ary)
3. Travel Agent (e.g., Flight Centre, Skyscanner)
4. Hotel Reservation (e.g., Booking.com, AirBnB)
5. Food Delivery (e.g., Menulog, Deliveroo)
6. Medical Clinic (e.g., MedicalOne, Geelong City Medical)
7. Insurance Company (e.g., Aami, Alianz)
8. Streaming Services (e.g., Netflix, Spotify)
9. Taxi/Rideshare Company (e.g., Silver Cab, Uber)
10. Telecommunication Provider (e.g., Telstra, Optus)
The ones in the
acket are just examples, you can choose one outside of those. You can choose any real business
in any of those industries that is data driven. NOTE: Retail, online shopping, or e-commerce businesses (e.g.,
Coles, Woolworths, Amazon, eBay, etc.) that deal with selling products are not allowed. It is because we
have used many examples of those in classes and workshops. If you work for or know an organization in any
of the above industries, you can select it if you want.
In this task, you will design a database for the organization/company of your choice that you will implement
using MySQL in Task 7.2C.
Tasks to do
1. Provide name, website (if available and known), main business, and description of the company’s business
processes, functions, activities, scenarios and/or use cases. This part will give business rules to model
and design a database for the company. You may find these details in the company’s website, reports,
etc. if available. If you can’t find the information anywhere, you can make reasonable/appropriate
assumptions to make the business scenario complete.
Page 2 of 2
2. List all Entities that are required to model the data requirements for the company based on the business
scenario discussed in Task#1. For each Entity, identify all required/possible attributes and select the Primary
Key (PK)
Example: PRODUCT [Product ID (PK), Product Description, Product Category, Unit Price, Stock level,
Vendor Name, Vendor Contact]
3. Normalize all Entities described in Task#2 up to the Third Normal Form (3NF), i.e., No repeating groups and
PK is defined (1NF); No Functional Dependency (2NF); and No Transitive Dependency (3NF). For each
Entity, check if it is already up to 3NF:
• If the Entity is already in 3NF and does not require normalization, please provide justifications on why/how
you think it satisfies conditions for 1NF, 2NF and 3NF.
• If the Entity requires normalization, you need to discuss what you did to normalized it step-by-step.
Starting from 1NF,
o check if the Entity satisfy the condition(s) for each NF
o If the condition(s) are satisfied, provide justify why/how they are satisfied
o Otherwise state what condition(s) are violated, perform normalization to make sure the conditions
are satisfied and provide normalized solution
• Repeat for the next NF until the Entities satisfy conditions for up to 3NF.
4. Now you have a list of Entities all of which are normalized up to 3NF. Draw an ERD using the Crow’s Foot
Natation in a professional tool of your choice showing relationships between the entities. Your ERD must
clearly show the followings:
• Entities with major attributes including Primary Key (PK), Foreign Keys (FKs), and other constraints where
applicable
• Clearly labelled relationships between Entities with appropriate cardinalities (including mandatory/optional
elationship) shown using Crow’s foot notations.
• All relationships must be implemented appropriately. Note that many-to-many relationships are
implemented using
idging or associative entities.
Submission Requirements:
Submit one PDF/WORD file as a database design document for the company answering all tasks/questions
above. Please include/attach the ERD drawn with a professional tool in your report.
Submission Due
The due date for each task is stated on its OnTrack task information dashboard.