Extended Learning Module C

1

• Extended Learning Module C
• Designing Databases and Entity-Relationship
  Diagramming

2
Presentation Overview

• Designing and Building A Relational Database
• The four primary steps for designing a database
  include
• Defining entity classes and primary keys.
• Defining relationships among entity classes.
• Defining information (fields) for each relation.
• Using a data definition language to create your
  database.

3
Step 1 Defining Entity Classes and Primary
Keys

• Entity class - a concept - typically people,
  places, or things - about which you wish to store
  information and that you can identify with a
  unique key (called the primary key).
• Primary key - a field (or group of fields in some
  cases) that uniquely describes each record.
• Instance - an occurrence of an entity class that
  can be uniquely described.

4
Step 1 Defining Entity Classes and Primary Keys

• What are the entity classes and primary keys for
  the report below?

5
Step 1 Defining Entity Classes and Primary Keys

• Employee (primary key Employee ID)
• Job (primary key Job Number)
• Department (primary key Department Num)

6
Step 2 Defining Relationships Among Entity
Classes

• Entity-relationship (E-R) diagram - a graphic
  method of representing entity classes and their
  relationships.
• Rectangle entity class
• Dotted line relationship
• - single relationship
• O zero or optional relationship
• Crows foot (?) multiple relationship

7
Step 2 Defining Relationships Among Entity
Classes

• An E-R diagram for our Employee database.

8
Step 2 Defining Relationships Among Entity
Classes
9
Step 2 Defining Relationships Among Entity
Classes

• Employee-Department An Employee is assigned to
  one Department at a minimum and one Department at
  a maximum.

10
Step 2 Defining Relationships Among Entity
Classes

• Department-Employee A Department is not required
  to have any Employees assigned to it but may have
  many Employees assigned to it.

11
Step 2 Defining Relationships Among Entity
Classes

• All statements you derive from an E-R diagram
  should mirror the business rules at hand.

12
Step 2 Defining Relationships Among Entity
Classes

• Now its time to employ normalization.
• Normalization - a process of assuring that a
  relational database structure can be implemented
  as a series of two-dimensional relations.

13
Step 2 Defining Relationships Among Entity
Classes

• Three rules of normalization
• Eliminate repeating groups or many-to-many
  relationships.
• Assure that each field in a relation depends only
  on the primary key for that relation.
• Remove all derived fields from the relations.

14
Step 2 Defining Relationships Among Entity
Classes

• To eliminate repeating groups (many-to-many
  relationships) you must, create an intersection
  relation.
• The figure on the next slide shows a many-to-many
  relationship and the intersection relation that
  eliminates it.

15
Step 2 Defining Relationships Among Entity
Classes

• The intersection relation (Job Assignment) uses a
  composite primary key.

16
Step 2 Defining Relationships Among Entity
Classes

• Intersection relation - a relation you create to
  eliminate a many-to-many relationship.
• Composite primary key - consists of the primary
  key fields from the two intersecting relations.
• Foreign key - a primary key of one file
  (relation) that appears in another file
  (relation).

17
Step 2 Defining Relationships Among Entity
Classes

• Guidelines for creating an intersection relation
• Draw the part of the E-R diagram that contains a
  many-to-many relationship.
• Underneath each relation for which the
  many-to-many relationship exists, write down some
  of the primary keys.
• Create a new E-R diagram (showing no cardinality)
  with the original two relations on each end and a
  new one in the middle.
• Underneath the intersection relation, write down
  some composite primary keys.

18
Step 2 Defining Relationships Among Entity
Classes

• Create a meaningful name for the intersection
  relation.
• Move the minimum cardinality appearing next to
  the left relation just to the right of the
  intersection relation.
• Move the minimum cardinality appearing next to
  the right relation just to the left of the
  intersection relation.
• The maximum cardinality on both sides of the
  intersection relation will always be many.
• The new minimum and maximum cardinalities for the
  two original relations will be one and one.

19
Step 2 Defining Relationships Among Entity
Classes
20
Step 3 Defining Information (Fields) for Each
Relation

• Your goal in this step to make sure that the
  information in each relation is indeed in the
  correct relation and that the information cannot
  be derived from other information the second
  and third rules of normalization.

21
Step 3 Defining Information (Fields) for Each
Relation
22
Step 3 Defining Information (Fields) for Each
Relation

• Department Name does not belong in the Employee
  relation.
• It depends on Department Num.
• It does not depend on Employee ID.
• Num of Employees does not belong in the
  Department relation.
• It can be derived by counting employees in the
  Employee relation.

23
Step 3 Defining Information (Fields) for Each
Relation
24
Step 3 Defining Information (Fields) for Each
Relation
25
Step 4 Using a Data Definition Language to
Create Your Database.

• The final step in developing a relational
  database is to take the structure you created in
  steps 1 to 3 and use a data definition language
  to actually create the relation.

26
Step 4 Using a Data Definition Language to
Create Your Database

• The data definition language is part of the DBMS.
• Database management system (DBMS) - helps you
  specify the logical organization for a database
  and access and use the information within the
  database.