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.
Using the MySQL Workbench create a new database using your design specifications Add at least 10...
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 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 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 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 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...
This project will create a GUI to access data held in a MySQL database. You will design a GUI using Swing components that will allow you to add a record, delete a record, update a record and display all current records int he database. The design is your choice. Extra credit will be given for the ability to search for a specific record by a key value. You will need to provide details of the database domain, table and record...
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...
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
Create a series of ER Diagrams using MySQL Workbench. Each question below is a different version of the same database, with each version increasing in complexity. You need to submit an ER diagram for each version, in pdf or png format. The easiest would be to export your diagrams as png format and insert them into an MS Word document. For each ER diagram clearly indicate primary keys, foreign keys, and relationships, including cardinality (i.e. 1-to-many). Indicate in your diagram...
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...