I need some help figure out the primary and foreign keys for this question. Belo

Question

I need some help figure out the primary and foreign keys for this question. Below is the code that I have so far, I just need help getting the right code for the foreign key since I am confused about which ones are which. Is to be done in SQL not Oracle.

create database MakeUpTest --Question 1
GO

create table Invoice
(
INV_NUMBER char(4) primary key,
CUST_NUMBER char(5),
INV_DATE date,
INV_SUBTOTAL money,
INV_TAX money,
INV_TOTAL money,
INV_PAY_TYPE char(2),
INV_PAY_AMOUNT money,
INV_BALANCE char (2)
);

create table Customer
(
CUST_NUMBER char(5) primary key,
CUST_LNAME varchar(25),
CUST_FNAME varchar(25),
CUST_INITIAL varchar(2),
CUST_AREACODE char(3),
CUST_PHONE CHAR(7),
CUST_BALANCE CHAR(2)
);

CREATE TABLE ACCT_TRANSACTION
(
ACCT_TRANS_NUM CHAR(7) PRIMARY KEY,
ACCT_TRANS_DATE DATE,
CUST_NUMBER CHAR (5) FOREIGN KEY REFERENCES CUSTOMER(CUST_NUMBER),
ACCT_TRANS_TYPE VARCHAR(25),
ACCT_TRANS_AMOUNT MONEY,
);

create table LINE
(
INV_NUMBER char(4) primary key,
LINE_NUMBER char(4) ,
PROD_CODE CHAR (7) ,
LINE_UNITS CHAR (1),
LINE_PRICE MONEY,
LINE_AMOUNT MONEY,
);

create table product
(
PROD_CODE CHAR(7) PRIMARY KEY,
PROD_DESCRIPT VARCHAR (25),
PROD_INDATE DATE,
PROD_QOH CHAR(4),
PROD_MIN CHAR(4),
PROD_PRICE MONEY,
PROD_DISCOUNT MONEY,
VEND_NUMBER CHAR(5) ,
);

CREATE TABLE VENDOR
(
VEND_NUMBER CHAR(5) PRIMARY KEY,
VEND_NAME VARCHAR(20),
VEND_CONTACT VARCHAR(20),
VEND_AREACODE CHAR(3),
VEND_PHONE CHAR(7),
VEND_STATE VARCHAR(2),
VEND_ORDER VARCHAR(1)
);

2. Create table as shown in the diagram. Please make sure you specify the primary and foreign key in the tables. [6+2] FIGURE 10.1 THE CH10 SALECO DATABASE RELATIONAL DIAGRAM LINE INVOICE INV NUMBER INV NUMBER UNE NUMBER CUST NUMBER INV DATE PROD CODE INV SUBTOTAL LINE UNITS INV TAX LINE PRICE PRODUCT INV TOTAL LINE AMOUNT PROD CODE CUSTOMER INV PAY TYPE PROD DESCRIPT CUST NUMBER INV PAY AMOUNT PROD INDATE CUST LNAME INV BALANCE PROD.QOH CUST FNAME PROD MIN CUSTLINITIAL PROD PRICE CUST AREACODE co PROD DISCOUNT CUST PHONE VENDOR VEND NUMBER v VEND NUMBER L ACCT TRANSACTION CUST BALANCE ACCT TRANS NUM VEND-NAME ACCT TRANS DATE VEND CONTACT CUST NUMBER VEND AREACODE ACCT TRANSTYPE VEND PHONE ACCT TRANS AMOUNT VEND STATE VEND ORDER

Explanation / Answer

I have made some changes regarding foreign keys and primar keys with the help of relational diagram you provided.

////////////////////////////////////////////////////////////////////////////////////////////////CODE/////////////////////////////////////////////////////////////////////////////////////////

GO

create table Invoice
(
INV_NUMBER char(4) NOT NULL,
CUST_NUMBER char(5),
INV_DATE date,
INV_SUBTOTAL money,
INV_TAX money,
INV_TOTAL money,
INV_PAY_TYPE char(2),
INV_PAY_AMOUNT money,
INV_BALANCE char (2),
PRIMARY KEY(INV_NUMBER),
FOREING KEY(CUST_NUMBER) REFERENCES Customer(CUST_NUMBER)
);

create table Customer
(
CUST_NUMBER char(5) NOT NULL,
CUST_LNAME varchar(25),
CUST_FNAME varchar(25),
CUST_INITIAL varchar(2),
CUST_AREACODE char(3),
CUST_PHONE CHAR(7),
CUST_BALANCE CHAR(2),
PRIMARY KEY(CUST_NUMBER)
);

CREATE TABLE ACCT_TRANSACTION
(
ACCT_TRANS_NUM CHAR(7) PRIMARY KEY,
ACCT_TRANS_DATE DATE,
CUST_NUMBER CHAR (5),
ACCT_TRANS_TYPE VARCHAR(25),
ACCT_TRANS_AMOUNT MONEY,
FOREIGN KEY(CUST_NUMBER) REFERENCES Customer(CUST_NUMBER)
);

create table LINE
(
INV_NUMBER char(4) ,
LINE_NUMBER char(4)   ,
PROD_CODE CHAR (7) ,
LINE_UNITS CHAR (1),
LINE_PRICE MONEY,
LINE_AMOUNT MONEY,
FOREIGN KEY(INV_NUMBER) REFERENCES Invoice(INV_NUMBER) ,
FOREIGN KEY(PROD_CODE) REFERENCES product(PROD_CODE)

);

create table product
(
PROD_CODE CHAR(7) PRIMARY KEY,
PROD_DESCRIPT VARCHAR (25),
PROD_INDATE DATE,
PROD_QOH CHAR(4),
PROD_MIN CHAR(4),
PROD_PRICE MONEY,
PROD_DISCOUNT MONEY,
VEND_NUMBER CHAR(5) ,
FOREIGN KEY(VEND_NUMBER) REFERENCES VENDOR(VEND_NUMBER)
);

CREATE TABLE VENDOR
(
VEND_NUMBER CHAR(5) PRIMARY KEY,
VEND_NAME VARCHAR(20),
VEND_CONTACT VARCHAR(20),
VEND_AREACODE CHAR(3),
VEND_PHONE CHAR(7),
VEND_STATE VARCHAR(2),
VEND_ORDER VARCHAR(1)
);

/////////////////////////////////////////////////////////////////////////////////////////END////////////////////////////////////////////////////////////////////////////////////////////////////

Feedback is appreciated, let me know this helped you or not..

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---