1 / 18

The Database System Environment

The Database System Environment. Dr. Awad Khalil Computer Science Department AUC. Content. DBMS Environment DBMS Utilities The System Catalog The Data Dictionary The System Catalog for Relational DBMS Accessing Catalog Information. Database System. DBMS Components.

haig
Télécharger la présentation

The Database System Environment

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. The Database SystemEnvironment Dr. Awad Khalil Computer Science Department AUC CSCI 453 -- DBMS Environment

  2. Content • DBMS Environment • DBMS Utilities • The System Catalog • The Data Dictionary • The System Catalog for Relational DBMS • Accessing Catalog Information CSCI 453 -- DBMS Environment

  3. Database System CSCI 453 -- DBMS Environment

  4. DBMS Components CSCI 453 -- DBMS Environment

  5. CREATE TABLE EMPLOYEE (FNAME VARCHAR(15) NOT NULL, MINIT CHAR, LNAME VARCHAR(15) NOT NULL, SSN SSN_TYPE NOT NULL, BDATE DATE ADDRESS VARCHAR(30), SEX CHAR, SALARY DECIMAL(10,2), SUPERSSN SSN_TYPE, DNO INT NOT NULL, PRIMARY KEY (SSN), FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN), FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER)); CREATE TABLE DEPARTMENT (DNAME VARCHAR(15) NOT NULL, DNUMBER INT, NOT NULL, MGRSSN SSN_TYPE NOT NULL, MGRSTARTDATE DATE PRIMARY KEY (DNUMBER), UNIQUE (DNAME) FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN)); CREATE TABLE DEPT_LOCATIONS (DNUMBER INT NOT NULL, DLOCATION VARCHAR(15) NOT NULL, PRIMARY KEY (DNUMBER, DLOCATION), FOREIGN KEY (DNUMBER) REFERENCES DEPARTMENT(DNUMBER)); CREATE TABLE PROJECT (PNAME VARCHAR(15) NOT NULL, PNUMBER INT NOT NULL, PLOCATION VARCHAR(15) DNUM INT NOT NULL, PRIMARY KEY (PNUMBER), UNIQUE (PNAME) FOREIGN KEY (DNUM) REFERENCES DEPARTMENT(DNUMBER)); CREATE TABLE WORKS_ON (ESSN SSN_TYPE NOT NULL, PNO INT NOT NULL, HOURS DECIMAL(3,1) NOT NULL, PRIMARY KEY (ESSN, PNO), FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN), FOREIGN KEY (PNO) REFERENCES PROJECT(PNUMBER)); CREATE TABLE DEPENDENT (ESSN SSN_TYPE NOT NULL, DEPENDENT_NAME VARCHAR(15) NOT NULL, SEX CHAR,   BDATE DATE,   RELATIONSHIP VARCHAR(8) PRIMARY KEY (ESSN, DEPENDENT_NAME), FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN)); A Database Schema in SQL - DDL CSCI 453 -- DBMS Environment

  6. DBMS Components (Cont’d) • A DBMS is a complex software system. • The database and the system catalog are usually stored on disk. • Access to the disk is controlled primarily by the operating system (OS), which schedules disk input/output.. • A higher-level stored data manager module of the DBMS controls access to DBMS information stored on disk. • The precompiler extracts DML commands from an application program written in a host language. These commands are sent to the DML compiler for compilation into object code for database access. CSCI 453 -- DBMS Environment

  7. DBMS Components (Cont’d) • The DDL compiler processes schema definitions and stores descriptions of the schemas (meta-data) in the DBMS catalog. • The run-time database processor handles database accesses at run time. • The query compiler handles high-level queries that are entered interactively. CSCI 453 -- DBMS Environment

  8. DBMS Components (Cont’d) • DBMS Engine • Interface Subsystem (DDL, DML, DCL, Graphical User Interface, Forms Interface, Natural Language Interface • System Catalog • Concurrency Control Subsystem • Backup and Recovery Subsystem • Query Optimization Subsystem • Performance Management Subsystem •  Data Integrity Management Subsystem •  Application Development Subsystem •  Security Management Subsystem CSCI 453 -- DBMS Environment

  9. DBMS Utilities • Loading: A loading utility is used to load existing data files - such as text files or sequential files - into the database. • Backup: A backup utility creates a backup copy of the database, usually by dumping the entire database onto tape. • File reorganization: This utility can be used to reorganize a database file into a different file organization to improve performance. • Performance monitoring: Such a utility monitors database usage and provides statistics to the DBA. CSCI 453 -- DBMS Environment

  10. The System Catalog • TheSystem Catalog is at the heart of any general-purpose DBMS. • It is a “minidatabase” itself, and its function is to store the schemas, or descriptions, of the databases that the DBMS maintains. • The catalog stores data that describes each database; such data is often called meta-data. It includes a description of the conceptual database schema, the internal schema, any external schemas, and the mappings between the schemas at different levels. CSCI 453 -- DBMS Environment

  11. Data Dictionary versus System Catalog • The term data dictionary is often used to indicate a more general software utility than a catalog. • A catalogis closely coupled with the DBMS software; it provides the information stored in it to users and the DBA, but it is mainly accessed by the various software modules of the DBMS itself, such as DDL, and DML compilers, the query optimizer, the transaction processor, report generators, and the constraint enforcer. • A data dictionary software package may interact with the software modules of the DBMS but is mainly used by the designers, users, and administrators of a computer system for information management. CSCI 453 -- DBMS Environment

  12. Data Dictionary CSCI 453 -- DBMS Environment

  13. Catalogs for Relational DBMSs • The information stored in a catalog of a relational DBMS includes description of the following: • Relation names, • Attribute names, • Attribute domains (data types), • Primary keys, • Secondary key attributes, • Foreign keys, • Other types of constraints. • Descriptions of views. • Internal-level description, • Security/authorization information. CSCI 453 -- DBMS Environment

  14. Catalogs for Relational DBMSs (Cont’d) • In relational DBMSs it is common practice to store the catalog itself as relations and to use the DBMS software for querying, updating, and maintaining the catalog. CSCI 453 -- DBMS Environment

  15. Catalogs for Relational DBMSs (Cont’d) CSCI 453 -- DBMS Environment

  16. Catalogs for Relational DBMSs (Cont’d) CSCI 453 -- DBMS Environment

  17. Accessing Catalog Information • The following DBMS modules use and access a catalog very frequently; that is why it is important to implement access to the catalog as efficiently as possible. • DDL compilers. • Query and DML parser and verifier. • Query and DML compiler. • Query and DML optimizer. • Authorization and security checking. • External-to-conceptual mapping of queries and DML commands. CSCI 453 -- DBMS Environment

  18. Thank you CSCI 453 -- DBMS Environment

More Related