DB Lecture 2 - Fundamental Database Concepts PDF

10/9/2024 Lecture 2 Fundamental Database Concepts © 2023 by Dr. Manal Abdel-Kader Abdel-Fattah 2.1 1 Lecture objectives 1. Database Users and Roles 2. Transactions © 2023 by Dr. Manal Abdel-Kader Abdel-Fattah 2.2 2...

10/9/2024 Lecture 2 Fundamental Database Concepts © 2023 by Dr. Manal Abdel-Kader Abdel-Fattah 2.1 1 Lecture objectives 1. Database Users and Roles 2. Transactions © 2023 by Dr. Manal Abdel-Kader Abdel-Fattah 2.2 2 1  10/9/2024 1. DBMS Roles ❖ Actors On the Scene (people interested in the actual data): 1) Database administrators 2) Database designers 3) Systems analysts and application programmers 4) End users © 2023 by Dr. Manal Abdel-Kader Abdel-Fattah 2.3 3 Actors on the Scene 1) Database Administrators ❖Acquiring a DBMS. ❖Acquiring HW and SW to support the DBMS. ❖Managing the system (coordinating and monitoring its use). ❖Authorizing access (security policies). ❖Managing staff, including DB designers. © 2023 by Dr. Manal Abdel-Kader Abdel-Fattah 2.4 4 2  10/9/2024 Actors on the Scene 2) Database Designers ❖Identifying the information of interested in the system. ❖Designing the database conceptual schema. ❖Designing views for particular users. ❖Designing the physical data layout and logical schema. ❖Adjusting data parameters for performance. © 2023 by Dr. Manal Abdel-Kader Abdel-Fattah 2.5 5 Actors on the Scene 3) Systems Analysts and Application Programmers ❖System analysts: Determine requirements of end users. ❖ Application programmers: Implement the specifications as programs; then they test, debug, document, and maintain the transactions. ❖Such analysts and programmers— commonly referred to as software developers (generic database developers) or software engineers. © 2023 by Dr. Manal Abdel-Kader Abdel-Fattah 2.6 6 3  10/9/2024 Actors on the Scene 4) End Users: People whose jobs require access to the database for querying, updating, and generating reports ❖ Casual users: need different information each time ❖ Naïve or parametric users: using standard types of queries and updates called “canned transactions” that have been carefully programmed and tested ❖ Sophisticated users: people who understand the system and the data and use it in many novel ways ❖ Standalone users: people who use personal easy- to-use databases (ready-made program packages) for personal data. © 2023 by Dr. Manal Abdel-Kader Abdel-Fattah 2.7 7 DBMS Roles application application application program(s) application program(s) users of program(s) program(s) the data application developers query processor DBMS security manager system concurrency manager developers index manager database system designer administrator data (and DB designer) definition data processor data dictionary © 2023 by Dr. Manal Abdel-Kader Abdel-Fattah 2.8 8 4  10/9/2024 1. DBMS Roles ❖ Actors Behind the Scene: people who maintain the environment but aren't interested in the actual data. 1) DBMS designers and implementers. 2) Tools developers. 3) Operators and maintenance personnel. 4) Database researchers. © 2023 by Dr. Manal Abdel-Kader Abdel-Fattah 2.9 9 Actors Behind the Scene 1) DBMS designers and implementers ❖Work for the company that supplies the DBMS (i.e. Microsoft , Oracle, Sybase, MySQL …) ❖Programmers and engineers ❖Design and implement the DBMS © 2023 by Dr. Manal Abdel-Kader Abdel-Fattah 2.10 10 5  10/9/2024 Actors Behind the Scene 2) Tools Developers ❖Design and implement DBMS. ❖Kinds of tools: database design aids, performance monitoring tools, user and designer interfaces. © 2023 by Dr. Manal Abdel-Kader Abdel-Fattah 2.11 11 Actors Behind the Scene 3) Operators and maintenance personnel ❖Run and maintain the computer environment in which a DBMS operates. ❖Probably work for the database administrator (DBA). © 2023 by Dr. Manal Abdel-Kader Abdel-Fattah 2.12 12 6  10/9/2024 Actors Behind the Scene 4) Database Researchers ❖Academic or industrial researchers. ❖Develop new theory, new designs, new data models and new algorithms to improve future database management systems. © 2023 by Dr. Manal Abdel-Kader Abdel-Fattah 2.13 13 2.Transactions ❖ transaction = an indivisible unit of data processing ❖ All transactions must have the ACID properties: 1) Atomicity: all or nothing. 2) Consistency: no constraint violations. 3) Isolation: no interference from other concurrent transactions. 4) Durability: committed changes must not be lost due to any kind of failure. © 2023 by Dr. Manal Abdel-Kader Abdel-Fattah 2.14 14 7  10/9/2024 1) Atomic Transactions ❖ Aly wants to move $200 from his savings account to his checking account. 1) Money must be subtracted from savings account. 2) Money must be added to checking count. If both happen, Aly and the bank are both happy. If neither happens, Aly and the bank are both happy. If only one happens, either Aly or the bank will be unhappy. Aly’s transfer must be all or nothing. © 2023 by Dr. Manal Abdel-Kader Abdel-Fattah 2.15 15 Transactions are Atomic ❖ Transactions must be atomic (indivisible) ❖ The DBMS must ensure atomicity ❖ Everything happens, or nothing happens ❖ Boundaries of transaction (in time) are generally set by the application … ❖ The DBMS has no means of determining the intention of a transaction © 2023 by Dr. Manal Abdel-Kader Abdel-Fattah 2.16 16 8  10/9/2024 2) Consistent Transactions ❖ Mona tries to withdraw $1000 from account 387. Accounts No PIN Balance constraint: 101 8965 10965.78 account.Balance must be non-negative 387 6643 652.55 543 4287 8720.12 any transaction withdrawing more than $652.55 from acct 387 will violate this constraint Mona’s transaction cannot be accepted. © 2023 by Dr. Manal Abdel-Kader Abdel-Fattah 2.17 17 Transactions are Consistent ❖A transaction must leave the database in an valid or consistent state. ❖ valid state == no constraint violations ❖A constraint is a declared rule defining specifying database states. ❖ Constraints may be violated temporarily … but must be corrected before the transaction completes. © 2023 by Dr. Manal Abdel-Kader Abdel-Fattah 2.18 18 9  10/9/2024 3) Concurrent Transactions ❖ Aly is withdrawing $500 from account 543. ❖ Mona’s employer is depositing $1983.23 to account 543. ❖ These transactions are happening at the same time. Accounts Accounts No PIN Balance No PIN Balance 101 8965 10965.78 101 8965 10965.78 387 6643 652.55 387 6643 652.55 543 4287 8720.12 543 4287 10203.35 Combined result of both transactions must be correct © 2023 by Dr. Manal Abdel-Kader Abdel-Fattah 2.19 19 Transactions are Isolated ❖ Iftwo transactions occur at the same time, the cumulative effect must be the same as if they had been done in isolation ❖ ($8720.12 - $500) + $1983.23 = $10203.35 ❖ ($8720.12 + $1983.23) - $500 = $10203.35 ❖ Ensuring isolation is the task of concurrency control © 2023 by Dr. Manal Abdel-Kader Abdel-Fattah 2.20 20 10  10/9/2024 4) Durable Transactions ❖ Mona deposits $50,000 to account 387.Later, the bank’s computer crashes due to a lightning storm. Accounts Accounts No PIN Balance No PIN Balance 101 8965 10965.78 101 8965 10965.78 387 6643 652.55 387 6643 50652.55 543 4287 8720.12 543 4287 8720.12 Mona’s deposit cannot be lost. © 2023 by Dr. Manal Abdel-Kader Abdel-Fattah 2.21 21 Transactions are Durable ❖ Once a transaction's effect on the database state has been committed, it must be permanent ❖ The DBMS must ensure persistence, even in the event of system failures ❖ Sources of failure: ❖ Computer or operating system crash ❖ disk failure ❖ power outage, operator errors, … © 2023 by Dr. Manal Abdel-Kader Abdel-Fattah 2.22 22 11  10/9/2024 Thank you © 2023 by Dr. Manal Abdel-Kader Abdel-Fattah 2.23 23 12