Question

DML: Illustrative DataBase Column DataType SizeDecimal Domain PK? FK? Positions PRODUCT LINE PCODE PDESCRIPT Character1-35 Character 8 P INDATE Date P ONHAND Numeric P MIN P PRICE P DISCOUNT Numeric V CODE Numeric601000000 VENDOR PRODUCT Valid date Numeric Numeric z 0 0.00 & s 1.00 V NAME Character 1-35 V CONTACT Character 1 2 V AREACODE Numeric V PHONE Numeric Character Character 100 & 5 999 e 1010000 Alphabetic Y. 'N V ORDER Questions Given the metadata above for columns of the Product & Vendor tables, write CREATE TABLE statements for these tables incl PK, FK & CHECK constraints 8/15/2018 WINNING BEGINS WITH AN ATTITUDE 13 Opening file in Protecte

Answer 1

Please find the Tables created in Sequence as mentioned:-

1. TABLE - CUSTOMER

CREATE TABLE CUSTOMER(

CUST_NUM NUMBER,

CUS_LNAME VARCHAR2(100),

CUS_FNAME VARCHAR2(100),

CUS_INITIAL CHAR(5),

CUS_AREACODE NUMBER

CUS_PHONE NUMBER(10),

CUS_BALANCE NUMBER(10, 2)

CONSTRAINT CUSTOMER_PK PRIMARY KEY(CUST_NUM));

2. Table INVOICE

CREATE TABLE INVOICE(

INV_NUMBER NUMBER,

CUS_NUM NUMBER,

INV_DATE DATE,

CONSTRAINT INVOICE_PK PRIMARY KEY(INV_NUMBER),

CONSTRAINT INVOICE_FK FOREIGN KEY(CUS_NUM) REFERENCES CUSTOMER(CUS_NUM));

3. Table - LINE

CREATE TABLE LINE(

INV_NUMBER NUMBER,

LINE_NUMBER NUMBER,

P_CODE NUMBER,

LINE_UNITS NUMBER,

LINE_PRICE NUMBER,

CONSTRAINT LINE_PK PRIMARY KEY (INV_NUMBER, LINE_NUMBER),

CONSTRAINT LINE_FK1 FOREIGN KEY (P_CODE) REFERENCES PRODUCT(P_CODE),

CONSTRAINT LINE_FK2 FOREIGN KEY (INV_NUMBER) REFERENCES INVOICE(INV_NUMBER));

4. Table - PRODUCT

CREATE TABLE PRODUCT(

P_CODE NUMBER,

P_DESCRIPTION VARCHAR2(100),

P_INDATE DATE,

P_ONHAND NUMBER,

P_MIN NUMBER,

P_PRICE NUMBER,

P_DISCOUNT NUMBER,

V_CODE NUMBER,

CONSTRAINT PRODUCT_PK PRIMARY KEY(P_CODE),

CONSTRAINT PRODUCT_FK FOREIGN KEY (V_CODE) REFERENCES VENDOR(V_CODE));

5. Table - VENDOR

CREATE TABLE VENDOR(

V_CODE NUMBER,

V_NAME VARCHAR2(100),

V_CONTACT VARCHAR2(20)

V_AREACODE NUMBER,

V_PHONE NUMBER(10),

V_STATE CHAR(2),

V_ORDER CHAR(1),

CONSTRAINT VENDOR_PK PRIMARY KEY (V_CODE));

Please let me know in case of any clarifications required. Thanks!