Databases : Database Building Procedures

1
Databases Database Building Procedures

• 2007, Fall
• Pusan National University
• Ki-Joune Li

2
Importance of Database
Application of Databases
Garbage-In Garbage-Out
About 70 of Development Cost for Information
System DB Cost
3
Comparison with Software Lifecycle
Requirement Analysis
Functional Specification
Design
Development Environments
Coding
Test
Maintenance
Software Life Cycle Waterfall Model
4
Requirement Analysis

• Analysis of Status
• as it is and
• as it shall be.
• Output of Analysis
• Use-Case Diagram of UML Workflow Analysis
• Data items that have been maintained and to be
  maintained
• Description of each item Data Dictionary
• Relationships and Constraints on items
• Required Correctness
• Example Spatial Correctness, Temporal Correctness

Current State As it is
As it must be
5
Data Dictionary

• Definitions and Representation of Data Items such
  as
• Precise definition of data elements
• Integrity constraints or Constrains
• Stored procedures and trigger rules
• Specification of
• Producer and
• Consumer of data element
• Why it is so important?
• Common understanding on data items
• Consistency of databases
• Important input to data modeling

6
Data Modeling

• Data Modeling
• Understanding the real world and application
• A very small piece of the real world
• According to viewpoint
• Determined by applications
• Drawing what you have understood in formal method
• Class Diagram in UML
• 4 steps
• Definition of Entities
• Attributes of each Entity
• Relationships
• Constraints

7
Class Diagram Basic
Multiplicity
Customer
Simple Aggregation
1
Class
Abstract Class
Rental Invoice
1..
Rental Item abstract
1
0..1
Composition (Dependency)
Simple Association
Generalization
Checkout Screen
DVD Movie
VHS Movie
Video Game
8
Definition of Entities

• Extract nouns from
• Problem statement
• Use-Case Diagram
• Delete unnecessary entities
• Duplication
• Attributes rather than entity
• ex. Loan amount
• Definition of Features
• Geographic Entity
• Granularity

MyClassName
9
Definition of Attributes

• Attributes of Entity
• Description of Entity
• Photo Images, Names, and etc..
• Values
• Accuracy Level
• Different Levels of Detail (LOD)

MyClassName
SomePublicAttribute SomeType -SomePrivateAttrib
ute SomeType SomeProtectedAttribute SomeType
10
Relationship

• Relationship
• Relationship between entities
• Relationship may have its attributes
• Example Male ? marriage ? Femalemarriage

11
Constraints

• Conditions
• Entity must respect
• Relationship must respect
• Example
• Doctor ? Operation ? PatientDoctor.Major
  Surgeon

12
Schema Design

• Automatic Conversion from Data Modeling to Schema
• Check Points Performance Issues
• Materialization
• Index
• Based on Workload Analysis
• Distribution of operations
• Distribution of values

13
Materialization

• In SQL, view is a virtual table derived from a
  Select statement
• Eample
• CREATE VIEW ExcellentStudents ASSELECT Name,
  Department, ScoreFROM StudentsWHERE Score gt 4.0
• SELCT NameFROM ExcellentStudentsWhere
  DepartmentCS

14
Materialize or Not ?

• Materialization
• Duplication
• Not 3NF (BCNF) to be discussed at the end of
  this course
• Cause an inconsistency between the original and
  derived tables
• Update Overhead due to update propagation
• Extra Space Requirements
• Should be determined depending on the WORKLOAD
• Frequency of updates
• Cost for update propagation
• Especially when materialized view is
  geographically distributed