TABLE-DDL

--DROP TABLE Shopping_Group.dbo.EMPLOYEE, Shopping_Group.dbo.GDOWNER;
CREATE TABLE EMPLOYEE (EID int PRIMARY KEY, NAME char(30), STTIME time, ENDTIME time, ETYPE char(10));
CREATE TABLE GDOWNER (OID int PRIMARY KEY);
CREATE TABLE SHOP (SHID int PRIMARY KEY, NAME char(20), ADDR char(50), SIZE int, TEL char(12), OID int NULL REFERENCES GDOWNER(OID));
CREATE TABLE WAREHOUSE (WID int PRIMARY KEY, ADDR char(50), TEL char(12));
CREATE TABLE CUSTOMER (CID int PRIMARY KEY, TEL char(12));
CREATE TABLE STOREKEEPING (WID int NOT NULL REFERENCES WAREHOUSE(WID), EID int NOT NULL REFERENCES EMPLOYEE(EID), BEGINDATE date, ENDDATE date,
			CONSTRAINT PK_STOREKEEPING_WID_EID PRIMARY KEY(WID, EID)
			);
CREATE TABLE SALERECEIPT (RECID int PRIMARY KEY, EID int NOT NULL REFERENCES EMPLOYEE(EID), CID int NOT NULL REFERENCES CUSTOMER(CID), SDATE date);
CREATE TABLE GOOD (GID int PRIMARY KEY, PRICE int, CNTINSERIES int, NAME char(20), WID int REFERENCES WAREHOUSE(WID), SHID int REFERENCES SHOP(SHID));
CREATE TABLE SHOPPINGLIST (RECID int NOT NULL REFERENCES SALERECEIPT(RECID), GID int NOT NULL REFERENCES GOOD(GID), CNT int, 
			CONSTRAINT PK_SHOPPINGLIST_RECID_GID PRIMARY KEY(RECID, GID)
			);
CREATE TABLE EQUIPMENT (EQID int NOT NULL, SHID int NOT NULL REFERENCES SHOP(SHID), CNTAVAIL int, CNTBROKEN int,
			CONSTRAINT PK_EQUIPMENT_EQID_SHID PRIMARY KEY(EQID, SHID));
CREATE TABLE OWNING (OID int NOT NULL REFERENCES GDOWNER(OID), GID int NOT NULL REFERENCES GOOD(GID), CNT int,
			CONSTRAINT PK_OWNING_OID_GID PRIMARY KEY(OID, GID));
CREATE TABLE SUPPLIER (SUPID int PRIMARY KEY, NAME char(50), TEL char(12));
CREATE TABLE ORDERRECEIPT (ORECID int PRIMARY KEY, SUBMISSIONDATE date, DELIVERYDATE date, ORDERCNT int, 
							SUID int NULL REFERENCES SUPPLIER(SUPID), WID int NOT NULL REFERENCES WAREHOUSE(WID));
CREATE TABLE ORDERLIST (ORECID int NOT NULL REFERENCES ORDERRECEIPT(ORECID), GID int NOT NULL REFERENCES GOOD(GID), ORDERCNT int,
			CONSTRAINT PK_ORDERLIST_ORECID_GID PRIMARY KEY(ORECID, GID));
CREATE TABLE CLOTH (GID int PRIMARY KEY REFERENCES GOOD(GID), COLOR char(10), CTYPE char(10));
CREATE TABLE FOOD (GID int PRIMARY KEY REFERENCES GOOD(GID), EXPDATE date, PRODDATE date);
CREATE TABLE APPLIANCE (GID int PRIMARY KEY REFERENCES GOOD(GID), ISELEC bit);
CREATE TABLE SUPGOOD (SUPID int NOT NULL REFERENCES SUPPLIER(SUPID), NAME char(50),
			CONSTRAINT PK_SUPGOOD_SUPID_NAME PRIMARY KEY(SUPID, NAME));
CREATE TABLE EMPLOYEES (EID int NOT NULL REFERENCES EMPLOYEE(EID), SHID int NOT NULL REFERENCES SHOP(SHID), BEGINDATE date, ENDDATE date);