Question
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
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).
The file that was given. (below)
CREATE DATABASE Relational_Database; USE Relational_Database;
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');
/* -- */
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started