Transcript ppt

Database Overview
Evolution of Database System
Evolution of Database
1960s
1970s
1980s
1990s
2000+
File-based
Hierarchical
Network
Relational
Object-oriented
Entity-Relationship
Web-based
NoSQL
NewSQL
Database Design
2
Database: Historical Roots

Manual File System
To keep track of data
Used tagged file folders in a filing cabinet
Organized according to expected use
• e.g. file per customer
Easy to create, but hard to
• locate data
• aggregate/summarize data

Computerized File System
To accommodate the data growth and information need
Manual file system structures were duplicated in the computer
Data Processing (DP) specialists wrote customized programs to
• write, delete, update data (i.e. management)
• extract and present data in various formats (i.e. report)
Database Design
3
File System: Example
Database Systems: Design, Implementation, & Management: Rob & Coronel
Database Design
4
File System: Weakness

Weakness
“Islands of data” in scattered file systems 분산된 파일시스템.

Problems
Duplication 중복
• Same data may be stored in multiple files
Inconsistency 불일치
• Same data may be stored with different values/formats
Rigidity 경직성
• Requires customized programming to implement any changes
• Cannot do ad-hoc queries 즉석질의 불가

Implications
Waste 낭비 of space
Data inaccuracies 오류
High overhead 간접비용 of data manipulation and maintenance
Database Design
5
File System: Problem Case
CUSTOMER file
AGENT file
A_Name (15 char)
A_Name (20 char)
Carol Johnson
Carol T. Johnson
SALES file
AGENT (20 char)
Carol J. Smith
• Inconsistent field name, field size
• inconsistent data values
• data duplication
Database Design
6
Database System vs. File System
Database Systems: Design, Implementation, & Management: Rob & Coronel
Database Design
7
Hierarchical Data Model 계층적 데이터 모델

Hierarchical Model

To manage large amount of data for complex manufacturing projects
•
→
Information Management System developed by Rockwell & IBM
Files connected in Parent-Child (1:M) relationships
•
1 Parent - Multiple Children
Database Systems: Design, Implementation, & Management: Rob & Coronel
Database Design
8
Hierarchical Data Model 계층적 데이터 모델

Strengths

Conceptual Simplicity 개념적 단순성

Groups of data could be related to each other
 Related data could be viewed together

Centralization of data


Reduced redundancy  중복 and promoted consistency  일관성
Weaknesses

Limited representation of data relationships


Structural Dependence 구조 의존


Data access requires physical storage path
Complex Implementation 복잡한 구현


Did not allow Many-to-Many (M:N) relations
Required in-depth knowledge of physical data storage
Lack of Standards 표준 부족

Database Design
Limited portability
9
Network Data Model 네트워크 데이터 모델

Network Model
→
Extension of Hierarchical Model
•
→
Composed of Owner-Member (Parent-Child) sets
To represent Many-to-Many (M:N) relationships
•
Multiple Parents – Multiple Children
Database Systems: Design, Implementation, & Management: Rob & Coronel
Database Design
10
Relational Data Model 관계형 데이터 모델

Problems with legacy database systems
Required excessive effort to maintain
• Data manipulation (programs) too dependent on physical file structure
Hard to manipulate by end-users
• No capacity for ad-hoc query (must rely on DB programmers).

Relational Model

E. F. Codd’s proposal
• Separated the notion of physical representation (machine-view)
from logical representation (human-view)
→ Eliminated pointers and used tables to represent data
• Considered ingenious but computationally impractical in 1970

Dominant database model of today

Separation of design from implementation → Flexible
 Ad-hoc queries → Structured Query Language (SQL)
Database Design
11
Relational Database: Example

Tables (i.e. Relations)

→
→
Provide a logical “human-level” view of the data
and associations among groups of data
Organize data into rows 행 (records/tuples) and columns 열 (attributes)
Are related via shared attribute(s)
Customer_ID Customer_Account Agent_ID
1224
4556
1225
4558
Agent_ID
Customer_ID Last_Name
1224 Vira
1225 Davies
Database Design
Last_Name
23 Sturm
25 Long
First_Name
Dyne
Tricia
23
25
First_Name
David
Kyle
Phone
334-5678
556-3421
Phone
Account_Balance
678-9987
1223.95
556-3342
234.25
12
Entity Relationship Model

Peter Chen’s Landmark Paper (1976)
“The Relationship Model: Toward a Unified View of Data”
Graphical representation of entities and their relationships

Based on Entity, Attributes & Relationships

Entity → e.g. EMPLOYEE
• Thing about which data are to be collected and stored

Attributes → e.g. SSN, last name, first name
• Characteristics of the entity

Relationships → i.e. 1:M, M:N, 1:1
• Associations between entities
 Complements the relational data model concepts
•
•
Helps to visualize structure and content of data groups
Entity Relationship Diagram (ERD)
→ Tool for conceptual data modeling
→ Formalizes a way to describe relationships between groups of data
Database Design
13
E-R Diagram: Chen Model

Entity 개체


Relationship 관계


represented by a rectangle with its
name in capital letters.
represented by an active or passive
verb inside the diamond that
connects the related entities.
Connectivity 관계유형


