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

$50.00

or free if you
register a new account!

Assisting Tutor

Related Homework Solutions

SQL Server for Business Intelligence
Homework Solution
$20.00
Computer Science
Database Development
SQL
Server
Business
Intelligence
Multidimensional
Tabular
Analysis
Services
Warehouse
MOLAP
HOLAP
ROLAP
Partition
Engine
MDX
Hierarchies
Fact
Relationship
Property
Unknownmember
Action
Cube
Pr
Database: SQL DDL Query
Homework Solution
$28.00
Database
Computer
Science
DDL
Query
SQL
Retrieve
Total
Sum
Count
Select
Group
Development
SQL Server for Business Intelligence
Homework Solution
$20.00
SQL
Server
Business
Intelligence
Transactional
3rd
Normal
Form
Relational
Schema
Dimensional
Model
Fact
Table
OLAP
System
Warehouse
Database
Analysis
Services
Data
Tools
Management
Studio
Dimension
Database System Concepts
Homework Solution
$55.00
Transaction
Atomicity
Consistency
Isolation
Durability
Schedule
Dirty
Read
Serializable
Strict
2Pl
Action
Object
Concurency
Control
Interference
Data
Model
Relational
Hierarchical
Network
Object-oriented
Single
Multi
User
Centrali
Get help from a qualified tutor
Live Chats