Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- ----------------------------
- -- Table structure for "customers"
- -- ----------------------------
- CREATE TABLE "customers" ( "c_id" NUMBER NOT NULL, "name" CHAR(10BYTE) NOT NULL, "phone" CHAR(13BYTE), "email" CHAR(15BYTE));
- -- ----------------------------
- -- Table structure for "furniture"
- -- ----------------------------
- 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);
- -- ----------------------------
- -- Table structure for "check_out"
- -- ----------------------------
- CREATE TABLE "check_out" ( "c_id" NUMBER NOT NULL, "f_id" NUMBER NOT NULL, "ck_out_date" DATE NOT NULL, "ret_date" DATE);
- -- ----------------------------
- -- Primary key structure for table "customers"
- -- ----------------------------
- ALTER TABLE "customers" ADD CONSTRAINT "customer_key" PRIMARY KEY("c_id");
- -- ----------------------------
- -- Primary key structure for table "furniture"
- -- ----------------------------
- ALTER TABLE "furniture" ADD CONSTRAINT "furniture_key" PRIMARY KEY("f_id");
- -- ----------------------------
- -- Indexes structure for table "check_out"
- -- ----------------------------
- CREATE UNIQUE INDEX "unique_key" ON "check_out" ("c_id" ASC, "f_id" ASC);
- -- ----------------------------
- -- Foreign Keys
- -- ----------------------------
- 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");
- -- ----------------------------
- -- Sequence for auto-increment IDs
- -- ----------------------------
- create sequence customer_id_seq start with 1 increment by 1;
- create sequence furniture_id_seq start with 1 increment by 1;
- -- ----------------------------
- -- Customer Data
- -- ----------------------------
- INSERT INTO "customers" ("c_id", "name", "phone", "email") VALUES (customer_id_seq.nextval, 'jim', '555-5555', '1@2.com');
- INSERT INTO "customers" ("c_id", "name", "phone", "email") VALUES (customer_id_seq.nextval, 'jane', '555-5551', '1@3.com');
- INSERT INTO "customers" ("c_id", "name", "phone", "email") VALUES (customer_id_seq.nextval, 'bob', '555-5552', '1@4.com');
- INSERT INTO "customers" ("c_id", "name", "phone", "email") VALUES (customer_id_seq.nextval, 'jack', '555-5553', '1@5.com');
- INSERT INTO "customers" ("c_id", "name", "phone", "email") VALUES (customer_id_seq.nextval, 'jessica', '555-5554', '1@6.com');
- INSERT INTO "customers" ("c_id", "name", "phone", "email") VALUES (customer_id_seq.nextval, 'matt', '555-5555', '1@7.com');
- INSERT INTO "customers" ("c_id", "name", "phone", "email") VALUES (customer_id_seq.nextval, 'tim', '555-5556', '1@8.com');
- INSERT INTO "customers" ("c_id", "name", "phone", "email") VALUES (customer_id_seq.nextval, 'luke', '555-5557', '1@9.com');
- INSERT INTO "customers" ("c_id", "name", "phone", "email") VALUES (customer_id_seq.nextval, 'tom', '555-5558', '1@10.com');
- INSERT INTO "customers" ("c_id", "name", "phone", "email") VALUES (customer_id_seq.nextval, 'addam', '555-5559', '1@11.com');
- -- ----------------------------
- -- Furniture Data
- -- ----------------------------
- INSERT INTO "furniture" ("f_id", "category", "style", "price") VALUES (furniture_id_seq.nextval, 'bedroom', 'modern', '200.00');
- INSERT INTO "furniture" ("f_id", "category", "style", "price") VALUES (furniture_id_seq.nextval, 'bedroom', 'rustic', '205.00');
- INSERT INTO "furniture" ("f_id", "category", "style", "price") VALUES (furniture_id_seq.nextval, 'kitchen', 'rustic', '120.00');
- INSERT INTO "furniture" ("f_id", "category", "style", "price") VALUES (furniture_id_seq.nextval, 'kitchen', 'traditional', '215.00');
- INSERT INTO "furniture" ("f_id", "category", "style", "price") VALUES (furniture_id_seq.nextval, 'kitchen', 'modern', '229.65');
- INSERT INTO "furniture" ("f_id", "category", "style", "price") VALUES (furniture_id_seq.nextval, 'den', 'traditional', '20.00');
- INSERT INTO "furniture" ("f_id", "category", "style", "price") VALUES (furniture_id_seq.nextval, 'den', 'modern', '210.00');
- INSERT INTO "furniture" ("f_id", "category", "style", "price") VALUES (furniture_id_seq.nextval, 'den', 'rustic', '223.30');
- INSERT INTO "furniture" ("f_id", "category", "style", "price") VALUES (furniture_id_seq.nextval, 'garage', 'traditional', '205.00');
- INSERT INTO "furniture" ("f_id", "category", "style", "price") VALUES (furniture_id_seq.nextval, 'garage', 'modern', '203.34');
- -- ----------------------------
- -- Customer/Furniture Checkout Data
- -- ----------------------------
- INSERT INTO "check_out" VALUES ((SELECT "c_id" FROM "customers" WHERE "name" = 'jim'),
- (SELECT "f_id" FROM "furniture" WHERE "category" = 'kitchen' AND "style" = 'traditional'), TO_DATE('2011-09-08','YYYY-MM-DD'), null);
- INSERT INTO "check_out" VALUES ((SELECT "c_id" FROM "customers" WHERE "name" = 'jane'),
- (SELECT "f_id" FROM "furniture" WHERE "category" = 'kitchen' AND "style" = 'modern'), TO_DATE('2011-05-03','YYYY-MM-DD'), null);
- INSERT INTO "check_out" VALUES ((SELECT "c_id" FROM "customers" WHERE "name" = 'bob'),
- (SELECT "f_id" FROM "furniture" WHERE "category" = 'garage' AND "style" = 'traditional'), TO_DATE('2011-09-07','YYYY-MM-DD'), null);
- INSERT INTO "check_out" VALUES ((SELECT "c_id" FROM "customers" WHERE "name" = 'bob'),
- (SELECT "f_id" FROM "furniture" WHERE "category" = 'garage' AND "style" = 'modern'), TO_DATE('2011-09-07','YYYY-MM-DD'), null);
- INSERT INTO "check_out" VALUES ((SELECT "c_id" FROM "customers" WHERE "name" = 'jack'),
- (SELECT "f_id" FROM "furniture" WHERE "category" = 'den' AND "style" = 'modern'), TO_DATE('2011-09-05','YYYY-MM-DD'), null);
- INSERT INTO "check_out" VALUES ((SELECT "c_id" FROM "customers" WHERE "name" = 'matt'),
- (SELECT "f_id" FROM "furniture" WHERE "category" = 'den' AND "style" = 'traditional'), TO_DATE('2011-09-21','YYYY-MM-DD'), null);
- INSERT INTO "check_out" VALUES ((SELECT "c_id" FROM "customers" WHERE "name" = 'tim'),
- (SELECT "f_id" FROM "furniture" WHERE "category" = 'den' AND "style" = 'rustic'), TO_DATE('2011-06-08','YYYY-MM-DD'), null);
- INSERT INTO "check_out" VALUES ((SELECT "c_id" FROM "customers" WHERE "name" = 'luke'),
- (SELECT "f_id" FROM "furniture" WHERE "category" = 'bedroom' AND "style" = 'modern'), TO_DATE('2011-11-08','YYYY-MM-DD'), null);
- INSERT INTO "check_out" VALUES ((SELECT "c_id" FROM "customers" WHERE "name" = 'tom'),
- (SELECT "f_id" FROM "furniture" WHERE "category" = 'bedroom' AND "style" = 'rustic'), TO_DATE('2011-07-18','YYYY-MM-DD'), null);
- INSERT INTO "check_out" VALUES ((SELECT "c_id" FROM "customers" WHERE "name" = 'addam'),
- (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