create table shr
(shrcode char(3) not null,
shrfirm varchar(20),
shrprice decimal(6,2),
shrqty decimal(8),
shrdiv decimal(5,2),
shrpe decimal(2),
constraint pk_shr primary key (shrcode));
create table alien
(alnum integer not null,
alname varchar(20),
alheads integer,
alcolor varchar(15),
alsmell varchar(15),
constraint pk_alien primary key (alnum));
create table nation
(natcode char(3) not null,
natname varchar(20),
exchrate decimal(9,5),
constraint pk_nation primary key (natcode));
create table stock
(stkcode char(3) not null,
stkfirm varchar(20),
stkprice decimal(6,2),
stkqty decimal(8),
stkdiv decimal(5,2),
stkpe decimal(5),
natcode char(3),
constraint pk_stock primary key (stkcode),
constraint fk_hasnation foreign key (natcode) references nation(natcode));
create table person
(personid integer not null,
persfname varchar(25),
perslname varchar(25),
constraint pk_person primary key (personid));
create table car
(carid integer not null,
carcost decimal(7,2),
carsell decimal(7,2),
cartype varchar(10),
personid integer,
constraint pk_car primary key (carid),
constraint fk_soldby foreign key (personid) references person(personid));
create table sale
(saleno integer not null,
saledate date,
saletext varchar(50),
constraint pk_sale primary key (saleno));
create table item
(itemno integer not null,
itemname varchar(30),
itemtype char(1),
itemcolor varchar(10),
constraint pk_item primary key (itemno));
create table lineitem
(lineno integer not null,
lineqty integer,
lineprice decimal(7,2),
saleno integer not null,
itemno integer not null,
constraint pk_lineitem primary key (lineno,saleno),
constraint fk_hassale foreign key (saleno) references sale (saleno),
constraint fk_hasitem foreign key (itemno) references item (itemno));
create table donor
(donorno integer not null,
dlname varchar(15),
dfname varchar(15),
dphone decimal(4),
dstate char(2),
dcity varchar(15),
constraint pk_donor primary key (donorno));
create table year
(year integer not null,
yeargoal decimal(9),
constraint pk_year primary key (year));
create table gift
(amount decimal(8) not null,
year integer not null,
donorno integer not null,
constraint pk_gift primary key (year,donorno),
constraint fk_donatedin foreign key (year) references year(year),
constraint fk_donatedby foreign key (donorno) references donor(donorno));
create table dept 
(deptname varchar(15) not null,
deptfloor smallint not null,
deptphone smallint not null,
empno smallint not null,
constraint pk_dept primary key(deptname));
create table emp
(empno integer not null,
empfname varchar(10),
empsalary decimal(7,0),
deptname varchar(15),
bossno integer,
constraint pk_emp primary key(empno),
constraint fk_belongdept foreign key (deptname) references dept(deptname));
create table monarch (
	montype				varchar(5)			not null,
	monname				varchar(15),
	monnum				varchar(5),
	rgnbeg				date,
	premonname				varchar(15),
	premonnum				varchar(5),
			primary key(monname,monnum));
create table product
(prodid integer not null,
proddesc varchar(30),
prodcost decimal(9,2),
prodprice decimal(9,2),
constraint pk_product primary key(prodid));
create table assembly
(quantity integer not null,
prodid integer not null,
subprodid integer not null,
constraint pk_assembly primary key(prodid, subprodid));
create table qitem
(itemname varchar(30) not null,
itemtype char(1),
itemcolor varchar(10),
constraint pk_qitem primary key (itemname));
create table qemp
(empno integer not null,
empfname varchar(15),
empsalary decimal(6),
deptname varchar(15),
bossno integer,
constraint pk_qemp primary key (empno));
create table qdept
(deptname varchar(15) not null,
deptfloor integer,
deptphone integer,
empno integer not null,
constraint pk_qdept primary key (deptname),
constraint fk_hasboss foreign key (empno) references qemp (empno));
create table qsale
(saleno integer not null,
saleqty integer,
itemname varchar(30) not null,
deptname varchar(15) not null,
constraint pk_saleno primary key (saleno),
constraint fk_isitem foreign key (itemname) references qitem (itemname),
constraint fk_bydept foreign key (deptname) references qdept (deptname));
create table qspl
(splno integer not null,
splname varchar(25),
constraint pk_qspl primary key (splno));
create table qdel
(delno integer not null,
delqty integer not null,
itemname varchar(30) not null,
deptname varchar(15) not null,
splno integer not null,
constraint pk_qdel primary key (delno),
constraint fk_delitem foreign key (itemname) references qitem (itemname),
constraint fk_hasdept foreign key (deptname) references qdept (deptname),
constraint fk_hasspl foreign key (splno) references qspl (splno));
create table exped
(transid	integer,
salesdate date,
location varchar(10),
item varchar(30),
channel varchar(10),
quantity integer,
revenue decimal(9,2));