What to submit:

$1.$ Word doc contains your work. Name it yourFirstnameLastnameAssignment$4$

Due date: $10/14$ by $11$:$59$pm

$$

Instructions:

$1.$ Read ERD below and map the entities and their relationships to relational models $($relational database tables.

$$

$2.$ Use this format to describe the tables, DO NOT draw another diagram for table design:

table name $($column list$)$

primary key:

foreign keys:

columns with not null constraint:

columns with unique value constraint:

check out PPT for examples

$$

$3.$ Test your design: $$draw tables and add some testing records to tables, and make sure the tables can store the required information.

a$.$ Do NOT create tables in PostgreSQL server, you can include tables in the word doc.

b$.$ Insert $5$ employee records. $3$ fulltime employees, $2$ parttime employees.

c$.$ Insert $3$ department records.

d$.$ Insert $3$ project records.

e$.$ Let employee $1,3,5,$ belongs to department $1.$

f$.$ Let employee $2,4$ belongs to department $2.$

g$.$ Department $3$ has no employees.

h$.$ Let department $1$ controls project $1.$

i$.$ Let department $2$ controls project $2$ and $3.$

j$.$ Let employee $1$ and $2$ work for project $1.$

k$.$ Let employee $1$ and $3$ work for project $2.$

l$.$ Let employee $3,4$ and $5$ work for project $3.$

m$.$ Employee $1$ has $2$ children as dependent

n$....$ feel free to add more records for other scenarios

Verify your table design. You don$$t need to show anything in the submitted doc for step $4$ and $5.$ But please use those steps to verify the design to make sure it works.

$$

$4.$ Use normalization process to evaluate and update the table design if needed. Make sure your design is in $3$NF

$$

$5.$ Consider the tasks below when using the database with the application and make sure your table design supports the functionalities.

Try to write some data in your tables, then manually pick up the information from those tables for the tasks below. $($In the next topic, we will learn how to write SQL statement to query the tables$)$

$$

a$.$ Show employee demographic information

$$

b$.$ List employees by filer

$$ i$.$ By last name or by first name

$$ ii$.$ By salary range for fulltime employee

$$

c$.$ Generate letters $/$ reports to employee with different name format. For example, in one report, display the name as $$firstname lastname$$; in another report, display the name as $$lastname$,$ firstname$$

$$

d$.$ Display dependents for an employee, can search dependent by dependent first name or last name.

$$

e$.$ Be able to add multiple addresses for a department.

$$

f$.$ Be able to find out who is the manager of a department.

$$

g$.$ Be able to find out what are the projects controlled by a department

$$

h$.$ Be able to add more project controlled by a department

$$

i$.$ Be able to assign employees work on a project

$$

j$.$ Be able to move employee from one project to another project $($this doesn$$t mean the employee will be fired and hired again$)$