Question

Project Database Description After the requirement collection and analysis phase the software project of a database application about a company is described as follows: The company is organized into departments. Each department has an unique name, an unique number, and a particular employee who manages the department. We keep track of the start date when the employee began managing the dept. A dept may have multiple locations(cities). A dept controls a number of projects, each of which has a unique name, unique number, and single location. Each employee’s name, SSN, address, salary, sex, and DOB. An employee is assigned to one dept but may work on several projects, which are not necessary controlled by the same dept. We keep track of the number of hours per week that an employee works for each project. We also keep track of the direct supervisor of each employee. We want to keep track of the dependents of each employee for insurance purpose. We keep each dependent’s first name, sex, DOB, and relationship to the employee. ==================================================================== Using the information above, build the following reports using SQL: Build 5 views for the database. 1. Names of current projects (being worked on 2/28/2020). Please use the latest pay date you entered as the current date. In the suggested input that was 2/28/2020. 2. Names of employees without any dependents 3. Names of departments with current projects 4. Names of departments with no current projects 5. List the projects associated with each department that is worked on in 2020. 6. For these 5 views, select * from the view and turn in the first 5 rows of the output (please label each view as the statement for that view number. Build the following reports from SQL statements: Dependent names associated with each employee Number of hours worked on a project for each department List of departments with their locations. List of departments with their projects List of employees for each direct supervisor Number of hours worked on each project for each employee Number of hours worked on each project List the department with the most projects List the employee assigned to the most projects List the project with the most hours

Answer 1

/* Query to Create Table EMPLOYEE */
Create table EMPLOYEE(Name varchar(40), SSN int primary key, Address varchar(60), Salary decimal(9,2), Sex varchar(7), DOB date);
/* Query to Insert records into Table EMPLOYEE */
Insert into EMPLOYEE(Name, Ssn, DOB, Address, Sex, Salary)
Values('Tom Hanks',1,'1978/12/21','10 Downing Street','Male',2000.49), ('Mary Lucy',2,'1988/02/02','Town Centre','Female',3500.45),
('John Paul',3,'1978/11/21','10 Downing Street','Male',2040.49), ('Mac Qui',4,'1998/02/02','Town Centre','Male',5500.45),
('Jack Ryan',5,'1979/12/21','10 Downing Street','Male',2550.49), ('Monika Roy',6,'1994/09/13','Town Centre','Female',8500.45),
('Bobby Lee',7,'1974/09/13','Town Centre','Male',10500.45), ('Max Well',8,'1984/09/13','Town Centre','Male',9500.45);

/* Query to Create Table DEPARTMENT */
Create table DEPARTMENT(Dname varchar(30), Dnumber int primary key, Mgr_ssn int Foreign Key References EMPLOYEE(SSN), Mgr_start_date date);
/* Query to Insert records into Table DEPARTMENT */
Insert into DEPARTMENT(Dname, Dnumber, Mgr_ssn, Mgr_start_date)
Values('Space Science',1,7,'2017/12/21'), ('Science',2,8,'2017/12/21'), ('Social',3,7,'2017/12/21'), ('Bio-Technology',4,7,'2017/12/21'), ('Rocket Science',5,8,'2017/12/21'),
('Mathematics',6,7,'2018/12/31');

/* Query to Create Table DEPARTMENT_LOCATIONS */
Create Table DEPARTMENT_LOCATIONS(Dnumber int Foreign Key References DEPARTMENT(Dnumber), Department_location varchar(20));
/* Query to Insert records into Table DEPARTMENT_LOCATIONS */
Insert into DEPARTMENT_LOCATIONS(Dnumber, Department_location)
Values(1,'Burnaby'), (2,'Mexico'), (2,'Burnaby'), (3,'Cuba'), (4,'LA'), (4,'Cuba'),(5,'California'), (6,'LA');

/* Query to Create Table PROJECT */
Create Table PROJECT(Pname varchar(40), Project_number int primary key, Project_location varchar(30), Department_num int Foreign Key References DEPARTMENT(Dnumber),
Start_Date date, End_Date date);
/* Query to Insert records into Table PROJECT */
Insert into PROJECT(Pname, Project_number, Project_location, Department_num,Start_Date, End_Date)
Values('Mars Expedition',1,'Burnaby',1,'2019/12/31','2021/12/31'), ('Venus Expedition',2,'Burnaby',1,'2019/12/31','2020/07/31'), ('Water pH',3,'Burnaby',2,'2020/01/31','2021/12/31'),
('Akbar invasion',4,'Burnaby',3,'2019/01/01','2019/12/31'), ('SpaceX',5,'Mexico',5,'2018/12/31','2019/12/31'),('Bio Fertilizerz',6,'Burnaby',4,'2016/12/31','2019/12/31'),
('Bio Wastes',7,'Cuba',4,'2019/12/31','2020/01/01'), ('Rocket Sensors',8,'Burnaby',5,'2017/12/31','2018/12/31'), ('Rocket Cooants',9,'LA',5,'2019/02/11','2019/12/31');

/* Query to Create Table WORKS_ON */
Create Table WORKS_ON(Ssn int Foreign Key References EMPLOYEE(SSN), ProjectNo int Foreign Key References PROJECT(Project_number), Hours_per_week decimal(5,2));
/* Query to Insert records into Table WORKS_ON */
Insert into WORKS_ON(Ssn, ProjectNo, Hours_per_week)
Values(1,1,10),(1,2,10),(1,3,10),(1,4,10),(1,5,10),(1,6,10),(1,7,10),(1,8,10),(1,9,10),(3,2,50),(4,2,145),(5,8,60),(6,6,45),(6,5,20),(4,5,20);

/* Query to Create Table DEPENDENTS */
Create Table DEPENDENTS(SSN int Foreign Key References EMPLOYEE(SSN), FName varchar(20), Sex varchar(7), DOB date, Relation varchar(20));
/* Query to Insert records into Table DEPENDENTS */
Insert into DEPENDENTS(SSN, FName, Sex, DOB, Relation)
Values (1,'Shan','Male','2000/12/21','Son'),(2,'Christina','Female','1979/12/14','Spouse'), (3,'Jack','Male','1960/01/21','Father'), (4,'Mary','Female','1960/05/31','Mother'),
(4,'Shan','Male','1969/01/21','Father'),(5,'Jennifer','Female','1991/12/14','Daughter'), (5,'Sia','Female','1981/01/21','Spouse'), (6,'Chris','Male','1960/05/31','Father'),
(7,'George','Male','1959/12/21','Father'),(7,'Kristina','Female','1959/12/14','Mother'), (7,'Tom','Male','1988/01/21','Son'), (7,'Jenny','Female','1960/05/31','Spouse');

/* 1 */
Create View View_1
As
Select distinct a.Pname from PROJECT a, WORKS_ON b Where a.Project_number = b.ProjectNo
And End_Date > '2020/02/28';
/* or */
Create View View_1
As
Select distinct a.Pname from PROJECT a, WORKS_ON b Where a.Project_number = b.ProjectNo
And End_Date > '2020/06/30';
/* or */
Create View View_1
As
Select distinct a.Pname from PROJECT a, WORKS_ON b Where a.Project_number = b.ProjectNo
And End_Date > GETDATE();

/* 1 */ Create View View 1 As Select distinct a.Pname from PROJECT a, WORKS_ON b where a Project_number And End_Date > '2020/02/28'; /* or */ Create View View 1 b. ProjectNo As b. ProjectNo Select distinct a.Pname from PROJECT a, WORKS_ON b Where a.Project_number And End_Date > '2020/06/30'; /* or */ Create View View 1 As Select distinct a.Pname from PROJECT a, WORKS_ON b Where a. Project_number = b.ProjectNo And End_Date > GETDATE(); 100 % Messages Command(s) completed successfully.

/* 2 */
Create View View_2
As
Select Name as Empoyee_Name from EMPLOYEE Where SSN Not In (Select distinct SSN from DEPENDENTS);

/* 3 */
Create View View_3
As
Select Distinct a.Dname from DEPARTMENT a, PROJECT b Where a.Dnumber = b.Department_num;

/* 2 */ Create View View 2 As Select Name as Empoyee_Name from EMPLOYEE Where SSN Not In (Select distinct SSN from DEPENDENTS); 100 % Messages Command(s) completed successfully.

/* 3 */ Create View View 3 As Select Distinct a. Dname from DEPARTMENT a, PROJECT b Where a. Dnumber = b. Department_num; 100 % Messages Command(s) completed successfully.

/* 4 */
/* Latest Paydate as of today is 2020/06/30 */
Create View View_4
As
Select distinct a.Dname FROM DEPARTMENT a, PROJECT b where a.Dnumber = b.Department_num
And b.End_Date >= '2020/06/30';

/* 4 */ Create View View 4 As SELECT a. Dname FROM DEPARTMENT a|| WHERE NOT EXISTS SELECT NULL FROM PROJECT b WHERE b. Department_num = a. Dnumber); 100 % Messages Command(s) completed successfully.

