辅导 program、讲解 SQL语言编程
Assignment One: Database Design and Implementation
Overview:
The coursework involves the design of a relational database (using MariaDB) for a specified task, that involves the design of an ERD, relational schema, the SQL to generate the relations in MariaDB, and indexes.
Scenario:
You have been hired by HW Motors, a start-up UK based car/van rental company, to design a MariaDB database to manage the renting of their range of cars and vans to the public. They have a number of bases across the UK, and although they wish to grow as a company they are focussed on the UK market with no plans to expand overseas at this stage.
At each base they have a range of cars and vans, which have a fixed hire price per day. Booking of 7 days or more will receive a discount (e.g. 20%) from the total cost.
One of HW Motors’ unique selling points, is that when you book a vehicle you are guaranteed to get that exact vehicle, rather than an alternative model.
They offer a range of cars from small town car, to family car, MPV (7 seaters), sports car, luxury, and minivans. With each booking they will need to hold details of the person renting the vehicle, including their driving licence details, date of birth, home address, mobile number, and email address.
Each booking will have a start and end date, and store if the hirer has opted to take out any insurance extras.
All bookings will be made via webpage/mobile app which requires the user to register. The person that makes the booking must be the main driver, and there may be additional drivers listed with each booking. The website will support searching for vehicles based on number of seats, engine size, automatic/manual, cost and location.
HW Motors will handle its own vehicle servicing, this includes MOTs, and during these times the vehicles will not be available for rental.
Further Details / Assumptions:
Vehicles can be rented from a base and returned to another rental location (i.e 1 way rentals).
Each vehicle has a home base where it will be serviced. All service schedules for the vehicles will be handled by this system so that they know when cars are not available, but car parts, mechanics time, detailed service reports will be handled by a separate system.
They only rent to the public, not commercial rentals.
The minimum rental period is 1 day and rentals are whole numbers of days.
All cars can be driven on a standard UK driving licence.
The company policy sets a minimum age to rent a car as 25 years, and 30 years to hire an MPV, van, sports or luxury vehicle.
Rental prices will vary during the seasons (i.e. peak time prices).
Any booking which is cancelled will be removed from this system, however this will not impact user registrations for the web/mobile app booking system.
The website/app will show a picture of the actual car being hired with each booking.
Assume that all cars can be moved to any UK location overnight based on a car-carrier service.
The Tasks:
Overview (full details below):
Task Description Marks
1. Design and draw an ER-diagram that captures the requirements 15
2. Translate your ER-diagram into a relational schema 10
3. Implementation of the schema in MariaDB 10
4. Define 5 suitable indexes for your tables (other than PKs) 5
T1 Conceptual model (15 marks)
Create an ER diagram for the stated database requirements in the scenario provided using UML notation.
The diagram should include one-to-many and many-to-many relationships, and several other advanced ER features such as:
A recursive relationship;
Derived attributes;
Composite attributes;
Composite keys;
Repeating attributes;
Use of generalization and specialisation.
The diagram should contain a minimum of 10 (entities and relationships) for a group of 5/6 students, e.g. Employee Works for Department, and Department has Employee as manager => 2 entities and 2 relationships = 4 (entities and relationships).
You might need to use a drawing tool like yEd[ https://www.yworks.com/products/yed [accessed Jan 2024]], draw.io[ https://app.diagrams.net [accessed Jan 2024]], or Microsoft Visio; rather than a relational database tool which will put in the foreign keys.
Make a note in your report of any details of the requirements that could not be captured by the diagram.
Deliverable: Report to include the ER diagram for your database, and any supporting notes for you diagram.
T2 Translation into Relational Schema (10 marks)
Define a relational schema from your conceptual ER diagram.
Use the steps given in the lectures to convert your conceptual design into a relational schema. The schema will include foreign keys, and extra tables where required, e.g. for many-to-many relationships. Primary and foreign keys should be clearly identified. Make a note explaining how it has been derived from the original conceptual ER diagram, for those cases where you have had to make a decision.
Deliverable: Report to include the relational schema and notes for any unusual steps that were followed.
T3 Implementation of the Schema in MariaDB (10 marks)
Generate a script to create the tables in MariaDB using the InnoDB storage engine. The data definition language statements must specify:
Appropriate types for the attributes
The primary key;
Constraints such as NOT NULL and UNIQUE whenever appropriate;
Default values if appropriate;
FOREIGN KEY constraints, together with the policy for reacting to changes
Write comments into the script explaining what is happening in each part.
Deliverable: Commented SQL script should be included in your report
T4 Indexes (5 marks)
Provide 5 index definitions that are designed to improve performance for queries that could be asked from your database. This must be for fields other than the Primary Keys, which will be indexed already.
Write an SQL script containing a set of CREATE INDEX commands.
Deliverables:
Report to contain five INDEX definitions using MariaDB syntax.
Report to contain an explanation why each index is needed.