0

can anyone help me to check the relation table of the database.

Attachments
DROP TABLE CUSTOMERS;
DROP TABLE ORDERS;
DROP TABLE PUBLISHER;
DROP TABLE AUTHOR;
DROP TABLE BOOKS;
DROP TABLE ORDERITEMS;
DROP TABLE BOOKAUTHOR;
DROP TABLE PROMOTION;

Create table Customers
(Customer#  NUMBER(4),
LastName VARCHAR2(10),
FirstName VARCHAR2(10),
Address VARCHAR2(20),
City VARCHAR2(12),
State VARCHAR2(2),
Zip VARCHAR2(5),
Referred NUMBER(4),
Constraint Customers_Customer#_pk Primary Key(Customer#));

INSERT INTO CUSTOMERS
VALUES (1001, 'MORALES', 'BONITA', 'P.O. BOX 651', 'EASTPOINT', 'FL', '32328', NULL);
INSERT INTO CUSTOMERS
VALUES (1002, 'THOMPSON', 'RYAN', 'P.O. BOX 9835', 'SANTA MONICA', 'CA', '90404', NULL);
INSERT INTO CUSTOMERS
VALUES (1003, 'SMITH', 'LEILA', 'P.O. BOX 66', 'TALLAHASSEE', 'FL', '32306', NULL);
INSERT INTO CUSTOMERS
VALUES (1004, 'PIERSON', 'THOMAS', '69821 SOUTH AVENUE', 'BOISE', 'ID', '83707', NULL);
INSERT INTO CUSTOMERS
VALUES (1005, 'GIRARD', 'CINDY', 'P.O. BOX 851', 'SEATTLE', 'WA', '98115', NULL);
INSERT INTO CUSTOMERS
VALUES (1006, 'CRUZ', 'MESHIA', '82 DIRT ROAD', 'ALBANY', 'NY', '12211', NULL);
INSERT INTO CUSTOMERS
VALUES (1007, 'GIANA', 'TAMMY', '9153 MAIN STREET', 'AUSTIN', 'TX', '78710', 1003);
INSERT INTO CUSTOMERS
VALUES (1008, 'JONES', 'KENNETH', 'P.O. BOX 137', 'CHEYENNE', 'WY', '82003', NULL);
INSERT INTO CUSTOMERS
VALUES (1009, 'PEREZ', 'JORGE', 'P.O. BOX 8564', 'BURBANK', 'CA', '91510', 1003);
INSERT INTO CUSTOMERS
VALUES (1010, 'LUCAS', 'JAKE', '114 EAST SAVANNAH', 'ATLANTA', 'GA', '30314', NULL);
INSERT INTO CUSTOMERS
VALUES (1011, 'MCGOVERN', 'REESE', 'P.O. BOX 18', 'CHICAGO', 'IL', '60606', NULL); 
INSERT INTO CUSTOMERS
VALUES (1012, 'MCKENZIE', 'WILLIAM', 'P.O. BOX 971', 'BOSTON', 'MA', '02110', NULL);
INSERT INTO CUSTOMERS
VALUES (1013, 'NGUYEN', 'NICHOLAS', '357 WHITE EAGLE AVE.', 'CLERMONT', 'FL', '34711', 1006);
INSERT INTO CUSTOMERS
VALUES (1014, 'LEE', 'JASMINE', 'P.O. BOX 2947', 'CODY', 'WY', '82414', NULL);
INSERT INTO CUSTOMERS
VALUES (1015, 'SCHELL', 'STEVE', 'P.O. BOX 677', 'MIAMI', 'FL', '33111', NULL);
INSERT INTO CUSTOMERS
VALUES (1016, 'DAUM', 'MICHELL', '9851231 LONG ROAD', 'BURBANK', 'CA', '91508', 1010);
INSERT INTO CUSTOMERS
VALUES (1017, 'NELSON', 'BECCA', 'P.O. BOX 563', 'KALMAZOO', 'MI', '49006', NULL);
INSERT INTO CUSTOMERS
VALUES (1018, 'MONTIASA', 'GREG', '1008 GRAND AVENUE', 'MACON', 'GA', '31206', NULL);
INSERT INTO CUSTOMERS
VALUES (1019, 'SMITH', 'JENNIFER', 'P.O. BOX 1151', 'MORRISTOWN', 'NJ', '07962', 1003);
INSERT INTO CUSTOMERS
VALUES (1020, 'FALAH', 'KENNETH', 'P.O. BOX 335', 'TRENTON', 'NJ', '08607', NULL);

Create Table Orders
(Order# NUMBER(4) ,
Customer# NUMBER(4),
OrderDate DATE,
ShipDate DATE,
ShipStreet VARCHAR2(18),
ShipCity VARCHAR2(15),
ShipState VARCHAR2(2),
ShipZip VARCHAR2(5),
Constraint Orders_Order#_pk Primary Key(Order#),
Constraint Orders_Customer#_fk Foreign Key(Customer#)references Customers(Customer#));

INSERT INTO ORDERS
VALUES (1000,1005,'31-MAR-03','02-APR-03','1201 ORANGE AVE', 'SEATTLE', 'WA', '98114');
INSERT INTO ORDERS
VALUES (1001,1010,'31-MAR-03','01-APR-03', '114 EAST SAVANNAH', 'ATLANTA', 'GA', '30314');
INSERT INTO ORDERS
VALUES (1002,1011,'31-MAR-03','01-APR-03','58 TILA CIRCLE', 'CHICAGO', 'IL', '60605');
INSERT INTO ORDERS
VALUES (1003,1001,'01-APR-03','01-APR-03','958 MAGNOLIA LANE', 'EASTPOINT', 'FL', '32328');
INSERT INTO ORDERS
VALUES (1004,1020,'01-APR-03','05-APR-03','561 ROUNDABOUT WAY', 'TRENTON', 'NJ', '08601');
INSERT INTO ORDERS
VALUES (1005,1018,'01-APR-03','02-APR-03', '1008 GRAND AVENUE', 'MACON', 'GA', '31206');
INSERT INTO ORDERS
VALUES (1006,1003,'01-APR-03','02-APR-03','558A CAPITOL HWY.', 'TALLAHASSEE', 'FL', '32307');
INSERT INTO ORDERS
VALUES (1007,1007,'02-APR-03','04-APR-03', '9153 MAIN STREET', 'AUSTIN', 'TX', '78710');
INSERT INTO ORDERS
VALUES (1008,1004,'02-APR-03','03-APR-03', '69821 SOUTH AVENUE', 'BOISE', 'ID', '83707');
INSERT INTO ORDERS
VALUES (1009,1005,'03-APR-03','05-APR-03','9 LIGHTENING RD.', 'SEATTLE', 'WA', '98110');
INSERT INTO ORDERS
VALUES (1010,1019,'03-APR-03','04-APR-03','384 WRONG WAY HOME', 'MORRISTOWN', 'NJ', '07960');
INSERT INTO ORDERS
VALUES (1011,1010,'03-APR-03','05-APR-03', '102 WEST LAFAYETTE', 'ATLANTA', 'GA', '30311');
INSERT INTO ORDERS
VALUES (1012,1017,'03-APR-03',NULL,'1295 WINDY AVENUE', 'KALMAZOO', 'MI', '49002');
INSERT INTO ORDERS
VALUES (1013,1014,'03-APR-03','04-APR-03','7618 MOUNTAIN RD.', 'CODY', 'WY', '82414');
INSERT INTO ORDERS
VALUES (1014,1007,'04-APR-03','05-APR-03', '9153 MAIN STREET', 'AUSTIN', 'TX', '78710');
INSERT INTO ORDERS
VALUES (1015,1020,'04-APR-03',NULL,'557 GLITTER ST.', 'TRENTON', 'NJ', '08606');
INSERT INTO ORDERS
VALUES (1016,1003,'04-APR-03',NULL,'9901 SEMINOLE WAY', 'TALLAHASSEE', 'FL', '32307');
INSERT INTO ORDERS
VALUES (1017,1015,'04-APR-03','05-APR-03','887 HOT ASPHALT ST', 'MIAMI', 'FL', '33112');
INSERT INTO ORDERS
VALUES (1018,1001,'05-APR-03',NULL,'95812 HIGHWAY 98', 'EASTPOINT', 'FL', '32328');
INSERT INTO ORDERS
VALUES (1019,1018,'05-APR-03',NULL, '1008 GRAND AVENUE', 'MACON', 'GA', '31206');
INSERT INTO ORDERS
VALUES (1020,1008,'05-APR-03',NULL,'195 JAMISON LANE', 'CHEYENNE', 'WY', '82003');

Create Table Publisher
(PubID NUMBER(2),
Name VarCHAR2(23),
Contact VARCHAR2(15),
Phone VARCHAR2(12),
Constraint Publisher_PubID_pk Primary Key(PubID));

INSERT INTO PUBLISHER
VALUES(1,'PRINTING IS US','TOMMIE SEYMOUR','000-714-8321');
INSERT INTO PUBLISHER
VALUES(2,'PUBLISH OUR WAY','JANE TOMLIN','010-410-0010');
INSERT INTO PUBLISHER
VALUES(3,'AMERICAN PUBLISHING','DAVID DAVIDSON','800-555-1211');
INSERT INTO PUBLISHER
VALUES(4,'READING MATERIALS INC.','RENEE SMITH','800-555-9743');
INSERT INTO PUBLISHER
VALUES(5,'REED-N-RITE','SEBASTIAN JONES','800-555-8284');

Create Table Author
(AuthorID Varchar2(4),
Lname VARCHAR2(10),
Fname VARCHAR2(10),
Constraint Author_AuthorID_pk Primary Key(AuthorID));

INSERT INTO AUTHOR
VALUES ('S100','SMITH', 'SAM');
INSERT INTO AUTHOR
VALUES ('J100','JONES','JANICE');
INSERT INTO AUTHOR
VALUES ('A100','AUSTIN','JAMES');
INSERT INTO AUTHOR
VALUES ('M100','MARTINEZ','SHEILA');
INSERT INTO AUTHOR
VALUES ('K100','KZOCHSKY','TAMARA');
INSERT INTO AUTHOR
VALUES ('P100','PORTER','LISA');
INSERT INTO AUTHOR
VALUES ('A105','ADAMS','JUAN');
INSERT INTO AUTHOR
VALUES ('B100','BAKER','JACK'); 
INSERT INTO AUTHOR
VALUES ('P105','PETERSON','TINA');
INSERT INTO AUTHOR
VALUES ('W100','WHITE','WILLIAM');
INSERT INTO AUTHOR
VALUES ('W105','WHITE','LISA');
INSERT INTO AUTHOR
VALUES ('R100','ROBINSON','ROBERT');
INSERT INTO AUTHOR
VALUES ('F100','FIELDS','OSCAR');
INSERT INTO AUTHOR
VALUES ('W110','WILKINSON','ANTHONY');



Create table Books
(ISBN VARCHAR2(10),
Title VARCHAR2(30),
PubDate DATE,
PubID NUMBER (2) Not NUll,
Cost NUMBER (5,2),
Retail NUMBER (5,2),
Category VARCHAR2(12),
Constraint Books_ISBN_pk Primary Key(ISBN),
Constraint Books_PubID_pk Foreign Key(PubID) references Publisher(PubID));

INSERT INTO BOOKS
VALUES ('1059831198','BODYBUILD IN 10 MINUTES A DAY','21-JAN-01',4,18.75,30.95, 'FITNESS');
INSERT INTO BOOKS
VALUES ('0401140733','REVENGE OF MICKEY','14-DEC-01',1,14.20,22.00, 'FAMILY LIFE');
INSERT INTO BOOKS
VALUES ('4981341710','BUILDING A CAR WITH TOOTHPICKS','18-MAR-02',2,37.80,59.95, 'CHILDREN');
INSERT INTO BOOKS
VALUES ('8843172113','DATABASE IMPLEMENTATION','04-JUN-99',3,31.40,55.95, 'COMPUTER');
INSERT INTO BOOKS
VALUES ('3437212490','COOKING WITH MUSHROOMS','28-FEB-00',4,12.50,19.95, 'COOKING');
INSERT INTO BOOKS
VALUES ('3957136468','HOLY GRAIL OF ORACLE','31-DEC-01',3,47.25,75.95, 'COMPUTER');
INSERT INTO BOOKS
VALUES ('1915762492','HANDCRANKED COMPUTERS','21-JAN-01',3,21.80,25.00, 'COMPUTER');
INSERT INTO BOOKS
VALUES ('9959789321','E-BUSINESS THE EASY WAY','01-MAR-02',2,37.90,54.50, 'COMPUTER');
INSERT INTO BOOKS
VALUES ('2491748320','PAINLESS CHILD-REARING','17-JUL-00',5,48.00,89.95, 'FAMILY LIFE');
INSERT INTO BOOKS
VALUES ('0299282519','THE WOK WAY TO COOK','11-SEP-00',4,19.00,28.75, 'COOKING');
INSERT INTO BOOKS
VALUES ('8117949391','BIG BEAR AND LITTLE DOVE','08-NOV-01',5,5.32,8.95, 'CHILDREN');
INSERT INTO BOOKS
VALUES ('0132149871','HOW TO GET FASTER PIZZA','11-NOV-02',4,17.85,29.95, 'SELF HELP');
INSERT INTO BOOKS
VALUES ('9247381001','HOW TO MANAGE THE MANAGER','09-MAY-99',1,15.40,31.95, 'BUSINESS');
INSERT INTO BOOKS
VALUES ('2147428890','SHORTEST POEMS','01-MAY-01',5,21.85,39.95, 'LITERATURE');

CREATE TABLE ORDERITEMS
(ORDER# NUMBER(4) NOT NULL,
ITEM# NUMBER(2) NOT NULL,
ISBN VARCHAR2(10),
QUANTITY NUMBER(3), 
constraint pk_orderitems PRIMARY KEY (order#, item#),
Constraint ORDERITEMS_ISBN_fk Foreign Key(ISBN) references Books(ISBN),
Constraint Orders_Order#_fk Foreign Key(order#) references Orders(Order#));

INSERT INTO ORDERITEMS
VALUES (1000,1,'3437212490',1); 
INSERT INTO ORDERITEMS
VALUES (1001,1,'9247381001',1); 
INSERT INTO ORDERITEMS
VALUES (1001,2,'2491748320',1); 
INSERT INTO ORDERITEMS
VALUES (1002,1,'8843172113',2); 
INSERT INTO ORDERITEMS
VALUES (1003,1,'8843172113',1); 
INSERT INTO ORDERITEMS
VALUES (1003,2,'1059831198',1);
INSERT INTO ORDERITEMS
VALUES (1003,3,'3437212490',1);
INSERT INTO ORDERITEMS
VALUES (1004,1,'2491748320',2);
INSERT INTO ORDERITEMS
VALUES (1005,1,'2147428890',1);
INSERT INTO ORDERITEMS
VALUES (1006,1,'9959789321',1);
INSERT INTO ORDERITEMS
VALUES (1007,1,'3957136468',3);
INSERT INTO ORDERITEMS
VALUES (1007,2,'9959789321',1);
INSERT INTO ORDERITEMS
VALUES (1007,3,'8117949391',1);
INSERT INTO ORDERITEMS
VALUES (1007,4,'8843172113',1);
INSERT INTO ORDERITEMS
VALUES (1008,1,'3437212490',2);
INSERT INTO ORDERITEMS
VALUES (1009,1,'3437212490',1);
INSERT INTO ORDERITEMS
VALUES (1009,2,'0401140733',1);
INSERT INTO ORDERITEMS
VALUES (1010,1,'8843172113',1);
INSERT INTO ORDERITEMS
VALUES (1011,1,'2491748320',1);
INSERT INTO ORDERITEMS
VALUES (1012,1,'8117949391',1);
INSERT INTO ORDERITEMS
VALUES (1012,2,'1915762492',2);
INSERT INTO ORDERITEMS
VALUES (1012,3,'2491748320',1);
INSERT INTO ORDERITEMS
VALUES (1012,4,'0401140733',1);
INSERT INTO ORDERITEMS
VALUES (1013,1,'88431
3
Contributors
4
Replies
5
Views
5 Years
Discussion Span
Last Post by BitBlt
0

please post your question in full text
even i can't understand your question, so how can i help you?
please post your question again with some description of your question.

thanks

0

I looked at your SQL statements and it all looks fine, as far as I can tell. I did have to translate your SQL to a dialect I was more familiar with, but once I got all that fixed up, I didn't have any problems building the database and populating the tables.

There is one question about that PROMOTION table. Not sure if it's supposed to have referential integrity to anything. If so, it needs some work to get it to relate to anything.

If there's an actual question here, please elaborate.

-2

Draw a complete ER-diagram/Model for the PIZZA delivery Database which consists of topping, Pizzatopping, Pizza, Pizza order, Customer tables.

----------------------------------------------

drop table pizza;
drop table topping;
drop table pizzatopping;
drop table pizzaorder;
drop table customer;

create table pizza
(
  pizzaID   number,
  pizzaname varchar2(30),
  basecost  number(4,2),
  primary key   (pizzaID)
);

create table topping
(
  toppingID number,
  topping   varchar2(10),
  toppingtype   varchar2(5),
  toppingcost   number(4,2),
  primary key   (toppingID)
);

create table pizzatopping
(
  pizzaID   number,
  toppingID number,
  primary key (pizzaID, toppingID)
);

create table pizzaorder
(
  orderID   number,
  customerID    number,
  pizzaID   number,
  orderdate date,
  quantity  number,
  primary key   (orderID)
);

-- INSERT THE CREATE TABLE STATEMENT HERE TO CREATE THE CUSTOMER TABLE
-- -------------------------------------------------------------------
-- NOTE: The table structure and data types must be capable of storing
--       the test data already contained in the INSERT statements for
--       the 'customer' table at the end of this script.



insert into pizza values (1,'Barnsley Bellybuster', 2.00);
insert into pizza values (2,'Doncaster Double', 1.50);
insert into pizza values (3,'Texborough Meat Munch', 2.00);
insert into pizza values (4,'Pickering Plain One', 1.50);

insert into topping values (1,'Onion','Veg', 0.50);
insert into topping values (2,'Pepper','Veg', 0.50);
insert into topping values (3,'Mushrom','Veg', 0.50);
insert into topping values (4,'Beef','meat', 0.75);
insert into topping values (5,'Chicken','meat', 0.75);
insert into topping values (6,'Pineapple','fruit', 0.45);

insert into pizzatopping values (1, 1);
insert into pizzatopping values (1, 2);
insert into pizzatopping values (1, 3);
insert into pizzatopping values (1, 4);
insert into pizzatopping values (2, 4);
insert into pizzatopping values (2, 3);
insert into pizzatopping values (3, 4);
insert into pizzatopping values (3, 1);
insert into pizzatopping values (4, 6);

insert into pizzaorder values (1,3632,1,'02-JAN-2005',1);
insert into pizzaorder values (2,2726,3,'02-JAN-2005',2);
insert into pizzaorder values (3,1124,1,'05-JAN-2005',1);
insert into pizzaorder values (4,1837,3,'05-JAN-2005',2);
insert into pizzaorder values (5,2311,2,'11-JAN-2005',1);
insert into pizzaorder values (6,1232,2,'16-JAN-2005',1);
insert into pizzaorder values (7,1324,3,'16-JAN-2005',3);
insert into pizzaorder values (8,2321,4,'16-JAN-2005',1);
insert into pizzaorder values (9,2311,4,'19-JAN-2005',1);
insert into pizzaorder values (10,1124,2,'21-JAN-2005',2);

insert into customer values (8327, 'Elizabeth Lincoln', '34 Compton Gardens, High Heaton, Newcastle upon Tyne', '0191 4092874');
insert into customer values (3632, 'Francisco Chang', '158 Biddlestone Road, Heaton, Newcastle upon Tyne', '0191 2650393');
insert into customer values (1837, 'Helen Bennett', '20 Church Avenue, High Heaton, Newcastle upon Tyne', '07709 726 374');
insert into customer values (1124, 'Janine Labrune', '68 New Ash Green, Jesmond, Newcastle upon Tyne', '0191 2081234');
insert into customer values (2827, 'Karin Josephs', '22 Warton Terrace, Heaton, Newcastle upon Tyne', '0191 2782927');
insert into customer values (2726, 'Karla Jablonski', '4 Bythesea Close, Whitley Bay, Newcastle upon Tyne', '0191 3097877');
insert into customer values (1232, 'Laurence Lebihan', '24 Ashby Road, High Heaton, Newcastle upon Tyne', '07709 773 937');
insert into customer values (2321, 'Lesley Brown', '25 Tenth Avenue, Heaton, Newcastle upon Tyne', '0191 2789229');
insert into customer values (1324, 'Maria Larsson', '32 Jesmond Dene, Jesmond, Newcastle upon Tyne', '0191 2701063');
insert into customer values (2311, 'Matti Karttunen', '27 Seventh Av., Heaton, Newcastle upon Tyne', '0191 2659284');

commit;
Votes + Comments
This has nothing to do with your earlier question.
1

This has nothing to do with your earlier question. It also looks like a homework assignment.

We help out if you're stuck on something. We won't do your homework for you.

Edited by BitBlt

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.