IST 210 Databases and DBMSs Todd S Bacastow

IST 210 Databases and DBMSs Todd S. Bacastow January 2005 1

IST 210 A Process of Mapping Real World –High level model –Comprises –“Things” –“Descriptions” –“How things are connected” Conceptual Data Model –Relational –Hierarchical –Network –Object Oriented Logical Data Model Physical Data Model 2

IST 210 Data Models § A data model describes § the structure of a database § data types, relationships, constraints § a set of basic operations § insert, delete, modify, retrieve § user-defined operations 3

IST 210 Types of Data Models § Conceptual § concepts: entity, attribute, relationship § Entity-Relationship model (DBMS-independent) § Logical § data represented by record structure § E. g. relational, network, hierarchical § Physical § describes how data is stored in the disk 4

IST 210 DBMS Architecture External Level External View Conceptual Level Internal Level External View Conceptual Schema Internal Schema 5

External View IST 210 External Level External View Conceptual Schema Internal Schema § Describes a part of the database for a particular § § user group and hides the rest Supports multiple views of a database Same data model as the conceptual schema External Level External View 6

External View IST 210 Conceptual Level External View Conceptual Schema Internal Schema § Data Abstraction § hides unnecessary details § Conceptual Level § hides physical layer § Data types, Constraints, User Operations § Uses both conceptual/logical data models Conceptual Level Conceptual Schema 7

External View Conceptual Schema IST 210 Internal Level § Defines physical storage on the disk § Defines data location Internal Schema § path, blocks, pages, … § Device specific Internal Level Internal Schema STORED_EMP BYTES=20 PREFIX BYTES=20, OFFSET=0 EMP# BYTES=20, OFFSET=6, INDEX=EMPX DPET# BYTES=20, OFFSET=12 PAY BYTES=20, OFFSET=16, ALIGN=FULLWORD 8

IST 210 DB Schema vs. DB State § Database Schema § description of the database § is specified during database design § Database State (extension of the schema) § current state of the database: a snapshot § actual data instances in a DB § changes over time by update § initially, a database is empty state with no data 9

IST 210 DB Schema vs. DB State § Valid State § DBMS checks every state of the database § does it satisfy the structure and constraints specified in the schema? § Schema Diagram § Displays database schema 10

IST 210 Example Schema 11

IST 210 Database Schema Designer Goal : develop a schema that changes infrequently § Metadata § descriptions of the schema constructs and constraints § stored in the database catalog § Schema Evolution § Schema change prompted by the change of application requirements 12

External View IST 210 DBMS Mapping n External View Conceptual Schema Internal Schema Mappings for multi-level DBMS § to transform a request specified at one level into the request at another level § access: external conceptual internal DB § retrieve: DB internal conceptual external § Three-Schema Architecture § advantage: true data independence § disadvantage: overhead cost of mappings 13

IST 210 Data Independence § What happens when the schema changes at some level? § Data Independence § the capacity to change the schema at one level without having to change the schema at the next higher level § Two Types of Data Independence § logical and physical data independence 14

Data Independence (con’t) IST 210 1. Logical Data Independence § § capacity to change the conceptual schema without having to change the external schema when: logical reorganization of the database 2. Physical Data Independence § § change the internal schema without having to change the conceptual schema when: physical reorganization of the files 15

DBMS Languages IST 210 n Data Definition Language (DDL) • to define DB conceptual schema § Data Manipulation Language (DML) • • • to specify database requests: update, retrieval high-level DML: describes which data to retrieve low-level DML: describes how to retrieve it 16

DBMS Languages (con’t) IST 210 n n n High-level DML: set-oriented, declarative Low-level DML: record-oriented, procedural Types of DML • • • data sublangauge: DML embedded in a general purpose language (for DBAs) query language: high-level, interactive, stand-alone DML (casual end users) user-friendly interface for DML (naïve users) 17

IST 210 n Database System Environment DBMS Component Modules § Managers, i. e. , disk control § Compiler, i. e. , query § Processors 18

IST 210 19

DBMS Interfaces IST 210 n n n Menu-based interfaces Forms-based interfaces Natural language interfaces n n interpret requests to high-level queries Command line 20

IST 210 System Utilities & Tools § Loading § loads existing data files into the database § DBMS conversion, reformatting the data § Backup § provides a backup copy of the database § incremental backup: updates changes only § File Reorganization § to improve performance 21

IST 210 System Utilities & Tools § Performance Monitoring § monitors database usage § provides statistics § Data Dictionary § also called information repository § stores additional information: (catalog) + design decisions, usage standards, user information, application program descriptions 22

IST 210 Mainframe/ terminal Mainframe/terminal n n n Storage, Logic and Presentation all in same place No platform specific user interface Doesn’t take advantage of client machine Mainframe Storage Logic Presentation Network Terminal 23

IST 210 Client Server without stored procedures Server n n n Database server handles storage only Logic and presentation in client Takes advantage of client cpu Logic changes require client redistribution Integrity not maintained if other DB tool used Each user needs to be a specific database user DBMS Storage Network Client Logic Presentation 24

IST 210 n n Client Server with stored procedures Database handles storage and business logic Logic changed in one place, no redistribution of client DBMS dependent code Each user needs to be specific database user Server DBMS Storage Logic Network Client Presentation 25

IST 210 Client Server with 3 tiers Server n n n Storage in database Logic in Transaction Monitor Client does presentation only Authentication and Access control can be done in TP monitor Each user does NOT have to be a database user DBMS Storage Logic Client Network Presentation 26

IST 210 Client Server with 3 tiers Database Servers n n A component which sits between the client and the database server to insure reliable updates of information Used in airline reservation and banking systems DBMS Storage Network Transaction Monitor Logic Client Network Presentation 27

IST 210 Why 3 Tiers? n Scalability n n Flexibility Complexity n n multiple transaction monitors load balancing update multiple data stores Two phase commit with multiple databases 28

IST 210 Classifications of DBMSs § Data Model (OO, Relational, hierarchical) § Number of Users ( single vs. multi-user) § Number of Database Sites ( centralized vs. distributed vs. federated) § Special-purpose vs. general-purpose 29