Transcript Chapter10
Database Structures An alternative to a distributed information system based upon files is a centralized system based upon the concept of databases. File-Oriented System Rental Dept. Sales Dept. Purchasing Dept. Marketing Dept. Database-Oriented System Maintenanc e Dept. Payroll Dept. Sales Dept. Purchasing Dept. Rental Dept. Video Rental Files Video Sales Files Video Purchase Files Advertisement Files Store Upkeep Files Store Personnel Files DISADVANTAGES OF EACH SYSTEM File-Oriented Database-Oriented Duplication of effort Data security problems Multiple error sources Widespread error effects CS 111.01 Chapter 10 – Database Structures Integrated Database Payroll Dept. Maintenanc e Dept. Marketing Dept. Page 125 A Modular View of a Database System APPLICATION SOFTWARE System of programs specifying how to present data to the user DATABASE MANAGEMENT SYSTEM System of programs controlling how the data is accessed DATABASE The DBMS uses schema and subschema to ensure data security. A schema describes the way the entire database is organized. A subschema describes the organization of the portion of the database that is accessible to a particular type of user. The stored data of the system CS 111.01 Chapter 10 – Database Structures Page 126 The Relational Database Model The simplest conceptual arrangement of a database uses a table of rows (called tuples) and columns (called attributes). Last Name First Name M.I. SSN Hrs Class GPA Major DOB Moose Bullwinkle J 623549801 112 Sr 3.24 CHEM 03/21/76 Squirrel Rocky J 469213215 48 So 2.17 BIOL 02/16/79 Coyote Wile E 803662861 54 So 3.16 MATH 11/05/81 Hound Huckleberry H 901477524 75 Jr 3.05 MKTG 09/12/83 Gorilla Magilla B 388728279 102 Sr 3.76 ELED 12/12/80 Pig Porky P 477908263 66 Jr 2.38 ECON 05/02/82 The major advantage of this model is its logical conceptualization. The major disadvantage is the substantial amount of software and hardware overhead required to maintain and access the table. CS 111.01 Chapter 10 – Database Structures Page 127 Relational Operation: SELECT The SELECT operation determines which tuples have particular attributes. ORIGINAL TABLE NEW TABLE Code Description Price Code Description Price 213345 9v Battery 1.92 213345 9v Battery 1.92 311452 Power Drill 34.99 254467 60W Bulb 1.47 254467 60W Bulb 1.47 309772 Mini-Ratchet Set 6.50 309772 Mini-Ratchet Set 6.50 290031 Flat Screwdriver 8.45 256568 Halogen Light 12.99 290031 Flat Screwdriver 8.45 CS 111.01 Apply SELECT with Price < 10.00 Chapter 10 – Database Structures Page 128 Relational Operation: PROJECT The PROJECT operation limits the scope of the database to specific attributes. ORIGINAL TABLE NEW TABLE Code Description Price Code Price 213345 9v Battery 1.92 213345 1.92 311452 Power Drill 34.99 311452 34.99 254467 60W Bulb 1.47 254467 1.47 309772 Mini-Ratchet Set 6.50 309772 6.50 256568 Halogen Light 12.99 256568 12.99 290031 Flat Screwdriver 8.45 290031 8.45 CS 111.01 Apply PROJECT with Code & Price Chapter 10 – Database Structures Page 129 Relational Operation: JOIN The JOIN operation combines multiple tables that have common attributes. Table: CUSTOMER Table: SALESREP CusNo CusName CusZip RepNo RepID RepPhone 1132445 Walker 62449 231 125 6182439887 1321242 Rodriguez 62025 125 167 6183426778 1657399 Vanloo 62363 231 231 6182431124 1312243 Rakowski 62294 167 333 3145267759 1542311 Smithson 62025 421 1217782 Adares 62650 125 Apply JOIN with CUSTOMER.RepNo = SALESREP.RepID CS 111.01 NEW TABLE CusNo CusName CusZip RepNo RepPhone 1132445 Walker 62449 231 6182431124 1321242 Rodriguez 62025 125 6182439887 1657399 Vanloo 62363 231 6182431124 1312243 Rakowski 62294 167 6183426778 1217782 Adares 62650 125 6182439887 Chapter 10 – Database Structures Page 130 Concurrency Control A potential problem with database systems that allow multiple access points is the loss of data integrity. Transaction #1: Withdraw $400 Transaction #2: Deposit $200 Get balance (result $500) Stored Bank Balance $500 Get balance (result: $500) Subtract $400 from $500 $500 $500 Add $200 to $500 Store $100 difference $500 $100 Store $700 sum $700 Special database locking mechanisms must be implemented to avoid such difficulties with interwoven transactions. CS 111.01 Chapter 10 – Database Structures Page 131 Databases & Privacy The proliferation of information on database systems poses a potential threat to the privacy of people about whom the data refers. Example: Medical Databases Advantages: Disadvantages: • Reduction of paperwork •Fewer false insurance claims • Facilitates disease tracking • Immediate access in emergency • Cost-effective ID of treatment • More secure than paper records • Employer access might cost jobs • “High risk” insurance increases • Unsolicited advertisements • Fear inhibits candid disclosure • Inaccuracies are spread easily • Dr./patient confidentiality loss CS 111.01 Chapter 10 – Database Structures Page 132