Database Management Systems Chapter 1 Introduction Jerry Post

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 ª 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 Application Development tasks Feasibility Identify scope, costs, and schedule Analysis Gather information from users Design Define tables, relationships, forms, reports Development Create forms, reports, and help; test Implementation Transfer data, install, train, review time 4

D A T A B A S E DBMS Application Design 1. Identify business rules. 2. Define tables and relationships. 3. Create input forms and reports. 4. Combine as applications for users. 5

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 6

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 7

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

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

D A T A B A S E DBMS Query Processor All Database Engine Data Dictionary Query Processor Animal. ID Name Category Breed Category Count. Of. Animal. ID Dog Cat Field Category Animal. ID Table Animal Totals Group By Count Sort Criteria Or Descending 100 47 Bird 15 Fish 14 Reptile 6 Mammal 6 Spider 3 10

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

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

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

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 14

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

D A T A B A S E Database Management Approach ² Data is most important ª Data defined first ª Standard format ² Access 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 16

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

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 18

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 19

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 20

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? 21

D A T A B A S E Examples of Commercial Systems ² Oracle ² Informix (Unix) ² DB 2, SQL/DS (IBM) ² Access (Microsoft) ² SQL Server (Microsoft +) ² Many older (Focus, IMS, . . . ) ² my. SQL ² Progres. SQL 22

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

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

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

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 … 26

D A T A B A S E Base Data Types ² Numbers ª Integers ª Reals ² Text ª Length ª International ² Date/Time ² Images ª Bitmap ª Vector ² Sound ª Samples ª MIDI ² Video Input Numbers, Text, and Dates Images Sound Process Output 000001100 000001000 --------000010100 12 + 8 = 20 20 00100000000 0100000001001 011000011011 0111111001111111011111100011111 pitch, volume 8 9 20 7 8 19 5 6 15 time 000001000 000001001 000010100. . . Video 00101010111 0010111 11010101010 0101010 11110100011 00101011011 00101010111 1101010101010 11110100011 00101011011 27

D A T A B A S E Objects ² Object Definition-encapsulation. ª Object Name ª Properties ª Methods ² Most existing DBMS do not handle inheritance. ª Combine into one table. ª Use multiple tables and link by primary key. © More efficient. © Need to add rows to many tables. Class name Properties Methods Customer. ID Address Phone Add. Customer Drop. Customer Inheritance Commercial Contact Volume. Discount Government Contact Balance. Due Compute. Discount Bill. Late. Fees Add. Customer Polymorphism 28

D A T A B A S E Objects in a Relational Database ² Separate inherited classes. ² Link by primary key. ² Adding a new customer requires new rows in each table. ² Definitely need cascade delete. Customer. ID Address Phone Commercial. Customer. ID Contact Volume. Discount Government. Customer. ID Contact Balance. Due 29

D A T A B A S E OO Difficulties: Methods IBM Server Unix Server Database Object Personal Computer Database Object Customer Method: Add New Customer Program code Application Customer Name Address Phone How can a method run on different computers? Different processors use different code. Possibility: Java 30

D A T A B A S E SQL 99: OO Features ² Abstract data type ª User defined data types. ª Equality and ordering functions. ª Encapsulation: Public, Private, Protected. ª Inheritance. ² Sub-tables that inherit all columns from another table. ² Persistent Stored Modules (Programming Language). ª Create methods. ª SQL and extensions. ª External language. ² User defined operators. ² Triggers for events. ² External language support ª Call-Level Interface (CLI) © Direct access to DBMS ª Embedded SQL © SQL commands in an external language. 31

D A T A B A S E Abstract Data Types Procedure: Draw. Region { Find region components. SQL: Select … For each component { Fetch Map. Line Set line attributes Map. Line. Draw } } Geo. Point Latitude Longitude Altitude Geo. Line Number. Of. Points List. Of. Geo. Points 32

D A T A B A S E SQL 99 Sub-Tables CREATE SET TABLE Customer ( Customer. ID INTEGER, Address VARCHAR, Phone CHAR(15) ) Customer. ID Address Phone Inherits columns CREATE SET TABLE Commercial. Customer from Customer. ( Contact VARCHAR, Volume. Discount NUMERIC(5, 2) Commercial. Customer ) Contact UNDER Customer; Volume. Discount 33

D A T A B A S E SQL 99: Programming Database Data Types Tables, … Persistent Stored Modules SQL Extended SQL code External language code External Programs Embedded SQL Call-Level Interface CURSOR … SELECT … FETCH … 34

D A T A B A S E OODBMS Vendors Gem. Stone Systems, Inc. Hewlett-Packard, Inc. (Open. ODB) IBEX Corporation, SA. Illustra (Informix, Inc. ) Matisse Software, Inc. O 2 Technology, Inc. Objectivity, Inc. Object Design, Inc. ONTOS, Inc. POET Software Corporation Uni. SQL Unisys Corporation (OSMOS) Versant Object Technology 35

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 + 36

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

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 38

D A T A B A S E Economic Feasibility: NPV =NPV(B 14, $D$7: $D$11)+$D$6 =NPV(rate, range) + starting 39

D A T A B A S E Exercise: Build a First Database Employee(Employee. ID, Last. Name, First. Name, Address, Date. Hired) 332 442 553 673 773 847 Ant Bono Cass Donovan Moon Morrison Adam Sonny Mama Michael Keith Jim 354 Elm 765 Pine 886 Oak 421 Willow 554 Cherry 676 Sandalwood 5/5/1964 8/8/1972 2/2/1985 3/3/1971 4/4/1972 5/5/1968 Client(Client. ID, Last. Name, First. Name, Balance, Employee. ID) 1101 Jones Joe 113. 42 442 2203 Smith Mary 993. 55 673 2256 Brown Laura 225. 44 332 4456 Dieter Jackie 664. 90 442 5543 Wodkoski John 984. 00 847 6673 Sanchez Paula 194. 87 773 7353 Chen Charles 487. 34 332 7775 Hagen Fritz 595. 55 673 8890 Hauer Marianne 627. 39 773 9662 Nguyen Suzie 433. 88 553 9983 Martin Mark 983. 31 847 40

D A T A B A S E Exercise: Report Ant, Adam 5/5/1964 Brown, Laura 225. 24 Chen, Charles 487. 34 712. 58 Bono, Sonny 8/8/1972 Dieter, Jackie 664. 90 Jones, Joe 114. 32 779. 22 41