CREATE TABLE sales ( SalesNumber INT(10) AUTO_INCREMENT PRIMARY KEY, SalesDate DATE, SalesTotal DECIMAL(10,2) ); CREATE TABLE products ( number INT(11) AUTO_INCREMENT PRIMARY KEY, prodid VARCHAR(20) UNIQUE, prodname VARCHAR(30), price DECIMAL(10,2), onhand INT(11), CONSTRAINT fk_salesdetails_products FOREIGN KEY (prodid) REFERENCES salesdetails(prodid) ON DELETE RESTRICT ); CREATE TABLE salesdetails ( number INT(10) AUTO_INCREMENT PRIMARY KEY, SalesNumber INT(10), prodid VARCHAR(20), price DECIMAL(7,2), qty INT(10), CONSTRAINT fk_salesdetails_sales FOREIGN KEY (SalesNumber) REFERENCES sales(SalesNumber) ON DELETE CASCADE, CONSTRAINT fk_salesdetails_products FOREIGN KEY (prodid) REFERENCES products(prodid) ON DELETE CASCADE ); Using the above SQL and the schema diagram, create an ER diagram detailing all the fields of the tables and the relationship amount the tables.
CREATE TABLE sales (
SalesNumber INT(10) AUTO_INCREMENT PRIMARY KEY,
SalesDate DATE,
SalesTotal DECIMAL(10,2)
);
CREATE TABLE products (
number INT(11) AUTO_INCREMENT PRIMARY KEY,
prodid VARCHAR(20) UNIQUE,
prodname VARCHAR(30),
price DECIMAL(10,2),
onhand INT(11),
CONSTRAINT fk_salesdetails_products
FOREIGN KEY (prodid)
REFERENCES salesdetails(prodid)
ON DELETE RESTRICT
);
CREATE TABLE salesdetails (
number INT(10) AUTO_INCREMENT PRIMARY KEY,
SalesNumber INT(10),
prodid VARCHAR(20),
price DECIMAL(7,2),
qty INT(10),
CONSTRAINT fk_salesdetails_sales
FOREIGN KEY (SalesNumber)
REFERENCES sales(SalesNumber)
ON DELETE CASCADE,
CONSTRAINT fk_salesdetails_products
FOREIGN KEY (prodid)
REFERENCES products(prodid)
ON DELETE CASCADE
);
Using the above SQL and the schema diagram, create an ER diagram detailing all the fields of the tables and the relationship amount the tables.
Step by step
Solved in 3 steps
