Question

SQL

This assignment aligns with the following objectives:

• Create a relational database containing at least three tables
• Populate the database using the INSERT command
• Create integrity constraints on the tables created

Deli has hired you as a consultant to design a database for the deli. They have provided you with the following information:

• Every employee has a social security number, name, salary, and date of hire.
• The deli is organized into several departments. Each department has a unique name.
• Each department has exactly one employee as its manager but an employee may manage more than one department. The database should record the date when the most recent manager of each dept. was appointed in that position.
• very employee must be assigned a department.
• Every employee has a supervisor who need not be the manager of any department.

The deli has these four departments:

1. hot foods
2. sandwich
3. snacks
4. beverage

You are provided the following additional information, which should be inserted into the database using SQL queries (not using any GUI interfaces, such as PhpMyAdmin):

• Jim Jones (ssn: 134-56-8877, salary: $28,000, dept:hot foods, date of hire: 1/26/2015, supervisor: Rita Bita)
• Rita Bita (ssn: 138-56-8050, salary: $32,000, dept: beverages, date of hire: 2/15/2017, supervisor: Holly Dew. manages: beverages, starting 3/18/2018)
• Holly Dew (ssn: 334-55-8877, salary: $29,000, dept:sandwich, date of hire: 1/15/2016, supervisor: Pablo Escobar)
• Pablo Escobar (ssn: 666-56-6666, salary: $48,000, dept:snacks, date of hire: 1/26/2014, supervisor: Rita Bita, manages: snacks, starting 5/5/2015)
• Al Capone (ssn: 888-91-8870, salary: $40,000, dept:hot foods, date of hire: 1/26/2015, supervisor: Pablo Escobar, manages: hot foods, starting 1/1/2016)
• Bonnie Clyde (ssn: 111-22-3333, salary: $42,000, dept: sandwiches, date of hire: 4/7/2015, supervisor: Al Capone, manages: sandwich, starting 1/1/2016)

Helpful Hints:

Please do not assume that the database should be structured in the way this data is provided. Your database should be structured such that constraints such as “Each dept. has exactly one employee as a manager” can be enforced.

There should not be a separate table for Deli – the entire database represents the deli. There should be a table for each major entity mentioned in the specifications. Relations between entities (such as, who works in which department) should be their own tables. Primary keys for each table should be chosen to enforce constraints.

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

This demonstration is using MySQL Workbench.

Database :

/*create datbase*/
create database DeliDB;

/*use database to create tables*/
use DeliDB;

Tables :

1.Table Name :employee

SQL Query :

/*create table*/
create table employee(
ssn varchar(12) primary key,
name varchar(50) ,
Salary decimal(8,2),
dateofhire date);

/*Insert records */
insert into employee values ('134-56-8877','Jim Jones',28000,'2015/01/26');
insert into employee values ('138-56-8050','Rita Bita',32000,'2017/02/15');
insert into employee values ('334-55-8877','Holly Dew',29000,' 2016/01/15');
insert into employee values ('666-56-6666','Pablo Escobar',48000,' 2014/01/26');
insert into employee values ('888-91-8870','Al Capone',40000,' 2015/01/26');
insert into employee values ('111-22-3333','Bonnie Clyde',42000,' 2015/04/07');

/*selecting records*/
select * from employee;

Screen in MySQL Workbench :

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

2.Table Name :Department

SQL Query :

/*create table*/
create table Department(
DeptID int primary key,
DeptName varchar(20) not null,
managerssn varchar(20),
startingDate date,
foreign key (managerssn) references employee(ssn)
);

/*inserting records*/
insert into Department values (1,'hot foods','666-56-6666','2016/01/01')
insert into Department values (2,'sandwich','888-91-8870','2016/01/01')
insert into Department values (3,'snacks','138-56-8050','2018/03/18')
insert into Department values (4,'beverage','334-55-8877','2018/03/18')

/*selecting records*/
select * from Department;

Screen in MySQL Workbench :

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

3.Table Name :employeeDepartment

SQL Query :

/*create table*/
create table employeeDepartment(
ssn varchar(12),
supervisorSSN varchar(12),
DeptID int,
foreign key (ssn) references employee(ssn),
foreign key (supervisorSSN) references employee(ssn),
foreign key (DeptID) references department(DeptID)
);

/*inserting records*/
insert into employeeDepartment values ('134-56-8877','138-56-8050',1);
insert into employeeDepartment values ('138-56-8050',' 334-55-8877',4);
insert into employeeDepartment values ('334-55-8877',' 666-56-6666',1);
insert into employeeDepartment values ('666-56-6666','138-56-8050',3);
insert into employeeDepartment values ('888-91-8870','666-56-6666',1);
insert into employeeDepartment values ('111-22-3333','888-91-8870',2);

/*selecting records*/
select * from employeeDepartment;

Screen in MySQL Workbench :

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.