NEED ANSWER FOR QUESTION NUMBER 5 ASAP
page 2:
SORRY UPDATED WRONG QUESTION EARLIER. I NEED ANSWER FOR 5th QUESTION FROM THIS QUESTION 4 (10MARKS)
QUESTION 3 (10 marks) Write the data definition statements of SQL that modify the structures of a database listed on page 2 of this assessment in the way described below. Note that some of the modifications may require more than one SQL data definition statement. (1) Modify the consistency constraint of the sample database such that after the modification, it is possible to record in the database information about the trucks that have a capacity up to and including 200 (2 marks) (2) Modify the structure and consistency constraints of the sample database such that it is possible to store information in the database about the total number of legs a trip contains. Assume that a trip cannot contain of more than 10 legs. (2 marks) (3) Modify the structure and consistency constraints of the sample database such it is possible to store information in the database about the mechanics employed by a transportation company. Assume that a description of mechanic consists of an employee number, first name, last name, date of birth and qualification level. A qualification level is a positive integer number 1 or 2 or 3 or 4 or 5. Remember that a mechanic is an employee. (2 marks) (4) Modify the consistency constraints of the sample database, so it is possible to store information about the trip without providing information about a driver licence number. Such modification is required when a driver leaves a transportation company, and we would like to keep information about all trips performed by the driver. (2 marks) (5) Explain how the use of consistency constraints support the management of data security. Use original examples to illustrate your answer. (2 marks) Add your code into the question3.sql template provided and output your report file on your virtual machine to a fine question3.rpt. Add your name, student number and the date to the comments section of your SQL script. Submit your answers as the files question3.sql question3.rpt and question3.pdf using the templates provided Note your script may be tested and should not have any errors when run. CREATE TABLE STAFF ENUM DECIMAL (12) NOT NULL, /* Staft employee number ENAME VARCHAR (50) NOT NULL, First name LNAME VARCHAR (50) NOT NULL, Last name DOR DATE NULL, Date of birth CONSTRAINT STAFF_PKEY PRIMARY KEY (ENUM)); CREATE TABLE DRIVER ( ENUM DECIMAL (12) NOT NULL, Staff employee number LNUM DECIMAL (8) NOT NULL, Driving license number STATUS VARCHAR(10) NOT NULL Driver status CONSTRAINT DRIVER_PKEY PRIMARY KEY (ENUM), CONSTRAINT DRIVER UNIQUE UNIQUE (LNUM). CONSTRAINT DRIVER FKEY FOREIGN KEY CENUM REFERENCES STAFF (ENUM), CONSTRAINT DRIVER STATUS CHECK STATUS IN AVAILABLE", "IN SERVICES, ON LEAVE'>> ): CREATE TABLE TRUCK REGNUM VARCHAR(10) NOT NULL, Registration number CAPACITY DECIMAL (7) NOT NULL, 1. Capacity WEIGHT DECIMAL (7) NOT NULL, Weight STATUS VARCHAR(10) NOT NULL, 1. Present status CONSTRAINT TRUCK_PKEY PRIMARY KEY (REGNUM), CONSTRAINT TRUCK_STATUS CHECK STATUS IN ("AVAILABLE", USED", "MAINTAINED' ) ), CONSTRAINT TRUCK_WEIGHT CHECK ( WEIGHT > 0.0 AND WEIGHT 0.0 AND CAPACITY
