Consider the following database design: Also consider the virtual table ASSIGNMENTS, which doesn't meet the third normal form but is created by:CREATE VIEW ASSIGNMENT ASSELECT ASSIGN_NUM, ASSIGN_DATE, PROJ_NUM, EMPLOYEE.EMP_NUM, JOB.JOB_CODE AS ASSIGN_JOB, JOB_CHG_HOUR AS ASSIGN_CHG_HR, ASSIGN_HOURS, JOB_CHG_HOUR *ASSIGN_HOURS AS ASSIGN_CHARGEFROM ASSIGN, EMPLOYEE, JOBWHERE ASSIGN.EMP_NUM = EMPLOYEE.EMP_NUM ANDEMPLOYEE.JOB_CODE = JOB.JOB_CODE; In SQL design the following queries for information search:Create and run a query to list employees who were hired before the year 1995.Create and run a query to list job code, job description, and employee last name, first name sorted by job description and employee last name.Create and run a query to list the name of employees who were assigned to the Rolling Tide project (which is one of the entries in the column PROJ_NAME).Create and run a query to list the project name, the number of employees worked for the project, and the total hours and total charges assigned to each project sorted by project name. PROJECTod9 PROJ_NUMΕMPLOYEE.* EMP_NUMEMP_LNAMEPROJ_NAMEEMP_FNAMEPROJ_VALUEEMP_INITIALPROJ_BALANCEEMP_HIREDATEEMP_NUMJOB_CODEASSIGN* ASSIGN_NUMJOBASSIGN_DATE* JOB_CODEPROJ_NUMJOB_DESCRIPTIONEMP_NUMJOB_CHG HOURASSIGN_HOURSJOB_LAST_UPDATE

Structured Query Language : SQL represents Structured Query Language SQL allows you to get to and…

Answered: Consider the following database design:…

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Question

Consider the following database design:

Also consider the virtual table ASSIGNMENTS, which doesn't meet the third normal form but is created by:

CREATE VIEW ASSIGNMENT AS

SELECT ASSIGN_NUM, ASSIGN_DATE, PROJ_NUM, EMPLOYEE.EMP_NUM, JOB.JOB_CODE AS ASSIGN_JOB, JOB_CHG_HOUR AS ASSIGN_CHG_HR, ASSIGN_HOURS, JOB_CHG_HOUR *ASSIGN_HOURS AS ASSIGN_CHARGE

FROM ASSIGN, EMPLOYEE, JOB

WHERE ASSIGN.EMP_NUM = EMPLOYEE.EMP_NUM AND

EMPLOYEE.JOB_CODE = JOB.JOB_CODE;

In SQL design the following queries for information search:

Create and run a query to list employees who were hired before the year 1995.
Create and run a query to list job code, job description, and employee last name, first name sorted by job description and employee last name.
Create and run a query to list the name of employees who were assigned to the Rolling Tide project (which is one of the entries in the column PROJ_NAME).
Create and run a query to list the project name, the number of employees worked for the project, and the total hours and total charges assigned to each project sorted by project name.

PROJECT
od9 PROJ_NUM
ΕMPLOYEE.
* EMP_NUM
EMP_LNAME
PROJ_NAME
EMP_FNAME
PROJ_VALUE
EMP_INITIAL
PROJ_BALANCE
EMP_HIREDATE
EMP_NUM
JOB_CODE
ASSIGN
* ASSIGN_NUM
JOB
ASSIGN_DATE
* JOB_CODE
PROJ_NUM
JOB_DESCRIPTION
EMP_NUM
JOB_CHG HOUR
ASSIGN_HOURS
JOB_LAST_UPDATE

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 5 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.

Similar questions