資料庫系統簡介臺北大學統計系莊東穎

T.Y. Juang Web DBChapter 1 - 1

資料庫系統簡介

臺北大學統計系莊東穎


Data Hierarchy Hierarchy of Data

– bits

– bytes (characters)

– fields - characters joined together

– records - a collection of related fields

– files - a collection of records

– databases - a collection of linked files


Data Hierarchy

Bits

Bytes

Fields

Records

Files

Databases


Definition

Database a collection of data organized in a manner that

allows users to retrieve and use those data an integrated collection of logically related

records and files a collection of information stored in an

organized form in a computer


Why Database?

– Compactness, Speed, Less Drudgery & Currency• A database consists of some collection of persistent data that is

used by the application systems of some given enterprise– Data Administration

• done by Data Administrator (DA)• a senior management level• decide what data should be stored in the database; establish the

policy of maintaining and dealing with the data (e.g., data security policy)

– Database Administration• done by Database Administrator (DBA)• technical level• create database and control the performance of the system


Definition

DataBase Management System (DBMS) a software package used to interact with a database a software tool for organizing storage and retrieval of that information a software involves the control of how database are created, interrogated, and maintained to provide information needed by end users and the organization


Major components of a database systemData: integrated and shared.Hardware: disk, CPU, Main Memory ...Software: DBMSUsers :

1. Application programmers 2. End users 3. Database administrator (DBA)

• Defining conceptual schema• Defining internal schema • Liaising with users • Defining security and integrity checks• Defining backup and recovery procedures • Monitoring performance and changing requirements


Files and File Management

Payrollprogram

Invoicingprogram

Inventory controlprogram

Payrollfiles

Invoicingfiles

Inventoryfiles


Databases

Payrollprograms

Invoicingprograms

Inventory controlprograms

DBMS Database

DBMS Database


Database System Database System Considerations

what information the database will contain? how much access should be given to various

users? how should the database be physically

organized? how can the database structure be made to seem

logical to the user?


Design Goals Goals

Overcome data redundancy Overcome program dependency on data Allow a large number of users to draw

information easily Link several files for data processing


Database System ArchitectureHost

Language+ DSL

Host Language

+ DSL

Host Language

+ DSL

Host Language

+ DSL

Host Language

+ DSL

User A1 User A2 User B1 User B2 User B3

External ViewA

External ViewB

External/conceptualmapping A

ConceptualView

External/conceptualmapping B

Conceptual/internalmapping

Stored database (Internal View)

Databasemanagement

system(DBMS)

<

DBA

Storagestructuredefinition(Internalschema)

Conceptualschema

Externalschema

A

Externalschema

B

(Build andmaintainschemas

andmappings)


Database Models Models (Structures)

a data model describes components of the database and how they work together

a data model describes linking of records that share fields

Common data models Hierarchical Network Relational Object Oriented


Models

Hierarchical database Network database Relational database


Hierarchical Data Models

Hierarchical The first conceptual model One-to-many relationship All relationships are established when the database is

created All access is through pre-established links A child can have only one parent IMS, DL/1 (IBM), System 2000(MRI),

METABAS(PMI), MARK IV(INFORMATICS)...


Network Data Models Network

Like a hierarchical database Can represent more complex logical relationships A child can have more than one parent Parent is called an owner, child is called a member CODASYL(Conference On Data Systems

Languages ) Permits all relationships: one-to-one, one-to-many,

many-to-many DMS-11(Burroughs), DMS-170(CDC),

TOTAL(Cincom), IDMS(Cullinane), DMS1100(Univac)


Network Data ModelsNetwork

Advantages More flexible than hierarchical database Successfully implemented and marketed

Disadvantages Schema must be defined at time database is created Retrieval of data is based solely on the schema


Relational Data ModelsRelational

E. F. Codd (1969) Data organized into tables or files called “relations” Tables have - rows, tuples or record

