Create a conceptual schema for the above database system using the concepts of the Entity-Relationship (ER) model. Show the following design steps: Draw partial ER diagrams showing each entity and all its attributes individually

COMP11109 - Database Design & Implementation 2024/25 T2

Coursework Assignment

Title: Appointment Scheduling for a Service-based SME

Introduction

You have been contracted to design and develop a database system for small SME that provides services to local end consumers. The SME provides small home repairs (like indoor painting, joinery, etc). This family-owned business used to manage all services on pen and paper, yet it has grown to the point where this method has become unfeasible and are looking to professionalise the management of the operation.

You have been contracted to design and deliver a Database system based on Microsoft Access.

The aim is to create a robust database system that efficiently manages core entities, including Services, Customers, Orders, and Payments, to facilitate seamless transactions and enhance the overall user experience.

Data Requirements

The following paragraphs describe the minimum requirements of the database system. It is offered as a starting point and is the result of the first discussion with the owners.

Entities

Client:

• The system needs to store client details.
• CRUD operations on client details must be allowed through forms
• Envisioned schema for this entity is [ClientID, ClientName, ClientAddress, ClientEmail, ClientMobile]

Employee

• The system needs to store client details.
• CRUD operations on client details must be allowed through forms
• Envisioned schema for this entity is [EmployeeID, EmployeePayrollNumber, EmployeeName, EmployeeMobile]
• A solution (DS1) is needed to design a way to connect the Employee Entity with the Service Catalogue (ne. ClientAppointments) that is 3NF compliant and conveys which services can be provided by each employee.

Service Catalogue

• The System must maintain a catalogue of service offered (“Indoor Painting”, “Exterior Painting”, “Joinery”, “Etc”)
• All services are offered at a base cost-per-hour rate plus expenses.
  • A solution (DS2) is needed to design a way to connect the Service Entity with the CustomerCataloge (n.e ServicesProvided) that is 3NF compliant and conveys which services have been performed to each customer on a specific date and reporting the expenses.
• Envisioned schema for this entity is [ServiceID, ServiceName, ServiceDescription, ServiceHourlyRate]

Invoice

• An invoice is an Entity that provides flexibility to DS2.This allows the business to offer discounts and be more competitive in larger jobs.
• Envisioned schema for this entity is [InvoiceID, InvoiceNumber, InvoiceCost, InvoiceDiscount, InvoiceTotal]
  • A solution (DS3) is needed to design a way to connect the Invoice Entity with the ClientAppointments (DS1) that is 3NF compliant and conveys which appointments have been grouped together to produce and Invoice.
  •  

Entity Relationships

As per the description above, the following Entity Relationships have been identified (see DS1, DS2 and DS3). The bullets bellow provide the minimum requirements identified for this relationship.

• ClientAppointments, a one-to-many relationship between Service and Client. Client can have multiple appointments, but each appointment is associated with a single client. This relationship also details the EmployeeID that provides the service, with an integrity constrain that the Employee is able to provide the service.
• ServicesProvided, a many-to-many relationship between Service and Employee. An employee can provide multiple services.
• InvoiceRows, a one-to-many relationship between Invoice and ClientAppointments.

Use Cases for the System

• CRUD operations for the main entities
• Generate an Invoice
• Report
  • Services by month
  • Income (sum of invoice) by month.

Tasks

General

You are required to design and implement this database system through data requirements analysis, conceptual design using concepts of the Entity-Relationship (ER) model, logical design using the concepts of the relational model, and implementation and testing using Microsoft Access 2013 or newer for Windows.

The specification above details the minimal requirements, you can design above or improve the proposed design. For each change you are required to document the rationale for the improvement.

Task 1 - Conceptual Design in ER

Create a conceptual schema for the above database system using the concepts of the Entity-Relationship (ER) model. Show the following design steps: 

• Draw partial ER diagrams showing each entity and all its attributes individually;

