Database administration the complete guide to practices and procedures
This presentation is the property of its rightful owner.
Sponsored Links
1 / 42

Database Administration: The Complete Guide to Practices and Procedures PowerPoint PPT Presentation


  • 127 Views
  • Uploaded on
  • Presentation posted in: General

Database Administration: The Complete Guide to Practices and Procedures. Chapter 3 Data Modeling & Normalization. Agenda. Data Modeling Concepts The Components of a Data Model Discovering Entities, Attributes & Relationships Conceptual, Logical & Physical Data Models Normalization

Download Presentation

Database Administration: The Complete Guide to Practices and Procedures

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Database administration the complete guide to practices and procedures

Database Administration:The Complete Guide to Practices and Procedures

Chapter 3

Data Modeling & Normalization


Agenda

Agenda

  • Data Modeling Concepts

  • The Components of a Data Model

  • Discovering Entities, Attributes & Relationships

  • Conceptual, Logical & Physical Data Models

  • Normalization

  • Additional Data Modeling Issues

  • Questions


Database administration the complete guide to practices and procedures

Data Thinking

  • Don’t think “how”; think “what”

  • Don’t think physical; think conceptual

  • Don’t think process; think structure

  • Don’t think navigation; think relationship

http://datatechnologytoday.wordpress.com/2010/12/06/data-modeling-concepts-every-dba-should-know/


The blind men the elephant

The Blind Men & The Elephant


Data modeling objectives

Data Modeling Objectives

achieves

Function

Objective

  • Document and communicate the business information requirements

  • Enable databases with:

    • Minimum redundancy

    • Maximum integrity

    • Sharability

    • Stability

    • Flexibility

    • Consistency

    • Timely access

    • Usability

  • Increase value of data resource

e.g., Data modeling

Database

has

Database

Characteristic


The components of a data model

The Components of a Data Model

  • Data models are comprised of multiple

  • components:

  • Entities

  • Entity Types

  • Entity Occurrences

  • Relationships

  • Attributes

  • Attribute Roles

  • Keys

  • Diagramming Techniques


Entities

Entities

  • Something that exists and is capable of being described.

  • A person, place, thing, concept or event about which an organization maintains facts.

Student

Instructor

Order

Course

Employee

Item


Entity examples

Entity

Person

Concept

Place

Thing

Event

Entity Examples

  • Person: Roles people play

    • Employee• Customer

    • Supplier• Agent

  • Thing: Physical object type

    • Item• Part

    • Product• Building

    • Material • Equipment

  • Concept: Intangible ideas

    •Warranty• Route

    •Account

  • Place:Areas or geographic locations

    • Office• Warehouse

    • City• Region

  • Event:Things that "happen"

    • •Sale• Order

    • Transfer• Shipment

    • Project• Agreement

    • Reservation• Flight


Entity occurrence

Entity Occurrence

Customer

Entity Type

Customer: Moore

City: Pittsburgh

State: PA

Phone: 555

Credit:: Go

AMEX:

VISA:

Contact:

Entity

Occurrences

Customer: Jackson

City: An

State: MI

Phone: 5

Credit:: Ex

AMEX:

VISA:

Contact:

Customer: Mullins

City: Houston

State: TX

Phone: 555-1234

Credit:: Fair

AMEX: xxxxxxxxxxxxxxxxxxx

VISA: xxxxxxxxxxxxxxxxxxxx

Contact: Mike


Entity naming guidelines

Entity Naming Guidelines

  • Noun, or adjective noun format

    • Contract

    • Lease Agreement

  • Minimize use of adjectives

    • Contractor vs Contract Employee

    • Training vs Employee Course

  • Do not pluralize; use singular instead: Employee vs Employees

    • The entity type is a model or patternrather than the set of all employees

  • Use business terms

    • Vendor or Supplier?

    • User or Client?

  • Be consistent

  • Remove process specific artifacts from the entity name

    • State not Residence State

    • Agent not Selling Agent


Relationships

Relationships

  • How the different entities are associated with each other

Places

Order

Product

Customer

Is Placed By

Customer

Order

Places

Is Placed By


Cardinality

Cardinality

One to One Relationship

Optional

Mandatory

One to Many Relationship

1

Many to Many Relationship

Many

Cardinality


Entity relationship diagramming

Entity-Relationship Diagramming

MOVIE

MovieNum

STORE

MOVIE COPY

Name

StoreNum

Movie Copy Num

Director

is rented as

employed by

Manager

rents /

Description

EMPLOYEE

General Condition

Address

is in

Star

EmployeeNum

Phone

Rating

Genre

Name

