database System concepts and architecture Ch02.pdf

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. Slide 2-4 Data Models ⚫ Data Model: A set of concepts to describe the structure of a database, and certain constraints that the database should obey. – To provide data abstraction ⚫ Data Model Operations: Operations for specifying database retrievals and updates by referring to the concepts of the data model. Operations on the data model may include basic operations and user-defined operations. 6th Ed

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. Also include... Dynamic aspect or behaviour of a database application • Allows the database designer to specify a set of valid operations allowed on database objects ⚫ Such operations triggers whenever DB state changes ⚫ Includes specific operations in addition to generic operation of retrieval, insertion etc..

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. Categories of Data Models ⚫ High-level or conceptual data models ⚫ Close to the way many users perceive data ⚫ Uses concepts (facts) in modelling data ⚫ Low-level or physical data models ⚫ Describe the details of how data is stored on computer storage media and their accessibility in terms of access paths, indexes etc. ⚫ Representational or implementational data models ⚫ Easily understood by end users ⚫ Also similar to how data organized in computer storage ⚫ Includes relational, hierarchical, network data models ⚫ Represents data using record structures and hence called record based data models

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. Categories of Data Model contd.. ⚫ Entity Relationship Diagram (ERD) and Enhanced ERD ⚫ Conceptual data Model ⚫ Relational Model ⚫ Representational/Implementational data Model ⚫ Object Data model ⚫ New family of higher-level implementation data models ⚫ Also, closer to conceptual data models

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. Slide 2-8 Schemas versus Instances • Database Schema: The description of a database. Includes descriptions of the database structure and the constraints that should hold on the database. • Schema Diagram: A diagrammatic display of (some aspects of) a database schema. • Schema Construct: A component of the schema or an object within the schema, e.g., STUDENT, COURSE. • Database Instance: The actual data stored in a database at a particular moment in time. Also called database state (or occurrence). • Meta data : description of schema constructs and constraints stored in file called catalogue 6th Ed

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. Slide 2-9 Database Schema Vs. Database State • Database State: Refers to the content of a database at a moment in time. • Initial Database State: Refers to the database when it is loaded • Valid State: A state that satisfies the structure and constraints of the database. • Distinction • The database schema changes very infrequently. The database state changes every time the database is updated. • Schema is also called intension, whereas state is called extension. • Schema Evolution 6th Ed

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. Slide 2-13 Three-Schema Architecture • Supports following three main DBMS characteristics • Program-data independence. • multiple views of the data. • Self-describing data • Defines DBMS schemas at three levels: • Internal schema (at the internal level) • describes physical storage structures and access paths. Typically uses a physical data model. • Conceptual schema (at the conceptual level) • describes the structure and constraints for the whole database for a community of users. Uses a conceptual or an implementation data model. • External schemas (at the external level) • describes the various user views. Usually uses the same data model as the conceptual level. 6th Ed

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. Slide 2-15 Three-Schema Architecture contd.. ⚫Mappings among schema levels are needed to transform requests and data. ⚫Program (query) refers to an external schema, and are mapped by the DBMS to the internal schema for execution.

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. Slide 2-16 Data Independence • Logical Data Independence: The capacity to change the conceptual schema without having to change the external schemas and their application programs. • Physical Data Independence: The capacity to change the internal schema without having to change the conceptual schema. 6th Ed

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. Slide 2-17 Data Independence ⚫ When a schema at a lower level is changed, only the mappings between this schema and higher-level schemas need to be changed in a DBMS that fully supports data independence. ⚫ The higher-level schemas remains unchanged. ⚫ No change in application programs since they refer to the external schemas ⚫ Two levels of mappings result in overhead during running a query i.e. reduced DBMS efficiency. Hence, few DBMS implements full three-schema architecture 6th Ed

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. Slide 2-18 DBMS Languages • Data Definition Language (DDL): • Used by the DBA and database designers to specify the conceptual schema of a database. • In many DBMSs, the DDL is also used to define internal and external schemas (views). • In some DBMSs, separate storage definition language (SDL) and view definition language (VDL) are used to define internal and external schemas. 6th Ed

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. Slide 2-19 DBMS Languages • Data Manipulation Language (DML): Used to specify database retrievals and updates. • DML commands (data sublanguage) can be embedded in a general-purpose programming language (host language), such as COBOL, C or an Assembly Language. • Alternatively, stand-alone DML commands can be applied directly (query language).

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. Slide 2-20 DBMS Languages • High Level or Non-procedural Languages: e.g., SQL, are set-oriented and specify what data to retrieve than how to retrieve. Also called declarative languages. • Low Level or Procedural Languages: record-at-a-time; they specify how to retrieve data and include constructs such as looping (PL/SQL).

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. •Single-user (typically used with micro- computers) • MS Access Mysql •Multi-user (most DBMSs) • Oracle, Ingres, MongoDB, Hadoop DB, Elessandra Example of DBMS • NOSQL: non-relational, distributed, open-source and horizontally scalable. (JSON format) • SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full- featured, SQL database engine

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. Two main components of DBMS environment: 1. User Interface 2. Processing of Query by accessing DB and Catalogue

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. DDL Compiler: processes schema definition written in DDL and makes changes in catalogue Query compiler: compiles the interactive query generated thru interfaces for correctness and converts into internal form Query optimizer: optimizes query for efficiency interms of reordering operations, eliminating redundancy, use of correct data structure, index techniques for data retrieval etc.. Precompiler: Extracts query from the host language and sends to DML compiler DML Compiler: converts query into object code after checking correctness Compiled canned transaction: linking of object code of the application program and query to get a single executable file, which are executed by the parametric end user repeatedly by sending different parameters

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. Runtime database processor: executes the following: ⚫ Priviledged commands given by DBA staff ⚫ Query plan generated for naive user ⚫ Canned transaction with runtime parameters ⚫ Does buffer management for reducing I/o time (may be a seperate module) ⚫ Does concurrency control and transaction recovery (may be a seperate module) Stored data manager: does basic operating system services for reading/writing data from hard disks.

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. Important to note DBMS interfaces with: 1. operating system for data access. • If the computer system is shared by many users for different purposes OS will schedule DBMS disk access requests and DBMS processing along with other processes. • In a dedicated to running the database server the DBMS will control main memory buffering of disk pages. 2. compilers for general purpose host programming languages 3. application servers and client programs running on separate machines through the system network interface.

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. Slide 2-27 Classification of DBMSs • Based on the data model used: • Traditional: Relational, Network, Hierarchical. • Emerging: Object-oriented, Object-relational, native XML-dbs. • Big data systems, also known as key-value storage systems and NOSQL systems, use various data models: document-based, graph-based, column- based, and key-value data models. • Example • Relational DM: Oracle, ingres etc. • Hierarchical DM: IMS (IBM), Sys 2K (SAS corp.) • Network VSAM(IBM), DM : IMAGE (HP) • Legacy database System (Network, Hierarchical)

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. Slide 2-28 Data Models ⚫ Data Model: A set of concepts to describe the structure of a database, and certain constraints that the database should obey. ⚫ Data Model Operations: Operations for specifying database retrievals and updates by referring to the concepts of the data model. Operations on the data model may include basic operations and user-defined operations. 6th Ed

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. Slide 2-29 Categories of data models ⚫ Conceptual (high-level, semantic) data models: Provide concepts that are close to the way many users perceive data. (Also called entity-based or object-based data models.) ⚫ Physical (low-level, internal) data models: Provide concepts that describe details of how data is stored in the computer. ⚫ Implementation (representational) data models: Provide concepts that fall between the above two, balancing user views with some computer storage details. 6th Ed

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. •Single-user (typically used with micro- computers) • MS Access Mysql, •Multi-user (most DBMSs) • Oracle, ingres, Mongodb, Hbase, Elessandra On the basis of Users NOSQL: non-relational, distributed, open-source and horizontally scalable.

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. Slide 2-31 Number of sites ⚫ Centralized DBS (DBMS and data at one place) ⚫ Distributed Database Systems have now come to be known as client server based database systems because they do not support a totally distributed environment, but rather a set of database servers supporting a set of clients.

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. Slide 2-32 Variations of Distributed Environments: • Homogeneous DDBMS • Heterogeneous DDBMS • Federated or Multidatabase Systems – Middleware s/w to manage existing Dbs created usig Heterogeneous DBMS

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. Other categorization ⚫Cost ⚫Access Path for storing files ⚫Usage or purpose ⚫ General /Special ⚫ OLTP

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. Slide 2-34 History of Data Models ⚫ Relational Model: proposed in 1970 by E.F. Codd (IBM), first commercial system in 1981-82. Now in several commercial products (DB2, ORACLE, SQL Server, SYBASE, INFORMIX). Network Model: the first one to be implemented by Honeywell in 1964-65 (IDS System). Adopted heavily due to the support by CODASYL (CODASYL - DBTG report of 1971). Later implemented in a large variety of systems - IDMS (Cullinet - now CA), DMS 1100 (Unisys), IMAGE (H.P.), VAX -DBMS (Digital Equipment Corp.). ⚫ Hierarchical Data Model: implemented in a joint effort by IBM and North American Rockwell around 1965. Resulted in the IMS family of systems. The most popular model. Other system based on this model: System 2k (SAS inc.) 6th Ed

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. Slide 2-35 History of Data Models ⚫ Object-oriented Data Model(s): – comprises models of persistent O-O Programming Languages such as C++ (e.g., in OBJECTSTORE or VERSANT), and Smalltalk (e.g., in GEMSTONE). – Additionally, systems like O2, ORION (at MCC - then ITASCA), IRIS (at H.P.- used in Open OODB). ⚫ Object-Relational Models: – Most Recent Trend. – Informix Universal Server. Exemplified in the latest versions of Oracle-10i, DB2, and SQL Server etc. ⚫ XML data model: for exchange of data on Web with complex hierrachical structures 6th Ed

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. Slide 2-36 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" 6th Ed

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. Slide 2-38 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” 6th Ed

Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Copyright © 2004 Pearson Education, Inc. Type Hierrachical (1960-1980) Network (1969-) Relational (1970-) Relationship 1:N M:N 1:1,1:N, M:N Data structures Based on parent child relationship A record can have many parents as well as many children. Based on relational structures Data manipulation No Independent stand alone query interface (DL/1 of IMS) CODASYL (Conference on Data Systems Languages) SQL Data intergirty Deletion of parent results in deletion of child records Free from delete anomalies Free from delete anomalies Differences ...

database System concepts and architecture Ch02.pdf

More Related Content

Similar to database System concepts and architecture Ch02.pdf

Recently uploaded

database System concepts and architecture Ch02.pdf