NB: Only primary key attributes should be included in the simplified ER diagram (see examples in lecture notes and tutorial 2 solutions). Your ER diagram must make use of the building blocks of ER diagrams including primary key (simple or composite), alternate key, composite attribute, multi-valued attribute, attribute of a relationship, recursive relationship, 1:1 relationship, 1:m relationship, and m:n relationship, among others. 

1)Complete the ER diagram by using the notation shown in the example of the Student relation given below: 

Student{bannerID, fName, lName, address, gender, NIN, compID, DOB, ProgrammeTitle}

Primary key: bannerID

Foreign key: ProgrammeTitle references Programme(pTitle)

alternate key: NIN

NB: Your relational schema must show how you handle each of the building blocks used in your ER diagram including primary key (simple or composite), composite attribute, multi-valued attribute, attribute of a relationship, recursive relationship, 1:1 relationship, 1:m relationship, and m:n relationship, among others. You should use ‘natural’ primary keys and not surrogate keys, which may be prompted by MS Access.

Task 2 - Implement the Database

Create the tables and relationships between tables for database application using MS Access 2013 or newer. All the elements shown above in blue in your relational schema must be properly implemented. Note to enforce the referential integrity including update rules on each of the relationships between tables. Moreover, set field properties and in particular:

• Set Input Mask property for all primary keys whose values start with the first character of the table name followed by 2 digits, making sure the first character is stored in the database.
• Set Validation Rule and Validation text properties for the variables that you deem appropriate and document these decisions in Task 1, 2).
• Set index property for all alternate keys.

Task 3 – Populate the Tables with Data

Populate the tables with meaningful data. Enter sufficient data that reflect the relationships` structural constraints (i.e., participation constraints and cardinality ratio specified with ‘min..max’) and test the queries in Task 4.

Task 4 - Query the Database

Write at least 10 queries using SQL and run them in your database, and you should use screen dumps to show that your SQL statements work: 

Task 5 - Database Application

Develop and implement an application that will allow the database users to access and retrieve data from the database. The application should have a `user friendly` graphical interface. The application should allow the users to perform the following:

a) Run Use Cases for the System;

b) View data in Tables Employee and Clients in datasheet view; 

Task 6 – Considerations on Privacy and Security

The design presented above has no considered data privacy and security requirements. You are requested to produce a one-page critique of the issues that can potentially become incidents.

You should include:

• An Identification of Sensitive data
• Data protection measures that you would add to the design.
• GDPR compliance. Reflect on data minimization, purpose limitation, storage limitation, and data subject rights (e.g., right to access, rectify, or erase personal data). Provide recommendations on how the system can adhere to these principles and ensure the lawful processing of personal data.

Task 7 - Critical Evaluation

Submit a critical assessment of your work, as well as the value of this coursework in understanding and appreciating (or otherwise) the techniques and methods you learned to design and implement a relational database. A brief statement of ‘my contributions’ must be included in here from each member of the group. This section should not be longer than two pages. 

Task 8 – The final coursework submission:  

• Note: You may be required to demonstrate your database application through a Microsoft Teams meeting of 10 minutes or in person, and if so, your coursework will not be marked without the demonstration.
• Your coursework report (all text should be typed using Microsoft Word and nothing else, e.g., PDF or any other software).
• The report should have:
• A cover sheet with the names and banner id of all students involved in the submission.
• Should have a heading for each task.
• For each task, you should clearly explain your reasoning and provide sufficient evidence of your work. This may include screenshots of database design diagrams, database tables, and SQL statements, where applicable.
• Submit only a Word document to the Aula submission point. Do not upload your database files. Instead, include screenshots of your database within the Word document.
• Keep your database files secure until you receive your official marks for the term, as you may be required to demonstrate your database if requested.
• Zipped/ compressed files will NOT be accepted in the submission point.

• The coursework accounts for 50% of the overall module assessment.
• Task 6 (in RED) contributes 10% to the total module assessment.

Marking Scheme for Groups of Four to Five Students:

Good Luck!