Question

Assignment 1

Consider the following relational schema:
Employee(eid: integer, ename: string, dateofbirth: date, salary: real)
Works(eid: integer, pid: integer, pct time: integer)
Project(pid: integer, budget: real, managerid: integer, duration: integer, city: string)
The attributes of the relation employee are straightforward. The relation Works tells which employee works on which project and also the percentage of the employee time allocated to the project. The relation Project list all the projects, their budgets, the managers, the main city where the project is located and the duration in months of the project.
I)Query Processing and Optimization
-Write every query in SQL
-Discuss indexes to be created to make the execution of the query run faster.
-Execute the query before and after creating the right indexes and
discuss any difference in the order of the query results
1. List all employees that make more than $100,000
2. Find all employees who work on a project that has been going on for more than 17 months as managers.
3. List the pairs of employee names who work on the same project.
4. List Employees whose projects are all located in Newark
5. List Employees whose projects are all located in the same city.

II)Write integrity constraints (domain, key, foreign key, or CHECK constraints; or assertions) or triggers to ensure each of the following requirements, considered independently.

1.Employees must make a minimum salary of $50000.
2.An employee must work on at least 1 project
3. Every manager of a project must be an employee assigned to that project
4. The budget of a project is computed as the total salary of the employees working on the project calculated proportionally to the percentage of the time the employee spends on the project. 20% of the total salary is added to the budget as management expenses.
5. The budget has to be updated each time an employee is added and dropped from a project, and also each time the salary of an employee changes.

Solution Preview

This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.

CREATE TABLE EMPLOYEE
(
EID NUMBER NOT NULL
, ENAME VARCHAR2(60)
, DATEOFBIRTH DATE
, SALARY NUMBER
, CONSTRAINT EMPLOYEE_PK PRIMARY KEY
(
    EID
)
   
);

CREATE TABLE PROJECT
(
PID NUMBER NOT NULL
, BUDGET FLOAT DEFAULT 0
, MANAGERID NUMBER
, DURATION NUMBER
, CITY VARCHAR2(60)
, CONSTRAINT PROJECT_PK PRIMARY KEY
(
    PID
)   
);

ALTER TABLE PROJECT
ADD CONSTRAINT PROJECT_FK1 FOREIGN KEY
(
MANAGERID
)
REFERENCES EMPLOYEE
(
EID
)
;

CREATE TABLE WORKS
(
EID NUMBER NOT NULL
, PID NUMBER NOT NULL
, PCT_TIME NUMBER
, CONSTRAINT WORKS_PK PRIMARY KEY
(
    EID
, PID
)   
);...

This is only a preview of the solution. Please use the purchase button to see the entire solution

Assisting Tutor

Related Homework Solutions

Creating Databases Using MS Access
Homework Solution
$20.00
Databases
MS Access
Business
Management
Employees
Department
Relationships
Primary Key
Foreign Key
Tables
UML Class Diagram
Database Development Questions
Homework Solution
$40.00
Computer Science
Database Development
SQL Queries
Models
Tables
Relational Calculus
Relational Algebra
Flights
Employees
Airline Information
Foreign Key
Primary Key
Constraints
Unsafe Queries
Data Mining and Warehouse Assignment
Homework Solution
$20.00
Flight
Data
Warehouse
OLAP
Dimension
Fact
Table
Cube
Operation
Business
Time
Arrival
Star
Schema
Doctor
Patient
SQL
Query
Relational
Database
Departure
Traveler
Roll-up
Select
Movie Database Project Using SQL & Java
Homework Solution
$240.00
Java
Programming
Codes
Algorithms
Computer Science
Statements
Variables
Loops
Input
Output
Integers
Strings
Database Management
SQL
Movies
Advertisements
Queries
File Management
Operations
Command Line
JDBC
Questions about Oracle 11g
Homework Solution
$18.00
Computer Science
Oracle 11g
Database
System Privilege
Block Size
SQL
Tunning
SYSAUX
PFILE
SPFILE
Get help from a qualified tutor
Live Chats