relational-model.sql

卡耐基梅陇大学网上教程ssd7 exercise7的答案。绝对正确 满分

create table Publisher(
Pub_num char(3) not null,
Pub_name varchar(100) not null,
Pub_Address varchar(250) not null,
PRIMARY KEY (Pub_num)
);


create table Categories(
Categ_num char(3) not null,
Categ_name varchar(50) not null,
PRIMARY KEY (Categ_num)
);


create table Categ_contain_Categ(
Large_Categ_num char(3) not null,
Small_Categ_num char(3) not null,
PRIMARY KEY (Large_Categ_num, Small_Categ_num),
FOREIGN KEY (Large_Categ_num) REFERENCES  Categories(Categ_num),
FOREIGN KEY (Small_Categ_num) REFERENCES  Categories(Categ_num)
);


create table BookTitle(
B_ISBN varchar(13) not null,
B_Title varchar(100) not null,
B_Author varchar(150) not null,
Pub_num char(3),
B_Edition char(3) not null,
B_DatePublished date,
B_Price float(6,2),
B_discont float(3,2) default 1,
B_Description varchar(200),
B_qutlnStore integer,
Categ_num char(3),
PRIMARY KEY (B_ISBN),
FOREIGN KEY (Categ_num) REFERENCES  Categories(Categ_num),
FOREIGN KEY (Pub_num) REFERENCES  Publisher(Pub_num)
);


create table Promotion(
B_ISBN varchar(13) not null,
P_DuDate date,
PRIMARY KEY (B_ISBN),
FOREIGN KEY (B_ISBN) REFERENCES  BookTitle(B_ISBN)
);


create table Customer(
C_num char(7) not null,
C_Email varchar(50) unique not null,
C_Fname varchar(20),
C_Mname varchar(20),
C_Lname varchar(15) not null,
C_addrCountry varchar(50) ,
C_addrState varchar(50) ,
C_addrCity varchar(50) ,
C_addAvg varchar(100) ,
C_zipCode char(7),
C_phNum varchar(12) ,
C_loginPaWo char(6) not null,
PRIMARY KEY (C_num)
);


create table CreditCard(
CredCar_Num varchar(16) not null,
CredCar_ExpDate date not null,
CredCar_type varchar(15) not null,
C_num char(7),
PRIMARY KEY (CredCar_Num),
FOREIGN KEY (C_num) REFERENCES  Customer(C_num)
);


create table Orders(
O_num char(9) not null,
C_num char(7) not null,
O_mailAddress varchar(250) not null,
credCar_num varchar(16) not null,
O_shipingdate date not null,
O_datePlaced date,
O_shipMeathod varchar(25) not null,
O_totalCost float(8,2) not null,
PRIMARY KEY (O_num),
FOREIGN KEY (C_num) REFERENCES  Customer(C_num),
FOREIGN KEY (CredCar_Num) REFERENCES  CreditCard(CredCar_Num)
);


create table Dielivery_agency(
DA_num char(5),
DA_name varchar(50),
DA_phone varchar(12),
PRIMARY KEY (DA_Num)
);

create table Shipment(
S_num char(9),
O_num char(9) not null,
S_date date not null,
DA_num char(5) not null,
PRIMARY KEY (S_num),
FOREIGN KEY (O_num) REFERENCES  Orders (O_num),
FOREIGN KEY (DA_Num) REFERENCES  Dielivery_agency (DA_Num)
);


create table OrderLine(
O_num char(9) not null,
B_ISBN varchar(13) not null,
OL_btQUT integer not null,
S_num char(9) default null,
PRIMARY KEY (O_num,B_ISBN),
FOREIGN KEY (O_num) REFERENCES  Orders (O_num),
FOREIGN KEY (B_ISBN) REFERENCES  BookTitle(B_ISBN),
FOREIGN KEY (S_num) REFERENCES  Shipment(S_num)
);