Create skill table and create tables that will link employee

Assignment Help PL-SQL Programming

Reference no: EM131917380

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.

Reference no: EM131917380

Questions Cloud

Should dimercurio be required to pursue claim : From time to time courts are called on to determine the enforceability of an arbitration clause contained in a contract. One such case was DiMercurio v. Sphere.

How do you think companies should respond to accusations : What do you think management at Renault should have done about the anonymous accusation about a senior official negotiating a bribe?

Discuss the foreign sovereign immunities act : Zedan received a telephone call from a Saudi Arabian organization offering him an engineering position at a construction project in Saudi Arabia.

Expectations of trader about the future price of stock : what are the expectations of the trader about the future price of the stock if this strategy is implemented?

Create skill table and create tables that will link employee : Create SKILL table and create tables that will link employees to their skills. Make sure to include appropriate primary and foreign keys.

Did the act of state doctrine apply in the case : Did the act of state doctrine apply in this case? If not, what should be the result in an American court? Explain. See Kalmich v. Bruno, 450 F.Supp. 227.

Discuss the product liability laws : Should a Mexican citizen who bought a Chrysler vehicle in Mexico be allowed to sue the manufacturer in a U.S. court under U.S. product liability laws

Write how we can have business relationship with japan : Describe the reasons why you want to learn Japanese language in Japan. Write how we can have business relationship with Japan.

Create an error bar chart for each variable by gender : Topic: SPSS Data Analysis and Testing. Create an error bar chart for each variable by gender. Is the graph consistent with the result from the t test

User Account

All Pages