top of page

Design and Construct a Database System for Rapid Bikes | Realcode4you

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:

  1. Customer

  • customer_id (primary key)

  • first_name

  • last_name

  • street_address

  • town

  • county

  • postcode

  • telephone_number


  1. Cycle

  • cycle_number (primary key)

  • customer_id (foreign key)

  • component_code (foreign key)


  1. Component

  • component_code (primary key)

  • component_name

  • component_type

  • manufacturer

  • price


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


  1. Employee

  • employee_code (primary key)

  • first_name

  • surname

  • hourly_rate


  1. 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 table
INSERT 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 table
INSERT 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 table
INSERT 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 table
INSERT 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 table
INSERT 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 table
INSERT 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 Component
WHERE price > 60.00;



b) Write a query that selects the details for components that are NOT sourced

from the United Kingdom.

SELECT *
FROM Component
WHERE 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 Job
WHERE 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 Component
JOIN Cycle ON Component.component_code = Cycle.component_code
JOIN Job ON Cycle.cycle_number = Job.cycle_number
WHERE Job.job_id = 78123;



e) Write a query that shows all the customer details and all job details for

customer C345.

 

SELECT *
FROM Customer
JOIN Job ON Customer.customer_id = Job.customer_id
WHERE Customer.customer_id = 'C345';



f) Write a query that produces the components from Japan that are used on

Job 78123.

SELECT Component.*
FROM Component
JOIN Cycle ON Component.component_code = Cycle.component_code
JOIN Job ON Cycle.cycle_number = Job.cycle_number
JOIN Manufacturer ON Component.manufacturer = Manufacturer.name
WHERE 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_cost
FROM Component
JOIN Cycle ON Component.component_code = Cycle.component_code
JOIN Job ON Cycle.cycle_number = Job.cycle_number
JOIN Employee ON Job.employee_code = Employee.employee_code
WHERE 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_rate
FROM Customer
JOIN Job ON Customer.customer_id = Job.customer_id
JOIN Cycle ON Job.cycle_number = Cycle.cycle_number
JOIN Component ON Cycle.component_code = Component.component_code
JOIN Employee ON Job.employee_code = Employee.employee_code;