Rental Rate

is

rented

under

Address

Phone

completes

SocialSecNum

HireDate

CUSTOMER

Salary

CustomerNum

Supervisor (FK)

MOVIE RENTAL RECORD

Name

Rental Record Date

Address

Phone

rents under

reports to

Rental Date

JoinDate

Due Date

CreditCardNum

Rental Status

receives

CreditCardExDate

Rental Rate

StatusCode

Overdue Charge Amt

makes

PAYMENT

Payment Transaction Num

Type

Amount

is made on

PaymentDate

Status


Diagramming conventions

Diagramming Conventions

One-to-One

One-to-Many

Many-to-Many

1

2

3

4

5

CUSTOMER

ACCOUNT

STUDENT

COURSE

EMPLOYEE

TEMP_EMP

TEMP_EMP

EMPLOYEE

CUSTOMER

ACCOUNT

COURSE

STUDENT

STUDENT

COURSE

EMPLOYEE

CUSTOMER

TEMP_EMP

ACCOUNT

M

1

M

M

1

1

EMPLOYEE

CUSTOMER

COURSE

TEMP_EMP

ACCOUNT

STUDENT

EMPLOYEE

TEMP_EMP

ACCOUNT

STUDENT

CUSTOMER

COURSE

(1) Ross, (2) Bachmann, (3) Martin, (4) Chen, (5) Rumbaugh.


Database administration the complete guide to practices and procedures

UML


Data model diagram guidelines

Data Model Diagram Guidelines

  • The relationship is read clockwise over the line

Reads left-to-right

Customer

Is placed by

Order

Read bottom-to-top

Read top-to-bottom

Order

Order

Places

Is placed by

Customer

Customer

Read right-to-left

Customer

Order

Places


Types of entities

Types of Entities

  • Examining relationships can help to determine the type of entity:

    • Fundamental Entities - represent fundamental, or basic, business objects

    • Characteristic Entities - contain multiple attributes or facts describing a basic entity

    • Associative Entities - describe a relationship between two other entities


Database administration the complete guide to practices and procedures

STUDENT

StudentID

COURSE

ENROLLMENT

CourseNum

StudentID

LastName

FirstName

CourseNum

CourseName

MiddleInit

Credits

CourseCompDateGrade

MajorID

Creating an Associative Entity

STUDENT

COURSE

StudentID

CourseNum

LastName

CourseName

FirstName

Credits

MiddleInit

MajorID


Attributes

Attributes

  • An attribute is a fact about an entity. It is a data element that is an inherent property of an entity.

  • An attribute will fulfill one (and only one) of these objectives:

    • Identify

    • Relate

    • Describe


Attributes1

Customer: Smith

Customer #: 0001

City: New York

State: New York

Phone: 555-1938

Credit: Good

Customer: Smith

Customer #: 0001

Attributes

Customer

Customer

Cust Number

Cust Name

Street Address

City

State

Zip Code

Phone Num

Credit Limit Amt

ENTITY = Nouns

ATTR = Adjectives


Domains

Domains

  • The domain defines the universe of valid values for an attribute

  • Each attribute is assigned a domain


Attribute naming guidelines

Attribute Naming Guidelines

CLASSABBREVIATIONDESCRIPTION

ADDRESSADDRAddress or location

AMOUNTAMTMonetary amount

CODECODEClassifications, codes, types

DATEDATECalendar dates

DESCRIPTIONDESCDescriptive text

IDENTIFIERIDAlphanumeric unique identifier

IMAGEIMGEncoded digital image

NAMENAMEAlphabetic name, identification

NUMBERNUMNumeric count

PERCENTPCTRelationships as %

QUANTITYQTYCounts and units

TEXTTXTFree form document text

TIMETIMETime; temporal data


Attribute values and null

Attribute Values and Null

  • Attributes and attribute values can be thought of in a similar way as entities and entity occurrences

  • An attribute value is an instance of an attribute.

  • If an attribute “value” can be null, it means one of two things:

    • the attribute is not applicable for certain occurrences of the entity, or

    • the attribute applies to all entity occurrences, but the information may be unknown.

  • More on Nulls in the next lesson on physical database design


Database administration the complete guide to practices and procedures

Keys

  • A key is an attribute, or group of attributes, that are used to identify.

  • There are three types of keys:

    • Candidate Keys

    • Primary Keys

    • Foreign Keys

  • A key should contain no embedded meaning.

    • The key’s purpose is to identify, not to describe.


Primary keys

Primary Keys

  • The attribute or attributes that uniquely identify an entity type

  • A primary key value uniquely identifies a single occurrence of an entity

