DROP TABLE COMPOSITE_CUSTOMER;
DROP TABLE COMPOSITE_DISCOUNT;

create table "COMPOSITE_DISCOUNT"
(
	"CREDIT_RATING" CHAR(1) not null,
        "PARTNER_STATUS" CHAR(1) not null,
	"RATE" DECIMAL(4) default NULL,
        CONSTRAINT PK_COMPOSITE_DISCOUNT PRIMARY KEY (CREDIT_RATING, PARTNER_STATUS)
);

create table "COMPOSITE_CUSTOMER"
(
	"CUSTOMER_ID" INTEGER not null primary key,
	"CREDIT_RATING" CHAR(1) not null,
        "PARTNER_STATUS" CHAR(1) not null,
	"ZIP" VARCHAR(10) not null,
	"NAME" VARCHAR(30) default 'NULL',
	"ADDR_LN1" VARCHAR(30) default 'NULL',
	"ADDR_LN2" VARCHAR(30) default 'NULL',
	"CITY" VARCHAR(25) default 'NULL',
	"STATE" CHAR(2) default 'NULL',
	"PHONE" CHAR(12) default 'NULL',
	"FAX" CHAR(12) default 'NULL',
	"EMAIL" VARCHAR(40) default 'NULL',
	"CREDIT_LIMIT" INTEGER default NULL,
	"LAST_SALE_DATE" DATE default NULL,
	"LAST_SALE_TIME" TIME default NULL,
	CONSTRAINT FK_COMPOSITE_DISCOUNT FOREIGN KEY (CREDIT_RATING, PARTNER_STATUS) REFERENCES COMPOSITE_DISCOUNT(CREDIT_RATING, PARTNER_STATUS)
);


INSERT INTO COMPOSITE_DISCOUNT VALUES ('H', 'G', 25), ('H', 'S', 15), ('L', 'G', 5), ('L', 'S', 0);

INSERT INTO COMPOSITE_CUSTOMER VALUES (
1,	'H',	'G',    '33015',	'SuperCom',			'490 Rivera Drive',	'Suite 678',	'Miami',	'FL',	'305-777-4632',	'305-777-4635',	'supercom@aol.com',	100000,	'1998-01-02',	'08:00:00');
INSERT INTO COMPOSITE_CUSTOMER VALUES (
2,	'H',	'S',    '33055',	'Livingston Enterprises',	'9754 Main Street',	'P.O. Box 567',	'Miami',	'FL',	'305-456-8888',	'305-456-8889',	'www.tsoftt.com',	50000,		'1998-01-02',	'09:00:00');
INSERT INTO COMPOSITE_CUSTOMER VALUES (
25,	'L',	'S',    '75200',	'Oak Computers',		'8989 Qume Drive',	'Suite 9897',	'Dallas',	'TX',	'214-999-1234',	'214-999-5432',	'www.oakc.com',		25000,		'1998-01-06',	'10:15:00');
INSERT INTO COMPOSITE_CUSTOMER VALUES (
3,	'L',	'S',	'12347',	'MicroApple',			'8585 Murray Drive',	'P.O. Box 456',	'Alanta',	'GA',	'555-275-9900',	'555-275-9911',	'www.Microapple.net',	90000,		'1998-01-03',	'11:01:06');
INSERT INTO COMPOSITE_CUSTOMER VALUES (
36,	'H',	'S',	'94401',	'HostProCom',			'65653 El Camino',	'Suite 2323',	'San Mateo',	'CA',	'650-456-8876',	'650-456-1120',	'www.hostprocom.net',	65000,		'1998-01-03',	'11:00:00');
INSERT INTO COMPOSITE_CUSTOMER VALUES (
106,	'L',	'G',	'95035',	'CentralComp',			'829 Flex Drive',	'Suite 853',	'San Jose',	'CA',	'408-987-1256',	'408-987-1277',	'www.centralcomp.com',	26500,		'1998-01-04',	'14:00:00');