Answer all questions on paper: Consider the database model below: The database m

Question

Answer all questions on paper: Consider the database model below: The database model FIGURE 7.1 CUSTOMER NVOICE LINE PK CUS CODE containsPK,FK1 INV NUMBER LINE NUMBER + generates PKV NUMBERcotainsPKK1 NVE UNNM PK INV NUMB CUS LNAME CUS FNAME CUS INITIAL CUS AREACODE CUS PHONE CUS BALANCE FK1 CUS CODE INV DATE FK2 P_CODE LINE UNITS LINE PRICE is found in VENDOR PRODUCT PK V CODE PK P CODE P DESCRIPT P INDATE P QOH P MIN P PRICE P DISCOUNT V NAME TACT HE-Supplies-? V AREACODE V PHONE V STATE V ORDER Write the sQL code for creating the database and all the above tables. State all primary keys and foreign keys constraints. CUSTOMER: CUS CODE(Numeric), CUS-FNAME, CUS LNAME, CUS-INITIAL, AREACODE, CUS PHONE(strings), CUSR_BALANCE (numeric) VENDOR: V-CODE (integer), V_NAME, VCONTACT, V_AREACODE, V_PHONE, V_STATE, V ORDER(string) PRODUCT: P DESCRIPT (String), P_INDATE (date), P.Q0H, P MIN, P-PRICE, P.DISCOUNT(numeric), INVOICE: INV NUMBER (numeric), INV DATE (date) LINE: LINE NUMBER, LINE _UNITS, LINE PRICE (numeric), P_CODE(string),

Explanation / Answer

QUES 1) CREATE TABLE QUERIES

CUSTOMER Table

CREATE TABLE CUSTOMER(

CUS_CODE INT NOT NULL PRIMARY KEY,

CUS_LNAME VARCHAR(50),

CUS_FNAME VARCHAR(50),

CUS_INITIAL VARCHAR(20),

CUS_ARECODE VARCHAR(50),

CUS_PHONE VARCHAR(20),

CUS_BALANCE DECIMAL(15,4)

);

VENDOR Table

CREATE TABLE VENDOR(

V_CODE INT NOT NULL PRIMARY KEY,

V_NAME VARCHAR(30),

V_CONTACT VARCHAR(100),

V_AREACODE VARCHAR(20),

V_PHONE VARCHAR(20),

V_STATE VARCHAR(30),

V_ORDER VARCHAR(50)

);

PRODUCT Table

CREATE TABLE PRODUCT(

P_CODE VARCHAR(50) NOT NULL PRIMARY KEY,

P_DESCRIPT VARCHAR(255),

P_INDATE DATE,

P_QOH INT,

P_MIN INT,

P_PRICE DECIMAL(10,2),

P_DISCOUNT INT,

V_CODE INT FOREIGN KEY REFERENCES VENDOR(V_CODE)

);

INVOICE Table

CREATE TABLE INVOICE(

INV_NUMBER INT NOT NULL PRIMARY KEY,

CUS_CODE INT FOREIGN KEY REFERENCES CUSTOMER(CUS_CODE),

INV_DATE DATE

);

LINE Table

CREATE TABLE LINE(

INV_NUMBER INT NOT NULL PRIMARY KEY FOREIGN KEY REFERENCES INVOICE(INV_NUMBER),

LINE_NUMBER INT NOT NULL PRIMARY KEY,

P_CODE VARCHAR(50) FOREIGN KEY REFERENCE PRODUCT(P_CODE),

LINE_UNITS INT,

LINE_PRICE DECIMAL(10,4)

);

Ques 7:

SELECT P_DESCRIPT,P_INDATE,P_PRICE FROM PRODUCT

WHERE (P_INDATE > '2012-01-15' AND P_PRICE < 50) OR V_CODE=24288;

Ques 8:

SELECT * FROM PRODUCT WHERE NOT V_CODE=21344;

Ques 9:

SELECT * FROM PRODUCT WHERE P_PRICE BETWEEN 50 AND 100;

Ques 10:

SELECT * FROM PRODUCT WHERE V_CODE = NULL;

Ques 11:

SELECT P_DESCRIPT, P_PRICE FROM PRODUCT ORDER BY P_PRICE DESC;

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.