as eate a database usin Consider a wholesale database with the following data: e

Question

as eate a database usin Consider a wholesale database with the following data: endor: phone_number vendor idnam (PK) category address (NotNull) email (Unique) (Not Null) (NotNull) (Unique) Walmart Supermarket 9191 W Flagler St service@walmart.com (786) 801-5704 (305) 894-2938 (305) 220-0147 (305) 594-6300 JCPenney Department 1603 NW 107th Ave service@macys.com 305) 477-1786 (305) 424-1140 Target Supermarket 10101 W Flagler St service@target.com VSPharmacy1549 SW 107th Ave contact@cvs.com 4 Macy's Department 1205 NW 107th Ave sales@macys.com Walgreens Pharmacy10700 W Flagler St contact@walgreens.com rand brand idname (PK) license contact phone (Unique) contact email contract_year (Not Null) (Not Null) Great Value Equate Radiance Alfani Worthington non-exclusive (955) 812-7462jake@worthington.com 2016 Botanics Gold Emblem non-exclusive (265) 213-7132 mindy@goldembl.com 2005 (786) 801-1234 george@ greatval.com 2008 2010 2010 2015 exclusive non-exclusive (360) 516-9897jace@equate.com non-exclusive (776) 636-9641 exclusive eric@radiance.com 4 (953) 474-8995sandy@alfani.com non-exclusive (305) 315-3700 cindy@botanics.com 2008

Explanation / Answer

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

CREATE TABLE Vendor(

vendor_id INT NOT NULL PRIMARY KEY,

name VARCHAR(20) NOT NULL,

category VARCHAR(20) NOT NULL,

address VARCHAR(30) NOT NULL,

email VARCHAR(50) UNIQUE NOT NULL,

phone_number CHAR(15) UNIQUE NOT NULL

);

CREATE TABLE Brand(

brand_id INT NOT NULL PRIMARY KEY,

name VARCHAR(20) NOT NULL,

license VARCHAR(20),

contact_phone CHAR(15) UNIQUE,

contact_email VARCHAR(30),

contract_year INT NOT NULL

);

CREATE TABLE Product(

product_id INT NULL PRIMARY KEY,

name VARCHAR(20) NOT NULL,

category VARCHAR(20) NOT NULL,

brand INT,

FOREIGN KEY(brand) REFERENCES Brand

);

CREATE TABLE Supply(

product_id INT,

vendor_id INT,

selling_price INT,

PRIMARY KEY(product_id, vendor_id),

FOREIGN KEY(product_id) REFERENCES Product,

FOREIGN KEY(vendor_id) REFERENCES Vendor

);

INSERT INTO Vendor VALUES(1, 'Walmart', 'supermarket', '9191 W Flagler St', 'service@walmart.com', '(786) 801-5704');

-- insert rest of rows like this...

-- Task2)

-- 1)

INSERT INTO Supply VALUES(2,1, 10),(8, 3, 4), (8, 6, 0), (7,6,15);

-- 2)

DELETE FROM Supply WHERE selling_price = 0;

-- 3)

ALTER TABLE Product ADD COLUMN production_cost INT;

-- 4)

UPDATE Supply SET selling_price = 20 WHERE vendor_id = 5 AND product_id = 4;

-- 5)

ALTER TABLE Product ALTER COLUMN brand INT REFERENCES Brand ON DELETE RESTRICT;

-- 6)

ALTER TABLE Supply ALTER COLUMN selling_price INT NOT NULL;

-- 7)

COPY persons TO 'supply.csv' DELIMITER ',' CSV HEADER;

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

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