Assignment 2 School of Engineering and Information Technology ITECH1006/5006: Database Management Systems Aims • To analyse and comprehend a given ER diagram and Database schema • To implement a database based on the given ER diagram and Database scheme • To write required SQL statements query the database • Write SQL statements to manipulate the data in the database Learning Objectives In the process of this assessment task you will: • plan, schedule and execute project tasks with a view to improving your personal productivity; • gain awareness of the typical challenges related to the practical implementation of databases; • learn how to used Data Definition statements to implement a database from a given ER diagram and the corresponding Database schema • learn how to use Data Manipulation statements to query a database, and insert and update data in the tables Due date: Week 11, Friday, 5pm Submit the individual work file, named ‘a2-<surname_id>.zip’, by Week 11, Friday, 5pm via Moodle. Late submission: Any submission after the due date will receive a deduction of 10% per day, this includes weekends. Marks: This assignment has a total 100 marks and it is worth 20% out of the total assessment. Extensions: An extension will only be considered with supporting documentation from a health professional and if the problem/illness occurred within the week prior to the due date. If an extension is granted the extension will then equal the number of days specified on the doctor’s certificate, with a maximum limit of one week. Authorship: This assignment is an individual assignment and it shall be completed by the individual student only. The final submission must be identifiably the work of the individual. Breaches of this requirement will result in an assignment not being accepted for assessment and may result in the offending student or students being required to present before the Disciplinary Committee. 1 Assignment 2 School of Engineering and Information Technology ITECH1006/5006: Database Management Systems Assignment Specification Commonwealth Transport Services (CTS) now require a partial implementation of the design made in Assignment 1. In order to keep consistency between the assignments, database specification containing the ER diagram and the corresponding schema are provided in this document. You should create your database according to this documentation. Make sure that your implementation is consistent with this design, i. e., your table names, field names, and data types are according to the specifications provided in this document. The implementation phase includes writing SQL statements to create a database and its tables, populating the tables with appropriate test data, and writing a number of queries to create reports that can be used by the management team. You need to insert at least five records in each of the tables and ensure that each of the query returns at least one record. Implementation of the Database and Manipulation of the Data You are required to perform the followings tasks: 1. Create a text file named Create_.sql (for example, Create_3087654.sql) that will contain SQL statements to: I. Create a database named CTSDB II. Create all of the tables for the database according to the Database schema given at the end of this document 2. Create a text file named Insert_.sql that will contain SQL statements to: I. Insert at least five records in each of the tables. The test data inserted into the table must ensure that each of the queries, specified in Task 3, outputs at least one record 3. Create a text file named Query_.sql that will contain all the queries to display the following I. A list of available Vehicles sorted according to seating capacity. Display the Model, Registration number, and the Seating capacity. II. A list of Official sorted according to their First name followed by Last name. Display their country name as well as the languages they speak. III. List the drivers who speak French. Display their First name and Last name. IV. List the drivers having first aid training. Display their First name, Last name, First aid level, and the Date the training was completed V. List the drivers having special security training. Display their First name, Last name, First aid level, the Date the training was completed, and the Name of the certifying body. VI. Find the vehicles which went for repair and/or maintenance in March 2015. Show their Registration number, Model, and the date of maintenance group by maintenance type. VII. Find the total cost incurred for maintenance and repair. Display type of maintenance and the total cost. 2 Assignment 2 School of Engineering and Information Technology ITECH1006/5006: Database Management Systems VIII. Find the vehicles whose repair cost was more than the average repair cost. IX. Find the locations whose street name starts with ‘B’. X. List the names of the drivers who has Security clearance level above T and who speak English. Display their First name, Last name, and the Security clearance level. 4. Create a text file named Transaction_.sql that will perform the followings. Make appropriate assumption if needed. I. An Official named Daniel Ortega from UK, having OfficialID AUS997, wants to make a booking. He speaks English and he will play role of a ‘Judge’ in the games. He wants to travel from 10 Elizabeth St, Brisbane to 117 Kings Road, Gold Coast on April 9, 2015. His expected start time is 10:00 am and end time is 1:15 pm. II. The above trip was performed using the vehicle having VIN number SANFDAE11U1286116. The starting odometer reading for the vehicle was 26982 KM. The trip started at 10:15 and ended at 1:30 pm. At the end of the trip the odometer reading was 27190 KM. The driver for the trip was John Arnold having Driver licence number 098674432. John Arnold, although has a First Aid training (level D), completed on August 17, 2013, he does not have a special security clearance. John Arnold also speaks English. You are required to adhere to the following output formatting conventions: • All monetary values should be printed with a dollar symbol ($), two digits after the decimal point, and with space for 7 digits before the decimal point • You must use consistent and legible formatting in laying out your SQL queries. Include (brief) comments for any query or procedure that uses an “unusual” approach. What to submit An electronic copy of your assignment should be submitted through Moodle and should include a copy of your report, completed according to the Federation University Australia Guide for the Presentation of Academic Work and the four files described in Task 1 – Task 4 above. Zip all the files into a single file before uploading. Your report should include: • A copy of the SITE Assignment Coversheet that includes a copy of the plagiarism statement. • A report of the results from running the SQL queries (Task 3) by using Copy/paste of their output. • A bibliography containing a list of all resources used to complete the assignment. If no resources, apart from the course materials, have been used please indicate this. 3 Assignment 2 School of Engineering and Information Technology ITECH1006/5006: Database Management Systems Assessment Criteria • How clear and well organised your presentation is. On the front page of your report you should include a list of acknowledgements of all people who have assisted you with this assignment including fellow students, along with a statement of completion. • Adherence to our standards. How clear and well organised your presentation is. You should write all the queries in consistent style and use indent format. • Data correctness and quality. Please use appropriate data for your examples (e.g. do not use inappropriate person names) • Joining of data from multiple tables should be completed using a WHERE statement only. JOINs are not to be used within any of the SQL statements. Use of any JOINs will result in 0 (zero) marks being allocated for each SQL statement that utilizes them. • Please refer to the provided marking guide (below) to see the distribution of marks. Assignment Resources • The Standard ER Diagram • Relational Database Schema Assessment Criteria and Marking Overview ITEM Marks 1. Presentation How clear and well-presented your submission is. 10 2. Creation of database and tables (Task 1) Create the relevant tables: Database correctly named and created, includes all of the PKs and FKs in the database. No evidence that tables have been created using the GUI. They work properly 20 3. Insertion (Task 2) Successfully inserts data into the tables. NO evidence exporting from GUI. They work properly. 30 4. Query (Task 3) Use of appropriate query statement. They work as intended 30 5. Transaction (Task 4) Use of appropriate query statement to perform the required actions. They work as intended 10 Total 100 4 DRIVER PK DriverLicenceNum DriverFirstName DriverLastName ClearanceLevel HasSecurity HasFirstAid IsAvailable DRIVER_FIRSTAID PK, FK DriverLicenceNum FirstAidLevel FirstAidDate DRIVER_SECURITY PK DriverLicenceNum SecurityLevel CertDate FK CertBodyID O driver_firstaid driver_security TRIP PK BookingRefNum FK DriverLicenceNum FK OfficialID FK PickUpLocID FK DropOffLocID FK VIN StartTimeIntended EndTimeIntended StartTimeActual EndTimeActual StartKM EndKM VEHICLE PK VIN VEH_Make VEH_Model VEH_Colour VEH_RegoNum VEH_Odometer VEH_Availability VEH_SeatCapacity MAINTENANCE_REPAIR PK, FK VIN PK OdometerReading MR_Type MR_Date MR_Cost MR_Detail needs used_for drives LOCATION PK LocationID StreetNo StreetName City FK LocTypeID drops_off picks_up OFFICIAL PK OfficialID FK LanguageCode FK CountryCode FK OfficialRole OfficialFirstName OfficialLastName books LANGUAGE PK LanguageCode LanguageName COUNTRY PK CountryCode CountryName has prefers speaks found_in DRIVER_LANGUAGE PK, FK LanguageCode PK, FK DriverLicenceNum is_in COUNTRY_LANGUAGE PK, FK CountryCode PK, FK LanguageCode written_in OFFICIAL_ROLE PK RoleID RoleDescription applies_to LOCATION_TYPE PK LocTypeID LocTypeDescription has_type CERTIFIYING_BODY PK CertBodyID: CertBodyName certifies ERD to Relational Database Schema Entity Field Name Datatype Length LANGUAGE LanguageCode CHAR 2 Primary Key LanguageName VARCHAR 50 COUNTRY CountryCode CHAR 2 Primary Key CountryName VARCHAR 50 COUNTRY_LANGUAGE CountryCode CHAR 2 Primary Key, Foreign Key References COUNTRY (CountryCode) LanguageCode CHAR 2 Primary Key, Foreign Key References LANGUAGE (LANGUAGECode) LOCATION LocationID INT (AUTO) Primary Key StreetNo VARCHAR 5 StreetName VARCHAR 50 City VARCHAR 30 LocTypeID CHAR 2 Foreign key references LOCATION_TYPE ( LocTypeID) LOCATION_TYPE LocTypeID CHAR 2 Primary key LocTypeDescription VARCHAR 40 DRIVER DriverLicenceNum CHAR 18 Primary Key DriverFirstName VARCHAR 20 DriverLastName VARCHAR 20 ClearanceLevel CHAR 1 HasSecurity CHAR 1 HasFirstAid CHAR 1 IsAvailable CHAR 1 DRIVER_LANGUAGE LanguageCode CHAR 2 Primary Key, Foreign Key References LANGUAGE (LANGUAGECode) DriverLicenceNum CHAR 18 Primary Key, Foreign Key References DRIVER (DriverLicenceNum) DRIVER_SECURITY DriverLicenceNum CHAR 18 Primary Key, Foreign Key References DRIVER (DriverLicenceNum) CertBodyID INT Foreign Key References CERTIFYING_BODY ( CertBodyID ) SecurityLevel CHAR 1 CertDate DATE Entity Field Name Datatype Length CERTIFYING_BODY CertBodyID INT (AUTO) Primary key CertBodyName VARCHAR 50 DRIVER_FIRSTAID DriverLicenceNum CHAR 18 Primary Key, Foreign Key References DRIVER (DriverLicenceNum) FirstAidLevel CHAR 1 FirstAidDate DATE VEHICLE VIN CHAR 17 Primary Key VEH_Make VARCHAR 30 VEH_Model VARCHAR 30 VEH_Colour VARCHAR 20 VEH_RegoNum CHAR 6 VEH_Odometer INT 6 VEH_Availability CHAR 1 VEH_SeatCapacity INT 2 MAINTANANCE_REPAIR VIN CHAR 17 Primary Key, Foreign Key references VEHICLE( VIN) OdometerReading INT 6 Primary Key MR_Type CHAR 1 MR_Date DATE MR_Cost DECIMAL MR_Details VARCHAR 100 OFFICIAL OfficialID CHAR 8 Primary key LanguageCode CHAR 2 Foreign key references LANGUAGE( LanguageCode) LanguageCode CHAR 2 Foreign key references COUNTRY( CountryCode) OfficialFirstName VARCHAR 40 OfficialLastName VARCHAR 40 OfficialRole CHAR 2 Foreign Key references OFFICIAL_ROLE (RoleID) OFFICIAL_ROLE RoleID CHAR 2 Primary key RoleDescription VARCHAR 40 Entity Field Name Datatype Length TRIP BookingRefNum INT 6 Primary Key VIN CHAR 17 Foreign Key references VEHICLE( VIN) DriverLicenceNum CHAR 18 Foreign Key References DRIVER (DriverLicenceNum) OfficialID CHAR 8 Foreign key references OFFICIAL( OfficialID) PickUpLocID INT Foreign key references LOCATION( LocationID ) DropOffLocID INT Foreign key references LOCATION( LocationID ) StartTimeIntended DATETIME EndTimeIntended DATETIME StartTimeActual DATETIME EndTimeActual DATETIME StartKM INT 6 EndKM INT 6
What Students Are Saying About Us
.......... Customer ID: 12*** | Rating: ⭐⭐⭐⭐⭐"Honestly, I was afraid to send my paper to you, but you proved you are a trustworthy service. My essay was done in less than a day, and I received a brilliant piece. I didn’t even believe it was my essay at first 🙂 Great job, thank you!"
.......... Customer ID: 11***| Rating: ⭐⭐⭐⭐⭐
"This company is the best there is. They saved me so many times, I cannot even keep count. Now I recommend it to all my friends, and none of them have complained about it. The writers here are excellent."
"Order a custom Paper on Similar Assignment at essayfount.com! No Plagiarism! Enjoy 20% Discount!"
