Maturity DB Process

Maturity DB Process Design Stage Review Logical Design Physical Design Review DDL Script Review Coding Unit Test Integration Test Evaluation Stress Test Production

Design decide the system quality Design Stage Coding Stage Testing Stage Production

Design Stage • Logical Design • Physical Design • Maintain Plan

I Logical Design

Data Model • What is a logical data model? • What is the purpose of data modeling? • How to design logical data model?

What is Data Model? • A model is an abstract representation of some real thing. • Data modeling is the action of exploring data-oriented structures. • A logical data model is a graphical representation of the information requirements of abusiness area, it is not a database.

Data Models Concepts • Conceptual data models • Logical data models (LDMs). • Physical data models (PDMs).

What is the differencebetween a logical datamodel and a physicaldatabase design?

A simple logical data model.

A simple physical data model

Logical Data Model Format • Logical Data Model is in format known as “Entity Relationship Diagram” (ERD) • Most popular data modeling tools are Erwin, ER Studio and Power Designer.

Advantages to Using a Model • Easier to understand model at a glance • No need to trace through narrative descriptions of relationships • Communicates one clear definition • Understood by business and technical staff

Benefits of a Logical Data Model • Using a Logical Data model speedsmaintenance and eases theTransition to new technologies. • Capture business requirements (ensure understanding) • Ability to share data across enterprise resulting in: • Accurate data • Consistent data • Reduced costs • Easier to implement changes in your business • Business requirements can be satisfied in database design

Who uses the logical data model? • The Business Area Experts own the logical data model. They describe their data requirements to the data modeler and review the models created. They use the models for impact analysis of changes to business requirements. • The Data Modeler conducts facilitated sessions with business area experts to gather the datarequirements and build the logical data model. The data modeler also works with the processanalyst to link data with processes. The data modeler is responsible for getting approval of thelogical data model from the business area experts and then works with the DBA to transitionthe logical model to the physical model. • The DBA (Designer) builds the physical data model from the logical datamodel. To create a good quality database design, the DBA reviews the logicalmodel to select technology appropriate keys, create indexes, detail data types, and buildreferential integrity to protect the data values. The database administrator may de-normalizethe database for efficiency. DBAs also are responsible for creating db schemas,maintaining referential integrity, and monitoring database performance.

Actions in Data Modeling • Identify – Determine which things are represented in the model. • Name – Each thing represented in the model needs to have a unique and meaningful name. • Describe – Name is important, but not sufficient. Description should be no more than three sentences, each with subject, object, and verb. Must answer: • What is it? • What it is not. • Sometimes: What are some examples? • Associate – Much of the meaning is in associations among the things represented in the model.

How to Model Data • Identify entity types • Identify attributes • Assign keys • Inversion Entries • Identify relationships • Normalize to Reduce Data Redundancy

What is an Entity? • Entity: a person, place, thing, concept or event that the business wants to store information about A movie is an entertainment, documentary, or educational event which has been recorded in a moving picture format. MOVIE

Entity and Instance • Each entity is made up by a group of objects, which are named as Instances. • Each instance can be identified from other instances.

EMPLOYEE People STUDENT OFFICE Place COUNTRY AUTOMOBILE Things CHEMICAL FUNDS TRANSFER Event TENNIS TOURNAMENT DEPARTMENT concept ORDER ENTITY Examples categoryENTITY Instance Mr.Koch Ms.Chou HongKong R.O.C BMW 525i Ammonia 42233 U.S. OPEN L789 I12345

ENTITY Name EMPLOYEE Employee Id Employee Last Name Employee First Name Employee Address Employee Phone Number attributes What is an Attribute? • Attribute: a fact or characteristic of an entity with only one meaning (atomic) • Each entity type will have one or more data attributes

CONSULTANT Consultant Id Consultant Last Name Consultant First Name Consultant Specialization Consultant Hourly Rate Key Attributes Non-key Attributes Two kinds of Attributes • Key Attributes • Non-key Attributes

TEACHER Teacher Last Name Teacher First Name Teacher Address Teacher Country Teacher Certificate Id Teacher Mother Maiden Name Teacher Phone Number Teacher Date of Birth TEACHER Teacher Last Name Teacher First Name Teacher Address Teacher Country Teacher Certificate Id Teacher Mother Maiden Name Teacher Phone Number Teacher Date of Birth Candidate Keys • One single attribute or a group of attributes that can be used to identify each instance.

Employee PK EMPLOY ID First Name Last Name Address Department Phone Number Birthday Primary Key • A candidate key with the highest priority that be used to identify the instance

