Introduction to Databases—Topics

1. Introduction to Databases—Topics • Information and Data • User needs • Organization for efficient storage and retrieval • Designing Database Systems • The Relational Data Model • Databases: Past and Future

2. Information and Data • We will think about information as being organized data • Organized to be meaningful to the user • Organized to be easily accessible for storage and retrieval • Organization adds value to the data • Lowers users' costs of perceiving the meaning of the data • Lowers costs of storing and retrieving the data

3. Data to Information (cont.) • Information systems typically require a large up front investment to provide a stream of benefits or reduced costs in the future • Careful design seeks to • Provide maximum value by identifying user needs • Data • Presentation to match decision making task • Reduce operating costs • Automate tasks • Reduce user effort

4. Database Management Systems (DBMS) • Database management software manipulates and manages databases • Oracle, SQL Server, Access, MySQL, DBII, etc. • Databases contain the data to be manipulated • One software instance can control several databases • Applications communicate with the data through the database software • Multiple applications can share the same or multiple databases • Userscan (but usually shouldn’t) manipulate data directly through the database software

5. Database Management Systems (cont.) • Rich history of data storage approaches • In Relational DBMS the meaning of data is stored with the data • Data can be addressed by its name, rather than by its place in the data order • New data fields can be added without disrupting existing programs • Programs can be changed without disrupting existing data • Characteristics of the relational data model (and object oriented data models) overcome many other problems

6. Designing Database Systems • Database Analyst designs the database components to reflect the business requirements • Entities (tables) • Attributes (fields or columns) • Indices and keys • Relationships • Foreign keys • Business rules • Information Products (outputs)

7. Designing Database Systems (cont.) • System Design Goals • Stored data reflects business needs • Storage efficiency • Minimum storage space • Minimum data redundancy • Data is stored, retrieved, and updated efficiently • Data is accurate Key Design Goal

8. Designing Database Systems (cont.) • We will see that these goals conflict with each other • Differences are more pronounced between OLTP and OLAP systems • OLTP—Online Transaction Processing • OLAP—Online Analytical Processing • Management Information Systems • Business Intelligence • Data Warehousing • Design must make trade offs between goals • Watch for these themes throughout the course

9. The Relational Data Model • Data is stored in rectangular storage structures called tables • Each table stores data about only one kind of business entity • Data in one table is related to data in other tables by common fields (keys) • Rules and guidelines apply to achieve system design goals

10. Record Number Last Name First Name Address 1 165 Johnson Amelia 20 Castle Court 166 Evans Barton 12896 Viscaino Road 167 Booker Troy 110 West Gadsden Street 169 Periatt Barry 709 Royal St. 170 Norbeck Matthew PSC 303 BOX 61 171 Futch David 513 Morris Rd. 173 Tomson Alan 3324 Mills Bayou Drive 174 West Dan P. O. Box 81 176 Hintz Willis 506 Pilot Avenue 177 Gaskin Jerry 715 Sunningdale Cove Database Tables Each data element (field) is described in the database Each table containsinformation on onekind of entity Member • Each table is rectangular • Same number of columns in every row • But cells may be empty

11. Number Record Number Member Number Last Name LName First Name FName Address 1 38 165 167 Johnson Booker Amelia Donna 20 Castle Court 40 166 173 Evans Tomson Barton Mary Anne 12896 Viscaino Road 167 42 187 Booker McCusker Troy Sandra 110 West Gadsden Street 169 43 166 Periatt Neves Barry Andrea 709 Royal St. 170 44 190 Norbeck Pogge Matthew Karen PSC 303 BOX 61 171 45 191 Futch Gajewski David LuAnn 513 Morris Rd. 173 46 200 Tomson Altshuler Alan Kathi 3324 Mills Bayou Drive 174 West Dan P. O. Box 81 176 Hintz Willis 506 Pilot Avenue 177 Gaskin Jerry 715 Sunningdale Cove Table Relationships Member Data in one table isrelated to data in othertables by common fields(keys) Guest

12. Problems • What bad designdecisions can weintroduce into thisexample? • Violate design goals • Useage efficiency problems • What design decisions mightbe good for OLTP but a problem for OLAP?

13. Databases: Past and Future • Database developments have been characterized by: • New approaches to organizing and presenting data • Falling prices for powerful hardware • Networking to distribute data • Growth of the Internet to extend data beyond the organization's boundaries • New technologies for developing databases • New technologies for developing database-based application programs

14. Databases: Past and Future (cont.) • What have been effects on business practices that you have seen in your lifetimes? • What changes can you expect to see if past trends continue? • What business changes will new technological capabilities introduce? • What role do you expect to play in all of this? (And how much money do you expect to make doing it?)