Скачать презентацию Database Management Systems Chapter 1 Introduction Jerry Post Скачать презентацию Database Management Systems Chapter 1 Introduction Jerry Post

7a3e1db08d2628948e8c3b6f16917949.ppt

  • Количество слайдов: 27

Database Management Systems Chapter 1 Introduction Jerry Post Copyright © 2001 1 Database Management Systems Chapter 1 Introduction Jerry Post Copyright © 2001 1

Goal: Build a Business Application Program SQL Design Tools: Database Design SQL (queries) Programming Goal: Build a Business Application Program SQL Design Tools: Database Design SQL (queries) Programming Design D A T A B A S E Best: Spend your time on design and SQL. Worst: Compensate for poor design and limited SQL with programming. 2

D A T A B A S E DBMS: Database Management System ² Database D A T A B A S E DBMS: Database Management System ² Database ª A collection of data stored in a standardized format, designed to be shared by multiple users. ² Database Management System ª Software that defines a database, stores the data, supports a query language, produces reports, and creates data entry screens. 3

D A T A B A S E Drawbacks of old File methods ² D A T A B A S E Drawbacks of old File methods ² Uncontrolled Duplication ª Wastes space ª Hard to update all files ² Inconsistent data ² Inflexibility ª Hard to change data ª Hard to change programs ² Limited data sharing ² Poor enforcement of standards ² Poor programmer productivity ² Excessive program maintenance 4

D A T A B A S E File Method Problems ² Files defined D A T A B A S E File Method Problems ² Files defined in program ª Cannot read file without definition ª Hard to find definition ª Every time you alter file, you must rewrite code ª Change in a program/file will crash other code ª Cannot tell which programs use each file ² Multiuser problems ª Concurrency ª Security © Access © Backup & Restore ª Efficiency © Indexes © Programmer talent Ÿ System Ÿ Application 5

D A T A B A S E Old File Method/3 GL Programs Payroll D A T A B A S E Old File Method/3 GL Programs Payroll Data Definition File 1 … File 2 … Benefits Data Definition File A File 2 File C … Files Pay History Benefits Employee Choices 6

D A T A B A S E Example of File Method v DBMS D A T A B A S E Example of File Method v DBMS COBOL File Division 01 Employees 02 ID 02 Name 02 Address 02 Cell Phone 01 Department 02 ID 02. . . More programs File Division 01 Employees. . . Employee File 112 Davy Jones 999 Elm Street. . . 113 Peter Smith 101 Oak St. . . ² Add to file (e. g. Cell phone) ª Write code to copy employee file and add empty cell phone slot. ª Find all programs that use employee file. © Modify file definitions. © Modify reports (as needed) © Recompile, fix new bugs. ² Easier: Keep two employee files? 7

D A T A B A S E Advantages of Database Approach ² Minimal D A T A B A S E Advantages of Database Approach ² Minimal data redundancy. ² Data consistency. ² Integration of data. ² Sharing of data. ² Enforcement of standards. ² Ease of application development. ² Uniform security, privacy and integrity. ² Data independence. 8

D A T A B A S E Database Management Approach ² Data is D A T A B A S E Database Management Approach ² Data is most important ª Data defined first ª Standard format ² Access DB through DBMS All Data ª Queries, Reports, Forms ª Application Programs ª 3 GL Interface DBMS ² Data independence ª Change data definition without changing code ª Alter code without changing data ª Move/split data without changing code Program 1 Queries Program 2 Reports 9

D A T A B A S E Modifying Data with DBMS ² Add D A T A B A S E Modifying Data with DBMS ² Add cell number to employee table ª Open table definition ª Add data element ª If desired, modify reports © Use report writer © No programming ² Existing reports, queries, code will all run as before with no changes. Field Name Data Type Description Employee. ID Taxpayer. ID Last. Name First. Name. . . Phone. . . Number Text Autonumber. . Federal ID Cell. Phone Text Cellular. . . 10

D A T A B A S E DBMS Features/Components ² Database engine ª D A T A B A S E DBMS Features/Components ² Database engine ª Storage ª Retrieval ª Update ² Query Processor ² Data dictionary ² Utilities ² Security ² Report writer ² Forms generator (input screens) ² Application generator ² Communications ² 3 GL Interface 11

