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 :

venereology answered 1 year ago

Next > < Previous

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 MySQL Workbench Data Modeler, create three (3) models from the tables below. Show Table 1...

-Using MySQL Workbench Data Modeler, create three (3) models from the tables below. Show Table 1 in 3rd Normal Form. Show Table 2 in 3rd Normal Form. Merge the 3rd Normal Forms of Table 1 and Table 2 into a single 3rd Normal Form model. Note that you only can model table and column names and data types. The data shown is for your reference to determine functional, partial, and transitive dependencies. -Provide a summary of the steps you took...

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...

I am having trouble writing these queries in MYSQL. Using the schema listed below, please write...

I am having trouble writing these queries in MYSQL. Using the schema listed below, please write the following queries in MYSQL: 1) Find the Content and the Reviewer Name for each comment, about “ACADEMY DINOSAUR” only if the same reviewer has commented about “ACE GOLDFINGER” too. 2) Retrieve the title of all the Movies in Japanese without any comment, ordered alphabetically. 3) Find all the movie titles where an actor called “TOM” or an actor called “BEN" acted, where there...

Use MySQL server. First step: create and use database called EDU. Syntax: CREATE SCHEMA edu; USE...

Use MySQL server. First step: create and use database called EDU. Syntax: CREATE SCHEMA edu; USE edu; Create a script which will create the tables listed below: STUDENT(STUDENT_ID, STUDENT_NAME, MAJOR_ID, DOB, PHONE_NUMBER) MAJOR(MAJOR_ID, MAJOR_NAME) ENROLLMENT(STUDENT_ID, COURSE_ID, GRADE) COURSE(COURSE_ID, COURSE_NAME) RESPONSIBILITY(FACULTY_ID, COURSE_ID) TEACHER(FACULTY_ID, DEPT_ID, TEACHER_NAME) DEPARTMENT(DEPT_ID, DEPARTMENT_NAME) Start the script with a series of DROP statements so that as you correct mistakes you will start fresh each time. To avoid referential integrity errors, the table drops should be in the opposite...

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...

Subjects