DB Chapter 2 Part 1 PDF

Chapter 2 Database System Concepts and Architecture [email protected] 1 Outlines:  Data Model  Categories of data model  Schema , instance, database state  The three-schema architecture  Data independence  DBMS languages shaimaa.sabri@uoz...

Chapter 2 Database System Concepts and Architecture [email protected] 1 Outlines:  Data Model  Categories of data model  Schema , instance, database state  The three-schema architecture  Data independence  DBMS languages [email protected] 2 Data Models Data model: is a collection of concepts that describe the structure of a database (data types, relationships, and constraints that apply to the data) and provides data abstraction. Most data models also include a set of basic operations for specifying retrievals and updates on the database. [email protected] 3 Categories of Data Models High-level or conceptual data models provide concepts that are close to the way many users perceive data. Low-level or physical data models provide concepts that describe the details of how data is stored on the computer storage media. Concepts provided by low-level data models are generally meant for computer specialists, not for end users. [email protected] 4 Categories of Data Models Representational (or implementation) data models, (between high-level and low-level data models) which provide concepts that may be easily understood by end users but that are not too far removed from the way data is organized in computer storage. Representational data models hide many details of data storage on disk but can be implemented on a computer system directly. [email protected] 5 Schemas, Instances, and Database State Database schema: The description of Construct a database, which is specified during database design and is not expected to change frequently. A displayed schema is called a schema diagram. Figure shows a schema diagram for the university database; the diagram displays the structure of each record type but not the actual instances of records. We call each object in the schema—such as STUDENT or COURSE—a schema Schema construct. Diagram [email protected] 6 Schemas, A schema diagram displays only some aspects of a Instances, and schema. such as the names of Database State record types and data items, and some types of constraints. The previous figure shows neither the data type of each data item, nor the relationships among the various files. Many types of constraints are not represented in schema diagrams. A constraint such as students majoring in computer science must take CS1310 before the end of their sophomore year is quite difficult to represent diagrammatically. [email protected] 7 Schemas, Instances, and Database State Database state or snapshot: The data in the database at a particular moment in time. It is also called the current set of occurrences or instances in the database. What are the Differences between database schema and database state ?? [email protected] 8  Schemas, Instances, and Database State Databa se State or Snapsh ot Instanc e Figure 2: Database state or Snapshot [email protected] 9  Schemas, Instances, and Database State Valid state: a state that satisfies the structure and constraints specified in the schema. DBMS is responsible for ensuring every state if database is a valid state. The schema is sometimes called the intension, and a database state is called an extension of the schema. [email protected] 10  The Three-Schema Architecture (1)Use of a catalog to store the database description (schema) so as to make it self- describing. (2)Insulation of programs and data (program-data and program-operation independence) (3)Support of multiple user views. Three schema architecture was proposed to help achieve the previous characteristics. The goal of the three-schema architecture is to separate the user applications from the physical database. [email protected] 11 The Three-Schema Architecture The three-schema architecture has the following three levels: 1. The internal level has an internal schema, which describes the physical storage structure of the database. The internal schema uses a physical data model and describes the complete details of data storage and access paths for the database. [email protected] 12  The Three-Schema Architecture 2. The conceptual level has a conceptual schema, which describes the structure of the whole database for a community of users. The conceptual schema hides the details of physical storage structures and concentrates on describing entities, data types, relationships, user operations, and constraints. 3. The external or view level includes a number of external schemas or user views. Each external schema describes the part of the database that a particular user group is interested in and hides the rest of the database from that user group. [email protected] 13 The Three-Schema Architecture Figure 3: The three-schema architecture. [email protected] 14 Data Independence There are two types of data independence in the three schema architecture: 1. Logical data independence: is the capacity to change the conceptual schema without having to change external schemas or application programs. 2. Physical data independence is the capacity to change the internal schema without having to change the conceptual schema or the external schemas. Which data independence type is harder ?? [email protected] 15  DBMS Languages After populating the database, users must have some means to manipulate the database. Typical manipulations include retrieval, insertion, deletion, and modification of the data. The DBMS provides a set of operations or a language called the data manipulation language (DML) for these purposes. In current DBMSs, the preceding types of languages are usually not considered distinct languages; rather, a comprehensive integrated language is used that includes constructs for conceptual schema definition, view definition, and data manipulation. [email protected] 16  DBMS Languages In many DBMSs where no strict separation of levels between the conceptual and internal is maintained: Data definition language (DDL), is used by the DBA and by database designers to define both (conceptual and internal) schemas. The DBMS will have a DDL compiler whose function is to process DDL statements in order to identify descriptions of the schema constructs and to store the schema description in the DBMS catalog. [email protected] 17 DBMS Languages In DBMSs where a clear separation is maintained between the conceptual and internal levels: DDL is used to specify the conceptual schema only. Storage definition language (SDL), is used to specify the internal schema. The mappings between the two schemas may be specified in either one of these languages. For a true three-schema architecture, we would need a third language, the view definition language (VDL), to specify user views and their mappings to the conceptual schema. In relational DBMSs, SQL is used in the role of VDL to define user or application views as results of predefined queries. [email protected] 18 DBMS Languages A typical example of a comprehensive database language is the SQL relational database language, which represents a combination of DDL, VDL, and DML, as well as statements for constraint specification, schema evolution, and other features. There are two main types of DMLs: High level (set-at-a-time or set-oriented) Low level or procedural (record-at-a-time) [email protected] 19