Database Management Systems Chapter 1 Introduction Copyright G

Database Management Systems Chapter 1 Introduction Copyright: G. Post, 2002; John Gerdes, Jr, 2002

D A T A B A S E File Systems ² A file system determines the organization and handling of files ² On a DOS computer (and many main-frame computers) the file system organizes files into directories. 2

D A T A B A S E Why is it important to have a good file system? 3

D A T A B A S E Why is it important to have a good file system? ² Information cannot be used effectively if stored in an unorganized, inflexible manner. ² Without proper file management it may be difficult and even impossible to extract information. [ 4]

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 3 File 2 File 4 … Files Pay History Benefits Employee Choices 5

D A T A B A S E Drawbacks of Old File Methods? 6

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. [ 7]

D A T A B A S E File / Data Method Problems ² File defined in programs ª Cannot read file without its 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 © Deadlock © Indexes © Programmer talent Ÿ System Ÿ Application. 8

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. 9

D A T A B A S E Advantages of Database Approach? 10

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

D A T A B A S E Example of File Method vs 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. . . ² Modify file (e. g. Add 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 Alternative: Keep two employee files. 12

D A T A B A S E Modifying Data with DBMS ² Add cell phone 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. . . 13

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

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

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. 16

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 17

D A T A B A S E Database Tables (MS Access) 18

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

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

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

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

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

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 24

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, …). 25

D A T A B A S E Types of Database System ² Hierarchical ² Network ² Relational ² Object Oriented. 26

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. 27

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

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, … 29

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. ID Description … New Item Sell Item Buy Item … Government Customer Commercial Contact. Name Customer Contact. Phone Contact. Name Discount, … Contact. Phone … New. Contact • Data structure similar to Relational DB • Addition of Methods 30

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

D A T A B A S E How do you sell a DBMS approach to your Boss? 32

D A T A B A S E How do you sell a DBMS approach? ² ² ² ² Applications change a lot, but use the same data Need for ad hoc questions and queries Need for security Need to reduce development times Need shared data Improve quality of data Enable users to do more of their own development. [ 33]