Employee Id Employee Last Name (AK1) Employee First Name (AK1) Employee Address Employee City Employee State Employee Zip Code Employee Phone Number (AK2) Employee Date of Birth (AK1,AK2) EMPLOYEE Alternate Key • All the candidate keys except PK

Employee Id Employee Last Name (AK1,IE2) Employee First Name (AK1) Employee Address Employee City (IE1) Employee State (IE1) Employee Zip Code Employee Phone Number Employee Date of Birth (AK1) EMPLOYEE Inversion Entries • Some of attributes be used to find out the instance wanted. The result may not be unique.

What is a Relationship? • Relationship: an association between occurrences of one or more entities which provides some relevant and valuable information VIDEO TAPE MOVIE is recorded on records

What is a Verb Phrase • Parent-to-child verb phrase describes how the parent is related to the child. In the example to the left, the verb phrase states that “STORE rents A MOVIE.” • Child-to-parent verb phrase describes how a child entity is related to a parent entity. In the example to the left, the verb phrase states that “MOVIE is rented from A STORE”

Cardinality of Relationship • One-to-one • One-to-many • Many-to-one • Many-to-many • All types can be optional for one or both entities

MOVIE COPY MOVIE MASTER Movie Master Id (FK)Movie Copy Number Movie Copy Create Date Movie Copy Due Date Movie Copy Condition Movie Master Id is rented as/ is created from Movie Name Movie Star Movie Type Movie Rating Identifying Relationship • An identifying relationship is a relationship between two entities in which an instance of a child entity is identified through its association with a parent entity, which means the child entity is dependent on the parent entity for its identify and cannot exist without it.

ORDER CUSTOMER Order Number Customer Id places/ is received from Customer Id (FK) Order Date Order Status Order Shipdate CustomerName CustomerAddress CustomerPhone Mandatory non-identifying relationship • A non-identifying relationship in which an instance of the child entity must be related to an instance of the parent entity.

Department Number DEPARTMENT EMPLOYEE DepartmentName DepartmentLocation Employee Id employs/ belongs to Department Number (FK) Employee Name Employee Address Non-mandatory non-identifying relationship • A non-identifying relationship in which an instance of the child entity can exist without being related to an instance of the parent entity.

is ordered from/sends us PART SUPPLIER Many-to-Many Relationship • A many-to-many relationship is one where a relationship and its inverse are both to-many (if you are used to entity-relationship modeling using a relational database.

Start 1 : M or M : M Cardinality of R M:M 1:M M:M Indentify or Non-identify Y inheritable or Non-inheritable N Draw and name anIdentifying Relationshipfrom Parent to Child Y Draw and name aNon-identifying Relationshipfrom Parent to Child FK - NULLS ALLOWED Draw and name aNon-identifying Relationshipfrom Parent to Child FK - NO NULL Build Relationship

Normalize to Reduce Data Redundancy • Data normalization is a process in which data attributes within a data model are organized to increase the cohesion of entity types.

Normalization • Step by step process to verify and refine logical data model • Condition of model at completion of each step is a “normal form” • DOT standard is third normal form • First normal form:Eliminate repeating groups • Second normal form:Ensure that all attributes depend on the entity identifier • Third normal form:Ensure that all attributes depend only on the entity identifier

1st Normal Form • Eliminate repeating groups • To remove the repeating group of fields, collapse them into a single field with multiple records in a new table, related back to the primary data.

2nd Normal Form • Uniquely identify each instance • Each table must contain attributes for a single subject and each table must contain an attribute (or set of attributes) that uniquely identify a single record within that table.

3rd Normal Form • Eliminate columns not dependent on the key • Each attribute must depend on the primary key, so the violating fields are moved into separate, related tables.

II Physical Design

Physical Design • Mapping Logical Model to Physical Model • Naming standard • Identify table type • Column Data Type • Group tables • Assign Keys • Choose Index • Denormalizate to improve performance • Storage

Mapping Logical Model to Physical Model • Entity -> Table • Attribute -> Column • Primary Key -> Primary Key • Relationship -> Foreign Key • Inversion Entry -> Index

Naming Standard • Name the db objects under defined naming standard Example: table should have a prefix t_ • Define abbreviation Example: Cargo -> CGO

Table Types • Table Purpose • Data Wave • Data Size

Table Purpose • Transaction Table • Log Table / Analysis table • Statistics Table • Supporting Table

Data Wave • Stable Table • Increasing Table • Volatile Table

Data Size • Large Table • Small Table

Group Table • Group table by business module • Group table by relationship

Column Data Type • Choose data type • Char Varchar2 • Number Integer Float • Length • LOB • Store in row • Store in another tablespace

Maturity DB Process