Hi I am posting this is assignment its a simple lab asssignment of few tasks every thing will be mentioned in attached document
Microsoft Word - Lab 3 - Using SQL DDL and DML.docx Diploma of Information Technology Session: Summer 2020 UOW College Australia DPIT115 Data Management and Security Laboratory 3 Published on 1 December 2020 Scope This laboratory includes the tasks related to the application of data definition (DDL) and data manipulation (DML) statements of SQL. This laboratory also includes tasks related to the applications of the advanced DML statements, and SELECT statements with WITH clause. The outcomes of the laboratory work are due by Sunday 13 December 2020, 11.55 pm. Please read the information listed below very carefully. This laboratory contributes to 5% of the total evaluation in a subject DPIT115. The submission procedure is explained at the end of the specification. This laboratory work consists of 2 tasks. The submission procedure is explained at the end of the specification. A submission in an incorrect file format, filename format, or as a compressed file (zipped, gzipped, rared, tared, 7-zipped, lhzed, … etc) will not be evaluated. A submission marked by Moodle as Late is treated as a late submission no matter how many seconds it is late. All files left on Moodle in a state "Draft(not submitted)" will not be evaluated. An implementation that does not compile due to one or more syntactical errors scores no marks. It is expected that all tasks included in Laboratory 3 will be solved individually without any cooperation with the other students. If you have any doubts, questions, etc. please consult your lecturer or tutor during lab classes. If it is suspected that you have received assistance from another person to complete the tasks, the matter will be investigated as an alleged breach of the UOW College Academic Integrity and Student Conduct Policy, in accordance with the Procedure for Managing Alleged Student Misconduct. As part of this investigation, you may be required to undergo an oral examination to verify your understanding of the assessment content. Prologue 1 In this laboratory, you must use a virtual machine with MySQL. All technical details on how to start and how to use a virtual machine have been explained and practised in Laboratory 1. Connect to Moodle and download the Sample database(Sample_database.zip) on Moodle. Extract the files dbcreate.sql, dbdrop.sql, dbload.sql, dbcount.sql, and dbschema.bmp. SQL script dbcreate.sql can be used to create the relational tables of a sample database. SQL script dbdrop.sql can be used to drop the tables of a sample database. SQL script dbload.sql can be used to load data into a sample database. SQL script dbcount.sql can be used to display the total number of rows in each table included in a sample database. Finally, a file dbschema.bmp contains a conceptual schema of a sample database. Connect to MySQL database server either through the command line interface MySQL or SQL Workbench graphical user interface. When connected, select a database csit115 with a command use csit115. To create the relational tables of a sample database, process SQL script dbcreate.sql. To load data into the relational tables created in the previous step process SQL script dbload.sql. To list the names of relational tables created, use a command show tables. To list a structure of a relational table use a command describe . To list the total number of rows in each relational table process a script dbcount.sql. Use an image viewer to open a file dbschema.bmp with a conceptual schema of the sample database. No report is expected from the implementation of the actions listed above. Tasks Task 1 (3 marks) Download a file solution1.sql and insert into the file the implementations of the following modifications of the structures, consistency constraints, and/or the contents of the sample database. Note, that you may need more than one SQL statement to implement a single subtask listed below. Your implementation must directly follow a comment with a specification of a subtask. (1) Modify a consistency constraint of the sample database such that after a modification, it is possible to record in the database information about the drivers who are sick. (2) Modify the structure and consistency constraints of the sample database such that after modification, it is possible to add information about the total number of repairs performed on each truck. Assume that, a truck cannot be repaired more than 1000 times. (3) Modify the structure and consistency constraints of the sample database such that after modification, it is possible to store optional information in the database about the cost of each trip. Assume, that cost of a single trip is a positive number not greater than 9999.99. (4) Modify the structure and consistency constraints of the sample database such it is possible to store information about the mechanics employed by a transportation company in the database. Assume that a description of a mechanic is the same as a description of a driver. (5) Assume that the mechanics are assigned to the trucks such that each mechanic is assigned to many trucks and each truck has at most one mechanic assigned. Modify the structure and consistency constraints of the sample database such that after modification, it is possible to store information about which mechanic is assigned to which truck in the database. (6) Add information about a new trip and information about two legs the new trip consists of to the database. All information about the new trip and its two legs is up to you. (7) Change the status of the truck with a registration number PKR768 to USED. (8) Remove all information from the database about the trip number 35. (9) Modify the consistency constraints of the sample database such that the values in the column REGNUM in a relational table TRIP are optional. (10) Remove information about the truck with a registration number KKK007 from the database. Leave information about the trips performed by the truck in the database. You can find a lot of information about application of data definition statements and data manipulation statements of SQL in a presentation 09 SQL - Data Definition Language (DDL) and in Cookbook, How to use data definition and basic data manipulation statements of SQL, Recipe 4.1 How to create and how to alter the relational tables? To implement and test the SQL script file solution1.sql you can either use graphical user interface SQL Workbench or the command line interface of MySQL. It is recommended to use the script dbdrop.sql to drop all relational tables that are modified during the processing of a script solution1.sql. To re-create the original database use the script dbcreate.sql. Your script should always operate on the original structures of the sample database. To create a report from the processing of a script file solution1.sql open a Terminal window and start the command line interface mysql in the following way: mysql -u csit115 -p -v -c Next, process SQL script solution1.sql and save a report in a file solution1.rpt. Note, that when started with the options -v and -c the command line interface includes both listing of SELECT statements processed and the comments included in the original version of a file solution1.sql. You have already practised saving a report from processing a SQL script in the Laboratories 1 and 3. You can also find more information about creating reports from the processing of SQL scripts in Cookbook, Recipe 3.1 How to use “mysql? Command based interface to MySQL database server? Step 4 How to save the results of SQL processing in a file?” A report that contains no listing of the processed SQL statements will scores no marks and report that contains errors of any kind will also score no marks! Deliverables The file solution1.rpt with a report from processing of SQL script solution1.sql. The report must be created with the command line interface mysql, the report MUST NOT include any errors, and the report MUST LIST ALL SQL statements processed and ALL comments included in the original (downloaded) version of solution1.sql. Marks will be deducted for the missing comments. Submission of a file with a different name and/or different extension and/or different type scores no marks. Marks will also be deducted if you do not enter your student details in the comments at the start of the script file. Task 2 (2 marks) Repeat the steps in the Prologue to prepare the virtual machine for this task. Download a file solution2.sql and insert into the file the implementations of the following data definition, data manipulation, and query operations on the sample database. Note that you have to use more than one SQL statement to implement the operations. (1) Create a new relational table to store information about the driving licenses of all drivers, together with a total number of trips performed by each driver. Enforce, the appropriate consistency constraints on the new table. Next, copy into the new table information about the driving license numbers of all drivers, and the total number of trips performed by each driver. Note, that all drivers who have not perform any trip yet must be included into the table with the total number of trips equal to zero (0). When