Scribd
Upload
5 views2 pages

DS Assignment

The document outlines a COMPANY schema with tables for EMPLOYEE, DEPARTMENT, DEPARTMENT-LOCATIONS, PROJECT, WORKS-ON, and DEPENDENT, detailing their attributes and relationships. It includes instructions for creating tables with primary and foreign key constraints, adding integrity constraints related to company start date and project locations, and answering various queries about employee and project data. Specific queries include listing employees with their managers, counting hours worked on projects, and retrieving information about employees and their dependents.

Uploaded by

kumarbackupyt
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
5 views2 pages

DS Assignment

The document outlines a COMPANY schema with tables for EMPLOYEE, DEPARTMENT, DEPARTMENT-LOCATIONS, PROJECT, WORKS-ON, and DEPENDENT, detailing their attributes and relationships. It includes instructions for creating tables with primary and foreign key constraints, adding integrity constraints related to company start date and project locations, and answering various queries about employee and project data. Specific queries include listing employees with their managers, counting hours worked on projects, and retrieving information about employees and their dependents.

Uploaded by

kumarbackupyt
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Consider the following COMPANY schema with following tables.

EMPLOYEE
Name Ssn Birthdate Address Sex Salary Super_ssn Dnumber

DEPARTMENT
Dname Dnumber Mgr_ssn Mgr-start-date

DEPARTMENT-LOCATIONS

Dnumber Dlocation

PROJECT

Pname Pnumber Plocation Dnumber

WORKS-ON

Ssn Pnumber Hours

DEPENDENT

Ssn Dependent-name Sex Birthdate Relationship

Ssn – refers to the unique id assigned to every employee.

Super_ssn – refers to the ssn number of supervisor.

Mgr_ssn – refers to the department manager’s ssn number.

Mgr-start-date - refers to date from which mgr_ssn started working as a manager in that
department.

Dnumber in Project relation – refers to the controlling department of the project.

DEPARTMENT-LOCATIONS – holds the various locations, where the department is


functioning.

WORKS-ON – records information about how many hours an employee spends on a


particular project.

DEPENDENT – records information about dependents of an employee.


1) Create the tables with primary and foreign key constraints
2) Add the following integrity constraints
a. Company was started on 15-August-2015. Add constraint to verify this condition
in the schema.
b. Project and Department locations should be strictly one of the following: Chennai
or Bangalore or Hyderabad. Add constraint to verify this.
3) Answer the following queries
a. List the names of all employees long with their managers.
b. Retrieve the number of employees and total number of hours spent for each project.
c. For the departments located in Chennai, list the department name along with its
current manager’s name.
d. Find the number of projects each employee works on.
e. List the names of all female employees in department 5 who work more than 10 hours
per week on “SBI” project.
Retrieve the names of employees who have no dependents.
b. List the names of managers who have at least one dependent.
c. Retrieve the name of each employee who works on all the projects controlled by
department number 5.
d. Display the employee’s name who is withdrawing second maximum salary.
e. For each project, retrieve the project number, the project name, and the number of
employees who work on that project.
f. For each project on which more than two employees work, retrieve the project
number, the project name, and the number of employees who work on the project.
g. 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.
h. Retrieve the names of all employees whose supervisor’s supervisor has ‘888665555’
for Ssn.

You might also like