ss

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

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),
    customerSSN_phone VARCHAR(14) NOT NULL ,
    CONSTRAINT sons_phoneNumber_un UNIQUE(phoneNumber),
    CONSTRAINT sons_phoneNumber_ch CHECK(LENGTH(phoneNumber) = 10),
    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_customerSSN_req_fk foreign KEY(customerSSN_req) references customer(customerSSN),
    CONSTRAINT cons_carsNumber_req_fk foreign KEY(carsNumber_req) references cars(carsNumbe)
    );