Docstoc

Database concepts (CDM)

Document Sample
Database concepts (CDM) Powered By Docstoc
					Database Concepts

1

What is a Database
Database refers to any of the following:
    



Personal address book in a Word document Collection of Word documents Collection of Excel Spreadsheets Very large flat file on which you run some statistical analysis functions Data collected, maintained, & used in airline reservation Data used to support launch of a space shuttle
2

Database Management System (DBMS)
    

Collection of interrelated data Set of programs to access the data DBMS contains information about a particular enterprise DBMS provides an environment that is both convenient and efficient to use. Database Applications:
– – – – – – Banking: all transactions Airlines: reservations, schedules Universities: registration, grades Sales: customers, products, purchases Manufacturing: production, inventory, orders, supply chain Human resources: employee records, salaries, tax deductions



Databases touch all aspects of our lives
3

Purpose of Database System
 

In the early days, database applications were built on top of file systems Drawbacks of using file systems to store data:
– Data redundancy and inconsistency


Multiple file formats, duplication of information in different files

– Difficulty in accessing data


Need to write a new program to carry out each new task

– Data isolation — multiple files and formats – Integrity problems
 

Integrity constraints (e.g. account balance > 0) become part of program code Hard to add new constraints or change existing ones
4



Drawbacks of using file systems (cont.)
– Atomicity of updates




Failures may leave database in an inconsistent state with partial updates carried out E.g. transfer of funds from one account to another should either complete or not happen at all Concurrent accessed needed for performance Uncontrolled concurrent accesses can lead to inconsistencies – E.g. two people reading a balance and updating it at the same time

– Concurrent access by multiple users
 

– Security problems


Database systems offer solutions to all the above problems

5

Models of Reality
DML REALITY • structures • processes DDL

DATABASE SYSTEM
DATABASE


   

Database is a model of structures of reality Use of a database reflect processes of reality Database system is a software system which supports definition & use of a database DDL: Data Definition Language DML: Data Manipulation Language
6

When does DBMS is used
         

Persistent Storage of Data Centralized Control of Data Control of Redundancy Control of Consistency & Integrity Multiple User Support Sharing of Data Data Documentation Data Independence Control of Access & Security Backup & Recovery
7

Data Modeling
DATABASE SYSTEM
REALITY • structures • processes data modeling MODEL


 

Model represents a perception of structures of reality Data modeling process is to fix a perception of structures of reality & represent this perception In data modeling process we select aspects & we abstract
8

Database Design
purpose of database design is to create a database which
  

is a model of structures of reality supports queries & updates modeling processes of reality runs efficiently

9

Levels of Abstraction
 



Physical level describes how a record (e.g., customer) is stored. Logical level: describes data stored in database, and the relationships among the data. type customer = record name : string; street : string; city : integer; end; View level: application programs hide details of data types. Views can also hide information (e.g., salary) for security purposes.
10

View of Data
An architecture for a database system

11

Instances and Schemas
 

Similar to types and variables in programming languages Schema – the logical structure of the database
– e.g., the database consists of information about a set of customers and accounts and the relationship between them) – Analogous to type information of a variable in a program – Physical schema: database design at the physical level – Logical schema: database design at the logical level



Instance – the actual content of the database at a particular point in time
– Analogous to the value of a variable



Physical Data Independence – the ability to modify the physical schema without changing the logical schema
– Applications depend on the logical schema – In general, the interfaces between the various levels and components should be well defined so that changes in some parts do not seriously influence others.

12

Database Terminology
      

Data Models Keys & Identifiers Integrity & Consistency Triggers & Stored Procedures Null Values Normalization Surrogates - Things & Names

13

Data Model
A data model consists of notations for expressing:

 

Data Structures Integrity Constraints Operations

14

Data Model - Data Structures
All data models have notation for defining:
 



attribute types entity types relationship types

FLIGHT-SCHEDULE FLIGHT# 101 545 912 242 AIRLINE delta american sc&inavian usair WEEKDAY mo we fr mo PRICE 156 110 450 231

DEPT-AIRPORT FLIGHT# AIRPORT-CODE 101 912 545 atl cph lax

15

Data Model - Constraints
Constraints express rules that cannot be expressed by data structures alone:
  

Static constraints apply to database state Dynamic constraints apply to change of database state E.g., ―All FLIGHT-SCHEDULE entities must have precisely one DEPT-AIRPORT relationship
FLIGHT-SCHEDULE FLIGHT# 101 545 912 242 AIRLINE delta american sc&inavian usair WEEKDAY mo we fr mo PRICE 156 110 450 231 DEPT-AIRPORT FLIGHT# AIRPORT-CODE 101 912 545 242 atl cph lax bos 16

Data Model - Operations
Operations support change & retrieval of data:




insert FLIGHT-SCHEDULE(97, delta, tu, 258); insert DEPT-AIRPORT(97, atl); select FLIGHT#, WEEKDAY from FLIGHT-SCHEDULE where AIRLINE=‘delta’;
DEPT-AIRPORT WEEKDAY mo we fr mo tu PRICE 156 110 450 231 258 FLIGHT# AIRPORT-CODE 101 912 545 242 97 atl cph lax bos atl 17 AIRLINE delta american sc&inavian usair delta

FLIGHT-SCHEDULE FLIGHT# 101 545 912 242 97

Data Model - Operations from Programs
FLIGHT-SCHEDULE

declare C cursor for select FLIGHT#, WEEKDAY from FLIGHT-SCHEDULE where AIRLINE=‘delta’;

FLIGHT#

AIRLINE

WEEKDAY

PRICE

101
545 912 242

delta
american sc&inavian usair

mo
we fr mo

156
110 450 231

97 open C; repeat fetch C into :FLIGHT#, :WEEKDAY; do your thing; until done; close C;

delta

tu

258

18

Keys & Identifiers
Keys (or identifiers) are uniqueness constraints




A key on FLIGHT# in FLIGHT-SCHEDULE will force all FLIGHT#’s to be unique in FLIGHT-SCHEDULE Consider following keys on DEPT-AIRPORT:
AIRPORT-CODE FLIGHT# AIRPORT-CODE FLIGHT# AIRPORT-CODE FLIGHT# AIRPORT-CODE

FLIGHT#

FLIGHT-SCHEDULE FLIGHT# 101 545 912 242 AIRLINE delta american sc&inavian usair WEEKDAY mo we fr mo PRICE 156 110 450 231

DEPT-AIRPORT FLIGHT# AIRPORT-CODE 101 912 545 242 atl cph lax bos 19

Integrity & Consistency
 

Integrity: does model reflect reality well? Consistency: is model without internal conflicts?

 

a FLIGHT# in FLIGHT-SCHEDULE cannot be null because it models existence of an entity in real world a FLIGHT# in DEPT-AIRPORT must exist in FLIGHT-SCHEDULE because it doesn’t make sense for a non-existing FLIGHTSCHEDULE entity to have a DEPT-AIRPORT
FLIGHT-SCHEDULE FLIGHT# 101 545 912 242 AIRLINE delta american sc&inavian usair WEEKDAY mo we fr mo PRICE 156 110 450 231 DEPT-AIRPORT FLIGHT# AIRPORT-CODE 101 912 545 242 atl cph lax bos 20

Triggers & Stored Procedures


Triggers can be defined to enforce constraints on a database, e.g.,
DEFINE TRIGGER DELETE-FLIGHT-SCHEDULE ON DELETE FROM FLIGHT-SCHEDULE WHERE FLIGHT#=‘X’ ACTION DELETE FROM DEPT-AIRPORT WHERE FLIGHT#=‘X’;
FLIGHT-SCHEDULE FLIGHT# 101 AIRLINE delta WEEKDAY mo PRICE 156 DEPT-AIRPORT FLIGHT# AIRPORT-CODE 101 atl



545
912 242

american
sc&inavian usair

we
fr mo

110
450 231

912
545 242

cph
lax bos

21

Null Values
CUSTOMER CUSTOMER# NAME MAIDEN NAME DRAFT STATUS

123-45-6789 Lisa Smith Lisa Jones 234-56-7890 George Foreman inapplicable 345-67-8901 unknown Mary Blake
 

inapplicable drafted inapplicable

Null-value unknown reflects that attribute does apply, but value is currently unknown. That’s ok! Null-value inapplicable indicates that attribute does not apply. That’s bad!

22

Surrogates - Things & Names
reality
name custom# addr customer
custom# name addr

customer

name-based representation
reality

name

custom# addr

customer
customer custom# name addr

customer

surrogate-based representation
 

name-based: a thing is what we know about it surrogates are system-generated, unique, internal identifiers
23

DATA MODELS
   

ER-Model Hierarchical Model Relational Model Object-Oriented Model(s)

24

ER Model

25

ER Model
dept time airport name airport addr airport code airport 1 street city zip arriv airport n 1 domestic flight dept airport

visa required
international flight p flight schedule flight#

1

n

weekdays

arriv time
customer# n reservation date

instance of n

customer name

n

customer

flight instance

seat# 26

Hierarchical Model
flight-sched flight#

flight-inst date

dept-airp airport-code

arriv-airp airport-code

customer customer# customer name

parent-child relationship types (1:n only!!): (flight-sched,flight-inst), (flight-inst,customer)  one record type is root, all or record types is a child of one parent record type only  substantial duplication of customer instances


27

Relational Model
 



Data Structures Integrity Constraints Operations




Commercial systems include: ORACLE, DB2, SYBASE, INFORMIX, INGRES, SQL Server Dominates database market on all platforms

28

Relational Model - Data Structures
 



domains attributes relations
relation name

attribute names flight-schedule flight#: airline: weekday: price: integer char(20) char(2) dec(6,2) domain names

29

Relational Model - Integrity Constraints
 




Keys Primary Keys Entity Integrity Referential Integrity
customer customer# customer name p reservation flight# date customer#
30

flight-schedule flight# p

Relational Model - Operations
 

Powerful set-oriented query languages Relational Algebra: procedural; describes how to compute a query; operators like JOIN,
SELECT, PROJECT





Relational Calculus: declarative; describes desired result, e.g. SQL, QBE insert, delete, & update capabilities

31

Object-Oriented Model(s)
  

based on object-oriented paradigm, e.g., Simula, Smalltalk, C++, Java area is in a state of flux object-oriented model has object-oriented repository model; adds persistence & database capabilities; object-oriented commercial systems include GemStone, Ontos, Orion-2, Statice, Versant, O2 object-relational model has relational repository model; adds object-oriented features; object-relational commercial systems include Starburst, POSTGRES
32



 


				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:680
posted:11/13/2008
language:English
pages:32