Lecture 2 2 Previous Lecture Introduction to DB

Lecture 2

2 Previous Lecture Introduction to DB and DBMS File-based systems and their limitations Database approach Components of the DBMS environment Roles in the DB environment Advantages/Disadvantages of DBMSs RUR rafiu. af@gmail. com Bakhtar 1/23/2022

3 Outline History of Database System Database Environment Architecture of DBMS Schema Mapping Structure of Database RUR rafiu. af@gmail. com Bakhtar 1/23/2022

4 History of Database Systems Roots of the DBMS § Apollo moon-landing project, 1960 s (The first type of DBMS, the hierarchical DBMS. IBM had the first model, developed on IBM 360 and was called IMS, ) § NAA (North American Aviation), prime contractor for the project § Developed a software GUAM (Generalized Update Access Method), hierarchical § In mid– 1960 s IBM joined NAA, result was IMS (Information Management System) RUR rafiu. af@gmail. com Bakhtar 1/23/2022

5 History of Database Systems. . IDS (Integrated Data Store) § By General Electric, network, mid-1960 CODASYL § Conference on Data Systems Languages DBTG § Data Base Task Group RUR rafiu. af@gmail. com Bakhtar 1/23/2022

6 History of Database Systems. . DBTG proposal (1971) included following components for DB system architecture: § The schema § The subschema § A data management language o Schema DDL o Subschema DDL o DML Proposal was not formally adopted by ANSI RUR rafiu. af@gmail. com Bakhtar 1/23/2022

7 History of Database Systems. . E. F. Codd, 1970 § IBM Research Laboratory § Relational model § Result of this project o Development of SQL o Commercial relational DBMS products e. g. DB 2, SQL/DS from IBM, Oracle from Oracle Corporation RUR rafiu. af@gmail. com Bakhtar 1/23/2022

Chapter 2 Database Environment

9 Architecture of DBS Architecture of DBMS Is named as ANSI-SPARC Architecture too There are three Levels ANSI-SPARC Architecture • Physical/internal Level • Conceptual Level • External Level (End User) RUR rafiu. af@gmail. com Bakhtar 1/23/2022

10 ANSI-SPARC Three-Level Architecture RUR rafiu. af@gmail. com Bakhtar 1/23/2022

11 RUR rafiu. af@gmail. com Bakhtar 1/23/2022

12 Physical Level Physical level describes the physical storage structure of data in database. It is also known as Internal Level. This level is very close to physical storage of data. At highest level, it can be viewed in the form of files. RUR rafiu. af@gmail. com Bakhtar 1/23/2022

13 Conceptual Level Conceptual level describes the structure of the whole database for a group of users. It is also called as the data model. Conceptual schema is a representation of the entire content of the database. It hides the internal details of physical storage. RUR rafiu. af@gmail. com Bakhtar 1/23/2022

14 External Level External level is related to the data which is viewed by individual end users. This level includes a no. of user views or external schemas. This level is closest to the user. External view describes the segment of the database that is required for a particular user group and hides the rest of the database from that user group. RUR rafiu. af@gmail. com Bakhtar 1/23/2022

15 Objectives of Three-Level Architecture All users should be able to access same data but have a different customized view A user’s view is immune to changes made in other views Users should not need to know physical database storage details RUR rafiu. af@gmail. com Bakhtar 1/23/2022

16 Objectives of Three-Level Architecture. . DBA should be able to change database storage structures without affecting the users’ views Internal structure of database should be unaffected by changes to physical aspects of storage DBA should be able to change conceptual structure of database without affecting all users RUR rafiu. af@gmail. com Bakhtar 1/23/2022

17 Schemas External Schemas • Also called subschemas • Multiple schemas per database • Corresponds to different views of data Conceptual Schema • Describes all the entities, attributes, and relationships together with integrity constraints • Only one schema per database RUR rafiu. af@gmail. com Bakhtar 1/23/2022

18 Schemas. . Internal Schema • A complete description of the internal model, containing the definitions of stored records, the methods of representation, the data fields, and the indexes and storage structures used • Only one schema per database RUR rafiu. af@gmail. com Bakhtar 1/23/2022

19 Mappings Mapping is the process of transforming request response between various database levels of architecture. The DBMS is responsible for mapping between these three types of schema: • The DBMS must check that each external schema is derivable from the conceptual schema, and it must use the information in the conceptual schema to map between each external schema and the internal schema Types of mappings • Conceptual/Internal mapping • External/Conceptual mapping RUR rafiu. af@gmail. com Bakhtar 1/23/2022

20 Conceptual/Internal Mapping In Conceptual / Internal mapping, it is necessary to transform the request from the conceptual to internal levels. Enables the DBMS to • Find the actual record or combination of records in physical storage that constitute a logical record in the conceptual schema, • It also allows any differences in entity names, attribute order, data types, and so on, to be resolved RUR rafiu. af@gmail. com Bakhtar 1/23/2022

21 External/Conceptual Mapping In External / Conceptual mapping, DBMS transforms a request on an external schema against the conceptual schema. Enables the DBMS to • Map names in the user’s view on to the relevant part of the conceptual schema RUR rafiu. af@gmail. com Bakhtar 1/23/2022

22 Structure of Database System A Database has both: • Physical structure. • Logical structure. By separating physical and logical database structure the physical storage of data can be managed without affecting the access to logical storage structure. RUR rafiu. af@gmail. com Bakhtar 1/23/2022

23 Data Independence 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 RUR rafiu. af@gmail. com Bakhtar 1/23/2022

24 Data Independence 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, storage devices etc. ) • Should not require change to conceptual or external schemas RUR rafiu. af@gmail. com Bakhtar 1/23/2022

25 Data Independence & the ANSI-SPARC Architecture RUR rafiu. af@gmail. com Bakhtar 1/23/2022

26 Summary Components of the DBMS environment Roles in the DB environment History of DBMS Advantages/Disadvantages of DBMSs ANSI-SPARC three-level architecture Schemas, mappings Data independence RUR rafiu. af@gmail. com Bakhtar 1/23/2022