B. Below is the relational model for warehouse inventory system for a local reta
ID: 3723226 • Letter: B
Question
B. Below is the relational model for warehouse inventory system for a local retailer. c) Based on the relational model, create the database metadata as part the physical design phase. From the metadata, write the SQL create database and table statements (DDL) for all entities (tables). f) g) Implement the tables in your MySQL DB. h) Export your database to a sql file and submit as part of your lab submission. NVENTORY UPC Warehouso Name Quantiy PRODUCT VP Name Buying Price Seling Price SizeManvcvoc.Noroe MANUFACTURER Manufacturer Name MStreet MCity MState MZip MPhone WAREHOUSE Warohouse Name wLocation WCity WState WZip WSpace 5 pts)
Explanation / Answer
metadata:
matadata represents the names of the tables and column of the database.
it is used to identify the data type and range(Domain) for every attribute of the database tables.
INVENTORY
ATTRIBUTE NAME DATA TYPE CONSTRAINT
UPC INT FOREIGN KEY
WAREHOUSE_NAME VARCHAR FOREIGN KEY
QUANTITY INT
PRODUCT
ATTRIBUTE NAME DATA TYPE CONSTRAINT
UPC INT PRIMARY KEY
NAME VARCHAR
BUYING_PRICE INT
SELLING_PRICE INT
SIZE VARCHAR
MANUFACTURER_NAME VARCHAR FOREIGNKEY(REFERENCES
MANUFACTURER(MANUFACTURER_NAME)
MANUFACTURER
ATTRIBUTE NAME DATA TYPE CONSTRAINT
MANUFACTURER_NAME VARCHAR PRIMARY KEY
MSTREET VARCHAR
MCITY VARCHAR
MSTATE VARCHAR
MZIP VARCHAR
MPHONE INT
WAREHOUSE
ATTRIBUTE NAME DATA TYPE CONSTRAINT
WAREHOUSE_NAME VARCHAR PRIMARY KEY
WLOCATION VARCHAR
WCITY VARCHAR
WSTATE VARCHAR
WZIP VARCHAR
WSPACE INT
TABLE CREATION
WAREHOUSE
CREATE TABLE warehouse( warehouse_name varchar(225),
wlocation varchar(225),
wcity varchar(225),
wstate varchar(30),
wzip varchar(10),
wspace int,
PRIMARY KEY(warehouse_name) );
MANUFACTURER
CREATE TABLE manufacturer( manufacturer_name varchar(225),
mstreet varchar(225),
mcity varchar(225),
mstate varchar(225),
mzip varchar(10),
mphone int,
PRIMARY KEY(manufacturer_name));
PRODUCT
CREATE TABLE product( upc int NOT NULL,
name varchar(225),
buying_price int,
selling_price int,
size varchar(30),
manufacturer_name varchar(225),
PRIMARY KEY(upc),
FOREIGN KEY(manufacturer_name) REFERENCES manufacturer(manufacturer_name)
);
INVENTORY
CREATE TABLE inventory(upc int,
warehouse_name varchar(225),
quantity int,
FOREIGN KEY(upc) REFERENCES product(upc),
FOREIGN KEY(warehouse_name) REFERENCES warehouse(warehouse_name));
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.