Activity Worksheet for Complex data Retrieval.pdf

PROBLEM DESCRIPTION - CASE 1

Company Database Management System

A company database needs to store information about the following business objects and their attributes:

Employees: Identified uniquely by ssn, with salary and phone as attributes.
Departments: Identified uniquely by dno, with dname and budget as attributes.
Children (Dependents): Identified uniquely by name when the parent employee is known, with age as an attribute.

The system must enforce the following relationship constraints and business rules:

Employee to Department (Works In): Employees work in departments.
Employee to Department (Manages): Each department is managed by an employee.
Employee to Child (Parent Of): A child is linked to a parent (who is an employee). We assume only one parent works for the company. We are not interested in information about a child once the parent leaves the company.

Audio/Video Presentation

AVP — Information Management (Individual Project)

Demonstration per Module

Business Objects / Entities Business Object

Employee - Strong entity

- ssn

- salary, phone

Department - Strong entity

- dno

- dname, budget

Child - Weak entity / dependent entity

- name, but only unique per parent

- age

Important note:

Child is a weak entity because a child cannot be uniquely identified by name alone in the whole database. The child is uniquely identified only when combined with the parent employee.

So the actual identifier for a child is:

(ssn, child_name) where ssn refers to the parent employee.

Business Object Characteristics

Employee

Each employee is uniquely identified by ssn.

Each employee has:

- salary

- phone

Employees work in departments.

An employee may also manage a department.

Department

Each department is uniquely identified by dno.

Each department has:

- dname

- budget

Each department is managed by one employee.

Each department can have many employees working in it.

Child

A child has:

- name

- age

A child belongs to one employee parent.

A child is only stored while the parent employee is still in the company.

Since the child depends on the employee, it is an existence-dependent entity.

Relationships Between Business Objects

Works_In

Employee — Department

Employees work in departments.

Manages

Employee — Department

Each department is managed by an employee.

Has_Child / Dependent_Of

Employee — Child

An employee may have children/dependents.

Relationship Constraints

A. Employee — Department: Works_In

Employee N : 1 Department

Meaning:

One employee works in one department.
One department can have many employees.

Participation:

Employee participation is total because the statement says employees work in departments.
Department participation may be partial or total, depending on whether every department must have employees. The problem does not explicitly say every department must have at least one employee.

B. Employee — Department: Manages

Employee 0..1 : 1 Department

Meaning:

Each department is managed by exactly one employee.
An employee may manage zero or one department.

Participation:

Department participation is total because every department is managed by an employee.
Employee participation is partial because not all employees are managers.

Possible constraint:

Manager must be an employee.

Unless explicitly stated, we cannot assume the manager must work in the same department they manage, though in many real systems that would be a reasonable additional business rule.

C. Employee — Child: Has_Child

Employee 1 : N Child

Meaning:

One employee can have many children.
Each child belongs to exactly one known employee parent.

Participation:

Child participation is total because a child cannot exist in the database without a parent employee.
Employee participation is partial because not all employees have children.

This is an identifying relationship because Child is a weak entity.

Child key:

Employee.ssn + Child.name

Conceptual Data Model

Logical Data Model

Physical Data Model

Page updated

Google Sites

Report abuse