drop table BasicPart
/
/*
The BasicPart table contains the parts that have no subparts.
Part is a surrogate value for a part.  Think of it as a part number.
PName is the part name.
Cost is how much the part costs when it is purchased from a supplier.
Sname is the supplier name.
*/
create table BasicPart(
	Part	char(4),
	PName	char(20),
	Cost	number(2),
	SName	char(15))
/
delete from BasicPart
/
insert into BasicPart values ('s201','seat-pad',5,'chairsRus')
/
insert into BasicPart values ('s202','back-pad',4 ,'chairsRus')
/
insert into BasicPart values ('s206','base-upper-frame',7,'chairsRus')
/
insert into BasicPart values ('s207','arm-rest-pad',1,'chairsRus')
/
insert into BasicPart values ('s210','center-connector',12,'chairsRus')
/
insert into BasicPart values ('s211','left-connector',4,'chairsRus')
/
insert into BasicPart values ('s212','right-connector',4,'chairsRus')
/
insert into BasicPart values ('s213','spring',5,'chairsRus')
/
insert into BasicPart values ('s214','adjuster',7,'chairsRus')
/
insert into BasicPart values ('s215','swivel-screw',5,'chairsRus')
/
insert into BasicPart values ('s216','swivel-nut',3,'chairsRus')
/
insert into BasicPart values ('s218','base-support',12,'chairsRus')
/
insert into BasicPart values ('s220','castor-support',3,'chairsRus')
/
insert into BasicPart values ('s221','castor-wheel',1,'chairsRus')
/
insert into BasicPart values ('c301','seat-pad',5,'chairsRus')
/
insert into BasicPart values ('c302','back-pad',4,'chairsRus')
/
insert into BasicPart values ('c303','frame',10,'chairsRus')
/
insert into BasicPart values ('d401','frame',30,'officeSpply')
/
insert into BasicPart values ('d403','regular-drawer',5,'officeSpply')
/
insert into BasicPart values ('d404','file-drawer',9,'officeSpply')
/
insert into BasicPart values ('d405','draw-table',3,'officeSpply')
/
insert into BasicPart values ('d406','drawer-lock',1,'officeSpply')
/
insert into BasicPart values ('d407','drawer-frame',6,'officeSpply')
/
insert into BasicPart values ('f503','frame',20,'officeSpply')
/
insert into BasicPart values ('f504','cabinet-lock',2,'officeSpply')
/
insert into BasicPart values ('f505','drawer-frame',10,'officeSpply')
/
insert into BasicPart values ('f506','drawer-handle',1,'officeSpply')
/
insert into BasicPart values ('f507','open-lever',1,'officeSpply')
/
drop table AssembledPart
/
/*
The AssembledPart table tells which parts are assembled basic parts and from
other assembled parts.
Part is a surrogate value for a part.
PName is the part name.
AssemblyCost is the labor cost of assembling a part.
*/
create table AssembledPart(
	Part		char(4),
	PName		char(20),
	AssemblyCost	number(2))
/
delete from AssembledPart
/
insert into AssembledPart values ('s200','swivel-chair',7)
/
insert into AssembledPart values ('s203','swivel-frame',2)
/
insert into AssembledPart values ('s204','upper-frame',2)
/
insert into AssembledPart values ('s205','lower-frame',2)
/
insert into AssembledPart values ('s208','rocker',15)
/
insert into AssembledPart values ('s209','swivel',5)
/
insert into AssembledPart values ('s217','swivel-base',4)
/
insert into AssembledPart values ('s219','castor',1)
/
insert into AssembledPart values ('c300','basic-chair',7)
/
insert into AssembledPart values ('d400','desk', 8)
/
insert into AssembledPart values ('d402','center-drawer',2)
/
insert into AssembledPart values ('f500','file-cabinet',4)
/
insert into AssembledPart values ('f501','cabinet-frame',2)
/
insert into AssembledPart values ('f502','file-drawer', 4)
/
drop table Subpart
/
/*
The Subpart table tells which parts are subparts of other parts.
Part is a surrogate value for a part.  In this table, Part means subpart.
SuperPart is a surrogate value for an assembled part containing the subpart.
Qty tells how many of the subpart is needed for assembling the superpart.
*/
create table Subpart(
	Part		char(4),
	SuperPart	char(4),
	Qty		number(1))
/
delete from Subpart
/
insert into Subpart values ('s201',  's200',      1)
/
insert into Subpart values ('s202',  's200',       1)
/
insert into Subpart values ('s203',  's200',       1)
/
insert into Subpart values ('s204',  's203',       1)
/
insert into Subpart values ('s205',  's203',       1)
/
insert into Subpart values ('s206',  's204',       1)
/
insert into Subpart values ('s207',  's204',      2)
/
insert into Subpart values ('s208',  's205',       1)
/
insert into Subpart values ('s209',  's205',      1)
/
insert into Subpart values ('s210',  's208',       1)
/
insert into Subpart values ('s211',  's208',       1)
/
insert into Subpart values ('s212',  's208',       1)
/
insert into Subpart values ('s213',  's208',       2)
/
insert into Subpart values ('s214',  's208',      1)
/
insert into Subpart values ('s215',  's209',       1)
/
insert into Subpart values ('s216',  's209',       1)
/
insert into Subpart values ('s217',  's209',       1)
/
insert into Subpart values ('s218',  's217',       1)
/
insert into Subpart values ('s219',  's217',      4)
/
insert into Subpart values ('s220',  's219',       1)
/
insert into Subpart values ('s221',  's219',       2)
/
insert into Subpart values ('c301',  'c300',       1)
/
insert into Subpart values ('c302',  'c300',       1)
/
insert into Subpart values ('c303',  'c300',       1)
/
insert into Subpart values ('d401',  'd400',       1)
/
insert into Subpart values ('d402',  'd400',       1)
/
insert into Subpart values ('d403',  'd400',       4)
/
insert into Subpart values ('d404',  'd400',       1)
/
insert into Subpart values ('d405',  'd400',       2)
/
insert into Subpart values ('d406',  'd402',       1)
/
insert into Subpart values ('d407',  'd402',       1)
/
insert into Subpart values ('f501',  'f500',       1)
/
insert into Subpart values ('f502',  'f500',       4)
/
insert into Subpart values ('f503',  'f501',       1)
/
insert into Subpart values ('f504',  'f501',       1)
/
insert into Subpart values ('f505',  'f502',       1)
/
insert into Subpart values ('f506',  'f502',       1)
/
insert into Subpart values ('f507',  'f502',       1)
/