- columns, fields or attributes Range of values for a field is the domain Relationships are determined at time of use Relationships are created by operations on tables Operations are performed at the time of a request No predetermined schema is required


Relational Data Models Relational

Users can ask virtually any questions Virtually all modern DBMS support these Most popular DB2 (IBM), Oracle (Oracle), SQL Server

(Sybase), INGRES (Computer Associ

ate), INFORMIX OnLine (INFORMIX), M

S SQL Server (MS), Access (MS), ….


關聯性資料庫管理系統 ( 續 )

stor_id

706670677131804263807896

stor_name

Bamum’sNews & BrewsDoc-U-Mat:Quality Laundry & BooksBookbeatEric the Read BooksFricative Bookshop

city

TustinLos GatosRemuladePortlandSeattleFremont

state

CACAWAORWACA

zip

927899674598014890769805690019

Stores Table


stor_id

706670677131804263807896

stor_name

Bamum’sNews & BrewsDoc-U-Mat:Quality Laundry & BooksBookbeatEric the Read BooksFricative Bookshop

city

TustinLos GatosRemuladePortlandSeattleFremont

state

CACAWAORWACA

zip

927899674598014890769805690019

Stores Table

Sales Tablestor_id

706670677131713180428042638063808042789678967896804270667131713171317131706770677067

stor_name

QA7442.3D4482N914008N914014423LL922423LL930722a6871P723X999QQ2299TQ456QA879.1A2976P3087aP3087aP3087aP3087aP2121P2121P2121

data

SepSepSepSepSepSepSepSepMarFebOctDecMayMayMayMayMayMayJunJunJun

131414141414131411212812222429292929151515

198519851985198519851985198519851988198819871987198719871987198719871987198719871987

12:AM12:AM12:AM12:AM12:AM12:AM12:AM12:AM12:AM12:AM12:AM12:AM12:AM12:AM12:AM12:AM12:AM12:AM12:AM12:AM12:AM

qty

7510202515103525351510305020251525402020

payterms

On invoiceNet 60Net 30Net 30On invoiceOn invoiceNet 60Net 60Net 30On invoiceNet 60Net 60Net 30Net 30Net 60Net 60Net 60Net 60Net 30Net 30Net 30

title_id

PS2091PS2091PS2091MC3021MC3021BU1032PS2091BU1032BU1111BU2075BU7832MC2222PC1035PC8888PS1372PS2106PS3333PS7777TC3218TC4203TC7777

Discounts Tablestor_id

71318042

discount

6.75.0


資料具整合性 (Data Integrity)使用結構化查詢語言 (Structured Query

Language) 維護及查詢資料方便性高效率的資料儲存方式安全性

關聯性資料庫管理系統 ( 續 )


RDBMS 基本功能 Data Definition

Data Manipulation

Data Control Statement

Data Retrieval Data Modification

Data consistencyRecoveryBackup and RestoreSecurityStored ProcedureTriggers

Creating Databases Creating tables

view grant


Object Oriented Data Model Object Oriented Database

Better handle more complex types of data (graphics, pictures, voice, text) than other database structures

Support encapsulation, inheritance Jasmine (CA) -物件資料庫系統


Normalization Normalization

– a logical database design involves using formal methods to separate the data into multiple, related tables

– A greater number of narrow tables (with fewer columns) is characteristic of a normalized database

– To improve performance– First Normal Form (1NF), Second Normal Form

(2NF), and Third Normal Form (3NF) and Forth Normal Form (4NF)

資料庫系統簡介臺北大學統計系莊東穎

Documents

Transcript of 資料庫系統簡介臺北大學統計系莊東穎

資料庫系統簡介 臺北大學統計系 莊東穎

Documents

Transcript of 資料庫系統簡介 臺北大學統計系 莊東穎

資料庫系統簡介臺北大學統計系莊東穎

Transcript of 資料庫系統簡介臺北大學統計系莊東穎