Part 2 Database ConceptsCase Study 1 Small Bank Master DiskNOWAcct FileCDFilePassbookFileLoansFileSafe DepositFileCredit/DebitPostingInterestPostingMonthlyStatementsMaintenanceInterestPostingQuarterlyStatementsNotificationMaturityMaintenanceCredit/DebitPostingInterestPostingQuarterlyStatementsMaintenanceCreditPostingI.D. LatePaymentsYearlySummaryMaintenanceRenewalNoticeMaintenance Ref: Jan Harrington, Relational Database Management for Microcomputers Copyright © 1971-2004 Thomas P. Sturm Database Concepts Part 2, Page 2Problems with a File System • redundant data • separate maintenance • varying formats/contents/standards • application program tied to data • little overall planning Problems with handling • address change • new account/credit report • field length change Copyright © 1971-2004 Thomas P. Sturm Database Concepts Part 2, Page 3Database Approach Small Bank Master DiskCredit/DebitPostingInterestPostingMonthlyStatementsMaintenanceInterestPostingQuarterlyStatementsNotificationMaturityMaintenanceCredit/DebitPostingInterestPostingQuarterlyStatementsMaintenanceCreditPostingI.D. LatePaymentsYearlySummaryMaintenanceRenewalNoticeMaintenancePooled, Shared DatabaseNOW Accounts Programs Certificates of Deposit ProgramsPassbook Savings Programs Loans ProgramsSafe-Deposit Box Programs Ref: Jan Harrington, Relational Database Management for Microcomputers Copyright © 1971-2004 Thomas P. Sturm Database Concepts Part 2, Page 4Why Database? Advantages over Paper-based Methods • Compactness • Speed • Less drudgery • Currency • Centralized control Advantages of Centralized Control (via a Database Administrator - DBA) • Redundancy can be reduced • Inconsistency can be avoided • The data can be shared • Standards can be enforced • Security restrictions can be applied • Integrity can be maintained • Conflicting requirements can be balanced Ref: C. J. Date, An Introduction to Database Systems Copyright © 1971-2004 Thomas P. Sturm Database Concepts Part 2, Page 5Advantages Disadvantages Economy of scale Size Getting more information from same amount of dataComplexity Data sharing Cost Balancing conflicting requirements Requirements in conflict Enforcing standards Having to adhere to standards Controlled redundancy Higher impact of a failureSecurity Recovery more difficult Consistency Integrity Flexibility Responsiveness Increased organizational productivity Improved program maintenance Data independence Copyright © 1971-2004 Thomas P. Sturm Database Concepts Part 2, Page 6Data Dependence Data Dependence: Application dictates • organization in secondary storage • access methods Knowledge of that organization and access method • built into application logic • built into executable code Impossible to change the storage structure or access methods without affecting the application Why Data Dependence is Undesirable: Different applications will need different views of the data The database administrator must have the freedom to change the storage structure or access methods Copyright © 1971-2004 Thomas P. Sturm Database Concepts Part 2, Page 7Stored Field, Record, File Stored Field: • smallest named unit of stored data • database will contain many occurrences or instances • classified into various types Stored Record: • named collection of related stored fields • record occurrence consists of a group of related field occurrences • also classified into various types by the variation in fields contained in the record Stored File: • named collection of all occurrences of one type of record Copyright © 1971-2004 Thomas P. Sturm Database Concepts Part 2, Page 8Data Independence The Immunity of Applications to Changes in Storage Structure and Access Methods Must Protect an Application from Variations in: • representation of numeric data (binary, BCD) • representation of character data (ASCII, EBCDIC) • units for numeric data (dollars, cents) • data coding (red, blue, green; 1, 2, 3; 01, 10, 11) • data materialization (for derived data) • structure of stored records (grouping fields into records) • structure of stored files (single volume, volume set, etc.) Copyright © 1971-2004 Thomas P. Sturm Database Concepts Part 2, Page 9Sample Database - Employees Overview of Content: The database contains organization, budget, and scheduling information for a software group that is developing an academic information system Entities: Employees - who have • a name • a job title • a manager who, in turn, is an employee • a hire date • an hourly billing rate • (possibly) a dollar annual bonus amount • membership in a department, which in turn has a name, location, and budget • a set of assigned tasks on projects • each task by each employee on each project has a time estimate in hours • each project has a name, description, budget, and due date Copyright © 1971-2004 Thomas P. Sturm Database Concepts Part 2, Page 10Sample Database - Departments and Projects Entities (continued) Departments - which have • a department number • a department name • a department location (room number) • an annual dollar budget • employees, who in turn have a name, job description, manager, hire date, hourly rate annual bonus, and a set of assigned tasks (as described above) Projects - which have • a project name • a project description • a project budget • a project due date • a set of tasks, each of which is to be performed by one or more employees (who in turn have a name, job description, manager, hire date, ...) with a time estimate for each employee for each task Copyright © 1971-2004 Thomas P. Sturm Database Concepts Part 2, Page 11Sample Database - Tasks Entities (continued) Tasks - each of which have • the name of the employee working on the task (who in turn has name, job description, ...) • the name of the project that the task is related to (which in turn has name, description, ...) • the name of the task being performed • the time estimate (in hours) of how long an employee will work on a particular type of task for a particular project Copyright © 1971-2004 Thomas P. Sturm Database Concepts Part 2, Page 12Sample Data (stated in relational form) Employees - (Table name emp) Ename Job Mgr Hired Rate Bonus DeptNo allen programmer barger