Case Study: Rapid Bikes
Background
You have been asked to design and construct a database system for Rapid Bikes, a bicycle repair business based in London, United Kingdom. They repair and refurbish bicycles, performing maintenance and replacing parts. They source components from companies across the world. They want a system that will help them keep track of their work, recording customers, jobs, components used in jobs and members of staff involved in each job.
How the company organises its work:
Rapid Bikes carry out repair for customers. A customer brings in one or more cycles. A job is for one cycle only. A job usually involves fitting components and is carried out by one or more member of staff. Rapid Bikes need to keep track of their jobs, customers, staff and all aspects of the jobs they perform.
The system should be capable of storing all the information needed for Rapid Bikes to carry out their business.
Please Note Further details of the case study are shown in the documents below which give examples of data that can be taken as representative of a much larger data set. You will need to add additional data.
Please note that the data as represented here is not necessarily in a normalised state and it is your job, as the database developer, to organise the data in its most optimal state.
Document 1 (Job Details Form)
This document is used to record the details of a job. It includes customer information, the job number, the components used on the job and the staff involved on the job. Each job has a document like this one. As can be seen from this document a job for one customer can have many different components and can involve one or more members of staff.
Document 2 (Part of a monthly summary of jobs)
This document is an excerpt from a list of jobs that have been undertaken in a given
month. It shows limited details about the job itself. Full details of a job are shown on
the Job Details Form (an example of which is shown as Document 1). Note that a
customer might come in with a cycle on more than one occasion (e.g. Job ID 78123
and Job ID 78124). Note also that a customer might own more than one cycle (e.g.
Customer ID C762 has cycles with numbers B89 and B23).
Document 3 (Manufacturer Details)
This document is used to record the contact details of manufacturers that Rapid Bikes
source components from.
Document 4 (Staff List)
This document records basic staff information. It includes the hourly paid rate of each member of staff. Note that the format of the name is different to that shown on the Job Details form.
Document 5 (Components List)
This document shows a sample of a list of components and their details including
price. Prices shown are in UK pounds (£).
Document 6
This document records relevant information about the countries where manufacturers are based. The Port attribute records where the manufacturer ships their components from. The Current Export Regulation Code records the legal reference that needs to be consulted for the export regulations for that country.
Solution:
TASK 1
Assumptions:
It is possible for a single customer to possess several cycles, with each cycle containing multiple components.
Each employment opportunity is linked to a singular cycle and a solitary client.
It is possible for a single job to comprise of several components that are worked on.
It is possible to assign a single employee to each job.
It is possible for each employee to undertake multiple job assignments.
It is a requirement that each component is associated with a single manufacturer.
Entities:
The system stores customer data, which includes their unique identifier, first and last name, street address, town, county, postal code, and contact number.
The Cycle entity contains data pertaining to cycles, such as their cycle number, component code, component name, component type, and manufacturer. It is linked to a client.
The component entity encompasses data pertaining to various components such as their unique code, nomenclature, classification, producer, and monetary value.
The system stores job-related data such as the unique job identifier, commencement and termination dates, and the total number of hours dedicated to the job. This phenomenon is linked to a recurring pattern involving the interplay between a consumer and a worker.
The system stores data pertaining to employees, encompassing their unique employee code, given name, family name, and hourly remuneration.
The "Manufacturer" entity retains data pertaining to manufacturers, encompassing their appellation, physical location, and nation of origin.
Relationships:
It is possible for a single customer to possess multiple cycles.
A single cycle has the potential to encompass numerous constituents.
A single occupation is linked to a solitary cycle and a lone client.
Each employee can be assigned only one job.
It is possible for a single employee to undertake multiple job responsibilities.
It is a fundamental principle that a single manufacturer can produce only one component.
Task 2
Normalisation is a fundamental technique employed in database management to optimise data storage by eliminating redundant data and ensuring that data is stored in the most efficient manner. The process entails decomposing a table into smaller, more granular tables in order to eliminate the duplication of data and enhance the coherence and soundness of data. Normalisation is a systematic procedure that entails adhering to a series of normal forms to attain a completely normalised database.
Within this particular context, six distinct entities have been identified, namely: Customer, Cycle, Component, Job, Employee, and Manufacturer. The author has additionally discerned the characteristics and associations of the entities in question, and subsequently utilised this information to construct an entity-relationship diagram. Based on the normalisation process, a set of relations has been created from the ER diagram.
The Rapid Bikes system has undergone a series of normalisation steps, which include:
The first normal form (1NF) was achieved by creating a distinct Component entity for the components utilised in each cycle, thereby removing any repeating groups in the Cycle entity.
The second normal form (2NF) was achieved by removing partial dependencies within the Job entity. This was accomplished through the creation of distinct Cycle and Customer entities, and the subsequent inclusion of foreign keys within the Job entity.
The third normal form (3NF) was achieved by segregating the manufacturers of the components used in each cycle into a distinct Manufacturer entity, thereby eliminating any transitive dependencies in the Cycle entity.
The set of relations that arise from the Rapid Bikes system are presented below:
The database table consists of the following attributes: customer_id (primary key), first_name, last_name, street_address, town, county, postcode, and telephone_number.
The data model includes a table named "Cycle" which consists of three columns: cycle_number (primary key), customer_id (foreign key), and component_code (foreign key).
The aforementioned data model consists of a table named "Component" with attributes including component_code (primary key), component_name, component_type, manufacturer, and price.
The job table consists of primary key job_id, start_date, end_date, hours_worked_on_job, foreign key cycle_number, foreign key customer_id, and foreign key employee_code.
The entity "Employee" consists of the attributes "employee_code" (primary key), "first_name", "surname", and "hourly_rate".
The data entity under consideration is the manufacturer, which comprises a primary key denoted by the name attribute, as well as the address and country attributes.
Every table is equipped with a unique primary key, as well as any required foreign keys and pertinent attributes. The tables have been normalised to third normal form (3NF) in order to eliminate any instances of transitive dependencies among the attributes.
In general, the normalisation procedure has yielded a systematically arranged collection of relationships that eradicate superfluous data and guarantee the coherence and soundness of data. The tables that ensue are facile to uphold and revise, and inquiries can be executed proficiently on them.
Presented below is a tabular representation of the data dictionary for each table:
Table: Customer
Column Name | Data Type | Primary key | Foreign Key |
customer_id | int | Yes | |
first_name | varchar(255) | No | |
last_name | varchar(255) | No | |
street_address | varchar(255) | No | |
town | varchar(255) | No | |
county | varchar(255) | No | |
postcode | varchar(10) | No | |
telephone_number | varchar(20) | No |
Table: Cycle
Column Name | Data Type | Primary Key | Foreign Key |
cycle_number | varchar(20) | Yes | |
customer_id | int | No | Yes |
component_code | varchar(20) | No | Yes |
Table: Component
Column Name | Data Type | Primary Key | Foreign Key |
component_code | varchar(20) | Yes | |
component_name | varchar(255) | No | |
component_type | varchar(255) | No | |
manufacturer | varchar(255) | No | Yes |
price | decimal(10,2) | No |
Table: Job
Column Name | Data Type | Primary Key | Foreign Key |
job_id | varchar(20) | Yes | |
start_date | date | No | |
end_date | date | No | |
hours_worked_on_job | int | No | |
cycle_number | varchar(20) | No | Yes |
customer_id | int | No | Yes |
employee_code | varchar(20) | No | Yes |
Table: Employee
Column Name | Data Type | Primary Key | Foreign Key |
employee_code | varchar(20) | Yes | |
first_name | varchar(255) | No | |
surname | varchar(255) | No | |
hourly_rate | decimal(10,2) | No |
Table: Manufacturer
Column Name | Data Tyoe | Primary Key | Foreign key |
name | varchar(255) | Yes | |
address | varchar(255) | No | |
country | varchar(255) | No |
The suitability of the data types employed in each table corresponds to the nature of the attributes they are intended to contain. Every table is assigned a primary key, denoted by a (PK) in the "Primary Key" column. When an attribute in a table serves as a foreign key that refers to a primary key in a different table, it is denoted by the acronym (FK) in the "Foreign Key" column.
In general, it can be observed that the tables within the Rapid Bikes system exhibit a high degree of organisation and adhere to the principles of normalisation in order to mitigate the presence of redundant data and promote the reliability and accuracy of the stored information. The data dictionary that ensues offers a lucid synopsis of the characteristics and interconnections of every table, thereby serving as a valuable resource for the upkeep and retrieval of information from the database.
TASK 3
Here is the entity listing for the Rapid Bikes system:
Customer
customer_id (primary key)
first_name
last_name
street_address
town
county
postcode
telephone_number
Cycle
cycle_number (primary key)
customer_id (foreign key)
component_code (foreign key)
Component
component_code (primary key)
component_name
component_type
manufacturer
price
Job
job_id (primary key)
start_date
end_date
hours_worked_on_job
cycle_number (foreign key)
customer_id (foreign key)
employee_code (foreign key)
Employee
employee_code (primary key)
first_name
surname
hourly_rate
Manufacturer
name (primary key)
address
country
The entity's primary key is indicated adjacent to the entity's name. The characteristics pertaining to each entity are enumerated beneath the respective entity denomination. The foreign keys are denoted within brackets adjacent to the corresponding attribute name. The Job entity comprises three distinct foreign keys, namely cycle_number, customer_id, and employee_code. The primary keys in the Cycle, Customer, and Employee entities are referenced by the foreign keys, respectively.
The present listing of entities offers a comprehensive summary of the entities, attributes, and relationships that are present in the Rapid Bikes system. This information can prove to be valuable in the context of designing, managing, and retrieving data from the database.
TASK 4:
CREATE TABLE Customer (
customer_id INT PRIMARY KEY,
first_name VARCHAR(255),
last_name VARCHAR(255),
street_address VARCHAR(255),
town VARCHAR(255),
county VARCHAR(255),
postcode VARCHAR(10),
telephone_number VARCHAR(20)
);
CREATE TABLE Cycle (
cycle_number VARCHAR(20) PRIMARY KEY,
customer_id INT,
component_code VARCHAR(20),
FOREIGN KEY (customer_id) REFERENCES Customer(customer_id),
FOREIGN KEY (component_code) REFERENCES Component(component_code)
);
CREATE TABLE Component (
component_code VARCHAR(20) PRIMARY KEY,
component_name VARCHAR(255),
component_type VARCHAR(255),
manufacturer VARCHAR(255),
price DECIMAL(10,2)
);
CREATE TABLE Job (
job_id VARCHAR(20) PRIMARY KEY,
start_date DATE,
end_date DATE,
hours_worked_on_job INT,
cycle_number VARCHAR(20),
customer_id INT,
employee_code VARCHAR(20),
FOREIGN KEY (cycle_number) REFERENCES Cycle(cycle_number),
FOREIGN KEY (customer_id) REFERENCES Customer(customer_id),
FOREIGN KEY (employee_code) REFERENCES Employee(employee_code)
);
CREATE TABLE Employee (
employee_code VARCHAR(20) PRIMARY KEY,
first_name VARCHAR(255),
surname VARCHAR(255),
hourly_rate DECIMAL(10,2)
);
CREATE TABLE Manufacturer (
name VARCHAR(255) PRIMARY KEY,
address VARCHAR(255),
country VARCHAR(255)
);
TASK 5:
-- Populate Customer tableINSERT INTO Customer (customer_id, first_name, last_name, street_address, town, county, postcode, telephone_number)VALUES (762, 'Sid', 'Ustinov', '11 Dell Street', 'Swindon', 'Wiltshire', 'SNI 4AB', '09876 12356');
-- Populate Component tableINSERT INTO Component (component_code, component_name, component_type, manufacturer, price)VALUES ('S88', 'Jusitso 6 and 7-Speed Tourney Derailleur - Black', 'Gear', 'Jusitso', 45.99), ('S179', 'Drake 26" Front Wheel, Double Wall, Quick Release Axle', 'Wheel', 'Drake', 60), ('S29', 'Faway Comfortable Men Women Bike Seat - Soft Memory Foam Padded Bicycle Seat with Taillight', 'Saddle', 'Faway', 17.99);
-- Populate Job tableINSERT INTO Job (job_id, start_date, end_date, hours_worked_on_job, cycle_number, customer_id, employee_code)VALUES (78123, '2022-02-03', '2022-02-03', 2, 'B89', 762, 'E31'), (78124, '2022-02-07', '2022-02-09', NULL, 'B89', 762, 'E31'), (78125, '2022-02-07', '2022-02-08', NULL, 'B23', 762, NULL);
-- Populate Employee tableINSERT INTO Employee (employee_code, first_name, surname, hourly_rate)VALUES ('E31', 'Joe', 'Lane', 25), ('E34', 'Sally', 'Collins', 30), ('E40', 'Anita', 'Khan', 25), ('E45', 'Frances', 'Lang', 30), ('E22', 'Thomas', 'Winder', 20);
-- Populate Manufacturer tableINSERT INTO Manufacturer (name, address, country)VALUES ('Jusitso', '134-1286,', 'Japan'), ('Sunny', 'Ikanikeisaiganaibaai, Osaka', 'Taiwan'), ('Drake', 'Prefecture 590-8577, Japan', 'United Kingdom'), ('Faway', 'No. 300, Jian Guo 3rd. Road,', 'Germany'), ('Stow', 'Chang Hua, Taiwan, R.O.C.', 'United Kingdom');
-- Populate Cycle tableINSERT INTO Cycle (cycle_number, customer_id, component_code)VALUES ('B89', 762, 'S88'), ('B33', 233, NULL), ('B41', 567, NULL), ('B23', 762, NULL), ('B44', 345, NULL), ('B99', 900, NULL);
TASK 6
a) Write a query that selects the details for the components that cost more than
£60.00.
SELECT *FROM ComponentWHERE price > 60.00;
b) Write a query that selects the details for components that are NOT sourced
from the United Kingdom.
SELECT *FROM ComponentWHERE manufacturer NOT IN ( SELECT name FROM Manufacturer WHERE country = 'United Kingdom');
c) Write a query that selects all the jobs that were completed between the 9th
and 11th of February 2022.
SELECT *FROM JobWHERE end_date BETWEEN '2022-02-09' AND '2022-02-11';
d) Write a query that shows all components used on the Job 78123.
SELECT Component.*FROM ComponentJOIN Cycle ON Component.component_code = Cycle.component_codeJOIN Job ON Cycle.cycle_number = Job.cycle_numberWHERE Job.job_id = 78123;
e) Write a query that shows all the customer details and all job details for
customer C345.
SELECT *FROM CustomerJOIN Job ON Customer.customer_id = Job.customer_idWHERE Customer.customer_id = 'C345';
f) Write a query that produces the components from Japan that are used on
Job 78123.
SELECT Component.*FROM ComponentJOIN Cycle ON Component.component_code = Cycle.component_codeJOIN Job ON Cycle.cycle_number = Job.cycle_numberJOIN Manufacturer ON Component.manufacturer = Manufacturer.nameWHERE Job.job_id = 78123 AND Manufacturer.country = 'Japan';
g) Write a query that shows the total costs for Job 78123 including the cost of
all components and the total hourly costs of the labour.
SELECT SUM(price) AS total_component_cost, SUM(hours_worked_on_job * hourly_rate) AS total_labour_cost, SUM(price) + SUM(hours_worked_on_job * hourly_rate) AS total_job_costFROM ComponentJOIN Cycle ON Component.component_code = Cycle.component_codeJOIN Job ON Cycle.cycle_number = Job.cycle_numberJOIN Employee ON Job.employee_code = Employee.employee_codeWHERE Job.job_id = 78123;
h) Use SQL to produce the information that could be used as the basis for the
Job Details Form shown in document 1.
SELECT Customer.customer_id, Customer.first_name, Customer.last_name, Customer.street_address, Customer.town, Customer.county, Customer.postcode, Customer.telephone_number, Job.job_id, Job.start_date, Job.end_date, Cycle.cycle_number, Component.component_code, Component.component_name, Component.component_type, Component.manufacturer, Employee.employee_code, Employee.first_name AS emp_first_name, Employee.surname, Employee.hourly_rateFROM CustomerJOIN Job ON Customer.customer_id = Job.customer_idJOIN Cycle ON Job.cycle_number = Cycle.cycle_numberJOIN Component ON Cycle.component_code = Component.component_codeJOIN Employee ON Job.employee_code = Employee.employee_code;
TASK 7:
-- Customer table
-- Component table
-- Job table
-- Employee table
-- Manufacturer table
-- Cycle table
TASK 8
The management of transactions is a crucial component of all database systems. Transactions guarantee that modifications to the database are executed in an atomic, consistent, and durable manner. Transaction management holds significant importance within the Rapid Bikes system, as it plays a crucial role in safeguarding against data corruption, loss, and inconsistency.
The following concerns pertain to transaction management in the recently implemented database system:
The provision of support for transactions is deemed essential as they constitute a fundamental unit of work that the system must be capable of accommodating. It is imperative that any modifications made to the database are consolidated into transactions that are executed as a single unit, either committed or rolled back in an indivisible manner. The provision of transactional support by the database system is necessary to guarantee the maintenance of data integrity, consistency, and durability.
Concurrency control is an essential mechanism that guarantees the prevention of interference among multiple users or processes that access the database. It is imperative for the database system to incorporate concurrency control mechanisms, such as locking or multi-version concurrency control (MVCC), to avert conflicts and uphold consistency.
The database system must offer recovery mechanisms to guarantee the restoration of the database to a consistent state in the event of a system failure or error. Various mechanisms can be employed for recovery, such as database backups, journaling, logging, or checkpointing.
The concept of isolation levels pertains to regulating the extent to which modifications executed by a given transaction are perceptible to other transactions. To ensure appropriate levels of concurrency and consistency for various applications, it is imperative that the database system supports diverse isolation levels, including but not limited to read committed, repeatable read, and serializable.
Deadlock detection and prevention is a crucial aspect of transaction management. Deadlocks arise as a result of multiple transactions waiting for one another to release resources. The provision of mechanisms for detecting and preventing deadlocks is imperative for the database system to avert such scenarios and guarantee the successful completion of transactions.
TASK 9
The data model developed exhibits a high degree of correspondence with the data architecture employed by the enterprise. The normalisation process was applied to the model in order to eliminate any instances of redundancy and anomalies, resulting in a third normal form (3NF) model. The interconnections among entities are unambiguously established, and the foreign keys are employed to preserve referential consistency. The ER diagram is representative of the business requirements, encompassing the monitoring of customers, jobs, components, and employees.
The formulated queries fulfil the business requirements and furnish valuable insights for facilitating decision-making. The inquiries encompass various data points, including particulars regarding employment, clientele, components, and expenditures. The aforementioned queries possess the capability to produce reports, oversee expenses, and monitor the advancement of tasks. The SQL queries exhibit efficiency and optimisation in order to reduce the extent of data scanning required.
The components of the task form a functional system. The data model offers a standardised and organised framework for the storage and administration of data. SQL queries offer a proficient approach to retrieve and extract data from a database. The database and associated queries have been developed with the aim of fulfilling the business requirements. These tools can be utilised for the purposes of monitoring job progress, cost tracking, and report generation. The comprehensive system offers a robust framework for effectively handling the information utilised by the enterprise.
There exist several potential enhancements that could benefit the enterprise in the future. A plausible enhancement could be the incorporation of additional features into the system, including a graphical user interface for the input and modification of data. An additional enhancement that could be considered is the incorporation of supplementary tables to encompass further information, such as customer orders and inventory. Incorporating business intelligence tools and analytics can potentially enhance the utility of the data by providing deeper insights and facilitating more informed decision-making.
Realcode4you provide best and quality Database Management System and SQL Project help services with reasonable price.
If you need any help then you can contact us or send your project requirement details at:
Comments