Database Systems (com116 – crn 32466 & 32467) Assignment Specification

COM116 –Database Systems (CRN 32466 & 32467) Assignment Specification 2022 – 23

Database Systems (COM116 – CRN 32466 & 32467)
Assignment Specification
Submission Deadline: before 5:00 pm on Friday 14^th April 2023
Description
This assignment contributes 70% of the overall module mark. It requires you to design a database capable of meeting the business needs of a driving school, implement and test your design using either SQL Server OR MySQL, and provide a walkthrough demonstration of your solution and underpinning code base via a recorded vodcast.
Outline Business Scenario
A driving school requires a system to manage aspects of its business.
There are a number of customers, each of whom may book a number of lessons, and each individual lesson is booked by an individual customer. Each lesson is taken by one instructor, and uses one vehicle, but an instructor can take many lessons and an individual vehicle can be used in many lessons. There are four different lesson types (Standard, Advanced, etc). Each vehicle is of one particular type (car, motorbike, lorry, etc) and each instructor is qualified for a number of different vehicle types. The company has at least one vehicle of each type and at least one instructor qualified for each type.
Information to be held and manipulated include:

• 
  the name, address, town, postcode, email address, phone number, date of birth and id number of each customer.
  
• 
  for each lesson, the lesson id, lesson type, lesson date and start time, pick-up point and cost of each lesson. Each lesson lasts for 2 hours and a record is kept of whether a lesson has been completed or not.
  
• 
  The registration number, make, model, date of manufacture and date of last maintenance of each vehicle.
  
• 
  the type id, name and short description of each vehicle type.
  
• 
  for each instructor, the instructor id, name, grade, email address and phone number.
  

Business processes to be supported include the ability to:

• 
  add details of a new customer together with the date, time, and type of a lesson they have booked, but without specifying the vehicle to be used or the instructor who will take the lesson.
  
• 
  find the number of times each vehicle is used, and the amount charged per vehicle for lessons booked between two given dates.
  
• 
  get the names of customers who have completed more than one lesson together with the number of lessons completed and the total cost of the completed lessons.
  
• 
  get the names of instructors available for a lesson at a given date and time. The query should also return the name and description of all vehicles for which each available instructor is qualified. Test your query for a lesson at 11:00 o’clock on 1 April 2021.
  
• 
  remove a vehicle temporarily for a period of time (e.g., for servicing) and get a list of vehicles that are not available.
  

An unstructured file of sample data in Excel format is available for download from Blackboard. The data shows all the lessons booked for 1 April 2021 (the current date) and a selection of lessons booked for given customers both before and after 1 April 2021.