Database Systems Introduction to Databases and Data Warehouses
Database Systems Introduction to Databases and Data Warehouses CHAPTER 10 - Overview of DBMS Functionalities and Database Administration Copyright (c) 2016 Nenad Jukic and Prospect Press
DBMS COMPONENTS § DBMS software is used for: • Creation of databases • Manipulation of the data in the databases (i. e. insertion, storage, retrieval, update and deletion) • Maintenance of databases • Creating front-end applications (in some DBMS packages) Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 10 – Slide
DBMS COMPONENTS Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 10 – Slide
DBMS COMPONENTS § Data definition component • Used to create the components of the database o E. g. database tables, referential integrity constraints connecting the created tables. • Uses DDL (Data Definition Language) SQL commands Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 10 – Slide
DBMS COMPONENTS § Data manipulation component • Used to insert, read, update, and delete information in a database • Uses DML (Data Manipulation Language) SQL commands • Single-user systems o Data manipulation component used by one user at a time • Multiuser systems o Data manipulation component used by multiple users at the same time Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 10 – Slide
DBMS COMPONENTS § Data administration component • Used for technical, administrative, and maintenance tasks of database systems • DCL (Data Control Language) and TCL (Transaction Control Language) SQL commands are used during these tasks Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 10 – Slide
DBMS COMPONENTS § Application development component • Used to develop front-end applications Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 10 – Slide
DATABASE ADMINISTRATION § Database administration • Encompasses the activities that are necessary for the proper functioning of a deployed database system, such as: o o o Monitoring and maintaining the database system Securing the database against unauthorized access Providing database backup and recovery Ensuring database integrity Optimizing database performance Developing and implementing database policies and standards Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 10 – Slide
DATABASE ADMINISTRATION § Monitoring and maintaining the database system • Sample activities o Recognizing instances when maintenance activities are needed o Observing the usage of tables o Managing and upgrading the database software and hardware resources Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 10 – Slide
DATABASE ADMINISTRATION § Monitoring and maintaining the database system • View materialization o Saving a view as an actual physical table (materialized view) Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 10 – Slide 10
DATABASE ADMINISTRATION § Monitoring and maintaining the database system • Data dictionary o Repository of the metadata • Catalog o The data dictionary created by the DBMS o Can be queried using SQL • Sample entries in data dictionary: Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 10 – Slide 11
Example - Querying data dictionary in the ZAGI Retail Company Sales Department Database SELECT table_name, column_name, data_type, data_length FROM user_tab_columns; Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 10 – Slide 12
DATABASE ADMINISTRATION § Securing the database against unauthorized access • Preventing unauthorized access to data • Using methods such as: o Authentication o Access privileges o Encryption Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 10 – Slide 13
DATABASE ADMINISTRATION § Authentication • Login procedure using user ID and password Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 10 – Slide 14
DATABASE ADMINISTRATION § Access privileges • Assigned to the database user account • Determine user’s privileges on database columns, relations and views • Include the following actions: o o o SELECT UPDATE ALTER DELETE INSERT Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 10 – Slide 15
DATABASE ADMINISTRATION § Access privileges • Authorization matrix – implements the access privileges o Provided by the DBMS o Managed by the DBA o Example: Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 10 – Slide 16
DATABASE ADMINISTRATION § Access privileges • DCL commands GRANT and REVOKE • Example: GRANT SELECT, UPDATE ON vendor TO alice; REVOKE UPDATE ON vendor FROM alice; Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 10 – Slide 17
DATABASE ADMINISTRATION § Access privileges • Role-based access control o Groups contain multiple users o Access privileges assigned to the groups o Example: CREATE ROLE accountant; GRANT SELECT ON payroll TO accountant; GRANT accountant TO brian; Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 10 – Slide 18
DATABASE ADMINISTRATION § Encryption • Encryption key - information scrambling algorithm • Decryption key - reverts the information to its original state Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 10 – Slide 19
DATABASE ADMINISTRATION § Providing Database Backup and Recovery • Backup - saving additional physical copies of the data • Recovery - recovering the content of the database after a failure Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 10 – Slide 20
DATABASE ADMINISTRATION § Providing Database Backup and Recovery • Recovery log o Logs database updates o Ensures against loss of updates • Checkpoint o Part of a recovery log o Indicates a point when updates are written on the disk Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 10 – Slide 21
DATABASE ADMINISTRATION § Providing Database Backup and Recovery • DBMS actions in the event of a failure o Rolling back to the checkpoint state o Redoing the updates in the recovery log since the last checkpoint • TCL command COMMIT o Causes all the updates to be recorded on the disk • TCL command ROLLBACK o Rolls back all the updates since the last COMMIT Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 10 – Slide 22
DATABASE ADMINISTRATION § Providing Database Backup and Recovery • Complete mirrored backup o Ensures against complete database destruction Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 10 – Slide 23
DATABASE ADMINISTRATION § Ensuring Database Integrity • Preventing insertion, modification, or deletion actions that result in invalid, corrupt, or low-quality data in the database • Database integrity can be compromised through events such as: o o o Unauthorized malicious data updates Update failure Accidental misuse Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 10 – Slide 24
DATABASE ADMINISTRATION § Optimizing database performance • Seeks to minimize the response time for database queries • Involves actions such as: o Indexing o Denormalization o View materialization o Query optimization Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 10 – Slide 25
DATABASE ADMINISTRATION § Query optimization • Examining multiple ways of executing the same query and choosing the fastest option • Query optimizer o o o DBMS feature Identifies the possible ways to execute the query (query plans) Chooses the fastest query plan • Query cost o The time length of execution • Query hint o Overrides the default behavior of the query optimizer Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 10 – Slide 26
DATABASE ADMINISTRATION § Developing and implementing database policies and standards • Policies and standards for database development o E. g. naming conventions • Policies and standards for database use o E. g. business rules • Policies and standards for database management and administration o E. g. policy for assigning administration tasks Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 10 – Slide 27
DATABASE ADMINISTRATION § Developing and implementing database policies and standards • Common purpose for database policies and standards is to reflect and support business processes and business logic Jukić, Vrbsky, Nestorov – Database Systems Copyright (c) 2016 Nenad Jukic and Prospect Press Chapter 10 – Slide 28
- Slides: 28