COMPANY DATABASE

QUESTION::

COMPANY database keeps track of a company’s employees, departments, and projects.

Suppose that after the requirements collection and analysis phase, the database

designers provide the following description of the miniworld—the part of the company

that will be represented in the database.

  • The company is organized into departments. Each department has a unique

name, a unique number, and a particular employee who manages the

department.We keep track of the start date when that employee began managing

the department. A department may have several locations.

 

  • A department controls a number of projects, each of which has a unique

name, a unique number, and a single location.

 

  • We store each employee’s name, Social Security number,2 address, salary, sex

(gender), and birth date. An employee is assigned to one department, but

may work on several projects, which are not necessarily controlled by the

same department. We keep track of the current number of hours per week

that an employee works on each project. We also keep track of the direct

supervisor of each employee (who is another employee).

 

  • We want to keep track of the dependents of each employee for insurance

purposes. We keep each dependent’s first name, sex, birth date, and relationship

to the employee.

  • Each department offers multiple courses which has ccode,fees and duration.

 

SOLUTION::

 

STEP 1:Identify the entities

  • Employee
  • Department
  • Project
  • Dependent
  • Courses

 

STEP 2: Identify the relations

  • MANAGES a 1:1 relationship type between EMPLOYEE and DEPARTMENT.

EMPLOYEE participation is partial. DEPARTMENT participation is not clear

From the requirements. We question the users, who say that a department

must have a manager at all times, which implies total participation.13 The

attribute Start date is assigned to this relationship type.

 

  • WORKS_FOR, a 1:N relationship type between DEPARTMENT and

EMPLOYEE. Both participations are total.

 

  • CONTROLS, a 1:N relationship type between DEPARTMENT and PROJECT.

The participation of PROJECT is total, whereas that of DEPARTMENT is

determined to be partial, after consultation with the users indicates that

some departments may control no projects.

 

  • SUPERVISION, a 1:N relationship type between EMPLOYEE (in the supervisor

role) and EMPLOYEE (in the supervisee role). Both participations are

determined to be partial, after the users indicate that not every employee is a

supervisor and not every employee has a supervisor.

 

  • WORKS_ON, determined to be an M:N relationship type with attribute

Hours, after the users indicate that a project can have several employees

working on it. Both participations are determined to be total.

 

  • DEPENDENTS_OF, a 1:N relationship type between EMPLOYEE and

DEPENDENT, which is also the identifying relationship for the weak entity?

type DEPENDENT. The participation of EMPLOYEE is partial, whereas that of

DEPENDENT is total.

  • OFFERS,a 1:N relationship type since each DEPARTMENT offers multiple COURSES.

 

 

STEP 3:Key attributes

  • Employee:ssn
  • Department:number
  • Project:number
  • Dependent:name
  • Courses:ccode

 

STEP 4::Other relevant attributes

 

  • Employee:bdate,sex,address,salary
  • Department:name,locations,no of employees
  • Project: name, location
  • Dependent: sex, birthrate, relationship
  • Courses: duration,fees

 

STEP 5::ER DIAGRAM

MUSUER

 

Advertisements