/* 5 */
Create View View_5
As
Select distinct a.Pname from PROJECT a, WORKS_ON b where a.Project_number = b.ProjectNo
And (a.Start_Date between '2020/01/01' And '2020/12/31') Or (a.End_Date between '2020/01/01' And '2020/12/31');

/* 6 */
Select Top(5) * from View_1;
Select Top(5) * from View_2;
Select Top(5) * from View_3;
Select Top(5) * from View_4;
Select Top(5) * from View_5;

/* 5 */ Create View View 5 As Select distinct a.Pname from PROJECT a, WORKS_ON b where a Project_number = b.ProjectNo And (a. Start_Date between '2020/01/01' And '2020/12/31') or (a. End_Date between '2020/01/01' And '2020/12/31'); 100 % Messages Command(s) completed successfully.

/* 6*/ Select Top (5) from View_1; Select Top (5) * from View_2; Select Top (5) * from View_3;|| Databa Select Top (5) * from View_4; Select Top (5) * from View_5; 100 % BE Results Messages Pname Mars Expedition 2 Venus Expedition 3 Water pH 1 Empoyee_Name Max Well 1 2 Dname Bio-Technology Rocket Science Science Social Space Science 3 4 5 1 Dname Science Space Science 2 1 Pname Bio Wastes Venus Expedition Water pH 2 3