INFT3007/COMP3350
Assignment 1 – Database Design & Implementation
Semester 1, 2019 Due Date April 3rd (Wednesday) at 11am
Assignment 1 is due on April 3rd (Wednesday) at 11am. Each team will
upload the assignment to UOnline by April 3rd (Wednesday) at 11am,
submit 1 hard copy of the assignment to the lecturer by April 3rd (Wednesday) 11am
and
demonstrate the assignment to the tutor in the lab session on April 3rd/4th
Weighting
25% of course mark
Assignment Team
You need to attempt the assignment in a group of 2 members (3 members in exceptional circumstances). You need to fill in the Group Formation Form at the end of the assignment and submit it to the tutor on March 13th/14th during the tutorial session. Members in the same group may be awarded different marks based on their contribution to the assignment.
This assignment has 5 sections to it. Progress in each part for the group is monitored during tutorials. Note that all SQL scripts and Java code created in this assignment needs to be commented for readability (Marks will be deducted for uncommented, illegible SQL scripts/Java code).
Assignment Background
Your group is asked to develop a conceptual database design using Enhanced Entity Relationship model for a database for AutoMartNewcastle.
AutoMartNewcastle is an autodealer in Newcastle. You are working for an IT Consultancy company – ITConsult Newcastle – and your manager has tasked your group to design and implement a database solution for AutoMartNewcastle based on the business requirements provided in this document.
Your lecturer will act as your client and you can query him for any further information and clarifications.
Business Requirements
Vehicle Information – There are a number of vehicles that the dealership sells. Each vehicle has a vehicle id (which is unique), VIN number (unique), a chassis number (unique), vehicle type, colour (interior and exterior), base price, advertised price,
options, arrival date, manufacturer warranty period (in months), registration plate (initially null for new vehicles), a flag to state that the vehicle is in stock for sale, vehicle status (i.e. new or used) and comments. The base price is the cost price of the vehicle for the dealership.
The manufacturer of the vehicle, model and year specifies each vehicle type. Note that advertised price is always greater than base price.
New Vehicle Sales and Leases – There are two ways that new vehicles can be obtained by customers: (i.) new vehicle sale; and (ii.) new vehicle lease.
New Vehicle Sale – Each new vehicle sale will consist of customer information, registration plate, date sold, selling price, salesperson making the sale and manufacturer warranty expiry date.
Customer information – Customers could be both personal customers or business customers. Customer information includes customer id (unique), address, contact number and comments. A personal customer has a name while a business customer has a business name and contact person.
Lease Type – Vehicles types for lease has a lease type which consists of a lease title, description, lease period in months, monthly payment, allowed monthly mileage (in kms), excess rate per excess kilometer and a flag to specify whether the lease type is available currently.
Lease – A vehicle lease consist of a vehicle, a customer, a lease start date, a lease end date, a salesperson and a lease type.
The customer pays the lease monthly and the payment details need to be maintained in the database. For each lease, the standard services for the vehicle leased are included and are done by the dealership. The service history details for the vehicle during the lease need to be maintained. At the end of the lease, the vehicle is re-sold at the dealership as a used car.
Used Vehicle Acquisition and Sales – There are two types how used vehicle are acquired by the dealership: (i.) vehicles that were previously leased, (ii.) used vehicles purchased (e.g. trade-ins).
A used vehicle that is purchased from a customer (i.e. not a lease vehicle) has the following information. Customer selling the vehicle, date sold and amount paid for the vehicle.
A used car may have repairs done on it prior to sale. Each repair is done at the dealer’s Service Department.
The base price for the used cars is calculated as below:
Buying price (i.e. amount paid) + cost of all repair work + $250 (admin fee)
Service, Inspections & Repairs – The dealership has a Service Department, which provides standard vehicle services, vehicle inspections and vehicle repairs.
There are a number of parts that the Service Department stocks and orders. Each part has a part number (unique), part name, type of vehicle the part belongs to, current price and current labour cost to fix the part. The database needs to maintain the stock available for parts.
The Service Department provides vehicle inspections. Each inspection has an inspection id (unique), inspection name, vehicle type and a current price.
The service department provides parts to customers. Each parts sale consists of customer, parts sold, quantity, subtotal, GST, total amount due and whether the customer has paid the amount.
There are a number of standard services (e.g. 10,000 km service) that the Service Department of the dealership completes. The standard service has a service ID (unique), service name, description, parts and inspections included in the service, current price and type of vehicle. The current price is derived by totaling the costs of inspections and parts included in the service.
When a vehicle is checked into the Service & Parts Department of the dealership, a service ticket is issued. The service ticket contains a unique service ticket issue number, a date, a customer, a vehicle and comments. The service ticket contains either one of more services, inspections and/or repairs done to the vehicle during the visit.
For each repair, part(s) used, the cost charged for the part and labour cost for the fixing the part is recorded. For each inspection, the current price of the inspection is charged. For each service, the current price of the service is charged.
The subtotal cost of the service ticket including cost of service(s), vehicle inspection(s) and any repairs to the vehicle, GST and total amount due are maintained in the database. Also, a flag that states whether the customer has paid the service ticket is maintained in the database.
Employees and Wages – There are a number of employees at the dealership.
Each employee has an employee id (unqiue), full name, date of birth, address, a contact number and a position. The start and end dates of the position by the employee are maintained in the database.
Each position has a title, description and current monthly base salary. For salesperson positions, there is also a current sales bonus percentage and a current lease bonus percentage.
At the end of each month, each employee is paid a monthly wage. A payslip is generated with the following information.
A payslip contains the following information:
Employee the payslip belongs to
Start date of pay period
End date of pay period
Base pay
Bonuses (if any)
T axable income
Tax
Net pay
The following information about taxes is maintained in the database in order to calculate the employee tax.
The start amount for tax bracket
The end amount for tax bracket
Tax rate as a percentage
Effective year in which the tax bracket is effective
Section 1: Conceptual DB Design (10 marks)
Based on the business requirements, develop a conceptual database design using EER model. Your lecturer will act as the client and you can speak to him to clarify any questions regarding the requirements.
Section 2: EER – Relational Mapping & Normalisation (2 marks)
The EER diagram needs designed in section 1 needs to be mapped to a relational schema and normalised.
The relational model needs to be described in DBDL format. Sample DBDL format is given in the below:
ISBN (id, number, itemNo)
Primary Key id
Alternate Key number
Foreign Key itemNo references Book(itemNo)
ON UPDATE CASCADE, ON DELETE CASCADE
DBDL format is provided in your text – Database Systems – A Practical Approach to Design, Implementation, and Management – 5th Edition.
Section 3: Database Implementation – Database Script (3 marks)
Create a T-SQL script for the database design in section 2. You will create a database with all the necessary tables and constraints: primary key, foreign key, not null, unique and check constraints. The database must be populated with sufficient and meaningful records for evaluation purpose.
Section 4: Stored Procedures (10 marks)
Implement the following stored procedure. Ensure that each stored procedure is tested with appropriate sample data. Error handling needs to be implemented and appropriate warnings and error messages are generated. Test cases should be saved in a separate test script. Note that marks are allocated for testing and well-documented code.
You may implement the stored procedures either as T-SQL or CLR stored procedures. Justify your choice.
(SP1) Generate service ticket
Procedure name
usp_GenerateServiceTicket
Parameters
vehicleId (input) – Id of the vehicle to be serviced
date (input) – Date of service ticket
customerId (input) – Id of the customer
serviceTypes (input) – A table-valued parameter (TVP) with the types of services that is conducted on the vehicle (if any)
repairs (input) – A list of part numbers used for the repairs (if any) passed as a table-valued parameter (TVP)
inspections (input) – A list of vehicle inspections (if any) passed as a table- valued parameter
GST (input) – GST rate (as a percentage)
serviceTicketNumber (output) – Ticket number of newly created service ticket
Functionality
Create a new service ticket for the service(s), repair(s) and vehicle inspection(s). The service ticket must include cost of the service, cost of each part + cost of labour, cost of inspection, GST and total cost of the service ticket.
Note that appropriate error messages are generated for incorrect/invalid input.
If a service ticket is successfully created, then the generated service ticket number for the service ticket is returned.
SQL script
create_usp_GenerateServiceTicket.sql
Test script
test_usp_GenerateServiceTicket.sql*
Section 5: Java Hibernate (2 marks) – Optional (extra credit)
Implement the following Java application that has a Form that creates a vehicle sale. The input parameters include: Id of vehicle being sold, date of sale, customer buying the vehicle, salesperson making the sale, registration plate of vehicle and selling price of vehicle. Assume that customer details already exist in the database. Use Hiberate ORM to interact with the database.
The form should have a submit button to create a new vehicle sale in the database and generate a sale id for the sale.
Note that the form needs to be user-friendly (e.g. populate vehicle details when id of vehicle is inserted, salesperson info is shown when employee id is inserted, etc.
You need to test your form with valid and invalid data and ensure appropriate error messages are generated for incorrect input.
Submission Requirements
Each group will submit both soft and hard copies for the assignment.
Soft copy
Soft copy submission is via UOnline. Each group will submit a zip file which contains the following items:
1. Databasedocumentation.
2. SQL Server Backup (.bak file)
3. SQLScriptfiles
4. Javafiles
A Peer Group Evaluation Form needs to be submitted individually by each member via the TurnItIn link.
The database documentation must contain the following sections (in the order specified):
1. Assumptions: Assumptions made by your group with respect to the business
requirements
2. ERD:TheERDmustshowalltheentities,attributesandrelationships.
3. Database Schema: The logical model must show all the tables, columns, column
types, null/not null constraints, primary keys and foreign keys
The SQL script files to be included are listed below.
Hard copy
Hard copy is to be submitted to your lecturer in person. The following items will be required for submission.
Group Assessment cover sheet signed by each member of group
Printout of database documentation (Requirements, Revised EER Model,
Normalised Relational Model)
Progress Monitoring
Each groups need to show progress of their assignment during the tutorial sessions. Each member of group needs to attend these tutorial sessions.
Demonstration
All groups need to demonstrate their database implementation. Group demonstration for Assignment 1 will be held during Apr 3rd and April 4th tutorial sessions. Each member’s contribution will be evaluated individually during demonstrations. Failure to attend the demonstration can result in a zero grade for the assignment by the member of the group.
Section
SQL scripts
Database
createDB.sql
Stored Procedures
create_usp_GenerateServiceTicket.sql test_usp_GenerateServiceTicket.sql
Section
Progress
Sections 1
March 13th
Sections 2 and 3
March 20th
Sections 4 and 5
March 27th
Group Formation Form
GROUP NO: ___________________________ (TO BE FILLED BY LECTURER)
GROUP MEMBERS:
I agree to participate in the mentioned group for Assignment 1
Student ID
Name
Signature