Transcript 슬라이드 1

Databases :
Database Building
Procedures
2007, Fall
Pusan National University
Ki-Joune Li
PNU
STEM
Importance of Database
Application of
Databases
Garbage-In Garbage-Out
About 70% of Development Cost for Information System: DB Cost
2
PNU
STEM
Comparison with Software Lifecycle
Requirement Analysis
Requirement Analysis
Functional Specification
Modeling
Design
Schema Design
Development Environments
DB Environments
Coding
Data Collection and Input
Test
Quality Control
Maintenance
Maintenance
Software Life Cycle – Waterfall Model
DB Life Cycle
3
PNU
STEM
Requirement Analysis

Analysis of Status


as it is and
as it shall be.
Current State: As it is

As it must 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
4
PNU
STEM
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
5
PNU
STEM
Data Modeling

Data Modeling


Understanding the real world and application
A very small piece of the real world



Drawing what you have understood in formal method


According to viewpoint
Determined by applications
Class Diagram in UML
4 steps




Definition of Entities
Attributes of each Entity
Relationships
Constraints
6
PNU
STEM
Class Diagram: Basic
MyClassName
+SomePublicAttribute : SomeType
Multiplicity
Customer
-SomePrivateAttribute : SomeType
#SomeProtectedAttribute : SomeType
Simple
1
Class
Aggregation
+ClassMethodOne()
+ClassMethodTwo()
Responsibilities
Rental Invoice
-- can optionally be described here.
Abstract
Class
Rental Item
{abstract}
1..*
1
0..1
Composition
Simple
(Dependency)
Generalization
Association
Checkout Screen
DVD Movie
VHS Movie
Video Game
7
PNU
STEM
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
8
PNU
STEM
Definition of Attributes

Attributes of Entity

Description of Entity



MyClassName
Photo Images, Names, and etc..
Values
+SomePublicAttribute : SomeType
-SomePrivateAttribute : SomeType
#SomeProtectedAttribute : SomeType
Accuracy Level

Different Levels of Detail (LOD)
9
PNU
STEM
Relationship

Relationship


Relationship between entities
Relationship may have its attributes

Example: Male  marriage  Female
marriage:
10
PNU
STEM
Constraints

Conditions



Entity must respect
Relationship must respect
Example

Doctor  Operation  Patient
Doctor.Major = “Surgeon”
11
PNU
STEM
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
12
PNU
STEM
Materialization

In SQL, view is a virtual table derived from a Select
statement

Eample
Materialization
CREATE VIEW ExcellentStudents AS
SELECT Name, Department, Score
FROM Students
WHERE Score > 4.0
SELCT Name
FROM ExcellentStudents
Where Department=‘CS’
ExcellentStudents
Invoke
13
PNU
STEM
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
14