i.e., types of relationship
written next to each entity box.
Database Systems: Design, Implementation, & Management: Rob & Coronel
Database Design
14
E-R Diagram: Crow’s Foot Model

Entity 개체


Relationship 관계


represented by a rectangle with its
name in capital letters.
represented by an active or passive
verb that connects the related
entities.
Connectivity 관계유형

indicated by symbols next to
entities.


2 vertical lines for 1
“crow’s foot” for M
Database Systems: Design, Implementation, & Management: Rob & Coronel
Database Design
15
E-R Model: Pros & Cons

Advantages

Exceptional conceptual simplicity



Integration with the relational database model


Easily viewed and understood representation of database
Facilitates database design and management
Enables better database design via conceptual modeling
Disadvantages

Incomplete model on its own

Limited representational power
→
→


cannot model data constraints not tied to entity relationships
 e.g. attribute constraints
cannot represent relationships between attributes within entities
No data manipulation language (e.g. SQL)
Loss of information content

Database Design
Hard to include attributes in ERD
16
Object-Oriented Database

Semantic Data Model (SDM)
►
Modeled both data and their relationships in a single structure (object)


Developed by Hammer & McLeod in 1981
Object-oriented concepts became popular in 1990s
►
►

객체지향
Modularity facilitated program reuse and construction of complex structures
Ability to handle complex data types (e.g. multimedia data)
Object-Oriented Database Model (OODBM)
►
►
Maintains the advantages of the ER model but adds more features
Object = entity + relationships (between & within entity)

►
consists of attributes & methods
→ methods are all relevant operations that can be performed on an object
Class  Template for objects


e.g. EMPLOYEE class = (employ1 object, employ2 object, …)
organized in a class hierarchy
→
►
e.g. PERSON > EMPLOYEE, CUSTOMER
Incorporates the notion of inheritance

Database Design
attributes and methods of a class are inherited by its descendent classes
17
OO Database Model vs. E-R Model
OODBM:
- can accommodate relationships within a object
- objects to be used as building blocks for autonomous structures
Database Systems: Design, Implementation, & Management: Rob & Coronel
Database Design
18
Object-Oriented Database: Pros & Cons

Advantages

Semantic representation of data



Modularity, reusability, inheritance
Ability to handle



Fuller and more meaningful description of data via object
Complex data
Sophisticated information requirements
Disadvantages

Lack of standards


Complex navigational data access


Class hierarchy traversal
Steep learning curve


No standard data access method
Difficult to design and implement properly
High system overhead

Database Design
Slow transactions
19
Web Database

Not a database model, but a system



For storing information that can be accessed via Web
That supports complex data types & relationships
In a Client-Server architecture

Server hosts database & DBMS (e.g., MySQL)
 Client accesses the server for database use
Client
Server
Initiates a Connection
Waits & Responds
to Incoming Connections
Database
Web
Client
HTTP request
Web
Server
Data request
DB
Server
(e.g. Chrome)
Webpage
(e.g. Apache)
Retrieved data
(e.g. MySQL)
Database Design
20
NoSQL/NewSQL Database

NoSQL (Not Only SQL)


Non-relational: e.g., objects instead tables
For big (unstructured, distributed) data & real-time Web applications
More scalable & better performance
 Flexible & agile development


NewSQL

NoSQL + Relational
Consistent
→ Scalable
→ Flexible
→
Database Design
21
MS Access
Introduction
Database Design
22
MS Access: Overview

MS Access
A Database Management System (DBMS)
• designed to create applications that organize, store, retrieve, and
manipulate large collections of data.
GUI-driven with built-in automations
Based on relational database theory

Theory & Practice
Need a solid understanding of database theory and principles
as well as DBMS skills to develop an effective database system.
DBMS expert ≠ Database Design expert
MS Access: Database Objects
•
Objects
–
Tables store data
–
Forms display data
• for viewing, editing, entering
–
Reports summarize & present data
• forms can change data, but reports cannot.
–
Queries manipulate data
• combine, filter, modify, retrieve, etc.
•
–
Macros are simplified programs that automate tasks.
–
Modules are Visual Basic for Application (VBA) programs.
Object Views
–
(Normal) View
• the mode used to interact w/ the object & its data.
–
Design view
• the mode used to change the object design.
GCF LearnFree.org
MS Access: User Interface
Database Design
GCF LearnFree.org
25
MS Access: The Ribbon
Minimize
Ribbon
GCF LearnFree.org
 Common activities/commands organized by tabs & groups
• Tabs → Groups → Commands
Database Design
26
MS Access: Quick Access Toolbar
Right Click
 Direct access to any command
•
•
Database Design
Click Customize Quick Access Toolbar icon & select More Commands…
Right-click a command & click Add to Quick Access Toolbar
27
MS Access: Navigation Pane
 Contains every object in the database
Database Design
•
Objects are organized into groups by type
•
Double-click the object name to open
•
Right-click to rename
•
Press the delete key to delete objects
•
Click the double arrow
to minimize/maximize the navigation pane
28
MS Access: File Tab
 Shows general file commands
Database Design
•
Recent → Recently open files
•
Options → Access options
29