Question

In: Computer Science

In MySQL, create a new schema titled <yourlastname>module3. Using the below file, create the tables in...

In MySQL, create a new schema titled <yourlastname>module3.

Using the below file, create the tables in your new schema and populate with the supplied data. Tables do not have keys. Do not define them at this time.

There are errors in the data population (INSERT) statements. It is your job to find the errors and correct them. This is important. You will need correct data for future exercises in this module. In the submission area, choose Write Submission and identify the errors your found in the INSERT statements and whether or not your corrections were successful.

Take a screen shot of your new schema in the Navigator window with Tables expanded

FILE:

/* Database Systems, 10th Ed., Coronel/Morris/Rob */
/* Type of SQL : SQL Server */


CREATE TABLE CUSTOMER (
CUS_CODE int,
CUS_LNAME varchar(15),
CUS_FNAME varchar(15),
CUS_INITIAL varchar(1),
CUS_AREACODE varchar(3),
CUS_PHONE varchar(8),
CUS_BALANCE float(8)
);
INSERT INTO CUSTOMER VALUES('10010','Ramas','Alfred','A','615','844-2573','0');
INSERT INTO CUSTOMER VALUES('10011','Dunne','Leona','K','713','894-1238','0');
INSERT INTO CUSTOMER VALUES('10012','Smith','Kathy','W','615','894-2285','345.86');
INSERT INTO CUSTOMER VALUES('10013','Olowski','Paul','F','615','894-2180','536.75');
INSERT INTO CUSTOMER VALUES('10014','Orlando','Myron','','615','222-1672','0');
INSERT INTO CUSTOMER VALUES('10015','O''Brian','Amy','B','713','442-3381','0');
INSERT INTO CUSTOMER VALUES('10016','Brown','James','G','615','297-1228','221.19');
INSERT INTO CUSTOMER VALUES('10017','Williams','George','','615','290-2556','768.93');
INSERT INTO CUSTOMER VALUES('10018','Farriss','Anne','G','713','382-7185','216.55');
INSERT INTO CUSTOMER VALUES('10019','Smith','Olette','K','615','297-3809','0');

/* -- */

CREATE TABLE INVOICE (
INV_NUMBER int,
CUS_CODE int,
INV_DATE date,
INV_SUBTOTAL float(8),
INV_TAX float(8),
INV_TOTAL float(8)
);
INSERT INTO INVOICE VALUES('1001','10014','1/16/2016','24.90','1.99','26.89');
INSERT INTO INVOICE VALUES('1002','10011','1/16/2016','9.98','0.80','10.78');
INSERT INTO INVOICE VALUES('1003','10012','1/16/20166','153.85','12.31','166.16');
INSERT INTO INVOICE VALUES('1004','10011','1/16/2017','34.97','2.80','37.77');
INSERT INTO INVOICE VALUES('1005','10018','1/16/2017','70.44','5.64','76.08');
INSERT INTO INVOICE VALUES('1006','10014','1/16/2017','397.83','31.83','429.66');
INSERT INTO INVOICE VALUES('1007','10015','1/16/2017','34.97','2.80','37.77');
INSERT INTO INVOICE VALUES('1008','10011','1/16/2017','399.15','31.93','431.08');

/* -- */

