Database Management Systems Lecture Notes PDF

Database Management Systems Chapter: 01 – Part2 Introduction to DBMS Assoc.Prof. Mohamed Abdelsalam   Store organized data The Purpose of  Manipulate these data Database  Retrieve it easily  what are in your mind w...

Database Management Systems Chapter: 01 – Part2 Introduction to DBMS Assoc.Prof. Mohamed Abdelsalam   Store organized data The Purpose of  Manipulate these data Database  Retrieve it easily  what are in your mind when you saw this picture  Unorganized closet.  I can't easily get and retrieve clothes.  It takes more time and effort to get what I want  The result of the randomness in storing clothes, I can't know what I have, so it's possible to buy clothes that I already own - in other words, repeating clothes  I've lost storage spaces that could have been used, resulting in additional costs to acquire extra storage space what are in your mind when you saw this picture  Organized closet.  I can easily get and retrieve clothes.  It doesn’t take more time and effort to get what I want  I can know what I have, so it’s not possible to buy clothes that I already own.  I’ve kept storage spaces that could have been used, resulting in no additional costs to acquire extra storage space the concept of Database  database as a structured collection of related data.  that is organized and stored in a way that allows for efficient retrieval, management, and manipulation of information  database is built to store logically interrelated data representing some aspects of the real world.  database is constructed according to a data model which defines the way in which data and interrelationships between them can be represented.  Databases play an important role in many applications and systems " backbone of any software" A Database-Management System (DBMS)  A Database Management System (DBMS) is software that provides an interface for managing and interacting with databases. It is a critical component of modern software applications and information systems, serving as an intermediary between users or application programs and the actual data stored in a database.  The primary goal of a DBMS is to provide a way to store and retrieve database information that is both convenient and efficient A Database-Management System (DBMS) A Database-Management System (DBMS) Here are some key aspects of a DBMS  Data Storage: DBMS stores data in an organized and structured manner, making it easy to retrieve, update, and manage.  Data Retrieval: It allows users and applications to retrieve data from the database using query languages like SQL (Structured Query Language).  Data Modification: DBMS supports the addition, modification, and deletion of data in the database. A Database-Management System (DBMS) Here are some key aspects of a DBMS  Data Security: It provides mechanisms to control access to data, ensuring that only authorized users can interact with it. This includes user authentication and authorization.  Data Integrity: DBMS enforces data integrity constraints to maintain the accuracy and consistency of data, such as enforcing primary keys, foreign keys, and other rules.  Concurrency Control: It manages multiple users accessing the database in the same moment to prevent conflicts and maintain data consistency. A Database-Management System (DBMS) Here are some key aspects of a DBMS  Backup and Recovery: DBMS systems often have features for creating backups of the data and recovering it in case of system failures or data corruption.  Query Optimization: DBMS can optimize the execution of queries to improve performance and reduce the time required to retrieve or modify data.  Data Relationships: It supports the establishment of relationships between different data tables, allowing for the creation of complex data models. Disadvantages of File System Data redundancy and inconsistency the same information may be duplicated in several places (files). For example, if a student has a double major (music and mathematics) the address and telephone number of that student may appear in a file that consists of student records of students in the Mathematics department. This redundancy leads to higher storage and access cost Disadvantages of File System Difficulty in accessing data Because of file system not flexible to generate different reports, you need ask the programmer to write codes for extracting your report. file-processing environments do not allow needed data to be retrieved in a convenient manner. More responsive data-retrieval systems are required for general use Disadvantages of File System Data isolation Because data are scattered in various files, and files may be in different formats, which can lead to several issues as, Data Redundancy, Data Inconsistency ……. Disadvantages of File System Integrity problems The data values stored in the database must satisfy certain types of consistency constraints. it's difficult to maintain data integrity because there are no centralized controls or constraints to ensure that data remains accurate and follows certain rules (e.g., data validation rules or referential integrity constraints in a database). The problem is compounded when constraints involve several data items from different files. Disadvantages of File System Atomicity problems In many applications, it is necessary that, if a failure occurs, the data be restored to the consistent state that existed prior to the failure. It is difficult to ensure atomicity in a conventional file-processing system. Disadvantages of File System Security problems Not every user of the database system should be able to access all the data. But in file processing system you can not control the user privileges. Characteristics and Benefits of a Database Self-describing nature of a database system it not only contains the database itself, but also metadata which defines and describes the data and relationships between tables in the database. This information is used by the DBMS software or database users if needed. Characteristics and Benefits of a Database isolation between program and data In the file-based system, the structure of the data files is defined in the application programs so if a user wants to change the structure of a file, all the programs that access that file might need to be changed as well.  Characteristics and Benefits of a Database isolation between program and data On the other hand, in the database approach, the data structure is stored in the system catalogue and not in the programs. Therefore, one change is all that is needed to change the structure of a file. Characteristics and Benefits of a Database Support for multiple views of data A view is a subset of the database, which is defined and dedicated for particular users of the system. Multiple users in the system might have different views of the system. Each view might contain only the data of interest to a user or group of users. Characteristics and Benefits of a Database Sharing of data and multiuser system it allow many users to access the same database at the same time. This access is achieved through features called concurrency control strategies. These strategies ensure that the data accessed are always correct and that data integrity is maintained. Characteristics and Benefits of a Database Control of data redundancy each data item is stored in only one place in the database. In some cases, data redundancy still exists to improve system performance, but such redundancy is controlled by application programming. Characteristics and Benefits of a Database Enforcement of integrity constraints Database management systems must provide the ability to define and enforce certain constraints to ensure that users enter valid information and maintain data integrity. A database constraint is a restriction or rule that dictates what can be entered or edited in a table such as a postal code using a certain format or adding a valid city in the City field. Characteristics and Benefits of a Database Enforcement of integrity constraints There are many types of database constraints. Data type, for example, determines the sort of data permitted in a field, for example numbers only. Data uniqueness such as the primary key ensures that no duplicates are entered. Constraints can be simple (field based) or complex (programming). Characteristics and Benefits of a Database Restriction of unauthorized access Not all users of a database system will have the same accessing privileges. a database management system should provide a security subsystem to create and control different types of user accounts and restrict unauthorized access Characteristics and Benefits of a Database Transaction processing A database management system must include concurrency control subsystems. This feature ensures that data remains consistent and valid during transaction processing even if several users update the same information. Database-System Applications Enterprise information Sales: For customer, product, and purchase information. Accounting: For payments, receipts, account balances, assets and other accounting information. Database-System Applications Human resources: For information about employees, salaries, payroll taxes, and benefits and for generation of paychecks. Manufacturing: For management of the supply chain and for tracking production of items in factories, inventories of items in warehouses and stores, and orders for items. Database-System Applications Banking: For customer information, accounts loans, and banking transactions. Credit card transactions: For purchases on credit cards and generation of monthly statements. Finance: For storing information about holdings, sales and purchases of financial instruments such as stocks and bonds. Database-System Applications Universities: For student information, course registrations, and grades. Airlines: For reservations and schedule information. Telecommunication: For keeping records of calls made, generating monthly bills, maintaining balances on prepaid calling cards, and storing information about the communication networks. Data Abstraction  Forthe system to be usable, it must retrieve data efficiently.  Since many database system users are not computer trained, developers hide the complexity from users through several levels abstraction, to simplify user’s interactions with the system: Data Abstraction Data Abstraction  Physical level. The lowest level of abstraction describes how the data are actually stored. the physical level describes complex low-level data structures in detail. Data Abstraction  Logical level. The next-higher level of abstraction describes what data are stored in the database, and what relationships exist among those data.  The user of the logical level does not need to be aware of this complexity. This is referred to as physical data independence. Data Abstraction  View level. The highest level of abstraction describes only part of the entire database.  Many users of the database system need to access only a part of the database.  The view level of abstraction exists to simplify their interaction with the system. The system may provide many views for the same database. Database users  A database user refers to an individual or an application that interacts with a database system. Users are granted specific privileges and permissions to perform operations on the database. They can be categorized into different types based on their roles and responsibilities.  It's important to note that the specific roles and responsibilities of users may vary depending on the organization, database management system, and the specific requirements of the database environment. Database users  Database Administrator (DBA)  Application Developers  Data Analysts  Data Scientists  Data Entry Operators  Business Users  External Users Database users  Database Administrator (DBA): ❑The Database Administrator is responsible for the overall management and maintenance of the database system. ❑They handle tasks such as database installation, configuration, security, performance tuning, backup and recovery, and user management. DBAs have elevated privileges to perform administrative tasks. Database users  Application Developers: ❑Application developers are users who design and develop software applications that interact with the database. ❑They create database schemas, write queries, and implement the business logic required for the applications. Developers require appropriate access permissions to perform their tasks effectively. Database users  Data Analysts: ❑Data analysts are users who extract, analyze, and interpret data from the database to derive meaningful insights. ❑They often work with business intelligence tools and reporting systems to generate reports, visualize data, and identify trends or patterns. Data analysts require read access to relevant database tables and views. Database users  Data Scientists: ❑ Data scientists are users who employ advanced statistical and analytical techniques to extract knowledge and make predictions from data. ❑ They utilize machine learning algorithms, statistical models, and data mining techniques to uncover hidden patterns and valuable insights. Data scientists typically need read and write access to perform their analyses. Database users  Data Entry Operators: ❑Data entry operators are responsible for entering data into the database system. ❑They input data from various sources, ensuring accuracy and consistency. ❑Data entry operators require appropriate access permissions and may have restricted privileges to perform their tasks. Database users  Business Users: ❑Business users are individuals who use the database system to access and retrieve data for their day-to-day operations. ❑They may include managers, executives, sales representatives, or customer support personnel. Business users require read access to relevant data to perform their job functions efficiently. Database users  External Users: ❑External users are individuals or entities who are granted limited access to the database system from outside the organization. ❑Examples include customers, clients, vendors, or partners who may require access to specific data or services. External users have restricted privileges and access rights. Database users Following are other Important Points regarding database roles:  User Roles and Permissions: ❑ Database systems often implement role-based access control (RBAC), where users are assigned specific roles with predefined permissions. ❑ Roles group related privileges together, simplifying user management and ensuring security. Database users Following are other Important Points regarding database roles:  Authentication and Authorization: ❑Database systems employ authentication mechanisms to verify the identity of users before granting access. ❑Authorization mechanisms control user access based on their roles and permissions, ensuring data security and integrity. Database users Following are other Important Points regarding database roles:  User Privileges: ❑Privileges determine the operations a user can perform on the database objects. ❑They include read, write, update, delete, create, and execute privileges. Privileges can be granted at various levels, such as database level, table level, or column level. Database users Following are other Important Points regarding database roles:  User Management: ❑Database administrators are responsible for creating, modifying, and deleting user accounts. ❑They also handle password management, account locking, and password expiration policies to ensure secure user access. Database users Following are other Important Points regarding database roles:  Auditing and Logging: ❑Database systems often provide auditing and logging features to track user activities. ❑This helps monitor user actions, detect unauthorized access attempts, and maintain an audit trail for compliance and security purposes.