Question

Create the following tables. The underlined bold column names are the primary keys. Make sure to specify the primary and foreign key constraints in your create table statements.

1. customer: (cus_code:integer, cus_lname:varchar(20), cus_fname:varchar(20), cus_initial:char, cus_areacode:integer,cus_phone:integer).

2. invoice: (inv_number:integer, cus_code:integer, inv_date:date,

                  foreign key cus_code references customer(cus_code))

3. vendor:(vend_code:integer,vend_name:varchar(30),vend_contact:varchar(30),vend_areacode:integer,vend_phone:integer)

4. product:(prod_code:integer, prod_desc:varchar(50), prod_price:integer, prod_quant:integer,vend_code:integer,

foreign key (vend_code) referenecs Vendor(vend_code))

5. line: (inv_number:integer, prod_code:integer ,line_units:integer,

foreign key (inv_number) references Invoice(inv_number),

foreign key (prod_code) references Product (prod_code) )

Answer 1

If you have any doubts, please give me comment...

CREATE TABLE customer(

    cus_code INTEGER NOT NULL,

    cus_lname VARCHAR(20),

    cus_fname VARCHAR(20),

    cus_initial CHAR(1),

    cus_areacode INTEGER,

    cus_phone INTEGER,

    PRIMARY KEY(cus_code)

);

CREATE TABLE invoice(

    inv_number INTEGER NOT NULL,

    cus_code INTEGER,

    inv_date DATE,

    PRIMARY KEY(inv_number),

    FOREIGN KEY(cus_code) REFERENCES customer(cus_code)

)

CREATE TABLE vendor(

    vend_code INTEGER NOT NULL,

    vend_name VARCHAR(30),

    vend_contact VARCHAR(30),

    vend_areacode INTEGER,

    vend_phone INTEGER,

    PRIMARY KEY(vend_code)

);

CREATE TABLE product(

    prod_code INTEGER NOT NULL,

    prod_desc VARCHAR(50),

    prod_price INTEGER,

    prod_quant INTEGER,

    vend_code INTEGER,

    PRIMARY KEY(prod_code),

    FOREIGN KEY(vend_code) REFERENCES Vendor(vend_code)

);

CREATE TABLE line(

    inv_number INTEGER,

    prod_code INTEGER,

    line_units INTEGER,

    PRIMARY KEY(inv_number, prod_code),

    FOREIGN KEY(inv_number) REFERENCES Invoice(inv_number),

    FOREIGN KEY(prod_code) REFERENCES Product (prod_code)

);