L3 Database Management

1. L3Database Management Santa R. Susarapu Ph.D. Student Virginia Commonwealth University Database Management

2. Objectives • Understand why databases are important to modern organizations • Understand how databases work • Demonstration of Database Creation using Oracle/SQL*Plus • Understand how organizations can maximize their strategic potential with databases Database Management

3. Database Management for Strategic Advantage • Use databases to: • Create a book • Track book sales • Set salaries and wages • Pay employees • Database – a collection of related attributes about entities and organized in a way to facilitate data searches • Entity = Table • Record = Column in a Table Database Management

4. Database Management

5. Database Management for Strategic Advantage • The Database Approach: Foundational Concepts • DBMS is a software to create, store, organize, and retrieve data from a single database or several databases • Example • Microsoft Access • Oracle Database Management

6. Database Management for Strategic Advantage • Traditional way to deal with electronic data manipulation • File Processing Approach • Each department will have a separate database • Decentralized approach • Difficult to modify Database Management

7. Database Management for Strategic Advantage • Database Approach - Centralized Approach • Advantages • 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 Database Management

8. Database Management for Strategic Advantage • Database Approach – Disadvantages • Ownership conflict • Backup and recovery issues • Complex management • Specialized personnel & training • Conversion costs Database Management

9. Database Management for Strategic Advantage • Effective Management of Databases • The database administrator (DBA) : • Works with programmers and analysts to design and implement the database • Works with users and managers to establish database policies • Implements security features and establishes database permissions Database Management

10. Key Database Activities • Entering Data • Online forms • Preprinted forms • Telephone conversations • Querying Data – Retrieval technique • Structured Query Language (SQL) • Query by example (QBE) • Demonstration using MS Access Database Database Management

11. Key Database Activities • Creating Database Reports • Report – a compilation of data that is organized and produced in printed format • Report Generators • Demonstration using MS Access Database Database Management

12. Key Database Activities • Database Design • Must be organized • Few or no redundancies • Data model – a map of entity relationships • Keys • Primary key • Combination primary key • Foreign key Database Management

13. Database Management

14. 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 • Example: Mountain Animals ERD • Handout Database Management

15. Key Database Activities • Database Associations • One-to-one relationship • One-to-many relationship • Many-to-many relationship Database Management

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 Database Management

17. Key Database Activities • Normalization • A technique for making complex databases more efficient and more easily handled by the DBMS • 1st Normal Form (1NF) • 2nd Normal Form (2NF) • 3rd Normal Form (3NF) • Boyce-Codd Normal Form (BCNF) • Eliminates data redundancy and modification anomalies Database Management

18. Database Management

19. Database Management

20. Key Database Activities • Data Dictionary a.k.a. metadata • 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 Database Management

21. Creation of Databases using Oracle/SQL*Plus • Oracle Enterprise Manager • SLQ*Plus • iSQL*Plus Database Management

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

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

24. 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 Database Management

25. 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 Database Management

26. 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 Database Management

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

28. 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 Database Management

29. 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 Database Management