COMP11007: Database Design - IT Computer Science Assignment Help

Assignment Task

Coursework Assignment

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

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):

2) Show your relational schema using the notation shown in the example of the Student relation given below:

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 the relationships' 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:

a) For each branch list branch number and the number of mechanics (under an appropriate header) employed by the branch.

b) 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.

c) 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.

d) 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.

e) List registration number, fuel type, and the owner’s last name of all Vauxhall vehicles.

f) List registration number, make, and fuel type of any vehicle repaired by a senior mechanic.

g) 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.

h) List last name, gender, salary and date-of-birth of each mechanic whose salary is greater than the average salary of all mechanics.

i) 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:

a) Run the last 3 queries in task 5;

b) View data in 2 tables of your choice in datasheet view;

c) 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.

This COMP11007-IT Computer Science Assignment has been solved by our IT Computer Science Experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics & referencing Style. Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered.

You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turn tin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.