ass2 for databases
by Guest - 2012-04-16 23:31:20.0
--Student ID: 1747150 Student Name: Wu Chi To
--1B.
Prompt Creating Table BOOK;
CREATE TABLE BOOK
( bid NUMBER(4),
title VARCHAR2(30) Not Null,
sellingprice NUMBER(6,2),
Primary Key (bid),
CONSTRAINT chk_BOOK CHECK (sellingprice >0) );
Prompt Creating Table AUTHOR;
CREATE TABLE AUTHOR
( authid NUMBER(4),
fname VARCHAR2(30),
sname VARCHAR2(30),
Primary Key (authid),
CONSTRAINT uc_AUTHOR UNIQUE (sname,fname) );
Prompt Creating Table ALLOCATION;
CREATE TABLE ALLOCATION
( bid NUMBER(4),
authid NUMBER(4),
payrate NUMBER(6,2),
Primary Key(bid,authid),
Foreign Key(bid) references BOOK,
Foreign Key(authid) references AUTHOR,
CONSTRAINT chk_payrate UNIQUE (payrate>=1) and (payrate<=79.99) );
--1C.
Insert into BOOK(bid,title,sellingprice)
Values(101,"Knitting with Dog Hair",6.99);
Insert into BOOK(bid,title,sellingprice)
Values(105,"Avoiding Large Ships",11);
Insert into BOOK(bid,title,sellingprice)
Values(107,"Dealing with stuff",6.5);
Insert into BOOK(bid,title,sellingprice)
Values(108,"Teach fish to sing",10.99);
Insert into BOOK(bid,title,sellingprice)
Values(109,"Guide to hands free texting",10.5);
Insert into BOOK(bid,title,sellingprice)
Values(113,"You call that a lecture?",17.5);
Insert into AUTHOR(authid,sname,fname)
Values(40,"Ziggle","Carl");
Insert into AUTHOR(authid,sname,fname)
Values(42,"Taylor","Tayla");
Insert into AUTHOR(authid,sname,fname)
Values(44,"Merdovic","Damir");
Insert into AUTHOR(authid,sname,fname)
Values(45,"Grossman","Paul");
Insert into AUTHOR(authid,sname,fname)
Values(47,"Ziggle","Annie");
Insert into AUTHOR(authid,sname,fname)
Values(48,"Zhao","Cheng");
Insert into AUTHOR(authid,sname,fname)
Values(50,"Phan","Annie");
Insert into ALLOCATION(bid,authid,payrate)
Values(101,42,"$25");
Insert into ALLOCATION(bid,authid,payrate)
Values(101,45,"$32");
Insert into ALLOCATION(bid,authid,payrate)
Values(108,47,"$35");
Insert into ALLOCATION(bid,authid,payrate)
Values(113,48,"$40");
Insert into ALLOCATION(bid,authid,payrate)
Values(109,47,"$42");
Insert into ALLOCATION(bid,authid,payrate)
Values(105,42,"$26");
Insert into ALLOCATION(bid,authid,payrate)
Values(105,47,"$25");
Insert into ALLOCATION(bid,authid,payrate)
Values(105,40,"$19");
Insert into ALLOCATION(bid,authid,payrate)
Values(107,42,"$35");
Insert into ALLOCATION(bid,authid,payrate)
Values(108,40,"$45");
|