Q:

The text file named retail query and use it to create the retail table with the given values. The table has following functional dependencies:

ItemNbr (ItemDesc,Price,Cost)

StoreNbr (Location,Manager)

(VisitID, StoreNbr,ItemNbr) (quantity)

Hint: the column quantity is the number of items that were purchased on that visit, it does not currently exist in the table and must be created with a count

Write the SQL necessary to place the table into 4NF. Include commands to create primary keys and referential integrity constraints.

retail quer.txt

CREATE TABLE retail

(

VisitID Integer NOT NULL,

StoreNbr Integer NOT NULL,

ItemNbr Integer NOT NULL,

ItemDesc char(36),

Price Decimal(9,2) NOT NULL,

Cost Decimal(9,2) NOT NULL,

Location char(24),

Manager char(24)

);

INSERT INTO retail VALUES (12341,581,1413,'GreenBeans',1.74,1.69,'Fayetteville','George');

INSERT INTO retail VALUES (12341,581,1034,'SoftDrink',1.49,1.4,'Fayetteville','George');

INSERT INTO retail VALUES (12341,581,1802,'Bread',2.34,2.25,'Fayetteville','George');

INSERT INTO retail VALUES (12342,316,1802,'Bread',2.34,2.25,'Conway','James');

INSERT INTO retail VALUES (12342,316,1086,'Butter',2.79,2.57,'Conway','James');

INSERT INTO retail VALUES (12343,270,1802,'Bread',2.34,2.25,'Rogers','Andrew');

INSERT INTO retail VALUES (12344,270,1413,'GreenBeans',1.74,1.69,'Rogers','Andrew');

INSERT INTO retail VALUES (12344,270,1034,'SoftDrink',1.49,1.4,'Rogers','Andrew');

INSERT INTO retail VALUES (12344,270,1913,'OrangeJuice',2.97,2.82,'Rogers','Andrew');

INSERT INTO retail VALUES (12344,270,1913,'OrangeJuice',2.97,2.82,'Rogers','Andrew');

INSERT INTO retail VALUES (12345,581,1302,'Rice',2.34,2.27,'Fayetteville','George');

INSERT INTO retail VALUES (12345,581,1413,'GreenBeans',1.74,1.69,'Fayetteville','George');

INSERT INTO retail VALUES (12346,316,1302,'Rice',2.34,2.27,'Conway','James');

INSERT INTO retail VALUES (12346,316,1164,'Apples',5.78,5.49,'Conway','James');

INSERT INTO retail VALUES (12346,316,1086,'Butter',2.79,2.57,'Conway','James');

INSERT INTO retail VALUES (12346,316,1802,'Bread',2.34,2.25,'Conway','James');

INSERT INTO retail VALUES (12347,270,1034,'SoftDrink',1.49,1.4,'Rogers','Andrew');

INSERT INTO retail VALUES (12348,316,1414,'Milk',5.23,5.02,'Conway','James');

INSERT INTO retail VALUES (12349,316,1164,'Apples',5.78,5.49,'Conway','James');

INSERT INTO retail VALUES (12349,316,1413,'GreenBeans',1.74,1.69,'Conway','James');

INSERT INTO retail VALUES (12349,316,1164,'Apples',5.78,5.49,'Conway','James');

INSERT INTO retail VALUES (12349,316,1164,'Apples',5.78,5.49,'Conway','James');

INSERT INTO retail VALUES (12349,316,1164,'Apples',5.78,5.49,'Conway','James');

INSERT INTO retail VALUES (12349,316,1802,'Bread',2.34,2.25,'Conway','James');

INSERT INTO retail VALUES (12350,581,1769,'PotatoChips',3.24,3.08,'Fayetteville','George');

INSERT INTO retail VALUES (12350,581,1913,'OrangeJuice',2.97,2.82,'Fayetteville','George');

INSERT INTO retail VALUES (12350,581,1223,'ChocolateChipCookies',3.98,3.7,'Fayetteville','George');

INSERT INTO retail VALUES (12350,581,1223,'ChocolateChipCookies',3.98,3.7,'Fayetteville','George');

INSERT INTO retail VALUES (12350,581,1164,'Apples',5.78,5.49,'Fayetteville','George');