Database Management Systems
Assessment-3 (ICT503) Assignment Help

Timelines and important information

Percentage value of Task: 40% Due: Sunday, Week 12 (1500-word report + recorded oral presentation)

Submitting your work late will attract a 10% reduction of mark for each day beyond the due date. A delay of more than 5 days will result in being granted a 0.

Overview:

This assignment is designed to enhance students’ skills in designing and implementing a relational  database tailored to a specific business scenario. Each student should choose a business case for  their web application based on the last digit (rightmost digit) of their student ID from the options  listed below. For example, if your student ID is S20200005, you must choose Case 5.

Objectives:

• Design a relational database that effectively meets the data management needs of the selected  business case.

• Implement the database schema using SQL in a database management system like XAMPP. • Populate the database with sample data to demonstrate functionality.

• Develop and execute SQL queries that illustrate data retrieval and manipulation capabilities  critical to the business’s operations.

Business Case Options:

Case 0: Healthcare Clinic Management System

Manage patient records, appointments, doctor schedules, and billing. Each patient record should  include a unique patient ID, personal information, medical history, and appointment details.

Case 1: Retail Inventory System

Track products, inventory levels, sales transactions, and supplier data. Each product should have a  unique product ID, description, price, and stock quantity.

Case 2: Digital Marketing Campaign Management

Track marketing campaigns, performance metrics, budget allocations, and lead generation activities.  Each campaign should have a unique campaign ID, target audience, cost details, and performance  outcomes.

Case 3: Real Estate Property Management

Handle property listings, client information, property agent details, and sales transactions. Each  property should have a unique property ID, location details, pricing, and agent responsible.

Case 4: Educational Institute Course Management

Manage course offerings, student enrollments, faculty assignments, and grading. Each course should  have a unique course ID, description, list of enrolled students, and assigned faculty.

Case 5: Event Planning and Ticketing System

Organize events, ticket sales, customer information, and event schedules. Each event should have a  unique event ID, date, location, and ticketing options.

Case 6: Automotive Repair Shop Management

Manage customer appointments, service records, part inventories, and billing information. Each  service record should have a unique ID, customer details, service performed, and parts used.

Case 7: Restaurant Reservation and Menu Management

Handle table reservations, menu items, order management, and customer feedback. Each  reservation should have a unique ID, customer details, table number, and time.

Case 8: Gym Membership and Schedule Management

Manage member subscriptions, class schedules, trainer assignments, and equipment maintenance  logs. Each member should have a unique member ID, subscription details, class enrollments, and  trainer interactions.

Case 9: Hotel Booking System

Manage room bookings, guest information, service records, and payment transactions. Each booking  should have a unique booking ID, guest details, room type, and duration of stay.

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. 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.

ER Diagram: Create an entity-relationship diagram using tools such as draw.io or Lucid chart to  visually represent the database structure.

2. SQL Implementation and Data Population:

Build the database model using a suitable platform like XAMPP.

Populate each table with at least two 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.

Submission Requirements:

The report should be 1500 words (+/- 10%), excluding diagrams and SQL code.

Evaluation Criteria:

Clarity and completeness of the database design.

Correctness and efficiency of SQL queries.

Creativity in addressing the chosen business scenario’s needs.

Quality and professionalism of the report presentation.

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: