Database Principles PDF

Chapter One Database Principles 2 Who is Dr. Edgar Frank Codd ? Dr. Edgar Frank Codd (1923-2003) was a British computer scientist who is best known for his pioneering work in the field of databases. He is considered the father of relational database management systems (RDBMS) due to his...

Chapter One Database Principles 2 Who is Dr. Edgar Frank Codd ? Dr. Edgar Frank Codd (1923-2003) was a British computer scientist who is best known for his pioneering work in the field of databases. He is considered the father of relational database management systems (RDBMS) due to his development of the relational model for database management. Codd's work revolutionized the way data is stored, organized, and accessed in computer systems. He introduced the concept of organizing data into tables with rows and columns, where relationships between data entities could be established using keys. His model provided a theoretical foundation for modern database systems and greatly simplified the process of querying and manipulating data. Codd's contributions to the field of databases earned him numerous awards and honours, and his ideas continue to form the basis of database technology used widely today. 1 Database Principles 1.1 Database (DB) Definition It is a collection of interrelated data stored together without harmful or unnecessary redundancy to serve multiple applications; the data is stored so as to be independent of the programs which use it. 1.2 Database Management System (DBMS) Definition It is a system that generates runs and maintains database, and as such the system must include all the software needed for the purpose. 3 1.3 Database Advantages Database has various advantages over the classical approach. In that they are able to satisfy the criteria listed below: 1. The ability to operate on different data structure. 2. Reduction in a data redundancy. 3. Independent of data from the medium on which it is stored. 4. High speed of retrieval and fast online use. 5. High degree of flexibility in the handling of the data format. 6. Minimum cost by minimizing the total storage requirements. 7. Inconsistency can be avoided. 8. Integrity can be maintained. 9. Standard can be enforced. 10. Security restrictions can be applied. 1.4 Database Architecture The architecture of the database divides into three general levels (see Fig(1)): Internal (Physical), Conceptual (Logical) and External (View). The internal level is the one closest to physical storage. The external level is the one concerned with the way in which the data is viewed by individual users, and the conceptual level comprises a unique central description of the various information contents that may be in a database. This includes the description of what actions, such as changes and retrievals, are permissible on the information content. 4  Fig(1): Database Architecture 1.5 Schema It is a chart of the types of data that are used. It gives the name of the entities and attributes and specifies the relations among them. It is a framework into which the values of the data items can be fitted. For each database there is only one schema. For example, PRODUCT (PId ,PName , PUnit_Price,PUsages). 1.6 Instances is the actual content of the database at a particular point in time. Analogous to the value of a variable. For example, PRODUCT (444 ,Car , 25000000,Travel). 1.7 Data Models A data model is a set of concepts and constructs (tools) by which the contents of relationships within a database can be described. In other words, it can describe: Data, Data relationships, Data semantics, Data constraints. For example, ER model, EER model, DFD model. 5 1.8 Database Applications 1. Banking: all transactions 2. Airlines: reservations, schedules 3. Universities: registration, grades 4. Sales: customers, products, purchases 5. Manufacturing: production, inventory, orders, supply chain 6. Human resources: employee records, salaries, tax deductions 1.9 Introduction to Normalization Normalization is a formal process for deciding which attributes should be grouped together in a relation, before we preceding with physical design, we need a method to validate the logical design to this point. Normalization is primarily a tool to validate and improve a logical design, so that it satisfies certain constrains that avoid unnecessary duplication of data. It includes 6 steps: 6  Chapter Tow Database Transaction 7 2 Transaction & Transaction Properties 2.1 Transaction Definition A transaction is a sequence of operations that takes database from a consistent state to another consistent state. For example, add new record to a table in database. An official example of transaction can be bank accounts of two users, say A & B. When a bank employee transfers amount of Rs. 500 from A's account to B's account, a number of tasks are executed behind the screen. This very simple and small transaction includes several steps: decrease A's bank account from 500. In simple words, the transaction involves many tasks, such as opening the account of A, reading the old balance, decreasing the 500 from it, saving new balance to account of A and finally closing it. To add amount 500 in B's account same sort of tasks need to be done: 8 2.2 Transaction ACID Prosperities In the context of transaction processing, the acronym ACID refers to the four key properties of a transaction: atomicity, consistency, isolation, and durability. Atomicity All changes to data are performed as if they are a single operation. That is, all the changes are performed, or none of them are. For example, in an application that transfers funds from one account to another, the atomicity property ensures that, if a debit is made successfully from one account, the corresponding credit is made to the other account. Consistency Data is in a consistent state when a transaction starts and when it ends. For example, in an application that transfers funds from one account to another, the consistency property ensures that the total value of funds in both the accounts is the same at the start and end of each transaction. Isolation The intermediate state of a transaction is invisible to other transactions. As a result, transactions that run concurrently appear to be serialized. For example, in an application that transfers funds from one account to another, the isolation property ensures that another transaction sees the transferred funds in one account or the other, but not in both, nor in neither. Durability After a transaction successfully completes, changes to data persist and are not undone, even in the event of a system failure. For example, in an application that transfers funds from one account to another, the durability property ensures that the changes made to each account will not be reversed. 9 2.3 Transaction States A transaction in a database can be in one of the following states (see Fig(2)): Fig(2): Transaction States 1. Active: In this state the transaction is being executed. This is the initial state of every transaction. 2. Partially Committed: When a transaction executes its final operation, it is said to be in this state. After execution of all operations, the database management system performs some checks e.g. the consistency state of database after applying output of transaction onto the database. 3. Failed: If any checks made by database management system fails, the transaction is said to be in failed state, from where it can no longer proceed further. 4. Aborted: If any of checks fails and transaction reached in Failed state, the database management system rolls back all its write operation on the database to make database in the state where it was prior to start of execution of transaction. Transactions in this state are called aborted. Database management system can select one of the two operations after a transaction aborts: 10  Re-start the transaction Kill the transaction 5. Committed: If transaction executes all its operations successfully it is said to be committed. All its effects are now permanently made on database. 2.4 Transaction Scheduling There are many approaches to arrange group of transactions. When more than one transaction is executed by the operating system in a multiprogramming environment, there are possibilities that instructions of one transaction are interleaved with some other transaction. Therefore, in the following are some terms we will use them in transaction scheduling. Schedule: A chronological execution sequence of transaction is called schedule. A schedule can have many transactions in it, each comprising of number of instructions/tasks. Serial Schedule/Serializability: A schedule in which transactions are aligned in such a way that one transaction is executed first. When the first transaction completes its cycle then next transaction is executed. Transactions are ordered one after other. This type of schedule is called serial schedule/serializability as transactions are executed in a serial manner. Equivalence schedules: Schedules can equivalence of the following three types: Result Equivalence: If two schedules produce same results after execution, are said to be result equivalent. They may yield same result for some value and may yield different results for another values. That's why this equivalence is not generally considered significant. 11 View Equivalence: Two schedules are view equivalence if transactions in both schedules perform similar actions in similar manner. For example: If Trns reads initial data in T1 then Trns also reads initial data in T2. If Trns reads value written by J in T1 then Trns also reads value written by J in T2. If Trns performs final write on data value in T1 then Trans also performs final write on data value in T2. Conflict Equivalence: Two operations are said to be conflicting if they have the following properties: Both belong to separate transactions Both accesses the same data item At least one of them is "write" operation 12