讲解 INFT1032、辅导 SQL 程序设计
INFT1032: UO Data Driven Web Technologies
ADELAIDE METRO
INFT 1032 UO Data Driven Web Technologies SP3 2025
Copyright
This material has been reproduced and communicated to you by or on behalf of the University of South Australia under Sec�on 113P of the Copyright Act 1968 (the Act). The material in this communica�on may be subject to copyright under the Act. Any further reproduc�on or communica�on of this material by you may be the subject of copyright protec�on under the act.
Details
• Weigh�ng 25% of your overall grade.
• This assignment is to be completed individually.
• Due Week 5
Course Objec�ves to Achieve
CO1. Explain how data is stored and retrieved from a rela�onal database and the advantages of using the rela�onal mode.
CO2. Design a rela�onal database given a set of data and usage requirements.
CO3. Implement database queries to retrieve specified data from a rela�onal database.
CO4. Construct dynamic webpages using web mark-up languages and data sources.
CO5. Describe and discuss the advantages of a range of web architectures.
Academic Integrity
See Course Outline sec�on: Academic Integrity. Ar�ficial Intelligence (AI) tools are prohibited within this course and any detec�on of AI will be reported as Academic Integrity.
Important Note
This document proposes the requirements for all components of the assessment. This document may contain imperfectly or poten�ally incorrectly writen sec�ons. If you spot any errors, or are uncertain of anything, please post on the discussion forum.
Adelaide Metro
Background: Your consul�ng company has been contracted to design and prepare a database for Adelaide Metro, the public transport system of the Adelaide area, around the capital city of South Australia. Adelaide Metro offers an integrated network of bus, tram, and train services throughout the metropolitan area. The network has an annual patronage of 79.9 million, with 51 million journeys by bus, 15.6 million by train, and 9.4 million by tram (reported in 2018-19).
Adelaide Metro operates a diverse fleet of vehicles, including buses, trams, and trains, each requiring regular maintenance and service to ensure safety and reliability. The organiza�on employs a wide range of staff, from drivers and conductors to maintenance personnel and administra�ve staff. Addi�onally, Adelaide Metro offers account services for passengers, allowing them to manage their travel and payment op�ons conveniently.
INFT 1032 UO Data Driven Web Technologies SP3 2025
Requirements:
Adelaide Metro needs a comprehensive database to manage several key aspects of their opera�ons:
1. Fleet of Vehicles:
• Informa�on about each vehicle: This includes details such as the type of
vehicle (bus, tram, train), model, capacity, and unique iden�fiers.
• Service History: Maintenance and repair records for each vehicle to ensure
they are kept in op�mal condi�on.
2. Service History:
• Maintenance Records: Detailed logs of all maintenance ac�vi�es, including dates, types of service, and any specific issues addressed.
• Repair Records: Informa�on on any repairs performed, including parts
replaced and labor involved.
3. Staff:
• Employee Details: Informa�on about all employees, including their roles (e.g., drivers, conductors, maintenance staff), contact informa�on, and any vehicles they are assigned to.
• Training and Cer�fica�on: Records of any training or cer�fica�ons staff members have completed, ensuring they are qualified for their roles.
4. Passengers:
• Account Informa�on: Details of passengers who have accounts with Adelaide
Metro, including contact informa�on and account status.
• Journey History: Records of all journeys made by passengers, including dates,
start and end loca�ons, and the vehicles used.
Example Use Cases:
1. Fleet Management: Track the service history of each vehicle to schedule �mely maintenance and prevent breakdowns.
2. Staff Assignment: Assign drivers and conductors to specific vehicles and routes based on their qualifica�ons and availability.
3. Passenger Services: Manage passenger accounts and provide personalized services based on their travel history and preferences.
4. Repor�ng: Generate reports on vehicle usage, maintenance ac�vi�es, staff performance, and passenger journeys to support decision-making and improve opera�ons.
Addi�onal Informa�on:
You may refer to the following websites to get more informa�on on Adelaide Metro and what may need to be captured to create an effec�ve database schema: htps://www.adelaidemetro.com.au/about-us/what-is-adelaide-metro
Tasks
Below is a list of the tasks you need to complete:
INFT 1032 UO Data Driven Web Technologies SP3 2025
1. Design a Domain Model Diagram using UML Nota�on to capture all the relevant data and reduce the possibility of anomalies.
1. Provide Jus�fica�on for your design decisions. There are mul�ple ways to design this database, you need to jus�fy why you chose specific table structures and associa�ons.
2. The design needs to meet 3rd normal form. Adequate jus�fica�on must be provided for tables/atributes that do not meet these criteria giving examples with the reasoning.
3. Use the UML nota�on conven�ons demonstrated and discussed in class.
2. Translate your Domain Model Diagram into Table Schemas
1. Clearly iden�fy all relevant keys (including candidate keys, foreign keys)
2. Use the table schema conven�on demonstrated in class.
3. Construct Table crea�on statements.
1. These must run and include named constraints, meaningful data types
2. Using the SQL conven�ons demonstrated in class
4. Provide 3 example queries of how you might extract data from your database to
display useful Queries that demonstrate how your design will work. A “SELECT * FROM Table X” is insufficient for this. We would be looking for JOINs, Aggrega�on and Grouping. Consider the suggested use cases men�oned above for inspira�on.
Submission
Submit your design as a single word document clearly iden�fying:
• Your Domain model.
• Design jus�fica�on providing a detailed explana�on of your design choices,
demonstra�ng a though�ul considera�on of the project requirements and
constraints.
• 3rd Normalisa�on check of main tables - highligh�ng any devia�ons and providing
jus�fica�on for their inclusion. You can do this by showing the func�onal
dependencies as reviewed in the W1 Normalisa�on sec�on.
• Table crea�on statements with meaningful data types for the example data. Jus�fy
your choice of data types, considering factors such as data integrity, storage
efficiency, and compa�bility with the intended use of the database. • Your example queries and what ques�ons they address.
Marking Scheme
Domain Model UML [40]
• Atributes & Classes [10]
• Style [10]
• Normalisa�on Check and 3rd Normal Form [10] • Jus�fica�on [10]
Schemas [25]
• Atributes [10]
• Schema Keys [10]
• Consistency with the Domain Model applies to both!
Table Crea�on [25]
INFT 1032 UO Data Driven Web Technologies SP3 2025
• SQL Statements [10]
• Constraint names and other conven�ons [10]
• Consistency with the Schemas and Domain Model also applies to both!
Example Queries [10]
• Example aggregate + join SQL Statements to extract informa�on from your new DB structure
Reviews
All submissions may be subject to an in-person review to ensure academic integrity standards are upheld.
Extensions
All extension requests require a valid medical cer�ficate or suppor�ng documenta�on, or they will be declined without considera�on. These must be received in advance of the due date. Extensions requested in the last days may be declined.