# Microsoft Word - Lab 3 - Using SQL DDL and DML.docx Diploma of Information Technology XXXXXXXXXXSession: Summer 2020 UOW College Australia DPIT115 Data Management and Security Laboratory 3 Published...

Hi I am posting this is assignment its a simple lab asssignment of few tasks every thing will be mentioned in attached document

## Answer To: Microsoft Word - Lab 3 - Using SQL DDL and DML.docx Diploma of Information Technology...

Shweta answered on Dec 12 2021
Solution73506/solution1-1-lrzwljxq.sql/* DPIT115             : Data Management and Security
SESSION             : Summer Session 2020
FILE                 : Solution 1
STUDENT NAMES     :
STUDENT NUMBER     :
STUDENT EMAIL    :
DATE                 :                                                  */
/* (1)    Modify a consistency constraint of the sample database such that after a modification
it is possib
le to record in the database information about the drivers who are sick. */
/* (2)    Modify a structure and consistency constraint of the sample database such that after
a 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 a structure and consistency constraint of the sample database such that after
a modification it is possible to store in the database optional information about
the cost of each trip. Assume, that cost of a single trip is a positive number not
greater that 9999.99. */
/* (4)    Modify a structure and consistency constraints of a sample database such it is
possible to store in a database information about the mechanics employed by
a transportation company. Assume that a description of mechanic is the same as
a description of 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
a structure and consistency constraints of a sample database such that after
a modification it is possible to store in the database information about which
mechanic is assigned to which truck. */
the new trip consists of. All information about the new trip and its two legs is up
to you. */
/* (7)    Change a status of a truck with a registration number PKR768 to USED. */
/* (8)    Remove from the database all information about a trip number 35. */
/* (9)    Modify a consistency constraint of the sample database such that the values in a
column REGNUM in a relational table TRIP are optional. */
/* (10)    Remove from the database information about a truck with a registration number KKK007.
Leave in the database information about the trips performed by the truck. */
Solution73506/SOLUTION1.RPT2 queries executed, 2 success, 0 errors, 0 warnings
Query: ALTER TABLE DRIVER DROP CONSTRAINT DRIVER_STATUS
0 row(s) affected
Execution Time : 0.665 sec
Transfer Time : 0.001 sec
Total Time : 0.666 sec
--------------------------------------------------
Query: ALTER TABLE DRIVER ADD CONSTRAINT DRIVER_STATUS CHECK ( STATUS IN ('AVAILABLE', 'BUSY', 'ON LEAVE','SICK'))
17 row(s) affected
Execution Time : 1.982 sec
Transfer Time : 0 sec
Total Time : 1.983 sec
------------------------------------------------------------------------------------
2 queries executed, 2 success, 0 errors, 0 warnings
Query: ALTER TABLE TRUCK ADD REPAIR INTEGER
0 row(s) affected
Execution Time : 0.973 sec
Transfer Time : 0.001 sec
Total Time : 0.974 sec
--------------------------------------------------
Query: ALTER TABLE TRUCK ADD CONSTRAINT TRUCK_REPAIR CHECK ( REPAIR >= 1 AND REPAIR <= 1000 )
11 row(s) affected
Execution Time : 3.999 sec
Transfer Time : 0 sec
Total Time : 3.999 sec
-----------------------------------------------------------------------
2 queries executed, 2 success, 0 errors, 0 warnings
Query: ALTER TABLE TRIP ADD COST DECIMAL(10) NULL
0 row(s) affected
Execution Time : 0.675 sec
Transfer Time : 0.001 sec
Total Time : 0.676 sec
--------------------------------------------------
Query: ALTER TABLE TRIP ADD CONSTRAINT TRIP_COST CHECK ( COST > 0.0 AND COST < 100000 )
35 row(s) affected
Execution Time : 3.009 sec
Transfer Time : 0 sec
Total Time : 3.010 sec
------------------------------------------------------------------------------
1 queries executed, 1 success, 0 errors, 0 warnings
Query: CREATE TABLE MECHANICS( ENUM DECIMAL(12) NOT NULL, IDENTITYNUM DECIMAL(8) NOT NULL, STATUS VARCHAR(10) NOT NULL, CONSTRAINT MECH...
0 row(s) affected
Execution Time : 0.728 sec
Transfer Time : 0.001 sec
Total Time : 0.730...
SOLUTION.PDF