Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Please write down again and make sure which sentence corrects which line? QUESTION 3 (10 marks) Write the data manipulation statements of SQL that modify

image text in transcribedimage text in transcribedimage text in transcribedPlease write down again and make sure which sentence corrects which line?

QUESTION 3 (10 marks) Write the data manipulation statements of SQL that modify the contents of a database listed on page 2 of this lab task in the ways described below. Note that you are not allowed to modify andor to drop any consistency constraints. Also note, that to implement some of the modifications listed below, you may need more than one data manipulation statement. (1) A new trip has been completed today. The trip was from Wollongong to Orange. The trip has been performed by the driver with the licence number 412443 (column LICENSENUM in a relational table TRIP) who used the truck with registration number AD29FZ. Insert the appropriate information into the sample database assuming the next trip number is 3452 . (2 marks) (2) Delete the information from the database about trip number 53. Remember, that the foreign keys in all CREATE TABLE statements have no ON DELETE CASCADE clause. (2 marks) (3) Change the status of al the drivers who have performed more than 24 trips to be ON LEAVE. (2 marks) (4) Copy information about all employees born before the year 2000 to a new table S20CENT. There is no need to erforce any consistency constraints on the new table. (2 marks) (5) Explain how the Data Definition Languate (DDL) statements of SQL are used h data management and security. Use original examples including sample code that you make up yourself to illustrate your answer. (2 marks) Submit your answer as files named question3.sql and question3.pdf using the templates provided. Add your name, student number and the date to the comments section of your SQL script. /* (0) Assume that the user 'root' with a password 'csit115' created a database called transport and the user 'root' executed CREATE TABLE statements given on page 2 of the paper to create the relational tables in the database transport. */ CREATE TABLE EMPLOYEE( EMPNUMFIRSTNAMELASTNAMEDOBDECIMAL(12)VARCHAR(50)VARCHAR(50)DATENOTNULL,NOTNULL,NOTNULL,NULL,/*Employeenumber/Firstname/*Lastname/*Dateofbirth CONSTRAINT EMPLOYEE_PKEY PRIMARY KEY(EMPNUM)); CREATE TABLE DRIVER( EMPNUM DECIMAL(12) NOT NULL, /* Employee number LICENSENUM DECIMAL(8) NOT NULL, /* Driving license number STATUS VARCHAR (10) N N NULL, Driver status * CONSTRAINT DRIVER_PKEY PRIMARY KEY(EMPNUM), CONSTRAINT DRIVER_UNIQUE UNIQUE(LICENSENUM), CONSTRAINT DRIVER_FKEY FOREIGN KEY(EMPNUM) REFERENCES EMPLOYEE(EMPNUM), CONSTRAINT DRIVER_STATUS CHECK (STATUS IN ('AVAILABLE', 'BUSY', 'ON LEAVE')) ); CREATE TABLE TRUCK( REGNUM VARCHAR(10) N NOT NULL, Registration number CAPACITY DECIMAL(7) NOT NULL, * Capacity * WEIGHT DECIMAL(7) NOT NULL, * * weight STATUS VARCHAR(10) NOT NULL, * 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 N / LICENSENUM DECIMAL(8) NOT NULL, /* Driving license number REGNUM VARCHAR(10) N NOT NULL, /* Truck registration number */ TDATE DATE NOT NULL, N* Trip date * CONSTRAINT TRIP_PKEY PRIMARY KEY (TRIPNUM), CONSTRAINT TRIP_CKEY UNIQUE (LICENSENUM, REGNUM, TDATE), CONSTRAINT TRIP_FKEY1 FOREIGN KEY (LICENSENUM) REFERENCES DRIVER(LICENSENUM), CONSTRAINT TRIP_FKEY2 FOREIGN KEY (REGNUM) REFERENCES TRUCK(REGNUM) ); /* (2) Delete the information from the database about trip number 53. Remember, that the foreign keys in all CREATE TABLE statements have no ON DELETE CASCADE clause. (2 marks)*/ /* (3) Change the status of all the drivers who have performed more than 24 trips to be on LEAVE. 2 marks)*/ /* (4) Copy information about all employees born before the year 2000 to a new table s20CENT. There is no need to enforce any consistency constraints on the new table. (2 marks)*/ /* / select sysdate() from dual

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Database Systems Design Implementation And Management

Authors: Peter Robb,Carlos Coronel

5th Edition

061906269X, 9780619062699

More Books

Students also viewed these Databases questions

Question

Types of Interpersonal Relationships?

Answered: 1 week ago

Question

Self-Disclosure and Interpersonal Relationships?

Answered: 1 week ago