Chapter 3 Database Management

1. Chapter 3Database Management Dr. Hassan Ismail

2. Chapter 3: ObjectivesFrom text book • Understand why databases are important to modern organizations • Understand how databases work • Understand how organizations can maximize their strategic potential with databases

3. Database Management for Strategic Advantage • Database – a collection of related data organized in a way to facilitate data searches • Publisher can use databases to: • Create a book • Track book sales • Set salaries and wages • Pay employees • DB management systems allow organization to retrieve, store and analyze information easily .

4. Traditional File Environment accounting Each business function developed its files file3 file4 file1 file2 Finance file3 file1 file2 Sales & Marketing file1 file2

5. Traditional File Environment • Field: title – column • Record: a group of fields – entity • File: a group of records – table • Key: unique field to identify record • Database: a group of files + relationships

6. Problems • Data Redundancy and Confusion • Program-Data Dependence • Lack of Flexibility • Poor Security • Lack of Data-Sharing and Availability

7. DataBase Management System DBMS is a software that permits an organization to centralize data, manage them, and access by application programs 3 components 1 Data Definition Language 2 Data Manipulation Language 3 Data Dictionary Language that end users and programmers use to manipulate data (SQL) Structured Query Language For storing and organizing information about the data maintained in DB Defines each data element as it appears in the database

8. Advantages of the Database Approach • Easy to change business needs • Single copy of data stored • Avoid redundancy => more data is more consistent • Easy to deploy and control data from centralized system • Data standards make it easier to build and modify applications. • Centralized system make it easier enforce rules for data creation/modification/naming/deletion. • Centralized control, no redundancy, enhance data quality • Easy to access from inside or outside organization • Information changed in the central DB is replicated seamlessly throughout all applications. • Program-data independence • Minimal data redundancy • Improved data consistency • Improved data sharing • Increased productivity of application development • Enforcement of standards • Improved data quality • Improved data accessibility • Reduced program maintenance

9. …Foundation Concepts • DBMS : is used to interact with the databases, DBMS is a software application with which you create, store organize, and retrieve data from a single databases • Example: MS Access is an example of DBMS . • In DBMS, the individual Database is a collection of related attributes about entities: • Entity: is collection of data that belong to the same domain, or same meaning like people/class/ anima/ university etc… • Attribute (or Field) is an individual information that describe the entity. Entity is like table, where each row is a record and each column is an attribute.

10. Effective Management of DB • The database administrator (DBA) is responsible of the development and management of the organization’s databases, he will: • Work with programmers and system analysts to design and implement the database • Work with users and managers to establish database policies • Implement security features and establishes database permissions

11. Key Database Activities • Entering and Querying Data • Form: GUI which presents the information to the user in an intuitive way so that the user can easily see and enter the data. • Structured Query Language (SQL): is the most common language used to interface with databases • Query by example (QBE): it enables us to fill out a grid, or template, in order to construct a sample data we would like to see.

12. Key Database Activities • Creating Database Reports • Report – a compilation of data that is organized and produced in printed format • Example: quarterly sales report for restaurant. • Report Generators: retrieves data from database and manipulate and display it in a useful format. • Design Database • Must be organized • Few or no redundancies • Data model – a map of entity relationships • Keys • Primary key - example Student ID • Combination primary key - example Student ID + Course ID + Section + Term • Secondary key- example Major: Used to identify one or more records within a table that share a common value.

14. Key Database Activities • Database Associations • One-to-one (teams to stadiums) • One-to-many (player to team) • Many-to-many (players to games)

15. Key Database Activities • Entity-Relationship Diagramming (ERD) • Commonly used when designing databases • One draws entities (tables) as boxes and lines between entities to show relationships

16. Key Database Activities • The Relational Model of Databases • Entities linked by a common key field • Records = rows • Fields = columns • Other models exist • Hierarchical • Network • Object-oriented model

17. Key Database Activities • Normalization • A technique for making complex databases more efficient and more easily handled by the DBMS • Eliminates data redundancy

20. Key Database Activities • Data Dictionary • A document that explains each piece of information in the database • Field name • Data type • Numeric, text, date/time • Useful for sorting and allocating storage • Is this field a key field? • Business rules • Update authority • Valid data values

21. How Organizations Get theMost from Their Data • Linking Web Sites to Databases • Example: Amazon • 2.5 million titles • Managing online data effectively

22. How Organizations Get the Most from Their Data • Data Mining • A method for better understanding data • Information on customers, products, markets, etc. • Drill down: from summary to more detailed data • Sort and extract information • Trends, correlations, forecasting, statistics

23. How Organizations Get the Most from Their Data • Data Mining • Online Transaction Processing (OLTP) • Immediate automated responses to user requests • Multiple concurrent transactions • A big part of interactive Internet e-commerce

24. How Organizations Get the Most from Their Data • Data Mining • Online Analytical Processing (OLAP) • Graphical software tools that provide complex analysis of data stored in a database • Drills down to deeper levels of consolidation • Time series and trend analysis • “What if” and “why” questions

25. How Organizations Get the Most from Their Data • Data Mining • Merging Transaction and Analytical Processing • Real-time OLAP diminishes performance because the database must be “locked” during execution time • Solution: replicate transactions on a 2nd database server

26. How Organizations Get the Most from Their Data • Data Mining • Merging Transaction and Analytical Processing • Operational Systems • Interact with customers and run a business in real time • Examples: Order processing, reservation systems • Informational Systems • Support decision making based on stable point-in-time or historical data

27. How Organizations Get the Most from Their Data • Data Warehousing • Integrating multiple large databases into a single repository • Queries, analysis, and processing • Purpose: put key business information into the hands of decision makers • Cost: millions

28. How Organizations Get the Most from Their Data • Data Marts • Instead of one large data warehouse, many organizations create multiple data marts • Each contains a subset of the data • Example: finance, inventory, personnel • Each data mart is customized for particular DSS applications • Cost: typically less than $1 million