Design and Implementation of a Relational Database Application

Problem Statement

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), a 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 (starting with the letter T followed by 2 digits, e.g., T10), and 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 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 (star starting with the letter C followed by 2 digits, e.g., C10) identifies each customer. The name (first and last), address (not composite), gender, and phone number of each customer are also stored.

In addition to vehicle maintenance, the company branches store car accessories items such as tires, screen wipers, engine oil, etc., which they sell to their customers. Each branch stores many items and the company makes 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 (starting 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.

General

Tasks

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 notation with crowfoot, draw a simplified ER diagram showing all entities and the relationships between them (including structural constraints, i.e., min..max, and relationship attributes (if any)).

 

This ERD includes six entity types: Mechanic, Branch, Supervision, Vehicle, Customer, and Item. The relationships between these entities are shown using different types of lines and notations.

One location has a mechanic who, after being elevated, runs it. As a result, the “managed by” connection between Branch and the Mechanic indicates that a branch is managed by a single mechanic.

Both a mechanic and a mechanic can be under the supervision of another technician. The “supervises” connection between the mechanic and the mechanic, which is carried out by the Supervision body, serves as a representation of this.

A mechanic works on many different cars, and different mechanics work on the same vehicle. The “repairs” relationship between the mechanic and the vehicle, which is implemented as a many-to-many relationship with the extra property “hours spent,” serves as a representation of this.

One or more vehicles are held by a single client, and each customer possesses one or more vehicles. The “owns” connection between Customer and Vehicle serves as a representation of this.

A branch interacts with many clients, while a customer deals with one branch. The “deals with” connection between Customer and Branch serves as a representation of this.

A branch holds a lot of things, and branches store a lot of things. The “sells” relationship between Branch and Item, which is implemented as a many-to-many relationship with the extra property “quantity in stock,” serves as a representation of this.

A product can be offered to numerous customers, and a buyer can purchase numerous

A mechanic can oversee other mechanics, and each mechanic can only be under the supervision of one other mechanic, as mentioned in the problem. The Supervision entity is used to carry out this interaction.

The interaction between the two mechanics is represented by the supervision object. It has two foreign values, supervisor_id and supervisee_id, which stand for the identities of the two mechanics, one of whom is overseeing the other.

Because each mechanic may participate in numerous supervisions, the supervisor_id foreign key establishes a one-to-many connection between the mechanic and supervision entities. The supervisee_id foreign key establishes a one-to-one connection between the Mechanic entity and the Supervision entity because each Mechanic can only be overseen by one other Mechanic.

The connection between mechanics and their managers and supervisees, as well as how it is carried out by the Supervision entity, is captured in this ERD.

Each branch employs several mechanics, and each mechanic only works for one branch at a moment, as mentioned in the problem. The “employs” connection between Branch and the Mechanic serves as a representation of this partnership.

One of its technicians oversees each branch, and each mechanic can only oversee one branch at a moment. The “manages” connection between Branch and the Mechanic serves as a representation of this interaction. The Mechanic entity also has characteristics for the mechanic’s yearly incentive and the day they began overseeing the branch.

Each location serves a large number of clients. The “deals with” connection between Branch and Customer serves as a representation of this interaction.

The Branch entity has characteristics for the branch number, which begins with “B” and is followed by two numbers, as well as for the location, phone number, and fax number. The street, city, and postcode should be included at the very least in the location, which is not recorded as a composite property.

This ERD documents the connections between Branch, Mechanic, and Customer as well as how these relationships are put into practice.

Task 2 – Logical Design in Relational

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

• 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	relation Student entity entity entity
entity Module	relation Module
CRN [1…*] – the multivalued attribute in entity Module	relation CRNwith 1 foreign key Module Code that references the Module(model)
entity Programme	relation Programme
I registered for – m: n relationship between Student and Module	relation isRegisteredForwith 2 foreign keys: BannerID that references Student(spanner), Module Code that references Module(model)
isEnrolledOn – 1:m relationship between entity Student and entity Programme	attribute Programme Title about Student, which is a foreign key that references Programme(title)

 

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

Branch{ branch number, manager number, phone_number, address, postcode, city, street, start_date, fax number}

Primary Key:	branch number
Foreign Key:	Manager number references Mechanic
Alternate Key:

 

Mechanic{ mechanic number, first name, last_name, post_type, annual_bonus, qualification, address, gender, salary, date_of_birth }

Primary Key:	mechanic number
Foreign Key:
Alternate Key:

 

Customer {customer_number, first_name, last_name, gender, date_of_birth, address}

Primary Key:	customer number
Foreign Key:
Alternate Key:

 

Vehicle {reg_number, customer_number, make, vehicle_type, log_number, fuel_type, address}

Primary Key:	reg_number
Foreign Key:	customer_number references Customer log_number references Log_book
Alternate Key:

 

Log_book{logbook_number, logbook_date, logbook_text}

Primary Key:	logbook_number
Foreign Key:
Alternate Key:

 

items{item_number, name, price, qualtity}

Primary Key:	item_number
Foreign Key:
Alternate Key:

 

item_sale {item_sale_id, item_number, customer_number, date_of_sale}

Primary Key:	item_sale_id
Foreign Key:	item_number references items
Alternate Key:

 

repairs {repairs_id, vehicle_number, mechanic_number, repair_date }

Primary Key:	repairs_id
Foreign Key:	vehicle_number references vehicle mechanic_number references mechanic
Alternate Key:

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.

Brach Table Input Mask Property

 

The methods listed below can be used to establish the Input Mask property for all main keys according to specification if Microsoft Access is being used as the database management system:

1. Open Design View on the desktop.
2. By clicking on the field’s or fields’ column heading, choose the main key field(s).
3. Find the Input Mask option in the Field Properties area.
4. In the Input Mask property window, type the following input mask pattern:
5. B99*;
6. The first letter should be a “B” according to this input mask pattern, which should be followed by any numeric figure (*) that can be repeated zero or more times. (;).
7. Save the table changes.

Customer Table Input Mask Property

 

Item Sales Table Input Mask Property

 

Items Table Input Mask Property

 

Log Book Table Input Mask Property

 

Mechanic Table Input Mask Property

 

Repairing Table Input Mask Property

 

Vehicle Table Input Mask Property

 

• Set Validation Rule and Validation text properties for the ‘gender’ attribute.

 

 

The methods listed below can be used to establish the Validation Rule and Validation Text properties for a database in Microsoft Access that has the ‘gender’ attribute as a field:

1. Open the table in Design View.
2. Locate the ‘gender’ field in the table design.
3. Click on the ‘gender’ field to select it.
4. In the Field Properties section, locate the Validation Rule property.
5. Enter the following validation rule into the Validation Rule property box: “Male” Or “Female”
6. This validation rule will only allow the values “M” or “F” to be entered into the ‘gender’ field.
7. In the Field Properties section, locate the Validation Text property.
8. Enter the following validation text into the Validation Text property box:
9. “Please enter either Male or Female for gender.”
10. This validation text will be displayed if an invalid value is entered into the ‘gender’ field.
11. Save the table changes.

By doing this, it will be ensured that only valid values are entered into the field and that any effort to enter a value into the “gender” field that is not “Male” or “Female” will result in an error message with the stated validation text.

• 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 relationship’s structural constraints (i.e., participation constraints and cardinality ratio specified with ‘min..max’) and test the queries in Task 5.

Branch Table

 

Customer Table

 

Item Sale Table

 

Items Table

 

Log Book Table

 

Mechanic Table

 

Repairing Table

 

Vehicle Table

 

Relationships between relations or table

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:

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

Here is a SQL query that should carry out the job you described, assuming that the required data is kept in distinct tables for “branches” and “mechanics” and that the “branch_number” field is present in the “branches” table:

SELECT Count(*) AS number_mechanic, mechanic.branch_number

FROM mechanic, branch WHERE branch.branch_number=mechanic.branch_number Group by branch.branch_number

Using the branch_id field, this query joins the “mechanics” and “branches” tables. It then groups the results by branch_id, tallies the number of mechanics connected to each branch, and provides a result set with two columns: “branch_number” and “num_mechanics.” The “AS” term in the SELECT clause is used to appropriately title the “num_mechanics” column.

• 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.

Assuming that the customer number is stored in the “customer_number” column of the “vehicles” table, this query should group the vehicles by customer number, count the number of vehicles associated with each customer, and return the results only for customers who own more than one vehicle. The result set will include two columns: “customer_number” and “num_vehicles”.

SELECT customer_number, COUNT(*) AS num_vehicles

FROM vehicle

GROUP BY customer_number

HAVING COUNT(*) > 1;

• List the last name, salary, and date of birth of all mechanics working in a Glasgow branch. Order results by last name. Note that ‘Glasgow’ should only be part of the address of a branch.

Here is a SQL query that should carry out the job you described; assuming the required data is kept in distinct tables for “mechanics” and “branches” and that the “address” field for branches includes the city name:

SELECT last_name, salary, date_of_birth

FROM mechanic INNER JOIN branch ON  branch.manager_number=mechanic.mechanic_number

WHERE branch. address LIKE ‘%Glasgow%’

ORDER BY last_name;

The “mechanics” table and the “branches” table are joined using the branch_id field. The result is then filtered to only include rows whose branch location includes the word “Glasgow,” and the result set is sorted by last name. There will be three entries in the outcome set: “last_name,” “salary,” and “date_of_birth.”

• 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.

The SQL query would appear as follows, assuming that the pertinent tables are called accessories and branches and that the pertinent fields are accessory_id, branch_id, name, price, and quantity_in_stock:

SELECT branch_number, name, price, quantity

FROM items

WHERE quantity<15;

This query uses the branch_id column to combine the accessories table and the branches table and then chooses the appropriate fields for the output. The WHERE phrase limits the results to those products where there are fewer than 15 of them in storage.

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

Here is a SQL query that should carry out the job you described, presuming that the registration number, gasoline type, and ownership information are kept in distinct records for “vehicles,” “owners,” and “brands,” and that Vauxhall vehicles are recognized by a particular brand ID in the “brands” table:

SELECT vehicle.reg_number, vehicle. make vehicle.fuel_type, customer.last_name

FROM vehicle INNER JOIN customer ON vehicle.customer_number=customer.customer_number

WHERE vehicle.vehicle_name=’vauxhall’;

The “vehicles” table is joined with the “owners” table based on the owner_id field and the “brands” table based on the brand_id field. The output is then filtered to only include rows with “Vauxhall” as the brand name, and the “registration_number,” “fuel_type,” and “last_name” fields are chosen. Three columns—”registration_number,” “fuel_type,” and “last_name”—will be included in the outcome collection.

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

Here is a SQL query that should carry out the job you described, presuming that the required data is kept in separate tables for “vehicle,” “mechanic,” and “repairs,” and that “senior mechanic” is recognized by a “senior” flag in the mechanic table:

SELECT vehicle.reg_number, vehicle.fuel_type, mechanic.post_type

FROM vehicle, mechanic, repairs

WHERE repairs.mechanic_number = mechanic.mechanic_number AND vehicle.reg_number= repairs.vehicle_number AND mechanic.post_type = ‘senior’;

The “vehicle” table is joined with the “repairs” table using the vehicle_number field, and the “mechanic” database is then joined using the mechanic_number field. It chooses the reg number, gasoline type, and post type of each car that satisfies these requirements and filters the result set to only include entries where the technician is a senior mechanic (identified by the “senior” flag being put into post_type). There will be three entries in the outcome set: “reg_number,” “post_type,” and “fuel_type.”

• List the 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.

The SQL query to retrieve the necessary data would be as follows, assuming the necessary data is kept in a database called “repairs” with fields “registration_number,” “make,” “fuel_type,” “mechanic_number,” and “hours_spent.”

SELECT vehicle_number, make, fuel_type FROM repairs

WHERE vehicle_number IN ( SELECT vehicle_number

FROM repairs GROUP BY vehicle_number

HAVING SUM(hours_spent) > 4

)

AND mechanic_number IN (

SELECT DISTINCT mechanic_number

FROM repairs

)

To determine which vehicles’ license numbers required more than 4 hours of maintenance time in total, this query utilizes a sub-query. The initial database is then filtered to only contain entries with these registration numbers and one or more mechanics who serviced the van. Each van that satisfies these requirements is listed in the end outcome set along with its license number, model, and fuel type.

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

The SQL command to obtain the needed data is as follows:

SELECT last_name, gender, salary, date_of_birth

FROM mechanic

WHERE salary > (SELECT AVG(salary) FROM mechanic)

This query gets the last name, gender, salary, and date of birth of every mechanic whose salary is higher than the average after computing the average salary of all technicians using a subquery.

• List the name and phone number of any customer who did not buy any accessory item.

The SQL query for listing the name and phone number of any customer who did not purchase any accessory items can be written as follows, assuming that the customer and accessory item data is stored in two separate tables, “Customers” and “Items,” with a foreign key “customer_number” in the “Items” table referencing the “item_id” column in the “Customers” table.

SELECT first_name, phone

FROM Customer

WHERE Customer_number NOT IN (SELECT DISTINCT customer_number FROM item_sale)

Explanation:

All unique customer IDs are chosen from the “Items” database by the subquery (SELECT DISTINCT customer_number FROM Items).

All clients whose IDs are absent from the sub-query result are chosen and their names and phone numbers are included in the primary query. This implies that the query will only yield results for users who have not purchased any accessories.

Task 6 – Database Application

Develop and implement an application that will allow 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.

A Microsoft Access database file called “Vehicles Maintenance database. accdb” was developed to store and manage information on vehicle maintenance. This file’s function is to give users a systematic and orderly manner to save data about automobiles, their maintenance schedules, and other pertinent information.

Users may access and retrieve data from the database using the application’s user-friendly graphical user interface, which was built and implemented utilizing this database. Users may interact with the database appealingly and simply because of the interface’s simple design and ease of use.

Task 7–Checking your database in terms of Normalisation

Ideally, this should be done before implementation. Due to the timing of the topic of normalization 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 normalization 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 normalization process on the relation concerning the details of technicians including the decomposition of the relation if necessary. Your normalization process should include the identification of FDs, CKs, 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.

The terms Functional Dependencies (FDs), Candidate Keys (CKs), Non-Key Attributes (NKA), and Primary Key (PK) will all be used in the context of normalization to demonstrate their respective ideas with an illustration.

Consider a table “Branch” with the following attributes: branch_number, manager_number, phone_number, and address.

Functional Dependencies (FDs):

In this table, we can have the following functional dependencies:

branch_number -> manager_number, phone_number, address

manager_number -> phone_number, address

These functional dependencies indicate that the values of branch_number and manager_number determine the values of phone_number, and address respectively.

Candidate Keys (CKs):

A candidate key is a minimal set of attributes that can uniquely identify each row in a table. In this example, branch_number can be a candidate key because it uniquely identifies each branch in the table. manager_number can also be a candidate key because it uniquely identifies each mechanic. So, we have two candidate keys: { branch_number } and { manager_number}.

Non-Key Attributes:

Non-Key Attributes are the attributes in a table that are not part of any candidate key. In this example, phone_number, and address are non-key attributes because they are dependent on the candidate keys (branch_number and manager_number) and are not used to uniquely identify rows in the table.

Primary Key (PK):

The Primary Key is a candidate key that is chosen as the unique identifier for each row in a table. In this example, let’s assume that we choose branch_number as the primary key for the “Branch” table. This means that branch_number will be used as a unique identifier for each branch in the table, and it cannot contain duplicate values.

We can make sure that the “Branch” table is well-structured, free of duplication, and that data integrity is upheld by correctly designating candidate keys, primary keys, and non-key attributes. Creating a relational database that is effective and well-designed, is a crucial stage in the normalization process.

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 here from each member of the group. This section should not be longer than two pages.

Understanding relational databases and their design principles is essential in a Database Design course because it provides a strong basis for organizing, controlling, finding, and modifying data in practical applications. Additionally, it equips you with crucial abilities that are in high demand on the job market and necessary for dealing with data across a range of sectors.

Relational databases are a key idea in database architecture and are frequently employed in contemporary data management systems. In Database Design training, understanding relational databases is essential for several reasons.

1. Data organization and management:

Relational databases offer a systematic and organized method of data administration and organization. They show data in a tabular style with rows and columns, which makes it simple to comprehend and manipulate. You can successfully organize and handle data in practical applications by learning how to create relational databases, which is essential for creating reliable and scalable database systems.

2. Data consistency and integrity:

Relational databases offer tools for ensuring data consistency and integrity. They create relationships between databases using primary keys and foreign keys, ensuring that data is correctly and uniformly displayed across many tables. You can keep data integrity and continuity by designing relational databases correctly, which is essential for guaranteeing the quality and dependability of your data.

3. Data retrieval and manipulation:

Relational databases allow for sophisticated data selection, filtering, merging, and aggregation processes. These procedures are necessary for deriving significant conclusions from sizable databases and producing useful information from the data. You can quickly access and manipulate data for various data analysis and reporting duties by learning how to build and query relational databases.

4. Scalability and performance:

Relational databases offer methods for enhancing performance, such as indexing and normalization, and are built to manage large quantities of data. Effective database design strategies, like normalization, can help reduce duplication and enhance query speed, resulting in quicker and more effective database operations. For the development of high-performing database systems that can manage big amounts of data and multiple users, it’s crucial to understand how to build scalable and performant relational databases.

5. Industry-standard:

Relational databases are used frequently in industry and are regarded as the industry standard for data administration tools. Since most companies and organizations depend on relational databases to store, manage, and analyze their data, knowledge of relational databases is highly valued in the employment market. For prospective database managers, data architects, and data scientists, knowledge of relational databases and their design principles is crucial.F

 

For more information, visit our Site: https://cgit.pk/.