CMSC424 Database Design Instructor Amol Deshpande amol cs umd edu Administrative Homework 1 out today Laptops Email to me write CMSC424 in the title Today Data management challenges in a very simple application Why we can t use a file system to do database management Data Modelling Going from conceptual requirements of a application to a concrete data model Example Simple Banking Application Need to store information about Accounts Customers Need to support ATM transactions Queries about the data Instructive to see how a na ve solution will work A file system based solution Data stored in files in ASCII format seperated files in usr db directory usr db accounts Account Number Balance 101 900 102 700 usr db customers Customer Name Customer Address Account Number Johnson 101 University Blvd 101 Smith 1300 K St 102 Johnson 101 University Blvd 103 A file system based solution Write application programs to support the operations In your favorite programming language To support withdrawals by a customer for amount X from account Y Scan usr db accounts and look for Y in the 1 st field Subtract X from the 2nd field and rewrite the file To support finding names of all customers on street Z Scan usr db customers and look for partial matches for Z in the addess field What s wrong with this solution 1 Data redundancy and inconsistency No control of redundancy Customer Name Customer Address Account Number Johnson 101 University Blvd 101 Smith 1300 K St 102 Johnson 101 University Blvd 103 Especially true when programs data organization evolve over time Inconsistencies Data in different files may not agree Very critical issue What s wrong with this solution 2 Evolution of the database is hard Delete an account Will have to rewrite the entire file Add a new field to the accounts file or split the customers file in two parts Rewriting the entire file least of the worries Will probably have to rewrite all the application programs What s wrong with this solution 3 Difficulties in Data Retrieval No sophisticated tools for selective data access Access only the data for customer X Inefficient to scan the entire file Limited reuse Find customers who live in area code 301 Unfortunately no application program already written Write a new program every time What s wrong with this solution 4 Semantic constraints Semantic integrity constraints become part of program code Balance should not fall below 0 Every program that modifies the balance will have to enforce this constraint Hard to add new constraints or change existing ones Balance should not fall below 0 unless overdraft protection enabled Now what Rewrite every program that modifies the balance What s wrong with this solution 5 Atomicity problems because of failures Jim transfers 100 from Acct 55 to Acct 376 1 Get balance for acct 55 2 If balance55 100 then a balance55 balance55 100 b update balance55 on disk CRASH c get balance from database for acct 376 d balance376 balance376 100 e update balance376 on disk Must be atomic Do all the operations or none of the operations What s wrong with this solution 6 Durability problems because of failures Jim transfers 100 from Acct 55 to Acct 376 1 Get balance for acct 55 2 If balance55 100 then a balance55 balance55 100 b update balance55 on disk c get balance from database for acct 376 d balance376 balance376 100 e update balance376 on disk f print receipt CRASH After reporting success to the user the changes better be there when he checks tomorrow What s wrong with this solution 7 Concurrent access anomalies Joe ATM1 Withdraws 100 from Acct 55 1 Get balance for acct 55 2 If balance55 100 then a balance55 balance55 100 b dispense cash c update balance55 Jane ATM2 Withdraws 50 from Acct 55 1 Get balance for acct 55 2 If balance55 50 then a balance55 balance55 50 b dispense cash c update balance55 What s wrong with this solution 7 Concurrent access anomalies Joe ATM1 Withdraws 100 from Acct 55 1 Get balance for acct 55 2 If balance55 100 then a balance55 balance55 100 b dispense cash Jane ATM2 Withdraws 50 from Acct 55 1 Get balance for acct 55 2 If balance55 50 then a balance55 balance55 50 b dispense cash c update balance55 c update balance55 Balance would only reflect one of the two operations Bank loses money What s wrong with this solution 8 Security Issues Need fine grained control on who sees what Only the manager should have access to accounts with balance more than 100 000 How do you enforce that if there is only one accounts file Database management provide an end to end solution to all of these problems How The key insight is whats called data abstraction Data Abstraction Probably the most important purpose of a DBMS Goal Hiding low level details from the users of the system Through use of logical abstractions Data Abstraction What data users and application programs see View Level View 1 What data is stored describe data properties such as data semantics data relationships How data is actually stored e g are we using disks Which file system View 2 Logical Level Physical Level View n Data Abstraction Banking Example Logical level Provide an abstraction of tables Two tables can be accessed accounts customers Columns account number balance Columns name address account number View level A teller non manager can only see a part of the accounts table Not containing high balance accounts Data Abstraction Banking Example Physical Level Each table is stored in a separate ASCII file separated fields Identical to what we had before BUT the users are not aware of this They only see the tables The application programs are written over the tables abstraction Can change the physical level without affecting users In fact can even change the logical level without affecting the teller DBMS at a Glance 1 Data Modeling 2 Data Retrieval 3 Data Storage 4 Data Integrity Data Modeling A data model is a collection of concepts for describing data properties and domain knowledge Data relationships Data semantics Data constraints We will discuss two models extensively in this class Entity relationship Model Relational Model Probably discuss XML as well Data Retrieval Query Declarative data retrieval program describes what data to acquire not how to acquire it Non declarative Declarative posed against the tables abstraction scan the accounts file look for number 55 in the 2nd field subtract 50 from the 3rd field Subtract 50 from the column named balance for the row corresponding to account number 55 in the accounts table How
View Full Document
Unlocking...