The following table is a sample of the data from a company, that store information about various stores and the employees that work on these stores. The data are as follows:
You can make the following assumptions:
Each store has one manager
Each employee has one supervisor
Many employees are assigned to each store. The attribute Hours tells the number of hours each employee has worked on the corresponding store per week and the attribute rating is the rating that this employee received for his work on this store.
Revenue and opening date refer to the store
***Based on this information:
Is this relation in 1NF? If not create the 1NF. If yes explain why.
Write down the functional dependencies
Use the functional dependencies to explain what the candidate key is.
Transform to 2NF
Transform to 3NF
Part 02
Create the DDL scrips for the 3NF schema that you just developed. This means that you need to write the SQL statements that will create the tables on oracle database.
You should also create for each table primary keys and foreign keys.
Now implement the 3NF relational schema on oracle database using the DDL scripts.
Insert the sample data.
Part 03
Write an SQL query to select the data that are shown in the table of part 01.
Part 04
Update the employee records and give a 1000$ increase to the employees of department 10.
Insert a new column that will hold the store square footage
Update this column to set the values to:
PA_Sore: 3000
NJ_Store: 2000
Change the name of the column that holds the employee id to ‘EMP_NUMBER’
Delete PA_Store
Truncate the table that holds the manager information.
Drop the column that holds the employee ratings.
Insert a new employee with and empty salary (Choose values for the rest of the columns)
Drop the table that holds the manager information.
Deliverables:
Upload on blackboard a Word document report including: