Reference no: EM133698210
Database Management Systems Assessment
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:
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 Lucidchart to visually represent the database structure.
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.
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.
*Note: Need to select case 6