Lecture_1_3com.pdf

Database Systems ‫نظم قواعد البيانات‬ First Term 2024-2025 Dr. Faten Elshwemy 1  Course Info Course Title: Database Systems Course Code: CCE 3112 Grading: – Term work: 30 Points – Practical/Oral: 30 Points – Final Exam: 90 Points...

Database Systems ‫نظم قواعد البيانات‬ First Term 2024-2025 Dr. Faten Elshwemy 1  Course Info Course Title: Database Systems Course Code: CCE 3112 Grading: – Term work: 30 Points – Practical/Oral: 30 Points – Final Exam: 90 Points Lecture: 4 hours Section: 2 hours TA: Eng. Mostafa Database Management Systems Lecture 1: Introduction What is a database? Data: known facts that can be recorded and have an implicit meaning. Database: A very large, collection of logically related information organized so that it can be easily accessible, managed, and updated. Who uses databases ? Databases are used - nearly - everywhere, nowadays. Database applications ? E-commerce : Amazon.com, etc. Enroll at university Airlines and travel services Scientific data such as biology, etc. Spatial data such as maps, World Wide Web Many others… Why use a database? Databases can store very large numbers of records efficiently. It is very quick and easy to find information. It is easy to add new data and to edit or delete old data. Data can be searched easily. Data can be sorted easily. More than one person can access the same database at the same time - multi-access. Security may be better than in paper files. Types of Databases Relational Databases:organize data into tables (relations). They are based on the relational model, which uses relationships between tables to manage data.Examples:MySQL,and Microsoft SQL Server. Object-Oriented Databases:store data as objects, similar to the way data is handled in object-oriented programming languages. Examples: ObjectDB. Types of Databases Graph Databases:are designed to handle data with complex relationships, represented as graphs with nodes, edges, and properties. They are used for applications that require analyzing relationships and network structures. such as social networks.Examples: Amazon Neptune. Hierarchical Databases: These databases organize data in a tree-like structure with parent-child relationships. They are suitable for representing hierarchical relationships but can be rigid in their structure. What is a DBMS ? DBMS stands for Database Management System A database is a collection of information DBMS is a software package designed to store, manage and provide access to databases. DBMS primarily functions as an interface between the end user and the database. What is a DBMS ? The DBMS is a general-purpose software system that facilitates the processes of defining, constructing, manipulating, providing data security, providing backup and recovery and sharing databases among various users and applications. What is a DBMS ? Defining a database involves specifying the data types, structures, and constraints of the data to be stored in the database. Constructing the database is the process of storing the data on some storage medium that is controlled by the DBMS. What is a DBMS ? Manipulating a database includes functions such as querying the database to retrieve specific data, updating the database, and generating reports from the data. Sharing a database allows multiple users and programs to access the database simultaneously. What is a DBMS ? Data Security: Controlling access to the data and ensuring data privacy. Protects data from unauthorized access, and corruption. Strategies include:  Authentication: Verifies the identity of users accessing the database.  Authorization: Controls access rights and permissions for different users.  Encryption: Secures data by converting it into a format that is unreadable without the appropriate decryption key. What is a DBMS ? Backup and Recovery: Providing mechanisms for data backup and recovery in case of failures. What is a DBMS ? Examples of DBMS: MS SQL Server Oracle DB2 (IBM) MS Access MySQL  Why Study Databases?? Many computing applications deal with large amounts of information Database systems give a set of tools for storing, searching and managing this information Datasets increasing in diversity and volume. Database Actors Different roles for users who may use the database in a daily basis: Database administrator (DBA): responsible for management the DBMS environment. Also. Responsible for authorizing access to the database. Database designer: responsible for identifying the data to be stored in the database and choosing the appropriate structure to store it. End users: the people who need access to query or update the database to perform their jobs. Application developer: implement programs that end-users will use to perform their jobs using the database. Database Design and Modeling  Conceptual Design: Creating a high-level model of the database structure using Entity-Relationship (ER) diagrams to represent entities and their relationships.  Logical Design: Translating the conceptual design into a logical model, specifying the tables, columns, and relationships.  Physical Design: Implementing the logical model into a physical database schema, optimizing for performance and storage.  Levels of Abstraction Many views: – Views describe how users see the data. Single conceptual (logical) schema – Conceptual schema defines logical structure Single physical schema: – Physical schema describes the files and indexes used. Levels of DBMS Architecture Diagram Example: University Database Conceptual schema: – Students(sid: string, name: string, login: string, age: integer, gpa:real) – Courses(cid: string, cname:string, credits:integer) – Enrolled(sid:string, cid:string, grade:string) Physical schema: – Relations stored as unordered files. – Index on first column of Students. External Schema (View): – Course_info(cid:string, enrollment:integer) – CS542Students(sid: string, grade:string) Data Independence Applications insulated from how data is structured and stored. Logical data independence: – Protection from changes in logical structure of data. Physical data independence: – Protection from changes in physical structure of data.  One of the most important benefits of using a DBMS!  Files vs. DBMS If we were to use files, we would have to : Must write special code for different queries Must protect data from inconsistency due to multiple concurrent users Must manage crash recovery in some special-purpose manner Each application has manages its own data in custom format in its owned files. Why Use a DBMS? Management of data redundancy Consistency of data Data sharing Increased data security Different applications/ users can access the database via the DBMS. Increased data backup and recovery Why Use a DBMS? Self describing nature of databases: The DBMS contains not only the data itself, but a complete definition and description of the data structure and constraints (known as meta-data) Why Use a DBMS? Insulation between Programs and Data: DBMS offer data-program independence where the structure of data files can be modified without requiring a modification to the program that uses it. Multiuser transaction processing : DBMS allows multiple users to access the database at the same time. DBMS include concurrency control to ensure if several users are trying to update the same data at same time, then the result will be correct.  Concurrency Control Concurrent execution of user programs is essential for good DBMS performance. Interleaving actions of different user programs can lead to inconsistency: – e.g., check is cleared while account balance is being computed. DBMS ensures such data inconsistency problems don’t arise: – E.g., users can pretend they are using a single-user system  These layers Structure of a DBMS must consider concurrency control and recovery A typical DBMS Query Optimization has a layered and Execution architecture. Relational Operators Files and Access Methods Concurrency Buffer Management control and Disk Space Management recovery components not shown. DB