1 / 41

Database system concept and Architecture

Database system concept and Architecture. Data Models, Schemas, and Instance Categories of Data Models Schemas, Instance, and Database State Three-schema Architecture and Data Independence The three levels of the Architecture Data Independence Database languages and Interface DBMS Language

scot
Download Presentation

Database system concept and Architecture

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database system concept and Architecture

  2. Data Models, Schemas, and Instance • Categories of Data Models • Schemas, Instance, and Database State • Three-schema Architecture and Data Independence • The three levels of the Architecture • Data Independence • Database languages and Interface • DBMS Language • DBMS Interfaces • The Database System Environment • DBMS component Modules • Database system Utilities • Centralize and Client/Server Architectures for DBMSs • Basic Client/Server • Two-tier Client/Server Architecture for DBMSs • Three-tier Client/Server Architectures for Web Applications • Classification of Database Management Systems • Summary

  3. Data Models, Schemas, and Instance • Data Model: a collection of concepts that can be use to describes the structure of a database – provides the necessary means to achieve this abstractions. • Structure of Database mean the data types, relationships, and constraints that should hold for the data.

  4. Categories of Data Models Categorize by the type of concepts they use to describe the database structure • High level or Conceptual Data Model • Low-level or Physical Data Model • Representational or Implementation Data Model

  5. Low Level Data Model • provide concepts that describe the details of how data is stored in the computer. Usually provided for specialists, not for typical end users.

  6. F_name Fcode D_name Dcode N 1 Compose of Faculty 1 in Department N Student D_code D_name High Level or Conceptual Data Model • provide concepts that are closed to the way many users perceive data. • Example: use concepts such as entities, attributes, and relationships among two or more entities.

  7. Fcode Dcode D_name Fcode F_name SID S_name S_Add Fcode Dcode Representation or Implementation Data Models • provide concepts that may be understood by end users but they are not too far removed from the way data is organized with in the computer. This hide some details of data storage but can be implemented on a computer system in a direct way. Faculty Department Student

  8. Schemas, Instances, and Database State • Database Schema • is the description of a data base • specified during database design • and is not expect to change frequency • Schema Diagram • A displayed schema example the diagram displays the structure of each record type but not the instance records. • It displays only some aspect schema, for example name of record types and data items and some type of constraints • Database state or snapshot • is the data in the database at a particular moment in time. • Also called the current set of occurrences or instances in the database

  9. Distinction between database schema and database state • When define a new database => specify only database schema to DBMS • At this point “database state is empty state” no data. • Get initial state => loaded with initial data • At any point in time, the database has a current state (current snapshot) • DBMS stores the descriptions of the schema constructs and constraints (called meta-data) in DBMS Catalog

  10. External level (Individual user Views) …….. Conceptual level (community user View) Internal level (Storage View) The three levels of the Architecture • The ANSI/SPARE architecture is divided into 3 levels • Internal Level • External Level • Conceptual Level

  11. External View • External level is individual user lever. • Each user has a language • For application programmer, the language will be either • conventional programming language (e.g. Java, C++) • Fourth-generation language • For the end user, the language will be either • a query language (SQL) • Special purpose language perhaps forms, or menu driven, tailored to that user’s requirements and supported by some application

  12. Conceptual Level • Conceptual view is arepresentation of the entire information content of the database. • The conceptual view consists of many types of conceptual record. • The conceptual view is defined by means of the conceptual schema, which includes definitions of each of the various conceptual record types. • The conceptual schema is written using another data definition language, the conceptual DDL. • The conceptual DDL definitions must not involve any considerations of physical representation or access techniques at all. • They must be no reference in the conceptual schema to stored field representation, stored record sequence, indexes, hashing schemas, pointer, or any storage and access details.

  13. Internal Level • Internal View is a low-level representation of the entire database; it consist of many occurrences of many types of internal record. • Internal record is the ANSI/SPARE term for the construct that we have been calling a stored record. • The internal view is described by means of the internal schema, which not only defines the various stored record types but also specifics what indexes exist, how stored fields are represented, what physical sequence the stored record are in, and so on. • The internal schema is written using yet another data definition language – the internal DDL.

  14. External 1 DCL 1 EMPP, 01 EMPC. 2 EMP# CHAR(6) 02 EMPNO PIC X(6). 2 SAL FIXED BN(31); 02 DEPTNO PIC X(4). External 1 Conceptual Internal Employee Employee_number Char(6) Department_Number Char(4) Salary Decimal(5) Stored_Emp Bytes=20 PREFIX BYTES=6,OFFSET=0 EMP# BYTES=6,OFFSET=6, INDEX=EMPX DEPT# BYTES=4,OFFSET=12, PAY# BYTES=4,ALIGN=FULLWORD,OFFSET=16 Example

  15. Stored Database (Internal View) User A1 User A2 User B1 User B2 User B3 Host Language +DSL Host Language +DSL Host Language +DSL Host Language +DSL Host Language +DSL External View B External View A *External Schema B *External Schema A External/conceptual Mapping A Conceptual/Internal Mapping B Schemas And mappings built and maintained by the database administrator (DBA) Database Management System (DBMS) Conceptual View Conceptual/Internal mapping Storage Structure Definition (internal Schema) *User interface

  16. Mappings • Conceptual/Internal mappings • defines the correspondence between the conceptual view and the stored database; • It specified how conceptual records and fields are represented at the internal level. • If the stored database is changes • If the change has effect to the stored database definition – then the conceptual/Internal mapping must be changes accordingly • The effects of changes must be isolated below the conceptual level, in order to preserve physical data independence.

  17. External/Conceptual mapping • Defines the correspondence between a particular external view and the conceptual view. • Example field and record name change

  18. Data Independence • Defines as the capacity to change the schema at one level of a database system without having to change the schema at the next higher level. • Define 2 types of data independence. • Logical Data independence • Physical Data independence

  19. Logical Data independence • Logical Data independence is the capacity to change the conceptual schema without have to change external schemas or application program. • Example • Expand the database by adding record type or data item. • To change the constraints • To reduce the database by removing a record type • The external schemas that refer only to the database should not be affected.

  20. Faculty Department Teacher Student Tcode SID Tcode T_name S_name S_Add S_Add Fcode Fcode Dcode Dcode Fcode Dcode D_name Fcode F_name Example

  21. Faculty Department Teach Student Tcode SID Tcode T_name S_name S_Add S_Add Fcode Fcode Dcode Dcode Fcode Dcode D_name Fcode F_name Example 2 Teacher Create view teacher1 as select sid ,sname, T_name from student, teach where student.tcode = teach.tcode Create view teacher1 as select sid ,sname, T_name from student, teacher where student.tcode = teacher.tcode Select sid, sname, T_name from teacher1

  22. Physical Data independence • Physical Data independence is the capacity to change the conceptual schema, the external schemas need not be changed. • Change may be needed because some physical files had to be reorganized • Example • Creating additional access structures to improve the performance of retrieval or update

  23. Database language and interface • DBMS language • Data definition language (DDL) is used by DBA and database designer to define schemas. • Storage Definition Language (SDL) is used to specify internal schema • Data Manipulation Language (DML) is a set of operation for retrieval insertion deletion and modifies the data.

  24. DBMS Interfaces • Menu Based Interfaces for web Clients or Browsing. • Form Based Interfaces • Graphical User Interfaces • Natural Languages Interfaces • Interface of Parametric Users • Interfaces for the DBA

  25. The Database System Environment • DBMS Component Modules

  26. Component modules of a DBMS and their interactions Application Programmers DBA staff APPLICATION PROGRAMS Casual users Parametric users PRIVILEGED COMMANDS DDL STATEMENTS Precompiled INTERACTIVE Query Host Language Compiler DML STATEMENTS Query Complier COMPILES Transaction A System Catalog/ Data Dictionary E DDL Complier B DML Complier C Run-time Database Processor execution execution D Stored Data Manager Concurrency control/ Backup/Recovery subsystem Stored Database

  27. Data Dictionary • The DBMS must provide a data dictionary function. • The data dictionary is a system database (not user database) • It contains “data about the data” called “meta-data or descriptor” – that is, definitions of those objects in the system, instead of just “raw data”. • It will keep all of various schemas and mapping and all of the various security and integrity constraints, both source and object form. • Other terms are “dictionary”, “catalog”, “data repository”.

  28. Database System Utilities Database Utility helps the DBA in managing the Database system • Loading : to create the initial version of the database from regular data file • Backup/Restore • File reorganization • Performance Monitoring

  29. Centralized and Client/Server Architecture for DBMSs • Centralized DBMSs Architecture • Centralized DBMS => all DBMS function • All processing was performed remotely on the computer system, only display information and controls were sent from the computer to display terminal

  30. Basic Client/Server • Idea is to define specialized servers with specific functionalities. • Client machines provide the user with the appropriate interfaces to utilize the server, and local processing power to run local application. • Client is a user machine that provide user interface capabilities and local processing. When requires process not in that machine it connects to a server that provide the needed functions. • Server is a machine that can provide service to the client machine.

  31. Two main type of basic DBMS architectures on client/server frame work • Two-tier Client/Server Architecture • Three-tier Client/Server Architecture

  32. Client site1 Client Site2 Server Site3 Server Client Site n …… Communication Network Two-tier Client/Server

  33. GUI Web Interface Client Application Program Web Pages Application Server Or Web server Database Management System Database Server Three-tier Client/Server Architecture Client contain GUI interface and some additional business rule This server plays an intermediate role by storing business rule (procedure/constraints) that are used to access data from the database server. Also improve database security.

  34. Classification of DBMS • By Data Model • Relational Data Model • Object Data Model • Hierarchical and Network Data Models • By numbers of users supported by the system • Single User system • Multi User system • Number of Sites • Centralized => the data stored at a single computer site • Distributed DBMS (DDBMS) => have the actual database and DBMS software distributed over many sites • Homogenous DDBMSs • Heterogeneous DBMSs • Purpose • General Purpose • Special Purpose

  35. Summary • Introduces the main concepts used in database systems • Define Data model & distinguished three main categories of data models: • High-level/Conceptual • Low Level • Representation/Implementation • Schema -> description of a database • Describe the three-schema DBMS architecture • Internal, Conceptual and External • Main types of languages and interfaces that DBMSs support • DDL - define Database conceptual schema • DSL – specifying views and storage structures • DML – retrieval, update • Classified DBMS

  36. Figure illustrates a typical variation of Oracle's memory and process structures

  37. Memory Structures and Processes • The mechanisms of ORACLE execute by using memory structures and processes. All memory structures exist in the main memory of the computers that constitute the database system. Processes are jobs or tasks that work in the memory of these computers. • Memory Structures • ORACLE creates and uses memory structures to complete several jobs. For example, memory is used to store program code being executed and data that is shared among users. Several basic memory structures are associated with ORACLE: the system global area (which includes the database and redo log buffers, and the shared pool) and the program global area. • SystemGlobal Area (SGA) is a shared memory region allocated by ORACLE that contains data and control information for one ORACLE instance. • An ORACLE instance contains the SGA and the background processes. • The SGA is allocated when an instance starts and deallocated when the instance shuts down. Each instance that is started has its own SGA. • The Program Global Area (PGA) is a memory buffer that contains data and control information for a server process. A PGA is created by ORACLE when a server process is started. The information in a PGA depends on the configuration of ORACLE.

  38. Database Structures The relational model has three major aspects: • Structures • Structures are well-defined objects that store the data of a database. Structures and the data contained within them can be manipulated by operations. • Operations • Operations are clearly defined actions that allow users to manipulate the data and structures of a database. The operations on a database must adhere to a pre-defined set of integrity rules. • Integrity Rule • Integrity rules are the laws that govern which operations are allowed on the data and structures of a database. Integrity rules protect the data and the structures of a database.

  39. An ORACLE database has both a physical and a logical structure. By separating physical and logical database structure, the physical storage of data can be managed without affecting the access to logical storage structures. Logical Database Structure • An ORACLE database's logical structure is determined by: one or more tablespaces. • the database's schema objects (e.g., tables, views, indexes, clusters, sequences, stored procedures). • The logical storage structures, including tablespaces, segments, and extents, dictate how the physical space of a database is used. The schema objects and the relationships among them form the relational design of a database.

  40. Tablespaces and Data Files Tablespaces are the primary logical storage structures of any ORACLE database. The usable data of an ORACLE database is logically stored in the tablespaces and physically stored in the data files associated with the corresponding tablespace. Figure 2 illustrates this relationship. • Although databases, tablespaces, data files, and segments are closely related, they have important differences: • databases and tablespaces • An ORACLE database is comprised of one or more logical storage units called tablespaces. The database's data is collectively stored in the database's tablespaces. • tablespaces and data files • Each tablespace in an ORACLE database is comprised of one or more operating system files called data files. A tablespace's data files physically store the associated database data on disk. • databases and data files • A database's data is collectively stored in the data files that constitute each tablespace of the database. For example, the simplest ORACLE database would have one tablespace, with one data file. A more complicated database might have three tablespaces, each comprised of two data files (for a total of six data files). • schema objects, segments, and tablespaces • When a schema object such as a table or index is created, its segment is created within a designated tablespace in the database. For example, suppose a table is created in a specific tablespace using the CREATE TABLE command with the TABLESPACE option. The space for this table's data segment is allocated in one or more of the data files that constitute the specified tablespace. An object's segment allocates space in only one tablespace of a database.

More Related