Reference no: EM133969367

Database Design

Objectives

This assessment item relates to the unit learning outcomes as in the unit descriptor. This assessment is designed to reinforce the lecture material and give students practice at applying Database design techniques, as well as providing a relational schema using DDL statements and relational DML statements to demonstrate sophisticated access of the Database to be built.

Assignment Case Study

This assessment will be completed in groups of 4 students from the same campus. Groups will be formed within the first 2 weeks, and students will be expected to work within these groups for the remainder of the semester on the group case study.

Each group must select an information system, analyse its data storage requirements, and build a database to support it. A list of suggested information systems is provided in the document titled HI5033 Group Assignment Case Studies.pdf, from which your group should choose one system. Alternatively, your group may select a different information system not included in the provided list.

Deliverable Description

The deliverable should consist of four parts, detailed as follows:

Requirement specification
Your group must provide an overview of the selected system and specify its requirements. These requirements should directly relate to the tasks outlined in the subsequent three parts.

Database ER Model
The ER Model should represent the database structure you plan to build for the group case study. It must be presented using Crow's Foot notation and accurately reflect the database to be built. The model should provide a realistic depiction of the data requirements for the chosen system and include at least 10 entity types.

Database Schema DDL statements
You are required to create Schema DDL statements to build your database. Your tables should include all appropriate primary and foreign keys, as well as any relevant constraints.

Sophisticated DML statements
You are required to construct SQL INSERT statements to populate all your tables with initial data, ensuring that each table contains at least 5 rows. You have the freedom to choose appropriate data for insertion. Additionally, you must create 5 SQL statements to demonstrate sophisticated data access. These statements must involve joins between multiple tables (Inner Joins only are acceptable). The test data should be sufficient to effectively demonstrate the use of your SQL statements.

Assignment Case Studies

Hospital Management System
Build a database to manage patient information, doctor profiles and schedules, appointment bookings, treatment records, billing details, and departmental structures. The system should support relationships between patients, doctors, appointments, and treatments for accurate and accessible data.

University Course Registration System
Build a database to manage student registrations, courses, instructors, grades, and schedules.
The system should maintain relationships between students, courses, faculties, and enrolments to organize academic data effectively.
Library Management System
Build a database to manage books, authors, borrowers, lending records, returns, and
reservations. It should link books to authors, borrowers to loans, and categories to books for efficient library management.
Hotel Booking System
Build a database to handle guest details, room bookings, payments, services, and staff schedules. The system should manage relationships between customers, rooms, bookings, and services to streamline operations.
E-commerce Order Management System
Build a database to track products, customers, orders, inventory, payments, and deliveries. It should link users, orders, and inventory to ensure smooth transaction management.
Human Resource Management System
Build a database to store employee information, job roles, departments, attendance, payroll, and performance evaluations. Relationships between employees, supervisors, roles, and pay
structures should be managed carefully.
Real Estate Property Management System
Build a database to manage properties, clients, agents, listings, sales, and rental agreements. The system should connect agents to listings, buyers to properties, and contracts to clients.
Point of Sale (POS) System for Retail
Build a database to manage sales transactions, product inventory, employees, and customer data. Relationships between sales, items, customers, and staff must be well organized.
Online Banking System
Build a database to manage user profiles, accounts, transactions, transfers, and financial products. It should maintain secure links between customers, accounts, and transactions.

Inventory and Warehouse Management System
Build a database to manage items, suppliers, stock levels, purchase orders, and warehouse locations. It should track relationships between products, suppliers, and inventory movement.
Gym Membership Management System
Build a database to manage member details, trainers, fitness classes, attendance, and subscriptions. The system should relate members to classes, trainers, and payments.
Restaurant Ordering and Reservation System
Build a database to manage table reservations, menu items, customer orders, billing, and kitchen operations. It should link orders to tables, customers, and menu selections.
Clinic Appointment and Billing System
Build a database to manage patient appointments, doctor schedules, billing, and treatment histories. Relationships between doctors, patients, services, and invoices should be supported.
Freight and Logistics Tracking System
Build a database to track parcels, routes, drivers, customers, and delivery statuses. It should relate shipments to clients, delivery routes, and statuses.
Learning Management System
Build a database to handle users, courses, instructors, assessments, and progress tracking. The system should manage links between students, courses, assignments, and grades.
Vehicle Rental System
Build a database to manage vehicles, bookings, customers, payments, and returns. It should relate customers to rentals, vehicles to availability, and bookings to payments.
Event Management System
Build a database to manage events, attendees, venues, schedules, and vendors. Relationships between events, guests, service providers, and bookings must be maintained.
Online Food Delivery System
Build a database to manage users, restaurants, menus, orders, delivery partners, and locations. It should link orders with customers, restaurants, and drivers.
Customer Relationship Management (CRM) System
Build a database to manage customer profiles, interactions, leads, sales, and support tickets. It should maintain relationships between sales staff, customer accounts, and activities.
Online Job Portal System
Build a database to manage employers, job seekers, applications, resumes, and job listings.
Relationships between job seekers, postings, employers, and applications should be established.
Travel Agency Booking System
Build a database to track customers, travel packages, flight and hotel bookings, payments, and itineraries. The system should maintain relationships between travellers, destinations, bookings, and services.

Pharmacy Management System
Build a database to manage medicines, prescriptions, customers, inventory, and suppliers. Relationships between prescriptions, customers, doctors, and medications should be supported.
Insurance Policy Management System
Build a database to store policyholder details, policy types, claims, payments, and agent data. It should track relationships between customers, policies, claims, and staff.
Online Learning Platform
Build a database to manage students, instructors, digital content, course progress, and feedback.
Relationships between users, lessons, activities, and performance must be maintained.
Voting and Election Management System
Build a database to manage voters, candidates, polling booths, votes, and election results. Relationships among voters, ballots, elections, and vote counts should be carefully managed.
Online Movie Ticket Booking System
Build a database to manage movies, theatres, showtimes, customers, seat reservations, and payments. It should relate bookings to seats, customers, and movie schedules.
Maintenance Request System for Property Management
Build a database to manage tenant information, property details, maintenance requests, service staff, and resolution logs. Relationships between tenants, properties, issues, and staff assignments should be supported.
Online Auction System
Build a database to manage products, users, bids, auction timelines, and transaction records. It should connect items to sellers, bids to buyers, and auctions to status updates.
Fitness Tracker Integration System
Build a database to store user profiles, activity logs, workout plans, goals, and progress. Relationships between users, devices, fitness data, and targets should be maintained.