Lab$-2$

Create an E$-$R Diagram for the Company database

In this lab, create an E$-$R Diagram for the Company database from the raw data files given below and the business rules of the company given below using the symbols of E$-$R Diagram.

To create the E$-$R Diagram for the Company database, we identified $4$ Entities in class as below. Entities: $$ Employee $$ Department $$ Dependent $$ Weak Entity $$ Project.

For each Entity:

$1.$ Identify all the attributes for each Entity

$2.$ Identify the Attribute Type of each attribute per Entity

$$ Multi$-$Valued Attribute,

$$ Composite Attributes,

$$ Key attribute:

$$ If there are more than one key attribute, mark all as a possible key attribute.

$$ If there is no key attribute in an Entity, identify multiple attributes as a composite key attribute.

$$ Derived Attributes.

With the given raw data at the end of this lab sheet and the given Company rules as specified below, Identify a relationship between any two Entities to create an E$-$R Diagram for the Company database scheme. Note that there could be more than one relationship between two entities. For example, between Employee and Department, there are more than one relationship. There could be no relationship between some pairs of two entities.

Company Rules:

$1.$ Every employee should work for only one department.

$2.$ A Department could have no employee.

$3.$ Every employee should work on at least one project or more.

$4.$ Every project should have at least one employee or more that work$($s$)$ on

$5.$ A Project can be worked by multiple employees

$6.$ A Manager is the one who manages a Department. Only some of the employees are Managers.

$7.$ A Supervisor is the one who supervises his$/$her regular employees.

$8.$ Only some of the employees are Supervisors.

$9.$ Every Department can have only one manager.

$10.$ A department controls projects.

$11.$ Every Project should be controlled by only one department.

$12.$ Every dependent should have only one employee that he$/$she belongs to$.$

For each relationship, identify the followings:

$1.$ Name the relationship

$2.$ Identify the Cardinality of each relationship between two entities as either $111$ N or N $1$ N $$ M

$3.$ Identify participant types: Total or Partial Participation for each entity that participates in a relationship

$4.$ If there is no clear indication either from raw data or the rules for participation info of a relationship, leave it as partial participation as default

Submit your Lab$-2$ report in doc file with your E$-$R diagram $($either you can use free software or pencil or paper would be ok$).$ Give a brief explanation of the identified relationships and cardinality of each relationship in your E$-$R diagram. Submit Both on Blackboard.

EMPLOYEE

FNAME MINIT LNAME SSN BDATE ADDRESS SEX SALARY SUPERSSN DNO

Burrow A Joe $1234567899-$Jan$-75713$ Broadview Road M $500008876543215$

Mixon B Joe $3334455558-$Dec$-85850$ Perl Road M $650009876543215$

Jennifer F English $66688444431-$Jul$-6218541$ Bunker Hill Dr F $450008876543215$

Anita D Tyler $32456789815-$Sep$-5219622$ Fair Isle Way F $750009876543334$

Alicia K Allen $20147895610-$Nov$-771000$ Pleasant Valley F $500009876543214$

Hilton P Mike $87451200020-$Jun$-811030$ State Road M $550008876543503$

Bell R Vonn $10023487529-$Mar$-591050$ Ridge Road M $650009876543214$

DEPARTMENT

DNAME DNUMBER MGRSSN MGRSTARTDATE DELOCATION

Headquarters $188866555519-$Jun$-71$ Houston

Administration $49876543211-$Jan$-85$ Stafford

Research $533344555522-$May$-78$ Stafford

Automation $71234567896-$Oct$-05$ Cleveland

DEPENDENT

SSN DEPENDENT$\_$NAME SEX BDATE RELATIONSHIP

$123456789$ Alice F $31-$Dec$-98$ Daughter

$123456789$ Elizabeth F $5-$May$-78$ Spouse

$123456789$ Michael M $23-$May$-01$ Son

$333445555$ Alice F $15-$Jan$-12$ Daughter

$333445555$ JoY F $5-$May$-88$ Spouse

$333445555$ Theodeore M $8-$Jul$-15$ Son

$874512000$ Abner M $24-$Jun$-83$ Spouse

PROJECT

PNAME PNUMBER PLOCATION DNUM

ProdX $1$ Bellaire $5$

ProdY $2$ Sugarland $5$

ProdZ $9$ Stafford $4$

ProdW $10$ Houston $1$

ProdV $20$ Stafford $5$

ProdM $30$ Stafford $4$