Database Design

1
Database Design

• Sections 6 7First Normal Form (1NF), Second
  Normal Form (2NF), Unique Identifiers (UID),
  Third Normal Form (3NF), Arcs, Hierarchies and
  Recursive relationships

2
UID types

• Artificial UID
• we need some to have a key, can use autonumber
• Relationship is part of UID
• use bar to indicate that key of parent is part of
  child key
• makes a composite key
• Composite key
• 2 attributes function as a UID
• Made of 2 unique attributes
• The 2nd UID could be a key but function together
  with other key
• Use primary key and secondary key both must be
  mandatory and must be unique

3
Unique Identifier (UID)

• Value or combination of values that enables the
  user to find your record in a file/table
• Simple UID is a single attribute

• DEPARTMENT
• code
• name

4
Composite Unique Identifier

• Composite UID is a combination of attributes

CONCERT TICKETdate of performance seat number
5
Artificial UIDs

• A UID that does not occur in the natural world
  but is created for identification purposes in a
  system
• Examples
• student number
• customer ID
• credit card number
• social security number
• passport number

6
UID Artificial or Composite
7
Example Composite UID

• Composite UID is made up of two or more
  attributes together.
• In bank example DD 6.1.7 (next slide)
• The Bank number (UID in BANK entity) is part of
  the composite key for ACCOUNT, thus the use of
  the barred relationship

8
UIDs from Barred Relationships
9
Intersection Entity in MM

10
Intersection entity not barred
11
Candidate and Secondary UID
12
Normalization

• To make sure you dont store the same data twice
  in the model
• Also, that you store the data in the correct
  place
• We will cover the first 3 normal forms.

13
Normalization

• Your goal as a database designer is to "store
  information in one place and the best place."
• Examine the first 3 Normal Forms (1NF, 2NF,
  3NF)
• There are more normal forms but the first three
  enough for business

14
First Normal Form (1NF)

• First Normal Form requires that there be no
  multi-valued attributes and no repeating groups.
• To check for 1NF, validate that each attribute
  has a single value for each instance of the
  entity.
• In other words One value per Attribute.

15
Example 1NF

• This has multiple values for the classroom.

SCHOOL BUILDING code name address classroom
16
Example 1NF

• Telephone is a multvalued attribute that could be
  an Entity. Ie. Home, work, mobile, fax etc.
• Not all Employees have multiple phones, so there
  would eliminate multiple NULL values.

EMPLOYEE id name address . . º telephone º
salary
17
1NF violations
18
1NF violation solutions
19
1NF Review

• Do a quick review of normalization and First
  Normal Form (all attributes must be
  single-valued). Explain that we will cover the
  second rule of normalization.
• First Normal Form (1NF)
• First Normal Form requires that there be no
  multivalued attributes and no repeating groups.
  To check for First Normal Form, validate that
  each attribute has a single value for each
  instance of the entity.

20
1NF Review

• Classroom will have multiple values
• This entity is not in First Normal Form

SCHOOL BUILDING code name address classroom
21
1NF Review School Building 1NF

• Classroom is now its own entity.
• All attributes have only one value
• Both entities are in First Normal Form

SCHOOL BUILDING code name address
CLASSROOM number floor size
22
Second Normal Form

• Any non-UID attribute must be dependent on the
  entire UID
• All attributes that are not part of the entitys
  UID should be dependent on the whole UID. If the
  attribute changes does the UID also change?
• This specifically applies to entities that have a
  UID that is composed of more than one attribute
  or a combination of attribute(s) and
  relationship(s).

23
2NF Example
24
2NF

• Second Normal Form requires that any non-UID
  attribute be dependent on the entire UID.
• This means that all attributes that are not part
  of the entitys UID should be dependent on the
  whole UID. This specifically applies to entities
  that have a UID that is composed of more than one
  attribute or a combination of attribute(s) and
  relationship(s).

25
2NF example
26
2NF Example - violation

27
Second normal form violation

28
Second Normal Form

• Every object in an entity must be identified by a
  unique value.
• In an ERD the symbol id is commonly used for the
  unique identifier.
• UIDs must be unique and NOT NULL.
• In practice a number is typically assigned as a
  UID.
• Some UIDs are composites being formed by barred
  relationships as in intersection entities for MM
  relationships.

29
2NF - Example
30
Third Normal Form