Order

Product

Customer

Order Number +

Line Number

Product

ID

Customer

Number

Order

Order #: 0001 Line #: 1

0001 / 1

ProdID: 1234

Qty: 5


Foreign keys

Foreign Keys

(Relationship Attributes)

  • An attribute (or attributes) in one entity that relates an occurrence of that entity to an occurrence of another entity

  • The relating attribute(s) contains the same value(s) as the primary key of the related entity occurrence

Employee

Org Unit

works in

employs

Emp Num

Emp Name

Org Unit Num (F)

Org Unit Num

Org Name

manages

Employee

Org Unit

managed by

Emp Num

Emp Name

Org Unit Num

Org Name


Candidate keys

Candidate Keys

  • There may be multiple possible primary keys

    • Must identify a specific occurrence of the entity

  • These are known as candidate keys

  • There should be one, and only one, primary key


Discovering entities attributes and relationships

Discovering Entities, Attributes, and Relationships

  • Keep track of the nouns, noun phrases, verbs and adjectives used by subject matter experts

  • Nouns are potential entities & attributes, or perhaps entity occurrences & attribute values

  • Adjectives and prepositional phrases are usually potential attributes

  • Verbs highlight potential relationships

  • Of course, these are not hard & fast rules and you will need to dig for meaning & understanding to build an accurate data model


Additional guidance

Additional Guidance

  • Put the words in context to further define them

  • Solicit input from multiple sources

    • Do not rely on just a single subject matter expert

  • Keep patterns in mind


Data model of modeling terms

Data Model of Modeling Terms

Relationship

Primary

Key

Entity

Occurrence

Attribute

Domain

Attribute

Value


Types of data models

Types of Data Models

  • Conceptual

    • High level

  • Logical

    • More detailed

    • No many-to-many relationships

    • Ready to move to physical

  • Physical

    • Additional DBMS details

    • Ready to be implemented as a database


Normalization

Normalization

The process of identifying the one best place a fact belongs.

  • First Normal Form

    A row is in first normal form if and only if all underlying domains contain atomic values only.

  • Second Normal Form

    A row is in second normal form if and only if it is in first normal form and every non-key attribute is fully dependent on the key.

  • Third Normal Form

    A row is in third normal form if and only if it is in second normal form and every non-key attribute is non-transitively dependent on the primary key.


Unnoramlized student data

Unnoramlized STUDENT Data


First normal form

First Normal Form

STUDENT

COURSE


Second normal form

Second Normal Form

STUDENT

ENROLLMENT

COURSE


Third normal form

Third Normal Form

MAJOR

STUDENT

ENROLLMENT

COURSE


Database administration the complete guide to practices and procedures

The Data Model

in Third Normal Form

STUDENT

StudentID

MAJOR

LastName

MajorID

FirstName

Major

MiddleInit

MajorID

ENROLLMENT

COURSE

StudentID

CourseNum

CourseNum

CourseName

Credits

CourseCompDate


Further normal forms

Further Normal Forms

  • Boyce Codd normal form (BCNF)

    • A further refinement of 3NF. Indeed, in his later writings Codd refers to BCNF as 3NF. A row is in Boyce Codd normal form if and only if every determinant is a candidate key. Most entities in 3NF are already in BCNF.

  • Fourth normal form (4NF)

    • No entity can have more than a single one-to-many relationship if the one-to-many attributes are independent of each other. An entity is in 4NF if and only if it is in 3NF and has no multiple sets of multivalued dependencies.

  • Fifth normal form (5NF)

    • Every join dependency for the entity must be a consequence of its candidate keys.


Normalization in practice

Normalization in Practice

  • Normalization ensures that we are creating a data blueprint for the best database design possible.

  • Normalization is a logical process.

    • Many times data modelers will try to mandate that the logical model become the physical database design with no changes. But it is not wise to force DBAs and technicians to forgo physical design.

  • In the best of all worlds, a one-to-one logical to physical translation would work, but for many reasons, this strict one-to-one mapping is not practical.

  • The only normal form required for relational databases is First Normal Form. 1NF ensures tabular data.


Additional data modeling issues

Additional Data Modeling Issues

  • Subtypes and super-types

  • Handling time

  • One-to-one relationships?

  • Derived data (see next slide)


Derived and base data

Derived and Base Data

Course

Course Num

Course Offering Date

Course Name

Course Fee

Enrollment

Student ID

Course Num

Course Offering Date

Course Completion Date

Course Charge Back Amt(DT)

Student

Student ID

Student Name

Student Date of Birth

Student Age(D)


Questions

Questions


  • Login