Database Design Lecture 21 Database System Architecture and

Database Design Lecture 2_1 Database System Architecture and concepts Text Chapter 2 Database Design lecture 2 Slide 1

ANSI-SPARC Three-level Architecture q q q External Level – Users' view of the database. – Describes that part of database that is relevant to a particular user. Conceptual Level – Community view of the database. – Describes what data is stored in database and relationships among the data. Internal Level – Physical representation of the database on the computer. – Describes how the data is stored in the database. Database Design lecture 2 Slide 2

Database Design lecture 2 Slide 3

Illustrating Example Database Design lecture 2 Slide 4

Data Independence q Logical Data Independence – Refers to immunity of external schemas to changes in conceptual schema. – Conceptual schema changes (e. g. addition/removal of entities) should not require changes to external schema or rewrites of application programs. Database Design lecture 2 Slide 5

Data Independence q Physical Data Independence – Refers to immunity of conceptual schema to changes in the internal schema. – Internal schema changes (e. g. using different file organizations, storage structures/devices) should not require change to conceptual or external schemas. Database Design lecture 2 Slide 6

Data Models q A collection of concepts for the description of data – High-level (semantic) models: ØProvide concepts that are close to the way end users perceive data. Eg entity-relationship model, object model – Logical models: ØProvide concepts about logical structure of data, closer to physical implementation. Eg Network, hierarchical, relational, object-oriented – Low-level (physical) models ØProvide concepts about physical structure of data on the storage media Database Design lecture 2 Slide 7

Schemas, Instances, and Database State q important to distinguish between description of database and the database itself – schema (intention) Ø when we define a new database, we specify its schema – the structure that describes the database Ø this schema remains 'fairly' static throughout the production life of the database – instance (a. k. a. database state / extension) Ø The actual data in the database at any point of time Ø when we initially load data into the database, it is said to move into the initial state of the database (ie. snapshot) Ø each write operation (insert, delete, modify) changes the current state of the database to its new state Database Design lecture 2 Slide 8

Database Languages q Data Definition Language (DDL) – Allows the DBA or user to describe and name entities, attributes, and relationships required for the application – plus any associated integrity and security constraints. Database Design lecture 2 Slide 9

Database Languages q Data Manipulation Language (DML) – provides basic data manipulation operations on data held in the database – query and update – Procedural DML Øallows user to tell system exactly how to manipulate data. – Non-Procedural DML Øallows user to state what data is needed rather than how it is to be retrieved. Database Design lecture 2 Slide 10

Classification of DBMSs q Based on the logical data model used: – Traditional: ØNetwork, Hierarchical (first generation) ØRelational (second generation) ØObject-oriented, Object-relational (third generation). – Emerging: ØSemi-structured. q Other classifications – Single-user vs. multi-user – Centralized vs. distributed Database Design lecture 2 Slide 11