ujj

DROP TABLE types_of_care CASCADE  CONSTRAINT;
DROP TABLE customer CASCADE  CONSTRAINT;
DROP TABLE cars CASCADE CONSTRAINT;
DROP TABLE Request;
DROP TABLE phoneNumber ;

CREATE TABLE types_of_care(
typeId NUMBER(2),
typeName VARCHAR(50),
CONSTRAINT cons_typeId_pk PRIMARY KEY(typeId),
CONSTRAINT cons_typeName_uniq UNIQUE(typeName)
);


CREATE TABLE customer(
    customerSSN VARCHAR(14),
    customerName VARCHAR(50) NOT NULL,
    CONSTRAINT cons_customerSSN_pk PRIMARY KEY(customerSSN)
);



-- ensure if check look on length or not
CREATE TABLE phoneNumber(
    phoneNumber VARCHAR(10) NOT NULL ,
    customerSSN_phone VARCHAR(14) NOT NULL ,
    CONSTRAINT sons_phoneNumber_un UNIQUE(phoneNumber),
    CONSTRAINT sons_phoneNumber_ch CHECK(LENGTH(phoneNumber) = 10),  -- must be 10 number
    CONSTRAINT cons_customerSSN_phone_fk foreign KEY(customerSSN_phone) references customer(customerSSN)
);


CREATE TABLE cars(
    carsNumber VARCHAR(10),
    carsColor VARCHAR(25),
    carsType NUMBER(2) NOT NULL,
    carsYear DATE,
    CONSTRAINT cons_carsNumber_pk PRIMARY KEY(carsNumber),
    CONSTRAINT cons_carsType_fk foreign KEY(carsType) references types_of_care(typeId)
);



CREATE TABLE Request(
    RequestId NUMBER(9),
    ReqStartDate DATE NOT NULL,
    ReqEndDate DATE NULL,
    customerSSN_req VARCHAR(14)  NOT NULL,
    carsNumber_req VARCHAR(10) NOT NULL,
    CONSTRAINT cons_RequestId_pk PRIMARY KEY(RequestId),
    CONSTRAINT cons_ReqEndDate_ch CHECK(  ReqEndDate > ReqStartDate ),
    CONSTRAINT cons_customerSSN_req_fk foreign KEY(customerSSN_req) references customer(customerSSN),
    CONSTRAINT cons_carsNumber_req_fk foreign KEY(carsNumber_req) references cars(carsNumber)
    );



INSERT INTO  customer VALUES('12345' , 'Ahmad');
INSERT INTO  customer VALUES('123456' , 'mhmod');

INSERT INTO  phoneNumber VALUES('1122233036' , '12345');
INSERT INTO  phoneNumber VALUES('2454165405' , '12345');
--insert into  phoneNumber values(null , '12345');


INSERT INTO  types_of_care VALUES('1' , 'KIA');
INSERT INTO  types_of_care VALUES('2' , 'TOYOTA');

INSERT INTO  cars VALUES('245-54545' , 'Red',1 , '17/12/2015');
INSERT INTO  cars VALUES('255-55644' , 'BLue',2 , '22/6/2019');
INSERT INTO  cars VALUES('255-55688' , 'Green',1 , '5/2/2020');


INSERT INTO  Request VALUES(1 , '22/2/2202','23/2/2202','12345','245-54545');
INSERT INTO  Request VALUES(2 , '22/2/2021','23/2/2021','123456','255-55644');
INSERT INTO  Request VALUES(3 , '22/2/2020','23/2/2020','123456','255-55688');



ALTER TABLE Request DROP CONSTRAINT  cons_ReqEndDate_ch ;
ALTER TABLE Request ADD CONSTRAINT  cons_ReqEndDate_ch CHECK(  ReqEndDate > ReqStartDate );