Answered step by step
Verified Expert Solution
Question
1 Approved Answer
need the sql solution the question relates to the data model shown below in the picture. the sql code needs to be written in regards
need the sql solution the question relates to the data model shown below in the picture. the sql code needs to be written in regards to the example data model given below
ecerything there nothing missing
the schema for the database is given
Tasks Task 1 (3 marks) Download a file solutioni.sqi 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 cach 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 cach 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 (DOL) 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 solutioni.sql you can either use graphical user interface SQL Workbench or the command line interface of MysQL 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 (DOL) 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 solutioni.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 telational tables that are modified during the processing of a script solutioni.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 solution.sql open a Terminal window and start the command line interface mysql in the following way mysql -u csit115 - - -- Next, process SQL script solution.sql and save a report in a file solutioni.rpt. Note, that when started with the options - and - the command line interface includes both listing of SELECT statements processed and the comments included in the original version of a file solutioni.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 crees of any kind will also score no marks! Deliverables The file solution.rpt with a report from processing of SQL script solution.sql. The report must be created with the command line interface mysal, 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. TD AVAILABLE DRIVER EMPLOYEE en Frane natalsie..11 ane deb 10..11 blog street suurt state zipcode BUSY DRIVER ON LEAVE DRIVER DRIVER inn ACHEN position LG departure destination . Consists of legno TRIP ID tdateres ID TRUCK regnun capacity ht status use csit 115 ALTER TABLE TRUCK DROP FOREIGN KEY TRUCK_FKEY; DROP TABLE MECHANIC; DROP TABLE TRIPLEG; DROP TABLE TRIP; DROP TABLE DRIVER; DROP TABLE ADMIN; DROP TABLE TRUCK; DROP TABLE EMPLOYEE; Tasks Task 1 (3 marks) Download a file solutioni.sqi 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 cach 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 cach 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 (DOL) 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 solutioni.sql you can either use graphical user interface SQL Workbench or the command line interface of MysQL 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 (DOL) 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 solutioni.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 telational tables that are modified during the processing of a script solutioni.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 solution.sql open a Terminal window and start the command line interface mysql in the following way mysql -u csit115 - - -- Next, process SQL script solution.sql and save a report in a file solutioni.rpt. Note, that when started with the options - and - the command line interface includes both listing of SELECT statements processed and the comments included in the original version of a file solutioni.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 crees of any kind will also score no marks! Deliverables The file solution.rpt with a report from processing of SQL script solution.sql. The report must be created with the command line interface mysal, 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. TD AVAILABLE DRIVER EMPLOYEE en Frane natalsie..11 ane deb 10..11 blog street suurt state zipcode BUSY DRIVER ON LEAVE DRIVER DRIVER inn ACHEN position LG departure destination . Consists of legno TRIP ID tdateres ID TRUCK regnun capacity ht status use csit 115 ALTER TABLE TRUCK DROP FOREIGN KEY TRUCK_FKEY; DROP TABLE MECHANIC; DROP TABLE TRIPLEG; DROP TABLE TRIP; DROP TABLE DRIVER; DROP TABLE ADMIN; DROP TABLE TRUCK; DROP TABLE EMPLOYEE Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started