drop table computer;
drop table employee;
drop table software;
drop table pc;
drop table package;

create table computer
(compid	char(4)	not null,
mfgname	char(10),
mfgmodel	char(5),
proctype	char(6),
CONSTRAINT ComputerPK PRIMARY KEY(COMPID));


create table employee
(empnum	integer	not null,
empname	char(30),
empphone	integer,
CONSTRAINT EmployeePK PRIMARY KEY(EMPNUM));


CREATE TABLE PACKAGE
(PACKID		CHAR(4)	NOT NULL,
 PACKNAME		VARCHAR(20) DEFAULT 'SW Package',
 PACKVER		DECIMAL(3,2),
 PACKTYPE		CHAR(30),
 PACKCOST		DECIMAL(5,2) CHECK (PACKCOST > 0),
 CONSTRAINT PackagePK PRIMARY KEY(PACKID));

create table pc
(tagnum	integer	not null,
location	char(30),
compid	char(4),
empnum	integer,
CONSTRAINT PCPK PRIMARY KEY(TAGNUM));

create table software
(packid	char(4)	not null,
tagnum	integer,
instdate	date,
softcost	decimal(5,2),
CONSTRAINT SWPK PRIMARY KEY(PACKID, TAGNUM),
CONSTRAINT PackageFK FOREIGN KEY (PACKID) REFERENCES PACKAGE(PACKID),
CONSTRAINT PCFK FOREIGN KEY(TAGNUM) REFERENCES PC(TAGNUM) ON DELETE CASCADE);

INSERT INTO COMPUTER VALUES ('B121', 'BANTAM', '48X', '486DX');
INSERT INTO COMPUTER VALUES ('B221', 'BANTAM', '48D', '486DX2');
INSERT INTO COMPUTER VALUES ('C007', 'CODY', 'D1', '486DX');
INSERT INTO COMPUTER VALUES ('M759', 'LEMMIN', 'GRL', '486SX');

INSERT INTO EMPLOYEE VALUES (124, 'ALVARES, RAMON', 1212);
INSERT INTO EMPLOYEE VALUES (567, 'FEINSTEIN, BETTY', 8716);
INSERT INTO EMPLOYEE VALUES (611, 'DINH, MELISSA', 2963);
INSERT INTO EMPLOYEE VALUES (711, 'JOHNS, MELISSA', 2963);
INSERT INTO EMPLOYEE VALUES (811, 'JOHNST, MELISSA', 2963);
INSERT INTO EMPLOYEE VALUES (911, 'JOHNSTON, MELISSA', 2963);
INSERT INTO EMPLOYEE VALUES (710, 'SMTH, MELISSA', 2963);
INSERT INTO EMPLOYEE VALUES (810, 'SMYTH, MELISSA', 2963);
INSERT INTO EMPLOYEE VALUES (910, 'SMITHSON, MELISSA', 2963);
INSERT INTO EMPLOYEE VALUES (912, 'SMOTHY, MELISSA', 2963);
INSERT INTO EMPLOYEE VALUES (913, 'SMITHSON, MELISSA', 2963);
INSERT INTO EMPLOYEE VALUES (914, 'Smithly, MELISSA', 2963);

INSERT INTO PACKAGE VALUES ('AC01', 'BOISE ACCOUNTING', 3.00, 'ACCOUNTING', 725.83);
INSERT INTO PACKAGE VALUES ('DB32', 'MANTA', 1.50, 'DATABASE', 380.00);
INSERT INTO PACKAGE VALUES ('DB33', 'MANTA', 2.10, 'DATABASE', 430.18);
INSERT INTO PACKAGE VALUES ('SS11', 'LIMITLESS VIEW', 5.30, 'SPREADSHEET', 217.95);
INSERT INTO PACKAGE VALUES ('WP08', 'WORDS AND MORE', 2.00, 'WORDPROCESSING', 185.00);
INSERT INTO PACKAGE VALUES ('WP09', 'FREEWARE', 4.27, 'WORDPROCESSING', 30.00);
INSERT INTO PACKAGE VALUES ('AC06', 'BOISE ACCOUNTING', 3.00, 'ACCOUNTING', 100.00);
INSERT INTO PACKAGE VALUES ('AC07', 'BOISE ACCOUNTING 2', 3.00, 'ACCOUNTING', 200.00);

INSERT INTO PC VALUES (32808, 'ACCOUNTING', 'M759', 611);
INSERT INTO PC VALUES (37691, 'SALES', 'B121', 124);
INSERT INTO PC VALUES (57772, 'INFOSYSTEMS', 'C007', 567);
INSERT INTO PC VALUES (59836, 'HOME', 'B221', 124);
INSERT INTO PC VALUES (77740, 'HOME', 'M759', 567);

INSERT INTO SOFTWARE VALUES ('AC01', 32808, '13-SEP-1995',754.95);
INSERT INTO SOFTWARE VALUES ('DB32', 32808, '03-DEC-1995',380.00);
INSERT INTO SOFTWARE VALUES ('DB32', 37691, '15-JUN-1995',380.00);
INSERT INTO SOFTWARE VALUES ('DB33', 57772, '27-MAY-1995',412.77);
INSERT INTO SOFTWARE VALUES ('WP08', 32808, '12-JAN-1996',185.00);
INSERT INTO SOFTWARE VALUES ('WP08', 37691, '15-JUN-1995',227.50);
INSERT INTO SOFTWARE VALUES ('WP08', 57772, '27-MAY-1995',170.24);
INSERT INTO SOFTWARE VALUES ('WP09', 59836, '30-OCT-1995',35.00);
INSERT INTO SOFTWARE VALUES ('WP09', 77740, '27-MAY-1995',35.00);