HomeworkLib
Question

Using the MySQL Workbench create a new database using your design specifications Add at least 10...

  1. Using the MySQL Workbench create a new database using your design specifications
  2. Add at least 10 records to your tables. Note: Certain tables may not require 10 records and that is ok as long as your main tables have 10 or more
  3. Create MySQL statements that will retrieve all records or rows from the tables in your database
  4. Create 10 MySQL statements that will retrieve specified records or rows from one table in your database
  5. Create 10 MySQL statements that will retrieve records or rows from more than one table in your database
  6. Create 10 MySQL statements that will retrieve specified records or rows from more than one table in your database
  7. Create 7 MySQL statements that will modify a value or values in a database
    1. Include one statement that will delete a record
    2. Include one statement that will update a value
  8. Use ALTER TABLE command to set constraints:
    1. on the Primary Key field of the table, if you did not include it in your create table statement
    2. to at least one field in one of your tables that restricts a value
engineering   Computer-Science   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Answer #1

CREATE TABLE employees (
emp_no INT NOT NULL,
birth_date DATE NOT NULL,
first_name VARCHAR(14) NOT NULL,
last_name VARCHAR(16) NOT NULL,
gender ENUM ('M','F') NOT NULL,
hire_date DATE NOT NULL,
PRIMARY KEY (emp_no)
);

CREATE TABLE departments (
dept_no CHAR(4) NOT NULL,
dept_name VARCHAR(40) NOT NULL,
PRIMARY KEY (dept_no),
UNIQUE KEY (dept_name)
);

CREATE TABLE dept_manager (
dept_no CHAR(4) NOT NULL,
emp_no INT NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
KEY (emp_no),
KEY (dept_no),
FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no,dept_no)
);

CREATE TABLE dept_emp (
emp_no INT NOT NULL,
dept_no CHAR(4) NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
KEY (emp_no),
KEY (dept_no),
FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no,dept_no)
);

CREATE TABLE titles (
emp_no INT NOT NULL,
title VARCHAR(50) NOT NULL,
from_date DATE NOT NULL,
to_date DATE,
KEY (emp_no),
FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no,title, from_date)
);

CREATE TABLE salaries (
emp_no INT NOT NULL,
salary INT NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
KEY (emp_no),
FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no, from_date)
);

