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 $($DS$1)$ is needed to design a way to connect the Employee Entity with the Service Catalogue $($ne$.$ ClientAppointments$)$ that is $3$NF 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 $($DS$2)$ is needed to design a way to connect the Service Entity with the CustomerCataloge $($n$.$e ServicesProvided$)$ that is $3$NF 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 DS$2.$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 $($DS$3)$ is needed to design a way to connect the Invoice Entity with the ClientAppointments $($DS$1)$ that is $3$NF 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 DS$1,$ DS$2$ and DS$3).$ 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.

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 Prog