Database Design Assignment

Wedgewood Pacific $($WP$)$ company was founded in $1957$ in Seattle. The company has departments with unique names $($administration$,$ accounting, etc.$)$ Every department has its own unique budget code, office number, and phone. The company has employees, each with a unique employee number. Every employee is associated with one department. Employees have first name, last name, position, a supervisor, phone, and email.

The company has a number of projects, each with a unique project ID$.$ Projects also have descriptive names. Every project is associated with only one department and has a $$maximum hours$($the maximum number of employee work hours that can be allocated to the project$),$ a start date, and an end date. Every project can have many employees assigned to it and every employee can be assigned to zero, one, or more projects. It is important for the company to know how many hours each employee has worked for every project.

A$.$ Identify the different $$themes$$ or entities in the above scenario $($e$.$g$.,$ EMPLOYEE$)$ and their respective attributes.

B$.$ Use the standard notation taught in previous exercises to create well$-$formed relations for each of the themes identified above to store WP$$s data.

Hint: Below is the structure of one of the relations. Note that primary key$($s$)$ are underlined, and foreign key$($s$)$ are italicized.

EMPLOYEE$($EmployeeNumber$,$ FirstName, LastName, Department, Position, Supervisor, OfficePhone, EmailAddress$)$

C$.$ List at least $3$ referential integrity constraints for the database.

D$.$ Browse to section $3\mathrm{.}1$ of your textbook. Using the data in Figures $3-2$: a$,$ b$,$ c$,$ and d$,$ answer the following questions:

a$.$ What is the phone number for the Finance department?

b$.$ How many employees work for the Finance department?

c$.$ Who is the CFO? What is the name of his supervisor?

d$.$ Who does the CFO supervise?

e$.$ How many hours in total have the employees supervised by the CFO worked on projects?

f$.$ How many hours in total have the employees supervised by the CFO worked on projects owned by the Finance department?

E$.$ Draw the Entity Relationship Diagram $($ERD$)$ for WP $($we will talk about ERD next time in class$).$