CREATE TABLE LINE (
INV_NUMBER int,
LINE_NUMBER int,
P_CODE varchar(10),
LINE_UNITS float(8),
LINE_PRICE float(8),
LINE_TOTAL float(8)
);
INSERT INTO LINE VALUES('1001','1','13-Q2/P2','1','14.99','14.99');
INSERT INTO LINE VALUES('1001','2','23109-HB','1','9.95','9.95');
INSERT INTO LINE VALUES('1002','1','54778-2T','2','4.99','9.98');
INSERT INTO LINE VALUES('1003','1','2238/QPD','1','38.95','38.95');
INSERT INTO LINE VALUES('1003','2','1546-QQ2','1','39.95','39.95');
INSERT INTO LINE VALUES('1003','3','13-Q2/P2','5','14.99','74.95');
INSERT INTO LINE VALUES('1004','1','54778-2T','3','4.99','14.97');
INSERT INTO LINE VALUES('1004','2','23109-HB','2','9.95','19.90');
INSERT INTO LINE VALUES('1005','1','PVC23DRT','12','5.87','70.44');
INSERT INTO LINE VALUES('1006','1','SM-18277','3','6.99','20.97');
INSERT INTO LINE VALUES('1006','2','2232/QTY','1','109.92','109.92');
INSERT INTO LINE VALUES('1006','3','23109-HB','1','9.95','9.95');
INSERT INTO LINE VALUES('1006','4','89-WRE-Q','1','256.99','256.99');
INSERT INTO LINE VALUES('1007','1','13-Q2/P2','2','14.99','29.98');
INSERT INTO LINE VALUES('1007','2','54778-2T','1','4.99','4.99');
INSERT INTO LINE VALUES('1008','1','PVC23DRT','5','5.87','29.35');
INSERT INTO LINE VALUES('1008','2','WR3/TT3','3','119.95','359.85');
INSERT INTO LINE VALUES('1008','3','23109-HB','1','9.95','9.95');

/* -- */

