Question

Suppose your job keeps track of each employee's degree status and university,draw an ER diagram that has the following 3 entities,Employee, Degree and University;also list at least 2 attributes in each and indicate what is the primary key within each entity.

Answer 1

Table: Employee

Attributes: Emp_no, Emp_name

Primary Key: Emp_no, Because, It uniquely identify each record

Emp_no	Emp_name
131	John
132	Bill
133	Don
134	Jack

Table:University

Primary Key: Uni_code

University code uniquely identify each record in this table

Uni_code	Uni_name	Uni_place
002	UNITO	Turin
004	Glasgow University	UK
005	IITB	India

Table:Degree

y reasonable solution is to create a new DEGREE entity that stores each degree in a separate

record, this producing the following tables. (There is a 1:M relationship between EMPLOYEE and

DEGREE. Note that the EMP_NUM can occur more than once in the DEGREE table. The

DEGREE table’s PK is EMP_NUM + DEGREE_CODE. This solution also makes it possible to

record the date on which the degree was earned, the institution from which it was earned, and so on.

DEGREE entity stores each degree in a separate record.

Also the University code is given. Uni_code is a foreign key in this table.

(1:M relationship between EMPLOYEE and DEGREE)

Note: Emp_no can appear one or more time in DEGREE table.

Primary Key of Degree table: Emp_no + Degree_code

Emp_no	Degree_code	Degree_date	Uni_code
131	BBA	2015-08-08	002
131	MBA	2017-01-01	002
131	PhD	2019-05-09	005
132	BS	2011-09-07	004
132	MS	2015-07-03	005
133	BS	2013-07-05	004
133	PhD	2019-03-04	005
134	BBA	2012-04-09	002

ER Diagram

Emp no Emp name one to one Employee one to many (mandatory) many qualifies Emp_no Degree date one or more (mandatory) Degree Uni_code Degree_code belongs University Uni place Uni code