The Design and Implementation of a Relational Database Application with Microsoft Access

Note: This coursework should be completed by a group of four or five students. For each group, only one submission should be uploaded to Aula, which must bear the BannerIDs and the full names of all members of the group. Your tasks are specified below. All members of the group must make contributions to all the tasks and a brief statement of ‘my contributions’ must be included in the submission from each member of the group. Otherwise, you may not receive a mark.

Issued: Tuesday 31Jan2023, Week 4 (1^st teaching week) of T2 22-23

Submission dates and times: 23:59 Friday 07Apr2023, a late submission within a week (i.e., 23:59 Friday 15Apr2023 (week 13)) is allowed with a penalty of 10% of scored mark

Introduction

Let us assume that the director of a maintenance company, which has branches in most of the main cities in the United Kingdom, has approached you for the design and implementation of a relational database system that will help improve the services provided by the company’s branches. The database will be used by the staff working in the company branches.

The requirements collection and analysis phase of the database design process has provided the following data requirements for the Vehicles Maintenance database system.

Data Requirements

Each branch employs several vehicle maintenance mechanics. Although they may move from one branch to another, a mechanic always works for one branch only at any given point in time. Each branch deals with many customers. A branch number (start with the letter B followed by 2 digits, e.g., B10), phone number, a fax number, and the address (it is not captured as a composite attribute, but should include at least street, city and postcode) of each branch are recorded in the database. Each branch is managed by one of its mechanics. The database should record the date when the mechanic started managing the branch and his/her annual bonus.

Mechanics repair customers’ vehicles. Each mechanic repairs more than one vehicle and in general a vehicle is repaired by more than one mechanic. The number of hours spent by a mechanic repairing a vehicle is stored in the database. The data stored on each mechanic includes his/her name (first and last), a unique mechanic number (start with the letter T followed by 2 digits, e.g., T10), address (consisting of street, town, postcode), which is captured as a composite attribute, gender, salary, date-of-birth, post type (i.e., senior or junior mechanic) and possibly multiple qualifications. A mechanic may supervise more than one mechanic, but a mechanic is supervised by only one mechanic. Not every mechanic is supervised and not every mechanic is a supervisor.

The data held on each vehicle are registration number, make, fuel type (e.g., petrol or diesel), and vehicle type (e.g., van or car). The registration number uniquely identifies each vehicle. For a vehicle, there might be a service logbook that bears a logbook number and the registration number of the vehicle. Any service logbook is always associated with one and one vehicle only. Vehicles are the property of customers. Each vehicle is owned by one customer. Although a customer may own one or more vehicles, he/she always deals with one branch only. A unique customer number (start with the letter C followed by 2 digits, e.g., C10) identifies each customer. The name (first and last), address (not composite), gender, and one phone number of each customer are also stored.

In addition to vehicles’ maintenance the company branches store car accessories items such as tyres, screen wipers, engine oil, etc., which they sell to their customers. Each branch stores many items and the company make sure that the items are available in all branches. The quantity in stock of each item in each branch is recorded. The data held on each accessory item includes a unique item number (start with the letter A followed by 2 digits, e.g. A10), name, and price. Not all customers buy items but those who do buy more than one item. Similarly, not all items are sold but an item may be sold to more than one customer. Whenever an item is sold the quantity and date of sale are recorded.

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.

Task 1 – Conceptual Design in ER

Create a conceptual schema for the Vehicles Maintenance 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;
• Using UML notation and not the notation with crow foot, draw an ER diagram showing all entities and the relationships between them (including structural constraints, i.e., min..max, and relationship attributes (if any)).

NB: Your ER diagram must make use of the building blocks of ER diagrams including primary key (including possible PPK for weak entities), 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.

Task 2 – Logical Design in Relational

Derive a relational schema from your conceptual schema using the concepts of the relational model.

1. Show how you map Entities, Relationships with various cardinality ratios and participation constraints and multi-valued attributes into a relational schema by using a table like this (assuming that the elements in an ER diagram include those in the left column of the table):

Elements in ER Diagram	which are represented by Elements in Relational Schema
entity Student	relation Student
entity Module	relation Module
CRN[1..*] – multivalued attribute in entity Module	relation CRN with 1 foreign key ModuleCode that references Module(mCode)
entity Programme	relation Programme
isRegisteredFor – m:n relationship between Student and Module	relation isRegisteredFor with 2 foreign keys: BannerID that references Student(sBannerID), ModuleCode that references Module(mCode)
isEnrolledOn – 1:m relationship between entity Student and entity Programme	attribute ProgrammeTitle in relation Student, which is a foreign key that references Programme(pTitle)

• Show your relational schema 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 (including possible PPK for weak entities), 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 3 – Implement the Database

Create the tables and relationships between tables for the Vehicles Maintenance 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 ‘gender’ attribute.
• Set index property for all alternate keys.

Task 4 – Populate the Tables with Data

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

Task 5 – Query the Database

Specify the following queries using SQL and run them in your database, and you should use screen dumps to show that your SQL statements work:

1. For each branch list branch number and the number of mechanics (under an appropriate header) employed by the branch.
2. For each customer who owns more than one vehicle, list the customer number and the number of vehicles (under an appropriate header) he/she owns.
3. List last name, salary, and date-of-birth of all mechanics working in a Glasgow branch. Order result by last name. Note that ‘Glasgow’ should only be part of the address of a branch.
4. For each accessory item whose quantity in stock is less than 15 in any branch, list the branch number, the item name, price, and quantity in stock.
5. List registration number, fuel type, and the owner’s last name of all Vauxhall vehicles.
6. List registration number, make, and fuel type of any vehicle repaired by a senior mechanic.
7. List registration number, make, and fuel type of each van that took more than 4 hours of mechanics’ time in total to repair. Note that more than one mechanic may work on a vehicle. 
8. List last name, gender, salary and date-of-birth of each mechanic whose salary is greater than the average salary of all mechanics.
9. List name and phone number of any customer who did not buy any accessory item.

Task 6 – Database Application by using Forms and associated Macros in Microsoft Access

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:

1. Run the last 3 queries in task 5;
2. View data in 2 tables of your choice in datasheet view;
3. Update data: using multi-table forms users should be able to:

•               add details of a new customer to a branch;

•               add details of a new vehicle to a customer.

Task 7 – Checking your database in terms of Normalisation

Ideally, this should be done before implementation. Due to the timing of the topic of normalisation being late in the module delivery, the 2^nd best way of covering the topic in the coursework seems for you to check your database by conducting a normalisation process on your relational schema and see whether all relations in it are in BCNF. To demonstrate that you are capable of doing this, show, as an example in this report, how you conduct the normalisation process on the relation concerning the details of technicians including the decomposition of the relation if necessary. Your normalisation process should include the identification of FDs, CKs and non-key attributes, and the PK, and checking the relation(s) against the definitions of 2NF, 3NF and BCNF. Note that there is no need for you to show how you check other relations in your schema.  

Task 8 – 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.

The final coursework submission: 

• 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 demon. Your coursework report (all text should be typed using Microsoft Word and nothing else, e.g., PDF or any other software) should consist of Tasks 1, 2, 5 (the SQL code and result screenshots), 7 and 8 (in this order).
• Save your coursework report and your database application within one folder and then compress the folder and upload it to Aula.
• Note that the filename of your coursework report and the name of your database application should have a prefix, i.e., your full name, for example, johnSmithDDCW.docx and johnSmithVehicleMaintenance.accdb

Marking Scheme for group of four or five students:

            Conceptual Design in ER                               20%

Logical Design in Relational                          20%

Implementation

Tables, relationships between tables 15%

Populate tables with data                    5%      

            Queries                                               15%

            Database Application                         10%

Normalisation                                                 10%

Critical Evaluation                                         5%

Junkang Feng

Get expert help for Database Design and many more. 24X7 help, plag free solution. Order online now!