CREATE TABLE PRODUCT (
P_CODE varchar(10),
P_DESCRIPT varchar(35),
P_INDATE date,
P_QOH int,
P_MIN int,
P_PRICE float(8),
P_DISCOUNT float(8),
V_CODE int
);
INSERT INTO PRODUCT VALUES('11QER/31','Power painter, 15 psi., 3-nozzle','2015-11-03','8','5','109.99','0','25595');
INSERT INTO PRODUCT VALUES('13-Q2/P2','7.25-in. pwr. saw blade','2015-12-13','32','15','14.99','0.05','');
INSERT INTO PRODUCT VALUES('14-Q1/L3','9.00-in. pwr. saw blade','2015-11-13','18','12','17.49','0','21344');
INSERT INTO PRODUCT VALUES('1546-QQ2','Hrd. cloth, 1/4-in., 2x50','2016-01-15','15','8','39.95','0','23119');
INSERT INTO PRODUCT VALUES('1558-QW1','Hrd. cloth, 1/2-in., 3x50','2016-01-15','23','5','43.99','0','23119');
INSERT INTO PRODUCT VALUES('2232/QTY','B&D jigsaw, 12-in. blade','2015-12-30','8','5','109.92','0.05','24288');
INSERT INTO PRODUCT VALUES('2232/QWE','B&D jigsaw, 8-in. blade','2015-12-24','6','5','99.87','0.05','24288');
INSERT INTO PRODUCT VALUES('2238/QPD','B&D cordless drill, 1/2-in.','2016-01-20','12','5','38.95','0.05','25595');
INSERT INTO PRODUCT VALUES('23109-HB','Claw hammer','2016-01-12','23','10','9.95','0.1','21225');
INSERT INTO PRODUCT VALUES('23114-AA','Sledge hammer' 12 lb.','2016-01-2','8','5','14.40','0.05','');
INSERT INTO PRODUCT VALUES('54778-2T','Rat-tail file, 1/8-in. fine','2015-12-15','43','20','4.99','0','21344');
INSERT INTO PRODUCT VALUES('89-WRE-Q','Hicut chain saw, 16 in.','2016-02-17','11','5','256.99','0.05','24288');
INSERT INTO PRODUCT VALUES('PVC23DRT','PVC pipe, 3.5-in., 8-ft','2016-02-27','188','75','5.87','0','24004');
INSERT INTO PRODUCT VALUES('SM-18277','1.25-in. metal screw, '25','2016-03-01','172','75','6.99','0','21225');
INSERT INTO PRODUCT VALUES('SW-23116','2.5-in. wd. screw, 50','2016-02-14','237','100','8.45','0','21231');
INSERT INTO PRODUCT VALUES('WR3/TT3','Steel matting, 4''x8''x1/6", .5" mesh','2016-01-27','18','5','119.95','0.1','25595');

/* -- */


CREATE TABLE VENDOR (
V_CODE int,
V_NAME varchar(15),
V_CONTACT varchar(50),
V_AREACODE varchar(3),
V_PHONE varchar(8),
V_STATE varchar(2),
V_ORDER varchar(1)
);
INSERT INTO VENDOR VALUES('21225','Bryson, Inc.','Smithson','615','223-3234','TN','Y');
INSERT INTO VENDOR VALUES('21226','SuperLoo, Inc.','Flushing','904','215-8995','FL','N');
INSERT INTO VENDOR VALUES('21231','D&E Supply','Singh','615','228-3245','TN','Y');
INSERT INTO VENDOR VALUES('21344','Gomez Bros.','Ortega','615','889-2546','KY','N');
INSERT INTO VENDOR VALUES('22567','Dome Supply','Smith','901','678-1419','GA','N');
INSERT INTO VENDOR VALUES('23119','Randsets Ltd.','Anderson','901','678-3998','GA','Y');
INSERT INTO VENDOR VALUES('24004','Brackman Bros.','Browning','615','228-1410','TN','N');
INSERT INTO VENDOR VALUES('24288','ORDVA, Inc.','Hakford','615','898-1234','TN','Y');
INSERT INTO VENDOR VALUES('25443','B&K, Inc.','Smith','904','227-0093','FL','N');
INSERT INTO VENDOR VALUES('25501','Damal Supplies','Smythe','615','890-3529','TN','N');
INSERT INTO VENDOR VALUES('25595','Rubicon Systems','Orton','904','456-0092','FL','Y');

Solutions

Expert Solution

1.Table Name :Customer
CREATE TABLE CUSTOMER (
CUS_CODE int,
CUS_LNAME varchar(15),
CUS_FNAME varchar(15),
CUS_INITIAL varchar(1),
CUS_AREACODE varchar(3),
CUS_PHONE varchar(8),
CUS_BALANCE float(8)
);
/*inserting records*/
INSERT INTO CUSTOMER VALUES('10010','Ramas','Alfred','A','615','844-2573','0');
INSERT INTO CUSTOMER VALUES('10011','Dunne','Leona','K','713','894-1238','0');
INSERT INTO CUSTOMER VALUES('10012','Smith','Kathy','W','615','894-2285','345.86');
INSERT INTO CUSTOMER VALUES('10013','Olowski','Paul','F','615','894-2180','536.75');
INSERT INTO CUSTOMER VALUES('10014','Orlando','Myron','','615','222-1672','0');
INSERT INTO CUSTOMER VALUES('10015','O''Brian','Amy','B','713','442-3381','0');
INSERT INTO CUSTOMER VALUES('10016','Brown','James','G','615','297-1228','221.19');
INSERT INTO CUSTOMER VALUES('10017','Williams','George','','615','290-2556','768.93');
INSERT INTO CUSTOMER VALUES('10018','Farriss','Anne','G','713','382-7185','216.55');
INSERT INTO CUSTOMER VALUES('10019','Smith','Olette','K','615','297-3809','0');

/* -selecting records- */
select * from CUSTOMER;

Screen showing table data :

***************************************************

2.Table Name :Product

CREATE TABLE PRODUCT (
P_CODE varchar(10),
P_DESCRIPT varchar(35),
P_INDATE date,
P_QOH int,
P_MIN int,
P_PRICE float(8),
P_DISCOUNT float(8),
V_CODE int
);
/*inserting records*/
INSERT INTO PRODUCT VALUES('11QER/31','Power painter, 15 psi., 3-nozzle','2015-11-03','8','5','109.99','0','25595');
INSERT INTO PRODUCT VALUES('13-Q2/P2','7.25-in. pwr. saw blade','2015-12-13','32','15','14.99','0.05','');
INSERT INTO PRODUCT VALUES('14-Q1/L3','9.00-in. pwr. saw blade','2015-11-13','18','12','17.49','0','21344');
INSERT INTO PRODUCT VALUES('1546-QQ2','Hrd. cloth, 1/4-in., 2x50','2016-01-15','15','8','39.95','0','23119');
INSERT INTO PRODUCT VALUES('1558-QW1','Hrd. cloth, 1/2-in., 3x50','2016-01-15','23','5','43.99','0','23119');
INSERT INTO PRODUCT VALUES('2232/QTY','B&D jigsaw, 12-in. blade','2015-12-30','8','5','109.92','0.05','24288');
INSERT INTO PRODUCT VALUES('2232/QWE','B&D jigsaw, 8-in. blade','2015-12-24','6','5','99.87','0.05','24288');
INSERT INTO PRODUCT VALUES('2238/QPD','B&D cordless drill, 1/2-in.','2016-01-20','12','5','38.95','0.05','25595');
INSERT INTO PRODUCT VALUES('23109-HB','Claw hammer','2016-01-12','23','10','9.95','0.1','21225');
INSERT INTO PRODUCT VALUES('23114-AA','Sledge hammer,12 lb.','2016-01-2','8','5','14.40','0.05',null);
INSERT INTO PRODUCT VALUES('54778-2T','Rat-tail file, 1/8-in. fine','2015-12-15','43','20','4.99','0','21344');
INSERT INTO PRODUCT VALUES('89-WRE-Q','Hicut chain saw, 16 in.','2016-02-17','11','5','256.99','0.05','24288');
INSERT INTO PRODUCT VALUES('PVC23DRT','PVC pipe, 3.5-in., 8-ft','2016-02-27','188','75','5.87','0','24004');
INSERT INTO PRODUCT VALUES('SM-18277','1.25-in. metal screw, 25','2016-03-01','172','75','6.99','0','21225');
INSERT INTO PRODUCT VALUES('SW-23116','2.5-in. wd. screw, 50','2016-02-14','237','100','8.45','0','21231');
INSERT INTO PRODUCT VALUES('WR3/TT3','Steel matting, 4''x8''x1/6", .5" mesh','2016-01-27','18','5','119.95','0.1','25595');

/* -selecting records- */
select * from product;

Screen showing table data :

*************************************************

3.Table Name :VENDOR
CREATE TABLE VENDOR (
V_CODE int,
V_NAME varchar(15),
V_CONTACT varchar(50),
V_AREACODE varchar(3),
V_PHONE varchar(8),
V_STATE varchar(2),
V_ORDER varchar(1)
);
/*inserting records*/
INSERT INTO VENDOR VALUES('21225','Bryson, Inc.','Smithson','615','223-3234','TN','Y');
INSERT INTO VENDOR VALUES('21226','SuperLoo, Inc.','Flushing','904','215-8995','FL','N');
INSERT INTO VENDOR VALUES('21231','D&E Supply','Singh','615','228-3245','TN','Y');
INSERT INTO VENDOR VALUES('21344','Gomez Bros.','Ortega','615','889-2546','KY','N');
INSERT INTO VENDOR VALUES('22567','Dome Supply','Smith','901','678-1419','GA','N');
INSERT INTO VENDOR VALUES('23119','Randsets Ltd.','Anderson','901','678-3998','GA','Y');
INSERT INTO VENDOR VALUES('24004','Brackman Bros.','Browning','615','228-1410','TN','N');
INSERT INTO VENDOR VALUES('24288','ORDVA, Inc.','Hakford','615','898-1234','TN','Y');
INSERT INTO VENDOR VALUES('25443','B&K, Inc.','Smith','904','227-0093','FL','N');
INSERT INTO VENDOR VALUES('25501','Damal Supplies','Smythe','615','890-3529','TN','N');
INSERT INTO VENDOR VALUES('25595','Rubicon Systems','Orton','904','456-0092','FL','Y');

/*selecting records*/
select * from vendor;

Screen showing table data :

****************************************************

4.Table Name :INVOICE
CREATE TABLE INVOICE (
INV_NUMBER int,
CUS_CODE int,
INV_DATE date,
INV_SUBTOTAL float(8),
INV_TAX float(8),
INV_TOTAL float(8)
);
/*inserting records*/
INSERT INTO INVOICE VALUES('1001','10014','2016/1/16','24.90','1.99','26.89');
INSERT INTO INVOICE VALUES('1002','10011','2016/1/16','9.98','0.80','10.78');
INSERT INTO INVOICE VALUES('1003','10012','2016/1/16','153.85','12.31','166.16');
INSERT INTO INVOICE VALUES('1004','10011','2017/1/17','34.97','2.80','37.77');
INSERT INTO INVOICE VALUES('1005','10018','2017/1/16','70.44','5.64','76.08');
INSERT INTO INVOICE VALUES('1006','10014','2017/1/16','397.83','31.83','429.66');
INSERT INTO INVOICE VALUES('1007','10015','2017/1/16','34.97','2.80','37.77');
INSERT INTO INVOICE VALUES('1008','10011','2017/1/16','399.15','31.93','431.08');

/* -selecting records- */
select * from INVOICE;
Screen showing table data :

****************************************************

5.Table Name :LINE
CREATE TABLE LINE (
INV_NUMBER int,
LINE_NUMBER int,
P_CODE varchar(10),
LINE_UNITS float(8),
LINE_PRICE float(8),
LINE_TOTAL float(8)
);
/*inserting records*/
INSERT INTO LINE VALUES('1001','1','13-Q2/P2','1','14.99','14.99');
INSERT INTO LINE VALUES('1001','2','23109-HB','1','9.95','9.95');
INSERT INTO LINE VALUES('1002','1','54778-2T','2','4.99','9.98');
INSERT INTO LINE VALUES('1003','1','2238/QPD','1','38.95','38.95');
INSERT INTO LINE VALUES('1003','2','1546-QQ2','1','39.95','39.95');
INSERT INTO LINE VALUES('1003','3','13-Q2/P2','5','14.99','74.95');
INSERT INTO LINE VALUES('1004','1','54778-2T','3','4.99','14.97');
INSERT INTO LINE VALUES('1004','2','23109-HB','2','9.95','19.90');
INSERT INTO LINE VALUES('1005','1','PVC23DRT','12','5.87','70.44');
INSERT INTO LINE VALUES('1006','1','SM-18277','3','6.99','20.97');
INSERT INTO LINE VALUES('1006','2','2232/QTY','1','109.92','109.92');
INSERT INTO LINE VALUES('1006','3','23109-HB','1','9.95','9.95');
INSERT INTO LINE VALUES('1006','4','89-WRE-Q','1','256.99','256.99');
INSERT INTO LINE VALUES('1007','1','13-Q2/P2','2','14.99','29.98');
INSERT INTO LINE VALUES('1007','2','54778-2T','1','4.99','4.99');
INSERT INTO LINE VALUES('1008','1','PVC23DRT','5','5.87','29.35');
INSERT INTO LINE VALUES('1008','2','WR3/TT3','3','119.95','359.85');
INSERT INTO LINE VALUES('1008','3','23109-HB','1','9.95','9.95');

/*selecting records*/
select * from LINE;

Screen showing table data :


Related Solutions

Using MYSQL in terminal, create the tables for your above designed relational schema and populate your...
Using MYSQL in terminal, create the tables for your above designed relational schema and populate your tables with appropriate data. All except time slot and the relationship it participates in. Use the names for the tables and attributes from the ER schema. Use ON DELETE CASCADE for foreign keys. Each basic table (corresponding to an entity in the ER schema developed for Part 1) should have 5-10 rows. Campus can have just 2. Building should have at least 6. At...
using mysql and the schema is provided below. thanks In this lab, you will be manipulating...
using mysql and the schema is provided below. thanks In this lab, you will be manipulating the database to add, delete and modify the values in the database. Please use a "select * from..." after each query to show the effects of your data manipulation query. 1. The title 'Time Flies' now has a new track, the 11th track 'Spring', which is 150 seconds long and has only a MP3 file. Insert the new track into Tracks table (Don’t hand-code...
using the lyrics database schema in mysql provided below. 1.)List the artist name of the artists...
using the lyrics database schema in mysql provided below. 1.)List the artist name of the artists who do not have a webaddress and their leadsource is “Directmail”? 2.)List the names of members in the artist called 'Today'. 3.)Report the total runtime in minutes FOR EACH album in the Titles table. 4.)List the firstname, lastname of members who are represented by the salesperson “Lisa Williams” 5.)List EACH salesperson’s firstname along with the number of Members that EACH SalesPerson represents. below is...
Using the sample.sql script, create the sample database in MySQL. Submit the MySQL interactive screen that...
Using the sample.sql script, create the sample database in MySQL. Submit the MySQL interactive screen that results. create database sample; use sample; create table customer (custno int auto_increment primary key, firstname varchar(20), middle varchar(20), lastname varchar(20), address varchar(60), telnum1 varchar(10), telnum2 varchar(10), telnum3 varchar(10), pin varchar(6), email varchar(30)); create table accttype (id int primary key, type varchar(10)); insert into accttype (id, type) values (1,'check'); insert into accttype (id, type) values (2,'save'); insert into accttype (id, type) values (3,'cd'); insert into...
Create a schema file that captures the requirements for a <student> element> Here are the specifications...
Create a schema file that captures the requirements for a <student> element> Here are the specifications for student: 3.1. A student must have a first name and last name. 3.2. A student may have a middle name, but it’s optional. 3.3. A student may have a home address, a work address, or both.             3.3.1. Use a complex type to connect to a single schema definition for “address” 3.4. An address has: a street address, city, state, and zip code....
Create three MySQL database tables and write SQL scripts to read, insert, and delete data. The...
Create three MySQL database tables and write SQL scripts to read, insert, and delete data. The first database table will contain the names of at least four movies. The second table will be a list of actors who appear in the movies. The third table will be an associative table that describes the relationship between the actors and their movies (which actors appear in which movies). Actors and movies have a “many-to-many relationship,” meaning an actor can be in multiple...
Create three MySQL database tables and write SQL scripts to read, insert, and delete data. The...
Create three MySQL database tables and write SQL scripts to read, insert, and delete data. The first database table will contain the names of at least four movies. The second table will be a list of actors who appear in the movies. The third table will be an associative table that describes the relationship between the actors and their movies (which actors appear in which movies). Actors and movies have a “many-to-many relationship,” meaning an actor can be in multiple...
Using the world_x database you installed on your MySQL Workbench, create a new table named “independence”...
Using the world_x database you installed on your MySQL Workbench, create a new table named “independence” with the following attributes (columns): A field named “id” which has data type auto_increment, A field named “country_name” which has data type varchar(50), and A field named “independence_date” which has type “date.” After you create the table, run the following SQL command: INSERT INTO independence(country_name, independence_date) VALUE (‘United States’,’1776-07-04’) Submit a 1-page Microsoft Word document that shows the following: The SQL command you used...
This refer to the “om” database (or Schema) that you will find in your MySQL Workbench...
This refer to the “om” database (or Schema) that you will find in your MySQL Workbench program if you have run the sample database install script. Please save all of your answers in one script (.sql) or type all your answers into Notepad++ and submit them as a single .sql file. Please test your SQL statements in Workbench 1.       Using an INNER JOIN, select the order_id, order_date, shipped_date, fname, and customer_phone from the orders and customers tables. The fname is a...
Pivot Tables - Please explain how to acheive the following: Using the data below, create a...
Pivot Tables - Please explain how to acheive the following: Using the data below, create a Pivot Table that answers the question “Which salesperson sold the most in any particular month.” A manager wants to click on the Pivot Table and choose a month and have the name of that person appear with his or her amount for that month. Sales Data Salesperson May June July Aug. Sept. Oct. Albertson, Kathy $3,947.00 $557.00 $3,863.00 $1,117.00 $8,237.00 $8,690.00 Allenson, Carol $4,411.00...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT