PPT《DATABASE CONCEPTS》

CUSTOMER Tom Ed Nick ... Liz Joe Louise Aggregation In an aggregation we form a concept from existing concepts. The phenomena that are members of the new concept'

s extension are composed of phenomena from the extensions of the existing concepts AIRPLANE COCKPIT ENGINE WING Generalization In a generalization we form a new concept by emphasizing common aspects of existing concepts, leaving out special aspects CUSTOMER ECONOMYCLASS BUSINESSCLASS 1STCLASS Generalization (cont.) CUSTOMER BUSINESSCLASS 1STCLASS Subclasses may overlap Subclasses may have multiple superclasses MOTORIZED VEHICLES AIRBORNE VEHICLES TRUCKS HELICOPTERS GLIDERS Relationships Between Abstractions T T T O O O aggregation generalization classification AbstractionConcretization classificationexemplification aggregationdecomposition generalizationspecialization intension extension DATABASE TERMINOLOGY Data ModelsKeys and IdentifiersIntegrity and ConsistencyTriggers and Stored ProceduresNull ValuesNormalizationSurrogates - Things and Names Data Model data structuresintegrity constraintsoperations A data model consists of notations for expressing: Data Model - Data Structures attribute typesentity typesrelationship types FLIGHT# AIRLINE WEEKDAY PRICE FLIGHT-SCHEDULE

101 delta mo

156 545 american we

110 912 scandinavian fr

450 242 usair mo

231 DEPT-AIRPORT FLIGHT# AIRPORT-CODE

101 atl

912 cph

545 lax All data models have notation for defining: Data Model - Constraints Static constraints apply to database stateDynamic constraints apply to change of database stateE.g., All FLIGHT-SCHEDULE entities must have precisely one DEPT-AIRPORT relationship FLIGHT# AIRLINE WEEKDAY PRICE FLIGHT-SCHEDULE

101 delta mo

156 545 american we

110 912 scandinavian fr

450 242 usair mo

231 Constraints express rules that cannot be expressed by the data structures alone: DEPT-AIRPORT FLIGHT# AIRPORT-CODE

101 atl

912 cph

545 lax

242 bos Data Model - Operations insert FLIGHT-SCHEDULE(97, delta, tu, 258)insert DEPT-AIRPORT(97, atl);

select FLIGHT#, WEEKDAY from FLIGHT-SCHEDULE where AIRLINE='

delta'

;

Operations support change and retrieval of data: FLIGHT# AIRLINE WEEKDAY PRICE FLIGHT-SCHEDULE

101 delta mo

156 545 american we

110 912 scandinavian fr

450 242 usair mo

231 97 delta tu

258 DEPT-AIRPORT FLIGHT# AIRPORT-CODE

101 atl

912 cph

545 lax

242 bos

97 atl Data Model - Operations from Programs declare C cursor for select FLIGHT#, WEEKDAYfrom FLIGHT-SCHEDULEwhere AIRLINE='

delta'

;

open C;

repeatfetch C into :FLIGHT#, :WEEKDAY;

do your thing;

until done;

close C;

FLIGHT# AIRLINE WEEKDAY PRICE FLIGHT-SCHEDULE

101 delta mo

156 545 american we

110 912 scandinavian fr

450 242 usair mo

231 97 delta tu

258 Keys and Identifiers A key on FLIGHT# in FLIGHT-SCHEDULE will force all FLIGHT#'

s to be unique in FLIGHT-SCHEDULEConsider the following keys on DEPT-AIRPORT: Keys (or identifiers) are uniqueness constraints FLIGHT# AIRPORT-CODE FLIGHT# AIRPORT-CODE FLIGHT# AIRPORT-CODE FLIGHT# AIRPORT-CODE DEPT-AIRPORT FLIGHT# AIRPORT-CODE

101 atl

912 cph

545 lax

242 bos FLIGHT# AIRLINE WEEKDAY PRICE FLIGHT-SCHEDULE

101 delta mo

156 545 american we

110 912 scandinavian fr

450 242 usair mo

231 Integrity and Consistency Integrity: does the model reflect reality well?Consistency: is the model without internal conflicts?a FLIGHT# in FLIGHT-SCHEDULE cannot be null because it models the existence of an entity in the real worlda FLIGHT# in DEPT-AIRPORT must exist in FLIGHT-SCHEDULE because it doesn'