INSERT INTO `employees` VALUES (10001,'1953-09-02','Georgi','Facello','M','1986-06-26'),
(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21'),
(10003,'1959-12-03','Parto','Bamford','M','1986-08-28'),
(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01'),
(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12'),
(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02'),
(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10'),
(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15'),
(10009,'1952-04-19','Sumant','Peac','F','1985-02-18'),
(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24'),
(10011,'1953-11-07','Mary','Sluis','F','1990-01-22'),
(10012,'1960-10-04','Patricio','Bridgland','M','1992-12-18'),
(10013,'1963-06-07','Eberhardt','Terkki','M','1985-10-20'),
(10014,'1956-02-12','Berni','Genin','M','1987-03-11'),
(10015,'1959-08-19','Guoxiang','Nooteboom','M','1987-07-02'),
(10016,'1961-05-02','Kazuhito','Cappelletti','M','1995-01-27'),
(10017,'1958-07-06','Cristinel','Bouloucos','F','1993-08-03'),
(10018,'1954-06-19','Kazuhide','Peha','F','1987-04-03'),
(10019,'1953-01-23','Lillian','Haddadi','M','1999-04-30'),
(10020,'1952-12-24','Mayuko','Warwick','M','1991-01-26'),
(10021,'1960-02-20','Ramzi','Erde','M','1988-02-10'),
(10022,'1952-07-08','Shahaf','Famili','M','1995-08-22';

INSERT INTO `departments` VALUES
('d001','Marketing'),
('d002','Finance'),
('d003','Human Resources'),
('d004','Production'),
('d005','Development'),
('d006','Quality Management'),
('d007','Sales'),
('d008','Research'),
('d009','Customer Service');

INSERT INTO `dept_manager` VALUES
(110022,'d001','1985-01-01','1991-10-01'),

(110039,'d001','1991-10-01','9999-01-01'),

(110085,'d002','1985-01-01','1989-12-17'),

(110114,'d002','1989-12-17','9999-01-01'),

(110183,'d003','1985-01-01','1992-03-21'),

(110228,'d003','1992-03-21','9999-01-01'),

(110303,'d004','1985-01-01','1988-09-09'),

(110344,'d004','1988-09-09','1992-08-02'),

(110386,'d004','1992-08-02','1996-08-30'),

(110420,'d004','1996-08-30','9999-01-01'),

(110511,'d005','1985-01-01','1992-04-25'),

(110567,'d005','1992-04-25','9999-01-01'),

(110725,'d006','1985-01-01','1989-05-06');

INSERT INTO `dept_manager` VALUES
(110022,'d001','1985-01-01','1991-10-01'),

(110039,'d001','1991-10-01','9999-01-01'),

(110085,'d002','1985-01-01','1989-12-17'),

(110114,'d002','1989-12-17','9999-01-01'),

(110183,'d003','1985-01-01','1992-03-21'),

(110228,'d003','1992-03-21','9999-01-01'),

(110303,'d004','1985-01-01','1988-09-09'),

(110344,'d004','1988-09-09','1992-08-02'),

(110386,'d004','1992-08-02','1996-08-30'),

(110420,'d004','1996-08-30','9999-01-01'),

(110511,'d005','1985-01-01','1992-04-25'),

(110567,'d005','1992-04-25','9999-01-01'),

(110725,'d006','1985-01-01','1989-05-06');

INSERT INTO `titles` VALUES (10001,'Senior Engineer','1986-06-26','9999-01-01'),
(10002,'Staff','1996-08-03','9999-01-01'),
(10003,'Senior Engineer','1995-12-03','9999-01-01'),
(10004,'Engineer','1986-12-01','1995-12-01'),
(10004,'Senior Engineer','1995-12-01','9999-01-01'),
(10005,'Senior Staff','1996-09-12','9999-01-01'),
(10005,'Staff','1989-09-12','1996-09-12'),
(10006,'Senior Engineer','1990-08-05','9999-01-01'),
(10007,'Senior Staff','1996-02-11','9999-01-01'),
(10007,'Staff','1989-02-10','1996-02-11'),
(10008,'Assistant Engineer','1998-03-11','2000-07-31'),
(10009,'Assistant Engineer','1985-02-18','1990-02-18'),
(10009,'Engineer','1990-02-18','1995-02-18'),
(10009,'Senior Engineer','1995-02-18','9999-01-01'),
(10010,'Engineer','1996-11-24','9999-01-01'),
(10011,'Staff','1990-01-22','1996-11-09'),
(10012,'Engineer','1992-12-18','2000-12-18'),
(10012,'Senior Engineer','2000-12-18','9999-01-01'),
(10013,'Senior Staff','1985-10-20','9999-01-01');

INSERT INTO `titles` VALUES (10001,'Senior Engineer','1986-06-26','9999-01-01'),
(10002,'Staff','1996-08-03','9999-01-01'),
(10003,'Senior Engineer','1995-12-03','9999-01-01'),
(10004,'Engineer','1986-12-01','1995-12-01'),
(10004,'Senior Engineer','1995-12-01','9999-01-01'),
(10005,'Senior Staff','1996-09-12','9999-01-01'),
(10005,'Staff','1989-09-12','1996-09-12'),
(10006,'Senior Engineer','1990-08-05','9999-01-01'),
(10007,'Senior Staff','1996-02-11','9999-01-01'),
(10007,'Staff','1989-02-10','1996-02-11'),
(10008,'Assistant Engineer','1998-03-11','2000-07-31'),
(10009,'Assistant Engineer','1985-02-18','1990-02-18'),
(10009,'Engineer','1990-02-18','1995-02-18'),
(10009,'Senior Engineer','1995-02-18','9999-01-01'),
(10010,'Engineer','1996-11-24','9999-01-01'),
(10011,'Staff','1990-01-22','1996-11-09'),
(10012,'Engineer','1992-12-18','2000-12-18'),
(10012,'Senior Engineer','2000-12-18','9999-01-01'),
(10013,'Senior Staff','1985-10-20','9999-01-01');

NOTE: As per HOMEWORKLIB POLICY I am allowed to answer specific number of questions (including sub-parts) on a single post. Kindly post the remaining questions separately and I will try to answer them. Sorry for the inconvenience caused.

0 0
Add a comment
Know the answer?
Add Answer to:
Using the MySQL Workbench create a new database using your design specifications Add at least 10...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions

  • Use MySQL Workbench for these questions. Do NOT use Lucidchart. MySQL Workbench has more capability than...

    Use MySQL Workbench for these questions. Do NOT use Lucidchart. MySQL Workbench has more capability than Lucidchart, as it allows creation of DDL SQL from the model. Turn in 1 Workbench file , 1 document with the forward-engineered sql statements, and a Word document with your inserted data. This problem mimics the one from the previous assignment. Model it in Workbench, then forward-engineer the database script and import into your database. If the import fails, fix it in the model,...

  • Using MySQL Workbench: Submit the printouts of your interaction with the DBMS. ID name ID delivery_timestamp...

    Using MySQL Workbench: Submit the printouts of your interaction with the DBMS. ID name ID delivery_timestamp supplier supplied item city name quantity 1. [30-point] Create an SQL database of the above ER-diagram using "create table” statements. The type of attribute "quantity" is integer, "delivery_timestamp" is timestamp, and, for all the others, the type is varchar (up to 100 chars). 2. [10-point] Insert 4 records into supplier, 4 records into item, and 10 records into supplied. 3. [60-points] Perform the following...

  • Use MySQL Workbench to create a simple ER model and use the Forward Engineering functionality of...

    Use MySQL Workbench to create a simple ER model and use the Forward Engineering functionality of Workbench to create a simple DB (specs follow). This focuses on how MySQL Workbench is used to create a model and then a DB from it, rather than on ER modeling. The sample model you are to create is on books, authors, and publishers - a model that could be part of a larger ER model for a library. A book has an ISBN...

  • How can I create the following stored procedure in MySQL Workbench?: Create a stored procedure na...

    How can I create the following stored procedure in MySQL Workbench?: Create a stored procedure named prc_new_rental to insert new rows in the RENTAL table. The procedure should satisfy the following conditions: a. The membership number will be provided as a parameter. b. Use a Count() function to verify that the membership number exists in the MEMBERSHIP table. If it does not exist, then a message should be displayed that the membership does not exist and no data should be...

  • Q.5] Answer the following questions based on the company database (based on the homework assignment 2) y dat...

    Q.5] Answer the following questions based on the company database (based on the homework assignment 2) y database (based on the homework assignment 2) 3 IPage 1. For each department whose average employee salary is more than $30,000, write a SQL statement to retrieve the department name and the number of employees working for that department 2. Write a SQL statement to retrieve first name, last name, and address of each employee who lives in Houston, TX. 3. Write a...

  • Write code for mySQL 1) create a new one database ; to create your table 2)...

    Write code for mySQL 1) create a new one database ; to create your table 2) Every table should have a primary key 3) Table relationship has to be based on foreign key and shown within your physical table 4) Insert 5 data records in every table 9) Create a database view that return result set of all record and all columns within all 6 table

  • Topic: Inventory Manangement Part A. Database Creation Your database should have a minimum of 4 tables...

    Topic: Inventory Manangement Part A. Database Creation Your database should have a minimum of 4 tables and include both one-to-many and many-to-many relationships. Be sure to include some numeric and/or date fields. Define all appropriate constraints, using the proper naming conventions (see Structure Notes below). Populate your database with at least 30 records in the main table(s), and whatever is needed in related tables. Submit the following: • a short description of the purpose of the database and what the...

  • Overview: Database management plays an integral role in nearly every area of business. Databases house customer, accoun...

    Overview: Database management plays an integral role in nearly every area of business. Databases house customer, accounting, and employee data, and these different data sets must all be efficiently managed in order to make the data accessible. Companies rely on database engineers to ensure that their records are accurate, updated, and tracked in real time. This course covers structured query language (SQL) and how it can be used to manage database schemas, manipulate data, and analyze data. For your final...

  • #1) Display Last Name, First Name, DonorId, Fundname, TotalPledged from the donor’s database (you may need...

    #1) Display Last Name, First Name, DonorId, Fundname, TotalPledged from the donor’s database (you may need to use donor and pledge tables to get the information) for Donor ID 59034. (Your results window should have 3 rows) #2) Display all records from the PurchaseOrderHeader from the AdventureWorks Database that were sold by Employee 261 (Your results window should have 401 rows) #3) Display salesorderid, orderdate, totaldue, and territory name from salesorderheader and salesterritory for all totaldue that are greater than...

  • For this set of Review Questions, we will create and use a database for the Wedgewood...

    For this set of Review Questions, we will create and use a database for the Wedgewood Pacific Corporation (WPC) that is similar to the Microsoft Access database we created and used in Chapters 1 and 2. Founded in 1957 in Seattle, Washington, WPC has grown into an internationally recognized organization. The company is located in two buildings. One building houses the Administration, Accounting, Finance, and Human Resources departments, and the second houses the Production, Marketing, and Information Systems departments. The...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT