BROOKHAVEN COLLEGE CS 1227 - Database Design - Assignment 3
Course Cs 1227-
Pages 3

Unformatted text preview:

Database Design - Assignment 3Due Date: March 21, 2022Part-11. Create the tables given in following schema. Define required constraints (primary keys, foreign keys) on given tables. Define triggered actions that will be attached to each foreign key constraint. Note: Oracle does not support “ON UPDATE”, so you only define ON DELETE. ON DELETE options supported by Oracle are “cascade” and “set null”.* Assume we add one additional attribute to BOOK_LOANS table with name “Return_date”. When a book is borrowed, Return_date takes initial value of NULL.Part-21. Write following queries in SQL. a. For each department whose average employee salary is more than $30,000, retrieve the department name and the number of employees working for that department. b. Same as a, except output the number of male employees instead of the number of employees. c. Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees. d. Retrieve the names of employees who make at least $10,000 more than the employee who is paid the least in the company. e. Retrieve the names of employees who is making least in their departments and have more than one dependent. (solve using correlated nested queries)2. Specify following views in SQL. a. A view that has the department name, manager name and manager salary for every department. b. A view that has the department name, its manager's name, number of employees working in that department, and the number of projects controlled by that department (for each department). c. A view that has the project name, controlling department name, number of employees working on the project, and the total hours per week they work on the project (for each project). d. A view that has the project name, controlling department name, number of employees, and total hours worked per week on the project for each project with more than one employee working on it. e. A view that has the employee name, employee salary, department that the employee works in, department manager name, manager salary, and average salary for the department. (Hint: Find average salary for the department by using correlated nested query. You can have correlated nested queries in SELECT clause.)Part-1 is 40 points. Part-2 is 60 points. Each question in Part-2 is 6 points.Deliverables: sql or .pdf file that includes all SQL statements for Part-1 and


View Full Document

BROOKHAVEN COLLEGE CS 1227 - Database Design - Assignment 3

Course: Cs 1227-
Pages: 3
Download Database Design - Assignment 3
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Database Design - Assignment 3 and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Database Design - Assignment 3 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?