Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Case Studies In Business Data Bases

Authors: James Bradley

1st Edition

0030141346, 978-0030141348

More Books

Students also viewed these Databases questions