DBMS

Three Tier Architecture

1. Presentation Layer
2. Application Layer
3. Data Layer

DB lies the third, lowest layer in the architecture.

Database Design Phases

1. Requirements Collection & analysis phase
2. Conceptual Design (ERD's)
3. Logical Design (Mapping)
4. Physical Design

1.Requirements Collection & analysis phase

two types of requirements

1. Functional
2. Data - Employee number, address, name, etc.

2.Conceptual Design (ERD's)

here is the the EERD for nansoft EP management system

Entities

• there are 4 main entities
1. Person
2. Customer, which is specification of Person
3. Employee, which is specification of Person
4. Project
• Person has composite attribute: name
• primary key for customer is customer_id
• primary key for Customer is customer_id
• primary key for Employee is ssn
• primary key for Project is project_id
• Project has a multi valued attribute project type

Relationships

• Customer and Employee are specification of Person with total & disjoint constraints
• Employee,Project:n:m relationship
  • One Employee can work on 0 or many projects ( o or many symbol on the Project side )
  • One Project can have 0 or many Employee ( o or many symbol on the Employee side )
• Customer,Project:1:m relationship
  • One Customer can own on 1 or many projects ( 1 or many symbol on the Project side )
  • One Project can have only one customer ( one and only one symbol on the Customer side )
• Employee has a recursive relationship for leadership :1:n
• Dependent is a week entity which is related with Employee

Note: There are 2 types of constraints: Completeness (total/partial) and Overlap/Disjoint

3.Logical Design (Mapping)

Mapping the current E-ERD

1. STEP 1

   map strong entities
   map strong entities with attributes

   we need to discard multi value and derived attributes in ths phase.

   Person

   SSN	DOB	first_name	middle_name	last_name

   Customer

   c_id	loyalty

   Employee

   e_id	salary

   Project

   p_id

2. STEP 2

   map week entities
   

   week entities will be given a separate relation with partial key of week entity and primary key of strong entity being the new primary key of the relation

   Dependent

   e_id	name	sex

3. STEP 3

   map 1:1 relationships
   

   Choose one of the relations and include its primary key as foreign key of the other relation. It is better to choose an entity type with total participation to add foreign key field. since this example has no 1:1 relationships, we will look at a different example.
   

   

   Employee

   e_id	salary	sex

   Department

   dno	name	manger_id	start_date

4. STEP 4

   map 1:n relationships
   

   the primary key of one side relation will be used in the many side as a foreign key. Also relationships attributes will go to the many side.

   Project

   p_id	c_id	assign_date

   Employee

   e_id	salary	super_e_id

5. STEP 5

   map n:m relationships
   

   a separate relation will be used. The new PK will be two primary keys.

   Employee-project

   e_id	p_id	work_hours

6. STEP 6

   map multi valued attributes
   

   a separate relation will be used. The new PK will be the primary keys of entity and the attribute itself.

   Project_type

   p_id	project_type

7. STEP 7

   map n-nary relationships
   

   Just like n:m mapping relationships we take a separate relation. The new PK will be n PKs of the strong entities. Any relationship attributes will also be in the relation.

8. STEP 8

   map specification and generalization
   

   There are 4 ways to map inheritance in EER Diagrams.

   1. Option 1 - (total/partial, disjoint/overlap)

      Have a super class relation with all the super class attributes. And for each subclass a relation which includes the primary key of the super class and the attributes of the subclass.

      Note: This is the most flexible option because it suits any type and support the requirement changes in future.

      Person

      SSN	DOB	first_name	middle_name	last_name

      Customer

      SSN	loyalty

      Employee

      SSN	salary	super_e_ssn

      Project

      p_id	c_ssn	assign_date

      Dependent

      SSN	name	sex

      Employee-project

      e_ssn	p_id	work_hours

      Project_type

      p_id	project_type

   2. Option 2 - (total)

      In our case this can be applied. But it won't cope with the changes to the total constraint in the future. In this option there will be no superclass relation because any type will belong to either one of subclasses. So all the attributes in superclass will be present in each subclass.

      Customer

      SSN	DOB	first_name	middle_name	last_name	loyalty

      Employee

      SSN	DOB	first_name	middle_name	last_name	salary	super_e_ssn

      Project

      p_id	c_ssn	assign_date

      Dependent

      SSN	name	sex

      Employee-project

      e_ssn	p_id	work_hours

      Project_type

      p_id	project_type

   3. Option 3 - (disjoint)

      Only one relation. There will be a extra attribute to represent which subclass the record belongs to.This is suitable for both total and partial as long as it's disjoint.

      Person

      SSN	DOB	first_name	middle_name	last_name	sub_type	loyalty	salary	super_e_ssn

      Project

      p_id	c_ssn	assign_date

      Dependent

      SSN	name	sex

      Employee-project

      e_ssn	p_id	work_hours

      Project_type

      p_id	project_type

   4. Option 4 - (overlap)

      Only one relation. There will be flags (boolean attribute) for each subclass type to represent which subclass the record belongs to.This is suitable for both total and partial as long as it's a overlap. If there a disjoints there will be null values.

      Person

      SSN	DOB	first_name	middle_name	last_name	CFlag	loyalty	EFlag	salary	super_e_ssn

      Project

      p_id	c_ssn	assign_date

      Dependent

      SSN	name	sex

      Employee-project

      e_ssn	p_id	work_hours

      Project_type

      p_id	project_type

9. STEP 9

   map union types
   

4.Physical Design