Objective:
To prepare students for real-world database design and implementation challenges, equipping them with the skills and knowledge necessary for advanced roles in IT and data management.
Learning Outcomes:
Students should be able to demonstrate their achievements in the following unit learning outcomes:
a. Advocate high level of capacity in designing and implementing database systems using advanced modelling techniques, such as entity-relationship (ER) modelling and normalization, for business applications.
b. Exemplify high level of written and verbal communication skills relevant to the planning, design, and implementation of a technical solution.
c. Compose a range of problem-solving skills and creative and critical thinking strategies in order to interpret entity relationship diagrams to implement databases. Describe relational algebra and its relationship to Structured Query Language (SQL).
d. Exhibit capacity in the use and interpretation of query language tools and techniques to obtain and manipulate data and information from databases.
Group Composition:
Groups should consist of 4 members. Collaboration is key. Divide tasks among members but ensure regular communication and consensus on design decisions.
Submission Guidelines:
The report is to be submitted in a word format. The assessment constitutes 30% of the total grade, with 20% allocated for the report and 10% for the presentation. The report should be between 1800 and 2000 words in length, excluding the title page and list of references. All pages must be numbered. Only one submission per group is needed. The similarity index of your report should be less than 20%.
When uploading your assessment report, name your document using the following syntax: [unitcode]_[Group No].doc (or ‘.docx’)
For example, ‘ICT503_MEL Group A1.doc’.
You must keep a backup copy of every assignment you submit until the marked assignment has been returned to you. In the unlikely event that one of your assignments is misplaced you will need to submit your backup copy.
Penalties for late submission:
Late submissions will incur a 10% deduction in marks for each day beyond the due date. Your PowerPoint file must be uploaded on the Moodle before the presentation. All students must give the presentation to receive marks. Zero marks will be awarded if a student is absent or fails to deliver the presentation.
Presentation:
Prepare a brief presentation summarizing your design process, database design, normalization, SQL codes, and key learning outcomes. Each group will give a presentation of 10 minutes (including a 2-3 minutes of Q&A session) in the lecture of week 12.
Assignment Scenario:
Your team has been tasked with designing a database system for one of the scenarios listed according to your group number; if you are in group N, you must choose scenario N. For example, Group 10 must choose Scenario 10. If you work on a different scenario, your assignment will not be marked.
List of Scenarios:
Choose one of the below Scenarios according to your group number.
Scenario 1: Online Retail Store
Develop a database system for an online retail store that manages products, inventories, customers, orders, and shipping details. The system should support online shopping features like product search, order tracking, and customer feedback.
Scenario 2: Public Library Management
Create a database for a public library to manage books, members, loans, and fines. The database should handle book reservations, track overdue items, and support queries for book availability and member activity.
Scenario 3: Event Management System
Develop a database for an event management company that handles events, attendees, venues, and vendors. The system should support event planning activities, including scheduling, ticket sales, and vendor services.
Scenario 4: Airline Reservation System
Create a database for an airline reservation system that manages flights, passengers, reservations, and crew assignments. The database should ensure efficient scheduling and offer flexibility for flight changes and cancellations.
Scenario 5: Employee Performance Tracking System
Design a database to track employee performance, project assignments, reviews, and promotions within a company. The system should support HR processes, including performance appraisal, training programs, and career development planning.
Scenario 6: Vehicle Rental Service
Develop a database for a vehicle rental service that manages vehicles, customers, rental agreements, and payments. The system should handle different types of vehicles, check vehicle availability, and track ongoing rentals.
Scenario 7: Restaurant Reservation and Ordering System
Create a database for a restaurant that manages table reservations, orders, menu items, and billing. The system should support real-time reservations, track orders from placement to delivery, and manage inventory based on daily sales.
Scenario 8: Corporate Training Program
Develop a database to manage corporate training programs, including courses, instructors, employee enrolments, and feedback. The system should track employee progress, course completion, and evaluate instructor performance.
Scenario 9: Film Production and Distribution Company
Develop a database for a film production and distribution company that manages films, casts, production schedules, and distribution channels. The system should track film production progress and manage distribution rights and revenues.
Scenario 10: Healthcare Patient Management System
Design a database for a hospital to manage patient records, appointments, medical histories, treatments, and billing information. Your database should ensure patient privacy and support efficient retrieval of medical records by authorized staff.
Scenario 11: University Accommodation Service
Design a database system for a university's accommodation service that manages student housing, room allocations, maintenance requests, and payments. The system should accommodate different housing options and prioritize allocations based on specific criteria.
Scenario 12: Real Estate Property Management
Design a database system for a real estate agency to manage properties, clients, viewings, and transactions. The system should support property listings, client preferences, and transaction history for buyers and sellers.
Scenario 13: Museum Artifact Cataloging System
Create a database for a museum to catalog artifacts, exhibitions, loans, and restorations. The system should manage artifact details, exhibition scheduling, and track items loaned to or from other institutions.
Scenario 14: Film Production and Distribution Company
Develop a database for a film production and distribution company that manages films, casts, production schedules, and distribution channels. The system should track film production progress and manage distribution rights and revenues.
Scenario 15: Sports League Management
Design a database for managing a sports league, including teams, players, game schedules, scores, and standings. The system should facilitate scheduling, track player statistics, and support dynamic updates to league standings.
Group Restrictions:
- Groups of 04 (Four) members
Tasks:
1. Database Design and Implementation:
- Entity Identification: Determine all relevant entities based on the chosen business case.
- Attribute Definition: Assign attributes to each entity and establish data types.
- ER Diagram: Create an entity-relationship diagram with 3 – 4 entities using tools such as draw.io or Lucidchart to visually represent the database structure.
- Schema Creation: Develop a database schema, indicating primary keys and indexing strategies where appropriate.
- Normalization: Ensure the database schema is normalized to at least the third normal form (3NF) to reduce redundancy and improve data integrity.
2. SQL Implementation and Data Population:
- Build the database model using a suitable platform like XAMPP.
- Populate each table with at least five records to simulate realistic data.
3. Query Development:
- Basic and Conditional Retrieval: Write queries to fetch data from single or multiple tables based on specific conditions.
- Aggregated Reports: Use SQL aggregate functions to generate summary reports.
- Data Manipulation: Demonstrate the ability to insert, update, and delete data.
- Complex Queries: Develop queries that involve joins, subqueries, and other advanced SQL features to address complex information needs.
Report Generation:
Document the entire project in a structured report. Include the ER diagram, descriptions of the database schema, SQL code for all queries along with explanations, and screenshots showing the results of the queries.
General Assessment Requirement
Incomprehensible submissions - Assessments provide the opportunity for students to demonstrate their knowledge and skills to achieve the required standard. To do this, assessment responses need to be both clear and easy to understand. If not, SISTC cannot determine that students have demonstrated their knowledge and skills. Assessments will, therefore, be marked accordingly including the potential for 0 (zero) marks where relevant.
Check with marking rubric: Before submitting your assessment, you should check it against the assessment criteria and the marking rubric included in this specification to ensure that you have satisfactorily addressed all the criteria that will be used to mark your submission.
Feedback
Feedback will be supplied through Moodle. Authoritative Results will be published on Moodle.
Academic Misconduct
To submit your assessment task, you must indicate that you have read and understood, and comply with, the Sydney International School of Technology and Commerce Academic Integrity and Student Plagiarism policies and procedures.
You must also agree that your work has not been outsourced and is entirely your own except where work quoted is duly acknowledged. Additionally, you must agree that your work has not been submitted for assessment in any other course or program.
Marking Rubric: Individual Project Report and Presentation
Scenario Understanding (Total: 3 marks)
Excellent HD (85-100%)
Demonstrates a thorough understanding of the case study, including its requirements and objectives.
- Concise introduction and explanation of database objectives. (1)
- Benefits and Logical assumptions. (1)
- Business rules. (1)
Very Good D (75-84%)
Understands the case study very clearly and identifies key entities, attributes, and relationships.
Good C (65-74%)
Understands the case study well and identifies key entities, attributes, and relationships.
Satisfactory P (50-64%)
Understands the case study but may overlook some key elements or relationships.
Poor F (0-49%)
Has limited understanding of the case study, resulting in significant omissions.
Entity Relationship Diagram (ERD) Design & Application of Normalization Principles (Total: 3 marks)
Excellent HD (85-100%)
Creates a clear, well structured, and comprehensive ERD that accurately represents the database schema. Applies normalization effectively, eliminating data redundancy and maintaining data integrity.
- ER Diagram (1)
- Application of Normalization Principles (2)
Very Good D (75-84%)
Creates a clear ERD that accurately represents the database schema effectively. Applies normalization principles correctly, and deliverables meeting expectations effectively.
Good C (65-74%)
Creates a clear ERD that accurately represents most aspects of the database schema. Applies normalization principles correctly but may have minor issues or omissions.
Satisfactory P (50-64%)
Creates an ERD with some clarity but may miss some details or relationships. Applies some aspects of normalization but may lack consistency or overlook key principles.
Poor F (0-49%)
Creates an unclear or incomplete ERD with significant inaccuracies. Fails to apply normalization principles, resulting in data redundancy and integrity issues.
DDL Query Implementation (DDL) (Total: 3 marks)
Excellent HD (85-100%)
Translates the ERD into a well-structured SQL schema with proper tables, constraints, and relationships. Implements appropriate data integrity constraints, including primary keys, foreign keys, unique constraints, and check constraints.
Very Good D (75-84%)
Translates the ERD into a structured SQL schema, and deliverables meeting expectations effectively. Implements data constraints meeting expectations effectively.
Good C (65-74%)
Translates the ERD into a structured SQL schema but may have minor issues or lack clarity. Implements data constraints effectively but may have minor issues or omissions.
Satisfactory P (50-64%)
Translates the ERD into an SQL schema, but it may lack structure or important details. Implements some data constraints but may lack consistency or overlook key constraints.
Poor F (0-49%)
Fails to provide a structured SQL schema, making it difficult to understand. Lacks proper implementation of data constraints, resulting in integrity issues.
DML Query Documentation (Total: 3 marks)
Excellent HD (85-100%)
Writes advanced SQL queries (DML) handling complex data retrieval and manipulation effectively. Each query is excellently documented with detailed explanations of its purpose and logic.
Very Good D (75-84%)
Writes advanced SQL queries (DML) that handle data retrieval and manipulation well. Queries are well documented with clear explanations.
Good C (65-74%)
Writes adequate SQL queries (DML) with somewhat complexity. Queries have good documentation with somewhat explanation.
Satisfactory P (50-64%)
SQL queries (DML) meet basic requirements with some errors. Writes basic SQL queries with limited complexity.
Poor F (0-49%)
SQL queries (DML) are incorrect or very poorly written. Fails to write effective queries.
Recommendations and Report Presentation (Total: 3 marks)
Excellent HD (85-100%)
Professional presentation. Appropriate use of APA referencing. Peer evaluation completed. Recommendations are thoroughly justified with strong evidence.
- Professional report presentation and structure. (1)
- Recommendations (1)
- Use of APA referencing. (1)
Very Good D (75-84%)
Appropriate presentation. Appropriate use of APA referencing. Peer evaluation completed. Recommendations are well justified with appropriate evidence or logic.
Good C (65-74%)
Somewhat professional presentation. Appropriate use of APA referencing. Peer evaluation completed. Justification is present but may be general or lack depth.
Satisfactory P (50-64%)
Poor presentation. Inappropriate use of APA referencing. Peer evaluation completed. Limited justification provided; reasoning is unclear or weak.
Poor F (0-49%)
Very little has been done or has been of extremely poor quality. No justification or unsupported recommendations.
Presentation (Total: 10 marks)
Excellent HD (85-100%)
Presentation was highly professional and demonstrated excellent presentation skills that kept the audience engaged and interested. Extremely well expressed with clear language and a linked and focused structured.
Very Good D (75-84%)
The presentation was professional and demonstrated good presentation skills. The audience were mostly engaged throughout the presentation. Mostly well expressed and structured.
Good C (65-74%)
The presentation performance was acceptable and demonstrated some presentation skills. The audience were engaged throughout most of the presentation. Well-expressed and a linked structure is evident.
Satisfactory P (50-64%)
The presentation performance was acceptable and demonstrated some presentation skills. The audience were engaged throughout most of the presentation. Language expression and acceptable structure detracted from the message.
Poor F (0-49%)
Presentation performance was of a poor standard or lacked credibility. The expression and structure of your presentation needs improvement.