• The rule of Third Normal Form states that no
  non-UID attribute can be dependent on another
  non-UID attribute.

31
Third Normal Form

• No non-UID attribute can be dependent on
  another non-UID attribute.

CITY id name size population mayor state s
tate flower
Third Normal Form Violation
32
Third Normal Form

STATE id name flower song motto
CITY id name size population mayor
Third Normal Form
in
have
33
CD example
34
Help you remember Normal Forms

• This saying might help you remember the 3 normal
  forms
• The truth the whole truth and nothing but the
  truth.
• The truth (1NF) no multivalue attributes, and
  depend on key
• The whole truth (2NF) whole UID, attributes
  depend on the whole key
• Nothing but the truth (3NF) depends only on the
  key

35
Arcs

• Constraints two or more relationships on an
  entity.
• Indicates that any instance of that entity can
  have only one valid relationship of the
  relationships in the arc at any one time.
• Models an exclusive or across the relationships.
  An Arc is therefore also called an exclusive arc.

36
Subtype/Supertype Example

37
Modeled as an ARC

PARTNER id first name last name
EVENT PLANNER expertise
DJ specialty
MANAGER authorized expense limit
OTHER
38
ARC Example

39
ARCS

• Arcs are similar to supertypes/subtypes, and are
  often modeled as such.
• Use supertypes/subtypes when you want to
  represent classifications, or types of things.
• Use arcs to represent mutually exclusive
  relationships between entities. (A type of
  either/or situation)

40
ARCS

owned by
is referred to
referring to
41
ARCS (previous screen)

• An arc always belongs to one entity.
• Arcs can include more than two relationships.
• Not all relationships of an entity need to be
  included in an arc.
• An entity may have several arcs.
• An arc should always consist of relationships of
  the same optionality
• All relationships in an arc must be mandatory or
  all must be optional.
• Relationships in an arc may be of different
  degree, although this is rare.

42
Hierarchy vs. Recursive

• Hierarchy Recursive

43
Model Hierarchical Data

• M1 relationships
• Company
• Division
• Department
• Team

44
Model Hierarchical Data

45
Model Hierarchical Data

• The UID of ROOM is the room id and the SUITE it
  is located within, the FLOOR it is on, and the
  BUILDING in which it is located.
• The UID of SUITE is the suite id, the FLOOR on
  which it is located, and the BUILDING in which it
  is located.
• The UID of FLOOR is the floor number and the
  BUILDING in which it is located.
• see notes

ROOM id
located within
the container of
SUITE number o tenant
located on
the container of
FLOOR number
contained in
the container of
BUILDING id name
46
Recursive Relationships

• A Recursive Relationship is a relationship
  between an entity and itself.
• Example Read the recursive relationship in the
  following E-R Diagram.
• Each EMPLOYEE may be managed by one and only one
  EMPLOYEE
• Each EMPLOYEE may be the manager of one or more
  EMPLOYEEs.

47
Recursive Relationship exampleModel Bill of
Materials

• Each COMPONENT may be a part of one or more
  COMPONENTs.
• Each COMPONENT may be made up of one or more
  COMPONENTs.

• Example For the automobile manufacturing
  organization, consider all elementary parts,
  subassemblies assemblies and products as
  instances of an entity called COMPONENT. Then
  the previous complex E-R Model can be remodeled
  as a simple recursive relationship.
• Bill of Materials data as a many-to-many
  recursive relationship

a part of
48
Many-to-Many examples part 1

• Example Consider the recursive model of a Bill
  of Materials structure. This model will track
  information about which components are part of a
  fan. But if a washer is part of a fan, will it
  also track how many washers are part of a fan?

made of of
49
Resolve MM recursive relationship part 2

• Resolve this MM recursive relationship by adding
  the intersection entity ASSEMBLY RULE and two M1
  relationships back to the COMPONENT entity.
  ASSEMBLY RULE will have an attribute of quantity.

50
Example A business hierarchy can be drawn as a
recursive relationship

made up of
51
Modeling Historical Data

• Modeling data that changes over time can be a
  tricky subject
• In this section we will cover factors that
  influence the decision to model historical data
• create a new entity to track it
• define a UID for such an entity

52
(No Transcript)
53
(No Transcript)
54
(No Transcript)
55
(No Transcript)
56
(No Transcript)
57
(No Transcript)
58
(No Transcript)