Database System Concepts and Architecture

1. Database System Concepts and Architecture Chapter 2 COSC 457 Sungchul Hong

2. The architecture of DBMS • Early monolithic system • Modular design • Client module • Server module • Storage, access, search, and other functions

3. Data Models, Schemas, and Instances • Data Model • A collection of concepts that can be used to describe that structure of a database. • Structure of a database: data types, relationships, and constraints • Basic operations • Specifying retrievals and updates on the database • Dynamic aspect (behavior): user defined operations.

4. Categories of Data Models • High-level (conceptual) Data Models • They provide concepts that are close to the way many users perceive data. • Representational (Implementation) Data Models • Between two extremes • Low-level (physical) Data Models • They provide concepts that describe the details of how data is stored in the computer.

5. Conceptual Data Model • Entities, attributes, and relationships • Entity represents a real-world object or concept. • An attribute represents some property of interest that further describes an entity. • A relationship among two or more entities represents an interaction among the entities. • Entity-Relationship model.

7. Representational Data Models • Relational data model • Network models • Hierarchical models • Object data models

8. Hierarchical Model • ADVANTAGES: • Hierarchical Model is simple to construct and operate on • Corresponds to a number of natural hierarchically organized domains - e.g., assemblies in manufacturing, personnel organization in companies • Language is simple; uses constructs like GET, GET UNIQUE, GET NEXT, GET NEXT WITHIN PARENT etc. • DISADVANTAGES: • Navigational and procedural nature of processing • Database is visualized as a linear arrangement of records • Little scope for "query optimization"

9. Network Model • ADVANTAGES: • Network Model is able to model complex relationships and represents semantics of add/delete on the relationships. • Can handle most situations for modeling using record types and relationship types. • Language is navigational; uses constructs like FIND, FIND member, FIND owner, FIND NEXT within set, GET etc. Programmers can do optimal navigation through the database. • DISADVANTAGES: • Navigational and procedural nature of processing • Database contains a complex array of pointers that thread through a set of records. Little scope for automated "query optimization”

10. Network Model

11. Relational Data Model • Advantages • Strong Mathematical background • Query optimization is possible • 4th generation language (non-procedural) • Disadvantage • Some (e.g. joint) Operations require a great amount of resources.

12. Relational Data Model N Branch Work 1 Staff

13. Physical Data Models • How data is stored in the computer by representing information such as record formats, record orderings, and access paths.

14. Schemas, Instances, and Database State • Schema (Meta data, intension) • The description of the database • It is specified during database design. • It is not expected to change frequently. • Schema diagram: names of record types and data items • Instances (extension of the schema) • The actual data in a database • It may change quite frequently. • Database state: a snapshot, current set of instances • DBMS is responsible for ensuring that every state of the database is a valid state.

15. Schema Instance

17. DBMS Architecture and Data Independence • The Three-Schema Architecture • Internal level • Conceptual level • External level • Data Independence • Logical data independence • Physical data independence

18. The Three-Schema Architecture • Internal level (internal schema) • Physical storage structure of the database. • Conceptual level (conceptual schema) • The structure of the whole database for a community of users. • Hides details • External (view) level (external schemas or user views) • User’s view point of a database. • Mappings: transforming requests and results between levels

20. Data Independence • Logical data independence • Capacity to change the conceptual schema without having to change external schemas or application programs. • Physical data independence • Capacity to change the internal schema without having to change to conceptual (or external) schema. • Only mappings between two levels will be changed.

21. DBMS Languages • Data Definition Language (DDL) • Storage Definition Language (SDL) • View Definition Language (VDL) • Data Manipulation Language (DML) • Comprehensive database language  SQL • Nonprocedural DML • Interactive (query language) or embedded (host/data) • Set-at-a-time, set-oriented, declarative (what) • Procedural DML • Embedded in a general-purpose programming language. • Record-at-a-time,

22. SQL Example (DDL) • CREATE TABLE DEPT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9), PRIMARY KEY (DNUMBER), UNIQUE (DNAME), FOREIGN KEY (MGRSSN) REFERENCES EMPON DELETE SET DEFAULT ON UPDATE CASCADE );

23. SQL Example (DML) • SELECT BDATE, ADDRESSFROM EMPLOYEEWHERE FNAME='John' AND MINIT='B’ AND LNAME='Smith’;

24. Staff SELECT staffNo, fName, lName, position, salary FROM Staff WHERE salary > 10000;

25. DBMS Interfaces • Menu-Based Interfaces for Browsing. • Browsing interface • Forms-Based Interfaces • Forms specification languages • Graphical User Interfaces • Natural Language Interface • Interfaces for Parametric Users • Bank tellers • Interfaces for the DBA: privileged commands • Creating accounts, setting system parameters, granting account authorization, changing a schema, …

26. DBMS Component Modules • Operating systems • Stored Data Manager • DDL compiler • Stores description of the schemas in the DBMS catalog. • Names of files, data items, storage details of each file, mapping in formations among schemas, constraints • Run-time database processor • Query compiler: interactive queries • Pre-compiler: DML command from an application program • DML compiler: compilation into object code.

28. Embedded Code (Java) try { rset= stmt.executeQuery("SELECT * from department"); ResultSetMetaData rsmd=rset.getMetaData(); while (rset.next()) { System.out.println(" " + rset.getString(1) + " " + rset.getString(2) + " " + rset.getString(3) +" "); } }

29. Interactive SQL • SQL> SELECT table_name FROM user_tables; • TABLE_NAME • ------------------------------ • DEPARTMENT • STUDENT

30. Database Systems Utilities • Loading • Convert non-database files into db files • Backup • File reorganization • organize a database file into a different file organization to improve performance • Performance monitoring • Monitoring database usage. • Sorting files, data compression, etc.

31. Tools, Application Environments, and Communication Facilities • Expanded data dictionary system • Usage standards, application program descriptions • Application development environment • Communications software

32. Classification of Database • Data model • Relational data model, Object data model, Hierarchical, Network • Number of users • Single-user (Access) , multi-user systems (Oracle) • Number of sites • Centralized, distributed • Costs • Types of Access path • Target area • General purpose, special purpose (on-line transaction processing system)