Following SQL queries are codes to create database 1

create table employee(

ssn varchar(12),

name varchar(20),

salary decimal(8,2),

date_of_hire date,

primary key (ssn));

insert into employee values ('134-56-8877','Jim Jones',28000,'2015/01/26'),

'138-56-8050','Rita Bita',32000,'2017/02/15'),

('334-55-8877','Holly Dew',29000,' 2016/01/15'),

('666-56-6666','Pablo Escobar',48000,' 2014/01/26'),

('888-91-8870','Al Capone',40000,' 2015/01/26'),

('111-22-3333','Bonnie Clyde',42000,' 2015/04/07');

select * from employee

create table department(

dept_name varchar(20) not null,

manager_ssn varchar(20),

starting_date date,

foreign key (manager_ssn) references employee(ssn),

primary key (dept_name));

insert into department values

('hot foods','888-91-8870','2016/01/01'),

('sandwich','111-22-3333','2016/01/01'),

('snacks','666-56-6666','2018/03/18'),

('beverage','138-56-8050','2018/03/18');

select * from department

create table employee_department (

ssn char(12),

supervisor_ssn char(12),

dept_working varchar(20),

foreign key (supervisor_ssn) references employee(ssn),

foreign key (dept_working) references department(dept_name),

foreign key (ssn) references employee(ssn))

insert into employee_department values ('134-56-8877','138-56-8050','hot foods'),

('138-56-8050','334-55-8877','beverage'),

('334-55-8877','666-56-6666','sandwich'),

('666-56-6666','138-56-8050','snacks'),

('888-91-8870','666-56-6666','hot foods'),

('111-22-3333','888-91-8870','sandwich');

select * from employee_department

Questions to solve by SQL:

All managers get 30% discount on any item purchased from the deli, all supervisors get 10% discount, and all other employees get 5% discount. So, if the database is modified to reflect this business rule, there would be a functional dependency from Status (Manager or Supervisor or Employee) to Discount Level.

1. Modify database 1 (or a revised version of it) to encode information about who is eligible for what type of discount so that your revised database design has mechanisms for enforcing this functionality.
2. Explain how your database design enforces this functional dependency (and the business rule that it represents) when a new employee (whatever their status) is added to the database? (If your revised database design does not have mechanisms for enforcing this functional dependency, then redo your database design so that it does have this feature.)
3. Is your database in 3NF? Justify your answer.
4. If your database is not in 3NF, then modify it so that it is in 3NF. Justify your claim that your modified database is in 3NF.
5. Does the database of step 5 enforce this functional dependency (and the business rule that it represents) when a new employee (whatever their status) is added to the database? Explain how.
6. On the database of step 5 create a view that shows for each employee their name, department, supervisor, and discount level and no other fields. Name this view "emp_discount".
7. Use "emp_discount" to write a query which outputs for each department, and the average discount for all members of the department.