Assignment
Builders_r_us.com is happy with your efforts and wants to extend your contract. THEY WANT YOU TO INCPRPORATE THE FOLOWING INFORMATION IN YOUR DATABASE
In addition to the current three tables, EMPLOYEE, ASSIGNMENT and PROJECT, they have one additional table SKILL that needs to be integrated in the database.
SKILL (skill_id, skill_description)
Skill currently desired by Builder_r_us are shown in table 1.
In addition they want to link skills to employees as shown in table 2.
Following tables (from assignment 1)
Following tables are from assignment 1
EMPLOYEE (E_Number, Name, Department, age)
ASSIGNMENT (E_Number, P_Number, Hrs_worked )
WHERE:
HRS_WORKED are total hours an employee worked on that project, for example for first row in the table assignment, employee number 1 worked
20 hours on project 1000, et.c
PROJECT(P_Number, Project_name, start_date, end_date, Project_Manager)
Table 1: Desired skill set
|
Skill_ID
|
Skill-Desc
|
|
1
|
Engineering-electronic
|
|
2
|
Database
|
|
3
|
BI
|
|
4
|
HR
|
|
5
|
Project Manager
|
|
6
|
Accounting
|
|
7
|
Finance
|
|
8
|
Networking
|
|
9
|
Supervisory
|
|
10
|
security
|
|
11
|
Ethics
|
|
12
|
Forensics
|
|
13
|
Systems
|
|
14
|
Marketing
|
|
15
|
Engineering-mechanical
|
|
16.
|
Design
|
Table 2: employees and their skills
|
Name
|
Skills
|
|
Harry, T
|
Database, Finance, Ethics
|
|
Sally, P
|
BI,HR
|
|
Lio, L
|
HR
|
|
Patel, P
|
HR, Project Manager,BI
|
|
Roner,K
|
Security, Forensics, Marketing, Systems
|
|
Jackson, O
|
Engineering-mechanical, Security, Design
|
|
Harry, L
|
Supervisory, Design
|
|
Paner, K
|
BI, Design
|
|
Dan, U
|
Forensics
|
You are to perform the following using ORACLE available at UB:
PART A: create tables and load data
a) Create SKILL table and create tables that will link employees to their skills. Make sure to include appropriate primary and foreign keys. You can use e_number to link employees to skills.
Part B Provide Table structure
Part C: Provide table contents
Part D: Develop queries in ORACLE and provide its output
All queries MUST be based SOLELY on the information provided and each question must use a SINGLE query..no views or separate queries, unless otherwise stated.
As always all queries should be data independent.
c) Answer the following queries in SQL:
1. Give the names of manager of employee Jackson, O
2. Builders_r_us has a policy of having at least one person with skill set listed in table1. List names of skills that builder needs to hire. (hint: think in terms of UNION, MINUS or INTERSECT)
3. Give the count of employees that have exactly three skills.
4. Builders_r_us is getting ready to start Big Data project and it requires HR, Forensics and security skills. List the name of employee that have either of these skills.
5. Give the names of employees that are working more than 100 hours (total).
6. Give the names of employees who have both HR AND BI skills. (note: it is NOT either/Or but AND) (hint: think of UNION, INTERSECT, MINUS)
7. Give the count of employees that do not have any skills.
8. Give the names of projects and their duration. Do not include projects that have not finished or started
9. Get the employee number of employees with skills similar to Sally's skills
10. Give the names of employees who worked more than 40 total hours (irrespective of project type)
Hint: create a view.
Give the name of project of minimum duration
PART E:
Draw one complete ERD of all entities from assignment 1 and 2.
You are to submit the following:
1. Part A:
a. Proper analysis to add SKILL (i.e, what tables are needed)
b. Create Statements with appropriate PK and FKs for each table
(can submit in word: with table create output from ORACLE)
2. Part B:
a. Write queries to provide table structure and queries output
3. Part C:
a. Write queries to provide table contents and queries output
4. Part D:
a. Each query/and its output in SQL (1 thru 10) and its result
5. Part E:
a. Draw a single ERD Diagram of ALL entities from assignment 1 and 2.