Based on the ERD below, display the assignment number, employee number and assigned hours for either assign job 501 or 502 with assigned hours more than 3. The image displays an Entity-Relationship Diagram (ERD) showcasing the relationships among four database tables: JOB, EMPLOYEE, ASSIGNMENT, and PROJECT.**Tables and Attributes:**1. **JOB Table** - **Primary Key:** - JOB_CODE - **Attributes:** - JOB_DESCRIPTION - JOB_CHG_HOUR - JOB_LAST_UPDATE2. **EMPLOYEE Table** - **Primary Key:** - EMP_NUM - **Attributes:** - EMP_LNAME - EMP_FNAME - EMP_INITIAL - EMP_HIREDATE - JOB_CODE (Foreign key referencing JOB table) - EMP_YEARS3. **ASSIGNMENT Table** - **Primary Key:** - ASSIGN_NUM - **Attributes:** - ASSIGN_DATE - PROJ_NUM (Foreign key referencing PROJECT table) - EMP_NUM (Foreign key referencing EMPLOYEE table) - ASSIGN_JOB - ASSIGN_CHG_HR - ASSIGN_HOURS - ASSIGN_CHARGE4. **PROJECT Table** - **Primary Key:** - PROJ_NUM - **Attributes:** - PROJ_NAME - PROJ_VALUE - PROJ_BALANCE - EMP_NUM (Foreign key referencing EMPLOYEE table)**Relationships:**- The JOB table is linked to the EMPLOYEE table through the JOB_CODE attribute, representing a one-to-many relationship (indicated by '1' and '∞').- The EMPLOYEE table connects to the ASSIGNMENT table using the EMP_NUM attribute, also in a one-to-many relationship.- The PROJECT table is associated with the ASSIGNMENT table through the PROJ_NUM attribute in a one-to-many relationship.- Additionally, the PROJECT table links back to the EMPLOYEE table with the EMP_NUM attribute, representing a potential one-to-many relationship.This ERD outlines a database structure suitable for managing job assignments, tracking project details, and organizing employee information effectively.

Here we are joining three tables ASSIGNMENT, EMPLOYEE, and JOB and the assigned job is either 501 or 502 and hours are more than 3. SQL statement is in the next step.Select ASSIGNMENT.ASSIGN_NUM, EMPLOYEE.EMP_NUM, ASSIGNMENT.ASSIGN_HOURS from ASSIGNMENT, EMPLOYEE, JOB WHERE JOB.JOB_CODE = EMPLOYEE.JOB_CODE AND EMPLOYEE.EMP_NUM = ASSIGNMENT.EMP_NUM AND ASSIGNMENT.ASSIGN_JOB = 501 OR ASSIGNMENT.ASSIGN_JOB = 502 AND ASSIGNMENT.ASSIGN_HOURS >3

Answered: Based on the ERD below, display the…

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

Concept explainers

Question

Based on the ERD below, display the assignment number, employee number and assigned hours for either assign job 501 or 502 with assigned hours more than 3.

The image displays an Entity-Relationship Diagram (ERD) showcasing the relationships among four database tables: JOB, EMPLOYEE, ASSIGNMENT, and PROJECT.

**Tables and Attributes:**

1. **JOB Table**
- **Primary Key:**
- JOB_CODE
- **Attributes:**
- JOB_DESCRIPTION
- JOB_CHG_HOUR
- JOB_LAST_UPDATE

2. **EMPLOYEE Table**
- **Primary Key:**
- EMP_NUM
- **Attributes:**
- EMP_LNAME
- EMP_FNAME
- EMP_INITIAL
- EMP_HIREDATE
- JOB_CODE (Foreign key referencing JOB table)
- EMP_YEARS

3. **ASSIGNMENT Table**
- **Primary Key:**
- ASSIGN_NUM
- **Attributes:**
- ASSIGN_DATE
- PROJ_NUM (Foreign key referencing PROJECT table)
- EMP_NUM (Foreign key referencing EMPLOYEE table)
- ASSIGN_JOB
- ASSIGN_CHG_HR
- ASSIGN_HOURS
- ASSIGN_CHARGE

4. **PROJECT Table**
- **Primary Key:**
- PROJ_NUM
- **Attributes:**
- PROJ_NAME
- PROJ_VALUE
- PROJ_BALANCE
- EMP_NUM (Foreign key referencing EMPLOYEE table)

**Relationships:**
- The JOB table is linked to the EMPLOYEE table through the JOB_CODE attribute, representing a one-to-many relationship (indicated by '1' and '∞').
- The EMPLOYEE table connects to the ASSIGNMENT table using the EMP_NUM attribute, also in a one-to-many relationship.
- The PROJECT table is associated with the ASSIGNMENT table through the PROJ_NUM attribute in a one-to-many relationship.
- Additionally, the PROJECT table links back to the EMPLOYEE table with the EMP_NUM attribute, representing a potential one-to-many relationship.

This ERD outlines a database structure suitable for managing job assignments, tracking project details, and organizing employee information effectively.

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Follow-up Questions

Read through expert solutions to related follow-up questions below.

Follow-up Question

Based on the ERD below, for each project calculate the difference between the project value and the project balance. Display the result in a new column name “Remaining Balance”. Include the project number and project name in the result output.

Solution

by Bartleby Expert

SEE SOLUTION

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