Question: TASK 1 (DOCUMENTATION) The relational schema of a database below is about employees, their departments and the projects they work on: DEPARTMENT DeptNo, DeptName, Location,

TASK 1 (DOCUMENTATION) The relational schema of a database below is about employees, their departments and the projects they work on: DEPARTMENT DeptNo, DeptName, Location, NumOfStaff EMPLOYEE EmpNo, EmpName, Salary, DeptNo PROJECT Projno, ProjName, ProjType, Budget WORKS EmpNo, ProjNo, Role [Primary key is underlined, foreign key is dotted underlined) Based on the relational schema, you are required to produce the following: 1) Create a Database named Staff Information, or any suitable name. 2) Create all relevant tables as required. Choose appropriate data types for each attribute. Indicate the primary key and foreign key in each table created. 3) Produce sample records for each table that observes the Referential Integrity Constraint. Minimum of FIVE records per table are enough. The data inserted must satisfy the following constraint: there are at least 3 departments and each department have at least 2 employees each employee only works in one department and can be assigned to more than one project. make sure you create the relationships between tables. Write SQL query and its result based on the records added earlier for the following tasks: (one query per question) a) Get the names of employees from department "Information Technology" who are working on the Database" project. b) Get the names of all projects worked on by the employee named "Atuff Hady". c) Get the projects name that have highest budget. d) Get the names of department that have less than 4 employees in it. e) Get the names and salaries of employees in the "Logistic" department who has a higher salary than the maximum salary in the "Accounting department. f) Update the salary of the staffs in sales department so it would match the average salary of the staffs in HR department. g) Modify the WORKS table by adding in new column entry for duration required to complete the project. Create ONE Form to handle the data entry for assignment project to each employee. The form must be able to enter and display data
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts
