Database Principle and Application - 8=========================There is a Company Database that manages the data of employee, department and project. The relational schemas are as follows:EMPLOYEE (Fname, Lname, Employee_id, Gender, Salary, DNO). The attribute means: First name, Last name, employee id, gender, salary, the department number in which the employee works. Each employee has a unique employee id, and can join in projects that in different department.DEPARTMENT (DNO, Dname, Manager_id, Dlocation). The attribute means: Department number, department name, department manager id, department location. The department manager is a type of the employee, thus the Manager_id is a foreign key that references the Employee_id in EMPLOYEE table.PROJECT (Project_NO, Pname, DNO). The attribute means: project number, project name, department number of the department that the project belongs to.Part of the data in the relations are as follows：Table1 EMPLOYEEFnameLnameEmployee_idGenderSalaryDNOJohnSmith20182101Male400002FranklinWong20182102Male500001JenniferWallace20183101Female350003JoyceClinton20183102Female280003PeterBush20184101Male330004 Table 2 DEPARTMENTDNODnameManager_idDlocation1Research20181105Stanford2Technology20182102Houston3Service20183101Houston4Administration20184101Stanford Table 3 PROJECTProject_NOPnameDNO1001TransportationMap11002TwitterFriendship11003PowerSystem12001Python22002Parallel23001Customer3Based on the above relational schema, please write the SQL scripts to do the question - 5. Notes: you do not need to give the query results. You only need to give the query script or the relational algebra expression.Question:5. Create a view E_INFO, which contains the employee name, employee id, gender, salary and the department names where he/she works.

View in SQL: In SQL, a view is considered as a virtual table that is designed with attributes of one…

Answered: Question: 5. Create a view E_INFO,…

Database Systems: Design, Implementation, & Management

12th Edition

ISBN:9781305627482

Author:Carlos Coronel, Steven Morris

Publisher:Carlos Coronel, Steven Morris

Chapter9: Database Design

Section: Chapter Questions

Problem 7P

See similar textbooks

Related questions

Concept explainers

Question

Database Principle and Application - 8

=========================

There is a Company Database that manages the data of employee, department and project. The relational schemas are as follows:

EMPLOYEE (Fname, Lname, Employee_id, Gender, Salary, DNO). The attribute means: First name, Last name, employee id, gender, salary, the department number in which the employee works. Each employee has a unique employee id, and can join in projects that in different department.

DEPARTMENT (DNO, Dname, Manager_id, Dlocation). The attribute means: Department number, department name, department manager id, department location. The department manager is a type of the employee, thus the Manager_id is a foreign key that references the Employee_id in EMPLOYEE table.

PROJECT (Project_NO, Pname, DNO). The attribute means: project number, project name, department number of the department that the project belongs to.

Part of the data in the relations are as follows：

Table1 EMPLOYEE

Fname	Lname	Employee_id	Gender	Salary	DNO
John	Smith	20182101	Male	40000	2
Franklin	Wong	20182102	Male	50000	1
Jennifer	Wallace	20183101	Female	35000	3
Joyce	Clinton	20183102	Female	28000	3
Peter	Bush	20184101	Male	33000	4

Table 2 DEPARTMENT

DNO	Dname	Manager_id	Dlocation
1	Research	20181105	Stanford
2	Technology	20182102	Houston
3	Service	20183101	Houston
4	Administration	20184101	Stanford

Table 3 PROJECT

Project_NO	Pname	DNO
1001	TransportationMap	1
1002	TwitterFriendship	1
1003	PowerSystem	1
2001	Python	2
2002	Parallel	2
3001	Customer	3

Based on the above relational schema, please write the SQL scripts to do the question - 5. Notes: you do not need to give the query results. You only need to give the query script or the relational algebra expression.

Question:

5. Create a view E_INFO, which contains the employee name, employee id, gender, salary and the department names where he/she works.

Expert Solution

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Question: 5. Create a view E_INFO, which contains the employee name, employee id, gender, salary and the department names where he/she works.