CMSC424: Database DesignAdministrativeTodayExampleA file-system based solutionSlide 6What’s wrong with this solution ?Slide 8Slide 9Slide 10Slide 11Slide 12Slide 13Slide 14Slide 15How ?Data AbstractionSlide 18Data Abstraction: Banking ExampleSlide 20DBMS at a GlanceData ModelingData RetrievalData StorageData IntegritySlide 26DBMS at a glanceRecapSlide 29MotivationDatabase Design StepsEntity-Relationship ModelSlide 33ER Diagram: Starting ExampleRest of the classNext: Relationship CardinalitiesCMSC424: Database DesignInstructor: Amol Deshpande [email protected]Homework 1 out today.Laptops.Email to me: write CMSC424 in the title.TodayData management challenges in a very simple applicationWhy we can’t use a file system to do database managementData ModellingGoing from conceptual requirements of a application to a concrete data modelExampleSimple Banking ApplicationNeed to store information about:AccountsCustomersNeed to support:ATM transactionsQueries about the dataInstructive to see how a naïve solution will workA file-system based solutionData 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 solutionWrite application programs to support the operationsIn your favorite programming languageTo support withdrawals by a customer for amount $X from account YScan /usr/db/accounts, and look for Y in the 1st fieldSubtract $X from the 2nd field, and rewrite the fileTo support finding names of all customers on street ZScan /usr/db/customers, and look for (partial) matches for Z in the addess field…What’s wrong with this solution ?1. Data redundancy and inconsistencyNo control of redundancyCustomer 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 timeInconsistenciesData in different files may not agree Very critical issueWhat’s wrong with this solution ?2. Evolution of the database is hardDelete an accountWill have to rewrite the entire fileAdd a new field to the accounts file, or split the customers file in two parts:Rewriting the entire file least of the worriesWill probably have to rewrite all the application programsWhat’s wrong with this solution ?3. Difficulties in Data RetrievalNo sophisticated tools for selective data accessAccess only the data for customer XInefficient to scan the entire fileLimited reuseFind customers who live in area code 301Unfortunately, no application program already writtenWrite a new program every time ?What’s wrong with this solution ?4. Semantic constraintsSemantic integrity constraints become part of program codeBalance should not fall below 0Every program that modifies the balance will have to enforce this constraintHard to add new constraints or change existing onesBalance should not fall below 0 unless overdraft-protection enabledNow what? Rewrite every program that modifies the balance ?What’s wrong with this solution ?5. Atomicity problems because of failuresJim transfers $100 from Acct #55 to Acct #3761. Get balance for acct #552. 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 diskCRASHMust be atomic Do all the operations or none of the operationsWhat’s wrong with this solution ?6. Durability problems because of failuresJim transfers $100 from Acct #55 to Acct #3761. Get balance for acct #552. 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 receiptCRASHAfter reporting success to the user, the changesbetter be there when he checks tomorrowWhat’s wrong with this solution ?7. Concurrent access anomaliesJoe@ATM1: Withdraws $100 from Acct #55 1. Get balance for acct #55 2. If balance55 > $100 thena. balance55 := balance55 – 100b. dispense cashc. update balance55Jane@ATM2: Withdraws $50 from Acct #55 1. Get balance for acct #55 2. If balance55 > $50 thena. balance55 := balance55 – 50b. dispense cashc. update balance55What’s wrong with this solution ?7. Concurrent access anomaliesJoe@ATM1: Withdraws $100 from Acct #55 1. Get balance for acct #55 2. If balance55 > $100 thena. balance55 := balance55 – 100b. dispense cashc. update balance55Jane@ATM2: Withdraws $50 from Acct #55 1. Get balance for acct #55 2. If balance55 > $50 thena. balance55 := balance55 – 50b. dispense cashc. update balance55Balance would only reflect one of the two operations Bank loses moneyWhat’s wrong with this solution ?8. Security IssuesNeed fine grained control on who sees whatOnly the manager should have access to accounts with balance more than $100,000How do you enforce that if there is only one accounts file ? Database management provide an end-to-end solution to all of these problemsHow ?The key insight is whats called data abstractionData AbstractionProbably the most important purpose of a DBMSGoal: Hiding low-level details from the users of the systemThrough use of logical abstractionsData AbstractionLogicalLevelPhysical LevelView LevelView 1 View 2 View n…How data is actually stored ? e.g. are we using disks ? Which file system ?What data is stored ? describe data properties such as data semantics, data relationshipsWhat data users and application programs see ?Data Abstraction: Banking ExampleLogical level:Provide an abstraction of tablesTwo tables can be accessed:accountsColumns: account number, balancecustomersColumns: name, address, account numberView level:A teller (non-manager) can only see a part of the accounts tableNot containing high balance accountsData Abstraction: Banking ExamplePhysical Level:Each table is stored in a separate ASCII file# separated fieldsIdentical to what we had before ?BUT the users are not aware of
View Full Document
Unlocking...