Question

Suppose you have two tables: Table Product:

PROD_ID	PROD_NAME	PROD_PRICE	PROD_PROD_DATE	PROD_VENDOR
1101	Table	100	1-Jan-18	2
1102	Chair	80	3-Mar-18	3
1103	Armchair	90	6-May-18	2
1104	Nightstand	110	4-Apr-18	1
1105	Bed	200	3-Mar-18	3
1106	Dresser	150	5-May-18	3
1107	Daybed	190	10-Feb-18	2

Table Vendor:

VEND_ID	VEND_NAME	VEND_ST
1	Green Way Inc	GA
2	Forrest LLC	NC
3	AmeriMart	NC

1. Post a question or provide an answer on the discussion board “Help with labs REQUIRED weekly posts”. Provide a screenshot showing your post.
2. Identify primary and foreign keys. Draw the relational schema to show the relationship between two tables using Crow Foot Notation https://www.codeproject.com/Articles/878359/Data-modelling-using-ERD-with-Crow-Foot-Notation You can use any software
3. Create tables. Do not forget about primary and foreign keys. Provide SQL code and screenshot of DESC statements.
4. Insert data. Provide SQL code and screenshot of select statements.
5. Suppose you wanted quick lookup capability to get a listing of all productssupplied by a given vendor. Which table would be the basis for the INDEX table, and what would be the index key? Create the index. Provide SQL code.

Answer 1

For Product Table

primary key:PROD-ID

foreign key:

For Vendor Table

primary key:VEND_ID

to create vendor table:

CREATE TABLE Vendor(VEND_ID number(3) primary key,VEND_NAME varchar(20),VEND_ST varchar(10));

desc Vendor;

CREATE TABLE Product(PROD_ID number(4) primary key,PROD_NAME varchar(20),PROD_PRICE number(4),PROD_PROD_DATE date,PROD_VENDOR references Vendor(VEND_ID));

desc Product;

insert into Vendor values(1,'Green Way Inc','GA');
insert into Vendor values(2,'Forrest LLC','NC');
insert into Vendor values(3,'AmeriMart','NC');

select * from Vendor;

insert into Product values(1101,'Table',100,'1-Jan-18',2);
insert into Product values(1102,'Chair',80,'3-Mar-18',3);
insert into Product values(1103,'armchair',90,'6-May-18',2);
insert into Product values(1104,'Nightstand',110,'4-Apr-18',1);
insert into Product values(1105,'Bed',200,'3-Mar-18',3);
insert into Product values(1106,'Dresser',150,'5-May-18',3);
insert into Product values(1107,'Daybed',190,'10-Feb-18',2);

select * from Product;

FOR Quicklook up question

ill use product table

TO CHECK THE PRODUCTS SUPPLYED BY VENDOR 2

WE WRITE

select PROD_VENDOR,PROD_ID,PROD_NAME from Product where PROD_VENDOR=2;

RELATIONAL SCHEMA