D A T A B A S E DBMS Engine, Security, Utilities Product Item. D A T A B A S E DBMS Engine, Security, Utilities Product Item. ID Description Order 887 Dog food Order. ID ODate Customer 946 Cat food 9874 3 -3 -97 Customer. ID Name 9888 3 -9 -97 1195 Jones 2355 Rojas Data Tables Product Customer Item. ID Integer, Unique Customer. ID Integer, Unique Description Text, 100 char Name Text, 50 char Database Engine Data Dictionary User Identification Access Rights Security Concurrency and Lock Manager Backup and Recovery Utilities Administration 12

D A T A B A S E Database Tables (Access) 13 D A T A B A S E Database Tables (Access) 13

D A T A B A S E Database Tables (Oracle) 14 D A T A B A S E Database Tables (Oracle) 14

D A T A B A S E DBMS Query Processor All Database Engine D A T A B A S E DBMS Query Processor All Database Engine Data Dictionary Query Processor 15

D A T A B A S E DBMS Report Writer All Database Engine D A T A B A S E DBMS Report Writer All Database Engine Data Dictionary Query Processor Report Writer Report Format and Query 16

D A T A B A S E Report Writer (Oracle) 17 D A T A B A S E Report Writer (Oracle) 17

D A T A B A S E DBMS Input Forms All Database Engine D A T A B A S E DBMS Input Forms All Database Engine Data Dictionary Query Processor Form Builder Input Form Design 18

D A T A B A S E DBMS Components All Data Communication Network D A T A B A S E DBMS Components All Data Communication Network Database Engine Data Dictionary Security 3 GL Connector Query Processor Form Report Builder Writer Application Generator Program 19

D A T A B A S E Examples of Commercial Systems ² Oracle D A T A B A S E Examples of Commercial Systems ² Oracle ² Ingres ² Informix (Unix) ² DB 2, SQL/DS (IBM) ² Access (Microsoft) ² SQL Server (Microsoft +) ² Many older (Focus, IMS, . . . ) ² Many limited PC (d. BASE, Paradox, …) 20

D A T A B A S E Hierarchical Database Customers Customer Order Items D A T A B A S E Hierarchical Database Customers Customer Order Items Ordered Orders Item Description 998 Dog Food 764 Cat Food Quantity 12 11 To retrieve data, you must start at the top (customer). When you retrieve a customer, you retrieve all nested data. 21

D A T A B A S E Network Database Entry point Customer Order D A T A B A S E Network Database Entry point Customer Order Items Ordered Items Entry point 22

D A T A B A S E Relational Database Customer(Customer. ID, Name, … D A T A B A S E Relational Database Customer(Customer. ID, Name, … Order(Order. ID, Customer. ID, Order. Date, … Items. Ordered(Order. ID, Item. ID, Quantity, … Items(Item. ID, Description, Price, … 23

D A T A B A S E Object-Oriented DBMS Order. ID Customer. ID D A T A B A S E Object-Oriented DBMS Order. ID Customer. ID … New. Order Delete. Order … Order. Item Order. ID Item. ID … Order. Item Drop. Order. Item … Customer. ID Name … Add Customer Drop Customer Change Address Item Government Customer Commercial Contact. Name Customer Contact. Phone Contact. Name Discount, … Contact. Phone … New. Contact Item. ID Description … New Item Sell Item Buy Item … 24

D A T A B A S E Why don’t all developers use a D A T A B A S E Why don’t all developers use a DBMS? ² Most new projects (in last 5 years) do use a DBMS ² Need specialized personnel ª Programmers ª Designers/Analysts ª Database administrators ² Need to define data for organization ² Cost ª PC: ª Large: $400 - $2000 $100, 000 + 25

D A T A B A S E Application development ² Modeling data – D A T A B A S E Application development ² Modeling data – ch 2 and 3 ² User interface design (forms and reports) – ch 4, 5, 6. ² Operational design – ch 7 and 8. ² DB tuning – ch 9. ² DB admin and security – ch 10. ² Distributed DB – ch 11. 26

D A T A B A S E Building the Right System: Feasibility ² D A T A B A S E Building the Right System: Feasibility ² Costs ª Up-front/one-time © Software ($ millions !) © Hardware © Communications © Data conversion © Studies and Design © Training ª On-going costs © Personnel © Software upgrades © Supplies © Support © Software & Hardware maintenance Easy to estimate ² Benefits ª Cost Savings © Software maintenance © Fewer errors © Less data maintenance © Less user training ª Increased Value © Better access to data © Better decisions © Better communication © More timely reports © Faster reaction to change © New products & services ª Strategic Advantages © Lock out competitors Hard to value 27