Advertisement
Yahya-Ak-Ayoub

ss

Mar 23rd, 2021
2,478
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2. DROP TABLE types_of_care;
  3. DROP TABLE customer;
  4. DROP TABLE phoneNumber;
  5. DROP TABLE cars;
  6. DROP TABLE Request;
  7.  
  8. CREATE TABLE types_of_care(
  9. typeId NUMBER(2),
  10. typeName VARCHAR(50),
  11. CONSTRAINT cons_typeId_pk PRIMARY KEY(typeId),
  12. CONSTRAINT cons_typeName_uniq UNIQUE(typeName)
  13. );
  14.  
  15.  
  16. CREATE TABLE customer(
  17.     customerSSN VARCHAR(14),
  18.     customerName VARCHAR(50) NOT NULL,
  19.     CONSTRAINT cons_customerSSN_pk PRIMARY KEY(customerSSN)
  20. );
  21.  
  22. -- ensure if check look on length or not
  23. CREATE TABLE phoneNumber(
  24.     phoneNumber VARCHAR(10),
  25.     customerSSN_phone VARCHAR(14) NOT NULL ,
  26.     CONSTRAINT sons_phoneNumber_un UNIQUE(phoneNumber),
  27.     CONSTRAINT sons_phoneNumber_ch CHECK(LENGTH(phoneNumber) = 10),
  28.     CONSTRAINT cons_customerSSN_phone_fk foreign KEY(customerSSN_phone) references customer(customerSSN)
  29. );
  30.  
  31.  
  32. CREATE TABLE cars(
  33.     carsNumber VARCHAR(10),
  34.     carsColor VARCHAR(25),
  35.     carsType NUMBER(2) NOT NULL,
  36.     carsYear DATE,
  37.     CONSTRAINT cons_carsNumber_pk PRIMARY KEY(carsNumber),
  38.     CONSTRAINT cons_carsType_fk foreign KEY(carsType) references types_of_care(typeId)
  39. );
  40.  
  41.  
  42. CREATE TABLE Request(
  43.     RequestId NUMBER(9),
  44.     ReqStartDate DATE NOT NULL,
  45.     ReqEndDate DATE NULL,
  46.     customerSSN_req VARCHAR(14)  NOT NULL,
  47.     carsNumber_req VARCHAR(10) NOT NULL,
  48.     CONSTRAINT cons_RequestId_pk PRIMARY KEY(RequestId),
  49.     CONSTRAINT cons_customerSSN_req_fk foreign KEY(customerSSN_req) references customer(customerSSN),
  50.     CONSTRAINT cons_carsNumber_req_fk foreign KEY(carsNumber_req) references cars(carsNumbe)
  51.     );
  52.  
  53.  
  54.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement