Advertisement
Yahya-Ak-Ayoub

ujj

Mar 23rd, 2021
432
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2. DROP TABLE types_of_care CASCADE  CONSTRAINT;
  3. DROP TABLE customer CASCADE  CONSTRAINT;
  4. DROP TABLE cars CASCADE CONSTRAINT;
  5. DROP TABLE Request;
  6. DROP TABLE phoneNumber ;
  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.  
  23.  
  24. -- ensure if check look on length or not
  25. CREATE TABLE phoneNumber(
  26.     phoneNumber VARCHAR(10) NOT NULL ,
  27.     customerSSN_phone VARCHAR(14) NOT NULL ,
  28.     CONSTRAINT sons_phoneNumber_un UNIQUE(phoneNumber),
  29.     CONSTRAINT sons_phoneNumber_ch CHECK(LENGTH(phoneNumber) = 10),  -- must be 10 number
  30.     CONSTRAINT cons_customerSSN_phone_fk foreign KEY(customerSSN_phone) references customer(customerSSN)
  31. );
  32.  
  33.  
  34. CREATE TABLE cars(
  35.     carsNumber VARCHAR(10),
  36.     carsColor VARCHAR(25),
  37.     carsType NUMBER(2) NOT NULL,
  38.     carsYear DATE,
  39.     CONSTRAINT cons_carsNumber_pk PRIMARY KEY(carsNumber),
  40.     CONSTRAINT cons_carsType_fk foreign KEY(carsType) references types_of_care(typeId)
  41. );
  42.  
  43.  
  44.  
  45. CREATE TABLE Request(
  46.     RequestId NUMBER(9),
  47.     ReqStartDate DATE NOT NULL,
  48.     ReqEndDate DATE NULL,
  49.     customerSSN_req VARCHAR(14)  NOT NULL,
  50.     carsNumber_req VARCHAR(10) NOT NULL,
  51.     CONSTRAINT cons_RequestId_pk PRIMARY KEY(RequestId),
  52.     CONSTRAINT cons_ReqEndDate_ch CHECK(  ReqEndDate > ReqStartDate ),
  53.     CONSTRAINT cons_customerSSN_req_fk foreign KEY(customerSSN_req) references customer(customerSSN),
  54.     CONSTRAINT cons_carsNumber_req_fk foreign KEY(carsNumber_req) references cars(carsNumber)
  55.     );
  56.  
  57.  
  58.  
  59. INSERT INTO  customer VALUES('12345' , 'Ahmad');
  60. INSERT INTO  customer VALUES('123456' , 'mhmod');
  61.  
  62. INSERT INTO  phoneNumber VALUES('1122233036' , '12345');
  63. INSERT INTO  phoneNumber VALUES('2454165405' , '12345');
  64. --insert into  phoneNumber values(null , '12345');
  65.  
  66.  
  67. INSERT INTO  types_of_care VALUES('1' , 'KIA');
  68. INSERT INTO  types_of_care VALUES('2' , 'TOYOTA');
  69.  
  70. INSERT INTO  cars VALUES('245-54545' , 'Red',1 , '17/12/2015');
  71. INSERT INTO  cars VALUES('255-55644' , 'BLue',2 , '22/6/2019');
  72. INSERT INTO  cars VALUES('255-55688' , 'Green',1 , '5/2/2020');
  73.  
  74.  
  75. INSERT INTO  Request VALUES(1 , '22/2/2202','23/2/2202','12345','245-54545');
  76. INSERT INTO  Request VALUES(2 , '22/2/2021','23/2/2021','123456','255-55644');
  77. INSERT INTO  Request VALUES(3 , '22/2/2020','23/2/2020','123456','255-55688');
  78.  
  79.  
  80.  
  81. ALTER TABLE Request DROP CONSTRAINT  cons_ReqEndDate_ch ;
  82. ALTER TABLE Request ADD CONSTRAINT  cons_ReqEndDate_ch CHECK(  ReqEndDate > ReqStartDate );
  83.  
Advertisement
RAW Paste Data Copied
Advertisement