Assignment 5 (20 points)

1. Unzip the folder for Assignment5. Using MySql Workbench, create a schema/database. Name the database Relational_Database. Use the .sql scripts to create the tables and insert all data. There are tables that can be combined because they have the same attributes. Please go ahead and combine them as one table. Also, please rename the tables with meaningful class names. Look to see if there are relationships between the tables and show the relationships (you can use E-R diagrams or use simple word file with table names, columns, primary key and foreign key(follow naming conventions capital letters, underline etc)).

Insert at 5-10 new records in each table using both GUI and command line interface (just practicing and familiarizing yourself with MySql workbench and command line).

We will have other assignments with more sql queries. Having a few databases created and populated will allow us to practice our sql statements.

Please submit the screenshot of the tables, and the data in those tables.

CREATE TABLE AGENT ( AGENT_CODE varchar(50), AGENT_PHONE varchar(50) );

INSERT INTO AGENT VALUES('125','6152439887'); INSERT INTO AGENT VALUES('167','6153426778'); INSERT INTO AGENT VALUES('231','6152431124'); INSERT INTO AGENT VALUES('333','9041234445');

/* -- */

CREATE TABLE CUSTOMER ( CUS_CODE varchar(50), CUS_LNAME varchar(50), CUS_ZIP varchar(50), AGENT_CODE varchar(50) );

INSERT INTO CUSTOMER VALUES('1132445','Walker','32145','231'); INSERT INTO CUSTOMER VALUES('1217782','Adares','32145','125'); INSERT INTO CUSTOMER VALUES('1312243','Rakowski','34129','167'); INSERT INTO CUSTOMER VALUES('1321242','Rodriguez','37134','125'); INSERT INTO CUSTOMER VALUES('1542311','Smithson','37134','421'); INSERT INTO CUSTOMER VALUES('1657399','Vanloo','32145','231');

/* -- */

CREATE TABLE DIFF_TBL1 ( STU_FNAME varchar(50), STU_LNAME varchar(50) );

INSERT INTO DIFF_TBL1 VALUES('George', 'Jones'); INSERT INTO DIFF_TBL1 VALUES('Jane', 'Smith'); INSERT INTO DIFF_TBL1 VALUES('Peter', 'Robinson'); INSERT INTO DIFF_TBL1 VALUES('Franklin', 'Johnson'); INSERT INTO DIFF_TBL1 VALUES('Martin', 'Lopez');

/* -- */

CREATE TABLE DIFF_TBL2 ( EMP_FNAME varchar(50), EMP_LNAME varchar(50) );

INSERT INTO DIFF_TBL2 VALUES('Franklin', 'Lopez'); INSERT INTO DIFF_TBL2 VALUES('William', 'Turner'); INSERT INTO DIFF_TBL2 VALUES('Franklin', 'Johnson'); INSERT INTO DIFF_TBL2 VALUES('Susan', 'Rogers');

/* -- */

CREATE TABLE DIV_TBL1 ( P_CODE varchar(50), CUS_CODE varchar(50) );

INSERT INTO DIV_TBL1 VALUES('123456', '10400'); INSERT INTO DIV_TBL1 VALUES('123456', '11501'); INSERT INTO DIV_TBL1 VALUES('123456', '10030'); INSERT INTO DIV_TBL1 VALUES('123456', '12550'); INSERT INTO DIV_TBL1 VALUES('234567', '12350'); INSERT INTO DIV_TBL1 VALUES('234567', '10040'); INSERT INTO DIV_TBL1 VALUES('234567', '10500'); INSERT INTO DIV_TBL1 VALUES('234567', '10030'); INSERT INTO DIV_TBL1 VALUES('234567', '12550'); INSERT INTO DIV_TBL1 VALUES('345678', '10400'); INSERT INTO DIV_TBL1 VALUES('345678', '11630'); INSERT INTO DIV_TBL1 VALUES('345678', '12550'); INSERT INTO DIV_TBL1 VALUES('456789', '11630'); INSERT INTO DIV_TBL1 VALUES('567890', '10600'); INSERT INTO DIV_TBL1 VALUES('567890', '10030'); INSERT INTO DIV_TBL1 VALUES('567890', '12550'); INSERT INTO DIV_TBL1 VALUES('678901', '11500'); INSERT INTO DIV_TBL1 VALUES('678901', '10400'); INSERT INTO DIV_TBL1 VALUES('678901', '11630');

/* -- */

CREATE TABLE DIV_TBL2 ( P_CODE varchar(50) );

INSERT INTO DIV_TBL2 VALUES('123456'); INSERT INTO DIV_TBL2 VALUES('234567'); INSERT INTO DIV_TBL2 VALUES('567890');

/* -- */

CREATE TABLE INT_TBL1 ( STU_FNAME varchar(50), STU_LNAME varchar(50) );

INSERT INTO DIFF_TBL1 VALUES('George', 'Jones'); INSERT INTO DIFF_TBL1 VALUES('Jane', 'Smith'); INSERT INTO DIFF_TBL1 VALUES('Peter', 'Robinson'); INSERT INTO DIFF_TBL1 VALUES('Franklin', 'Johnson'); INSERT INTO DIFF_TBL1 VALUES('Martin', 'Lopez');

/* -- */

CREATE TABLE INT_TBL2 ( EMP_FNAME varchar(50), EMP_LNAME varchar(50) );

INSERT INTO DIFF_TBL2 VALUES('Franklin', 'Lopez'); INSERT INTO DIFF_TBL2 VALUES('William', 'Turner'); INSERT INTO DIFF_TBL2 VALUES('Franklin', 'Johnson'); INSERT INTO DIFF_TBL2 VALUES('Susan', 'Rogers');

/* -- */

CREATE TABLE PROD_TBL1 ( P_CODE varchar(50), P_DESCRIPT varchar(50), PRICE numeric(4,2), );

INSERT INTO PROD_TBL1 VALUES('123456','Flashlight','5.26'); INSERT INTO PROD_TBL1 VALUES('123457','Lamp','25.15'); INSERT INTO PROD_TBL1 VALUES('123458','Box Fan','10.99'); INSERT INTO PROD_TBL1 VALUES('213345','9v battery','1.92'); INSERT INTO PROD_TBL1 VALUES('311452','Powerdrill','34.99'); INSERT INTO PROD_TBL1 VALUES('254467','100W bulb','1.47');

/* -- */

CREATE TABLE PROD_TBL2 ( STORE varchar(50), AISLE varchar(50), SHELF varchar(50) );

INSERT INTO PROD_TBL2 VALUES('23','W','5'); INSERT INTO PROD_TBL2 VALUES('24','K','9'); INSERT INTO PROD_TBL2 VALUES('25','Z','6');

/* -- */

CREATE TABLE PROJ_TBL1 ( P_CODE varchar(50), P_DESCRIPT varchar(50), PRICE numeric(4,2), );

INSERT INTO PROJ_TBL1 VALUES('123456','Flashlight','5.26'); INSERT INTO PROJ_TBL1 VALUES('123457','Lamp','25.15'); INSERT INTO PROJ_TBL1 VALUES('123458','Box Fan','10.99'); INSERT INTO PROJ_TBL1 VALUES('213345','9v battery','1.92'); INSERT INTO PROJ_TBL1 VALUES('311452','Powerdrill','34.99'); INSERT INTO PROJ_TBL1 VALUES('254467','100W bulb','1.47');

/* -- */

CREATE TABLE SEL_TBL1 ( P_CODE varchar(50), P_DESCRIPT varchar(50), PRICE numeric(4,2), );

INSERT INTO SEL_TBL1 VALUES('123456','Flashlight','5.26'); INSERT INTO SEL_TBL1 VALUES('123457','Lamp','25.15'); INSERT INTO SEL_TBL1 VALUES('123458','Box Fan','10.99'); INSERT INTO SEL_TBL1 VALUES('213345','9v battery','1.92'); INSERT INTO SEL_TBL1 VALUES('311452','Powerdrill','34.99'); INSERT INTO SEL_TBL1 VALUES('254467','100W bulb','1.47');

/* -- */

CREATE TABLE UNI_TBL1 ( P_CODE varchar(50), P_DESCRIPT varchar(50), PRICE numeric(4,2), );

INSERT INTO UNI_TBL1 VALUES('123456','Flashlight','5.26'); INSERT INTO UNI_TBL1 VALUES('123457','Lamp','25.15'); INSERT INTO UNI_TBL1 VALUES('123458','Box Fan','10.99'); INSERT INTO UNI_TBL1 VALUES('213345','9v battery','1.92'); INSERT INTO UNI_TBL1 VALUES('311452','Powerdrill','34.99'); INSERT INTO UNI_TBL1 VALUES('254467','100W bulb','1.47');

/* -- */

CREATE TABLE UNI_TBL2 ( P_CODE varchar(50), P_DESCRIPT varchar(50), PRICE numeric(4,2), );

INSERT INTO UNI_TBL2 VALUES('345678','Microwave','160'); INSERT INTO UNI_TBL2 VALUES('345679','Dishwasher','500'); INSERT INTO UNI_TBL2 VALUES('123458','Box Fan', '10.99');

/* -- */