implement your database on MySQL Your implementation should

implement your database on MySQL. Your implementation should include the following: using the diagram below.

1- Create all the tables that you specified in your relational schema. Make sure to include primary keys and foreign keys.

2- Populate all tables with data of your choice. Make sure that each table includes at least 5 rows.

3- Design and explain at least five query scenarios that may be useful on your database. Write SQL queries to answer each query.

4- Design and explain two triggers that may be useful in maintaining your database consistency.Implement the triggers using SQL

Solution

The sql queries for creating all the tables are given below:

i) create table Store(Store_id int NOT NULL, Store_name varchar(255), City varchar(255), Street varchar(255), State varchar(255), Zip_code varchar(255), PRIMARY KEY (Store_id))

ii) create table Shoe(Shoe_id int NOT NULL, Style varchar(255), Shoe_name varchar(255), Shoe_color varchar(255), Size int, PRIMARY KEY (Shoe_id))

iii) create table Customer(Customer_id int NOT NULL, Gender varchar(255), City varchar(255), Street varchar(255), State varchar(255), Zip_code varchar(255), PRIMARY KEY (Customer_id))

iv) create table Order(Order_id int NOT NULL, Date varchar(255), PRIMARY KEY (Order_id), FOREIGN KEY (Customer_id) REFERENCES Customer(Customer_id))

v) create table Order_Detail(Order_id int NOT NULL, Order_count int NOT NULL, Price int, Shoe_id int NOT NULL, PRIMARY KEY (Order_id), FOREIGN KEY (Customer_id) REFERENCES Customer(Customer_id), FOREIGN KEY (Shoe_id) REFERENCES Shoe(Shoe_id), FOREIGN KEY (Store_id) REFERENCES Store(Store_id) )

For Data insertaion we can use the following syntax:

INSERT INTO table_name (col1,col2,col3,...) VALUES (val1,val2,val3,...);

Here in above syntax you can replace table_name, column name and values.

The five query scenarios that may be useful are:

i) Select * from table_name (This query can be used to select all the data of table)

ii) delete from table_name (This query is for deleting a record from table)

iii) insert into table_name (This query is to insert into table)

iv) UPDATE table_name SET col1=val1,col2=val2,... WHERE some_col=some_val; (This query to update the table)

v) Select query with \"Where\" Clause (This query is to apply any condition)