Guest User

Untitled

a guest
Jan 22nd, 2018
306
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 6.70 KB | None | 0 0
  1.  
  2. -- ----------------------------
  3. -- Table structure for "customers"
  4. -- ----------------------------
  5. CREATE TABLE "customers" ( "c_id" NUMBER NOT NULL, "name" CHAR(10BYTE) NOT NULL, "phone" CHAR(13BYTE), "email" CHAR(15BYTE));
  6.  
  7. -- ----------------------------
  8. -- Table structure for "furniture"
  9. -- ----------------------------
  10. CREATE TABLE "furniture" ( "f_id" NUMBER NOT NULL, "category" CHAR(20BYTE) NOT NULL, "style" CHAR(15BYTE) NOT NULL, "price" NUMBER(5,2) NOT NULL, CONSTRAINT "style" CHECK ("style" in ('modern', 'rustic', 'traditional')) ENABLE);
  11.  
  12. -- ----------------------------
  13. -- Table structure for "check_out"
  14. -- ----------------------------
  15. CREATE TABLE "check_out" ( "c_id" NUMBER NOT NULL, "f_id" NUMBER NOT NULL, "ck_out_date" DATE NOT NULL, "ret_date" DATE);
  16.  
  17. -- ----------------------------
  18. -- Primary key structure for table "customers"
  19. -- ----------------------------
  20. ALTER TABLE "customers" ADD CONSTRAINT "customer_key" PRIMARY KEY("c_id");
  21.  
  22. -- ----------------------------
  23. -- Primary key structure for table "furniture"
  24. -- ----------------------------
  25. ALTER TABLE "furniture" ADD CONSTRAINT "furniture_key" PRIMARY KEY("f_id");
  26.  
  27. -- ----------------------------
  28. -- Indexes structure for table "check_out"
  29. -- ----------------------------
  30. CREATE UNIQUE INDEX "unique_key" ON "check_out" ("c_id" ASC, "f_id" ASC);
  31.  
  32. -- ----------------------------
  33. -- Foreign Keys
  34. -- ----------------------------
  35. ALTER TABLE "DBCLASS4"."check_out" ADD CONSTRAINT "foreign_c" FOREIGN KEY ("c_id") REFERENCES "DBCLASS4"."customers" ("c_id") ADD CONSTRAINT "foreign_f" FOREIGN KEY ("f_id") REFERENCES "DBCLASS4"."furniture" ("f_id");
  36.  
  37. -- ----------------------------
  38. -- Sequence for auto-increment IDs
  39. -- ----------------------------
  40. create sequence customer_id_seq start with 1 increment by 1;
  41. create sequence furniture_id_seq start with 1 increment by 1;
  42.  
  43. -- ----------------------------
  44. -- Customer Data
  45. -- ----------------------------
  46. INSERT INTO "customers" ("c_id", "name", "phone", "email") VALUES (customer_id_seq.nextval, 'jim', '555-5555', '1@2.com');
  47. INSERT INTO "customers" ("c_id", "name", "phone", "email") VALUES (customer_id_seq.nextval, 'jane', '555-5551', '1@3.com');
  48. INSERT INTO "customers" ("c_id", "name", "phone", "email") VALUES (customer_id_seq.nextval, 'bob', '555-5552', '1@4.com');
  49. INSERT INTO "customers" ("c_id", "name", "phone", "email") VALUES (customer_id_seq.nextval, 'jack', '555-5553', '1@5.com');
  50. INSERT INTO "customers" ("c_id", "name", "phone", "email") VALUES (customer_id_seq.nextval, 'jessica', '555-5554', '1@6.com');
  51. INSERT INTO "customers" ("c_id", "name", "phone", "email") VALUES (customer_id_seq.nextval, 'matt', '555-5555', '1@7.com');
  52. INSERT INTO "customers" ("c_id", "name", "phone", "email") VALUES (customer_id_seq.nextval, 'tim', '555-5556', '1@8.com');
  53. INSERT INTO "customers" ("c_id", "name", "phone", "email") VALUES (customer_id_seq.nextval, 'luke', '555-5557', '1@9.com');
  54. INSERT INTO "customers" ("c_id", "name", "phone", "email") VALUES (customer_id_seq.nextval, 'tom', '555-5558', '1@10.com');
  55. INSERT INTO "customers" ("c_id", "name", "phone", "email") VALUES (customer_id_seq.nextval, 'addam', '555-5559', '1@11.com');
  56.  
  57. -- ----------------------------
  58. -- Furniture Data
  59. -- ----------------------------
  60. INSERT INTO "furniture" ("f_id", "category", "style", "price") VALUES (furniture_id_seq.nextval, 'bedroom', 'modern', '200.00');
  61. INSERT INTO "furniture" ("f_id", "category", "style", "price") VALUES (furniture_id_seq.nextval, 'bedroom', 'rustic', '205.00');
  62. INSERT INTO "furniture" ("f_id", "category", "style", "price") VALUES (furniture_id_seq.nextval, 'kitchen', 'rustic', '120.00');
  63. INSERT INTO "furniture" ("f_id", "category", "style", "price") VALUES (furniture_id_seq.nextval, 'kitchen', 'traditional', '215.00');
  64. INSERT INTO "furniture" ("f_id", "category", "style", "price") VALUES (furniture_id_seq.nextval, 'kitchen', 'modern', '229.65');
  65. INSERT INTO "furniture" ("f_id", "category", "style", "price") VALUES (furniture_id_seq.nextval, 'den', 'traditional', '20.00');
  66. INSERT INTO "furniture" ("f_id", "category", "style", "price") VALUES (furniture_id_seq.nextval, 'den', 'modern', '210.00');
  67. INSERT INTO "furniture" ("f_id", "category", "style", "price") VALUES (furniture_id_seq.nextval, 'den', 'rustic', '223.30');
  68. INSERT INTO "furniture" ("f_id", "category", "style", "price") VALUES (furniture_id_seq.nextval, 'garage', 'traditional', '205.00');
  69. INSERT INTO "furniture" ("f_id", "category", "style", "price") VALUES (furniture_id_seq.nextval, 'garage', 'modern', '203.34');
  70.  
  71. -- ----------------------------
  72. -- Customer/Furniture Checkout Data
  73. -- ----------------------------
  74. INSERT INTO "check_out" VALUES ((SELECT "c_id" FROM "customers" WHERE "name" = 'jim'),
  75. (SELECT "f_id" FROM "furniture" WHERE "category" = 'kitchen' AND "style" = 'traditional'), TO_DATE('2011-09-08','YYYY-MM-DD'), null);
  76. INSERT INTO "check_out" VALUES ((SELECT "c_id" FROM "customers" WHERE "name" = 'jane'),
  77. (SELECT "f_id" FROM "furniture" WHERE "category" = 'kitchen' AND "style" = 'modern'), TO_DATE('2011-05-03','YYYY-MM-DD'), null);
  78. INSERT INTO "check_out" VALUES ((SELECT "c_id" FROM "customers" WHERE "name" = 'bob'),
  79. (SELECT "f_id" FROM "furniture" WHERE "category" = 'garage' AND "style" = 'traditional'), TO_DATE('2011-09-07','YYYY-MM-DD'), null);
  80. INSERT INTO "check_out" VALUES ((SELECT "c_id" FROM "customers" WHERE "name" = 'bob'),
  81. (SELECT "f_id" FROM "furniture" WHERE "category" = 'garage' AND "style" = 'modern'), TO_DATE('2011-09-07','YYYY-MM-DD'), null);
  82. INSERT INTO "check_out" VALUES ((SELECT "c_id" FROM "customers" WHERE "name" = 'jack'),
  83. (SELECT "f_id" FROM "furniture" WHERE "category" = 'den' AND "style" = 'modern'), TO_DATE('2011-09-05','YYYY-MM-DD'), null);
  84. INSERT INTO "check_out" VALUES ((SELECT "c_id" FROM "customers" WHERE "name" = 'matt'),
  85. (SELECT "f_id" FROM "furniture" WHERE "category" = 'den' AND "style" = 'traditional'), TO_DATE('2011-09-21','YYYY-MM-DD'), null);
  86. INSERT INTO "check_out" VALUES ((SELECT "c_id" FROM "customers" WHERE "name" = 'tim'),
  87. (SELECT "f_id" FROM "furniture" WHERE "category" = 'den' AND "style" = 'rustic'), TO_DATE('2011-06-08','YYYY-MM-DD'), null);
  88. INSERT INTO "check_out" VALUES ((SELECT "c_id" FROM "customers" WHERE "name" = 'luke'),
  89. (SELECT "f_id" FROM "furniture" WHERE "category" = 'bedroom' AND "style" = 'modern'), TO_DATE('2011-11-08','YYYY-MM-DD'), null);
  90. INSERT INTO "check_out" VALUES ((SELECT "c_id" FROM "customers" WHERE "name" = 'tom'),
  91. (SELECT "f_id" FROM "furniture" WHERE "category" = 'bedroom' AND "style" = 'rustic'), TO_DATE('2011-07-18','YYYY-MM-DD'), null);
  92. INSERT INTO "check_out" VALUES ((SELECT "c_id" FROM "customers" WHERE "name" = 'addam'),
  93. (SELECT "f_id" FROM "furniture" WHERE "category" = 'kitchen' AND "style" = 'rustic'), TO_DATE('2011-10-08','YYYY-MM-DD'), null);
Add Comment
Please, Sign In to add comment