Question

CREATE TABLE DRIVER(
ENUM         DECIMAL(12) NOT NULL,
LNUM         DECIMAL(8)    NOT NULL,
STATUS         VARCHAR(10) NOT NULL,
CONSTRAINT DRIVER_PKEY PRIMARY KEY(ENUM),
CONSTRAINT DRIVER_UNIQUE UNIQUE(LNUM),
CONSTRAINT DRIVER_FKEY FOREIGN KEY(ENUM) REFERENCES EMPLOYEE(ENUM),
CONSTRAINT DRIVER_STATUS CHECK ( STATUS IN ('AVAILABLE', 'BUSY', 'ON LEAVE')) );

(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.   */

CREATE TABLE TRUCK(
REGNUM         VARCHAR(10) NOT NULL,
CAPACITY    DECIMAL(7)    NOT NULL,
WEIGHT         DECIMAL(7)    NOT NULL,
STATUS         VARCHAR(10) NOT NULL,
CONSTRAINT TRUCK_PKEY PRIMARY KEY(REGNUM),
CONSTRAINT TRUCK_STATUS CHECK ( STATUS IN ('AVAILABLE', 'USED', 'MAINTAINED')),
CONSTRAINT TRUCK_WEIGHT CHECK ( WEIGHT > 0.0 AND WEIGHT < 500000 ),
CONSTRAINT TRUCK_CAPACITY CHECK ( CAPACITY > 0.0 AND CAPACITY < 100000 ) );

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.

CREATE TABLE TRIP(
TNUM         DECIMAL(10) NOT NULL,
LNUM         DECIMAL(8)    NOT NULL,
REGNUM         VARCHAR(10) NOT NULL,
TDATE       DATE    NOT NULL,
CONSTRAINT TRIP_PKEY PRIMARY KEY (TNUM),
CONSTRAINT TRIP_CKEY UNIQUE (LNUM, REGNUM, TDATE),
CONSTRAINT TRIP_FKEY1 FOREIGN KEY (LNUM) REFERENCES DRIVER(LNUM),
CONSTRAINT TRIP_FKEY2 FOREIGN KEY (REGNUM) REFERENCES TRUCK(REGNUM) );

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.

Answer 1

# I have solved all three problems.Adding comments for detailed explanation

CREATE TABLE DRIVER(
ENUM         DECIMAL(12) NOT NULL,
LNUM         DECIMAL(8)    NOT NULL,

HEALTH VARCHAR(10) NOT NULL,          #-----ADDING HEALTH VARIABLE
STATUS         VARCHAR(10) NOT NULL,
CONSTRAINT DRIVER_PKEY PRIMARY KEY(ENUM),
CONSTRAINT DRIVER_UNIQUE UNIQUE(LNUM),
CONSTRAINT DRIVER_FKEY FOREIGN KEY(ENUM) REFERENCES EMPLOYEE(ENUM),
CONSTRAINT DRIVER_STATUS CHECK ( STATUS IN ('AVAILABLE', 'BUSY', 'ON LEAVE'))

,CONSTRAINT DRIVER_HEALTH CHECK ( HEALTH IN ('HEALTHY','SICK')) #---CHECKING ITS CONSTRAINT

);

---------------------------2ND PART-------------------------

CREATE TABLE TRUCK(
REGNUM         VARCHAR(10) NOT NULL,

REPAIR_NUMBER DECIMAL(5) NOT NULL,       #ADDING REPAIR NUMBER AS A VARIABLE
CAPACITY    DECIMAL(7)    NOT NULL,
WEIGHT         DECIMAL(7)    NOT NULL,
STATUS         VARCHAR(10) NOT NULL,
CONSTRAINT TRUCK_PKEY PRIMARY KEY(REGNUM),
CONSTRAINT TRUCK_STATUS CHECK ( STATUS IN ('AVAILABLE', 'USED', 'MAINTAINED')),
CONSTRAINT TRUCK_WEIGHT CHECK ( WEIGHT > 0.0 AND WEIGHT < 500000 ),

CONSTRAINT TRUCK_REPAIR CHECK ( REPAIR_NUMBER <= 1000 ),       #CHECKING its values

CONSTRAINT TRUCK_CAPACITY CHECK ( CAPACITY > 0.0 AND CAPACITY < 100000 ) );

--------------------------------------------3RD PART------------------------------------

CREATE TABLE TRIP(
TNUM         DECIMAL(10) NOT NULL,
LNUM         DECIMAL(8)    NOT NULL,
REGNUM         VARCHAR(10) NOT NULL,
TDATE       DATE    NOT NULL,

COST_OF_TRIP DECIMAL(5,2) NOT NULL,            # adding variable to store variable.
CONSTRAINT TRIP_PKEY PRIMARY KEY (TNUM),

CONSTRAINT TRIP_COST CHECK(COST_OF_TRIP > 0.0 AND COST_OF_TRIP < 9999.99), #checking its constraint
CONSTRAINT TRIP_CKEY UNIQUE (LNUM, REGNUM, TDATE),
CONSTRAINT TRIP_FKEY1 FOREIGN KEY (LNUM) REFERENCES DRIVER(LNUM),
CONSTRAINT TRIP_FKEY2 FOREIGN KEY (REGNUM) REFERENCES TRUCK(REGNUM) );