Concepts of Database Management, Fifth Edition

1. Concepts of Database Management, Fifth Edition Chapter 1: Introduction to Database Management

2. Objectives • Why study database management? • Introduce Premiere Products, the company that is used as the basis for many of the examples throughout the text • Introduce basic database terminology • Describe database management systems • Explain the advantages and disadvantages of database processing • Introduce Henry Books, the company that is used in the case that runs throughout the text

3. Why manage data? • Changing view of data • Higher costs of lost data • Encouragement of “team problem-solving” • Flexible reporting • Integrating it into decision-making better

4. Background Info • DBMS software $25B/year industry • Networked DBMS growing fastest • DB research underpins • communication systems • Enterprise applications • multimedia • Internet • scientific applications

5. Data Management Tasks Common tasks for flat files and databases are: • designing the file or database structure • entering the data • updating data by adding, changing, or deleting • sorting the data • searching through the data for a record or group of records • obtaining screen or printed output

6. Approaches to Data Management include: • Custom Program Approach • File Processing System Approach • Database Management System Approach

7. Contrasting Database and File System Designs

8. Problems associated with file processing systems • Application/Program dependence • Data is separate and isolated • Data reduplication • Multiple formats; hard to share data across applications.

9. Operating System Database Management System Application Programs Database Management Databases Data Dictionary Database Management Systems

10. Disadvantages of DBMS Approach • Cost • Size • Complexity • Additional Hardware Requirements • Higher Impact of Failure • Recovery more difficult

11. External Databases Database Server End User Workstation Distributed Databases Operational Databases End User Databases Data Warehouse Databases Analytical Databases Major Types of Databases

12. Database Management Systems • Program(s) through which users interact with database • Popular DBMSs include • Access • Oracle • DB2 • SQL Server • Premiere Products decides to use Access

13. DBMS Classifications • Platform • Stand alone • Network • Mainframe • Organizational Level • `Individual • Workgroup • Enterprise-wide (ex. SAP use of Oracle) • Data Model Supported • Hierarchical • Network • Relational • Object-Oriented

14. Database Models • Hierarchical • Network • Relational • Object-oriented details follow

15. Employee Record 1 Employee Record 2 Employee Record 3 Employee Record 4 Name SS Salary Name SS Salary Name SS Salary Name SS Salary Data Data Data Data Data Data Data Data Data Data Data Data Logical Data Elements Personnel Database Payroll File Benefits File

16. Database Structures Network Structure Hierarchical Structure Relational Structure Dept Empno Dept A 1 A B 2 B C 3 C

17. Premiere Products • Distributor of appliances, house wares, and sporting goods • Uses spreadsheet software to maintain important data • Recent growth has made spreadsheet approach problematic

18. Figure 1.1: Sample Orders Spreadsheet

19. Problems Using Spreadsheet • Redundancy • Duplication of data or the storing of the same data in more than one place • Occurs when the same information is stored in more than one place • Difficulty accessing data • Limited security • Size limitations

20. Premiere Products Required Information • Sales Reps • Sales rep number, last name, first name, address, total commission, commission rate • Customers • Customer number, name, address, current balance, credit limit, customer sales rep • Parts Inventory • Part number, description, number units on hand, item class, warehouse number, unit price

21. Figure 1.2: Premiere Products Sample Order

22. Premiere Products Customer Order • Order • Order number, order date, customer number • Order line • Order number, part number, number units ordered, unit price • Overall order total • Not stored since it can be calculated

23. Database Background • Database • Structure that can store information about • Multiple types of entities • Attributes of those entities • Relationships among entities • Entity • Person, place, thing, or event • Premiere Products has sales reps, customers, orders, and parts

24. Database Background (con’t) • Attribute • Property of an entity • Customer has name, street, city, et cetera • May also be called a field or column

25. Figure 1.3: Entities and Attributes

26. Database Background (con’t.) • Relationship • Association between entities • One-to-many relationship - rep is related to many customers • Customer is related to a single rep • Data file • File used to store data • Computer counterpart to ordinary paper file

27. Figure 1.4: One-to-Many Relationship

28. Figure 1.5: Rep and Customer Tables

29. Figure 1.5: Orders and OrderLine Tables (con’t.)

30. Figure 1.5: Part Table (con’t.)

31. Figure 1.6: Alternative Orders Table

32. Entity-relationship Diagram • Visual way to represent a database • Rectangles represent entities • Lines represent relationships between connected entities

33. Figure 1.7: E-R Diagram

34. Figure 1.8 and 1.9: Using DBMSs in Different Ways

35. Building a Database • Database design determines the structure of a database • Design entered into DBMS during construction • Tables – stores data • Forms – screen objects used to maintain, view, and print from a database • Reports – provides formatted output • Switchboards – a set of special forms used to provide controlled access to the data, forms, report and other objects in a database

36. Figures 1.10 and 1.11: Part and Order Forms

37. Figure 1.12: Parts Report

38. Figure 1.13: Main Switchboard

39. Figure 1.14: Main Data Switchboard

40. Figure 1.15: Advantages of Database Processing

41. Figure 1.16: Disadvantages of Database Processing

42. Introduction to Henry Books Database Case • Book store chain operated by Ray Henry • Henry decided to use database to gather and store information on: • Branches • Publishers • Authors • Books

43. Figure 1.17: Sample Branch Data

44. Figure 1.17: Sample Publisher Data (con’t.)

45. Figure 1.18: Sample Author Data

46. Figure 1.19: Sample Book Data

47. Figure 1.20: Wrote Table Relates Authors to Books

48. Figure 1.20: Inventory Table Relates Branches to Books (con’t.)

49. Summary • Nondatabase approaches to management have problems with replication, redundancy, sharing, limited security, and size limitations • Entity - a person, place, object, event, or idea for which you want to store and process data • Attribute, field, or column - a characteristic or property of an entity • Relationship - an association between entities

50. Summary • One-to-many relationship - exists when • Each occurrence of the first entity is related to many occurrences of the second entity • Each occurrence of the second entity is related to only one occurrence of the first entity • Database is a structure that can store information about multiple types of entities • An entity-relationship (E-R) diagram represents a database pictorially • Database management system (DBMS) - a program, or a collection of programs, through which users interact with a database