1 / 89

Chapter 5 Database Processing

Chapter 5 Database Processing. Jason C. H. Chen, Ph.D. Professor of MIS School of Business Administration Gonzaga University Spokane, WA 99258 chen@jepson.gonzaga.edu. GearUp Scenarios. Chapter 5 : Database Processing

katen
Download Presentation

Chapter 5 Database Processing

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Chapter 5Database Processing Jason C. H. Chen, Ph.D. Professor of MIS School of Business Administration Gonzaga University Spokane, WA 99258 chen@jepson.gonzaga.edu

  2. GearUp Scenarios Chapter 5: Database Processing • GearUpdoes not have the easy access to data needed to analyze vendor quality and costs

  3. GearUp: “Can You Put the Data into Access?” • GearUp needs operating data to analyze for cost-cutting decisions • Need to extract and combine data from multiple systems • Will use Access to create report • In general, what does GearUp need forimproving its decisions? • Answer - Database processing

  4. Chapter Preview • Businesses of every size organize data records into collections called databases. At one extreme, small businesses use databases to keep track of customers; at the other extreme, huge corporations such as Dell and Amazon.com use databases to support complex sales, marketing, and operations activities. In between, we have businesses like FlexTime that use databases as a crucial part of their operations, but they don’t have a trained and experienced staff to manage and support the databases. To obtain answers to the one-of-a-kind queries he needs, Neil needs to be creative and adaptable in the way that he accesses and uses his database. • This chapter discusses the why, what, and how of database processing. We begin by describing the purpose of databases and then explain the important components of database systems. We then overview the process of creating a database system and summarize your role as a future user of such systems. • Users have a crucial role in the development of database applications. Specifically, the structure and content of the database depends entirely on how users view their business activity. To build the database, the developers will create a model of that view using a tool called the entity-relationship model. You need to understand how to interpret such models, because the development team might ask you to validate the correctness of such a model when building a system for your use. Finally, we describe the various database administration tasks. • This chapter focuses on database technology. Here we consider the basic components of a database and their functions. You will learn about the use of database reporting and data mining in Chapter 9.

  5. Study Questions Q1: What is the purpose of a database? Q2: What is a database? Q3: What are the components of a database application system? Q4: How do database applications make databases more useful? Q5: How are data models used for database development? Q6: How is a data model transformed into a database design? Q7: What is the users’ role in the development of databases? Q8: 2022?

  6. What is the most important computer resource on the Internet Search Engine (backend)? ?

  7. Answer: Database • You will learn the following: • 1) Database Concepts and • 2) Conceptual Database Design (Data Model)

  8. DATABASE TRENDS Linking Internal Databases to the Web Database tier User tier Server tier N

  9. 2. Request for data-based Web page 1. Request for data-based Web page 3. Data query 8. Data-based Web page 6. Retrieved data 7. Data-based Web page Web Browser Web server 4. Data query 5. Retrieved data Legend Communications Between Web browser And Web server Communications Between Web server And database server Database Server Web-Based Client/Server Database Architecture Network

  10. TWO TYPES OF DATA PROCESSING • FILE-BASED • DATA-BASED

  11. Figure: File-based Approach to Data Processing Checking Account Data Files Checking Account Programs Auto Loan Data Files Auto Loan Programs Savings Account Data Files Savings Account Programs

  12. Duplicate Data Three file processing systems at a company What is the main problem in the company’s processing systems?

  13. Disadvantages of File Processing • Program-Data Dependence • All programs maintain metadata for each file they use • Data Redundancy (Duplication of data) • Different systems/programs have separate copies of the same data • Limited Data Sharing • No centralized control of data • Lengthy Development Times • Programmers must design their own file formats • Excessive Program Maintenance • 80% of of information systems budget

  14. Questions • Question-1: How to overcome most of the problems from File-based data processing? • Answer: Data-based approach • Question-2: Is MS/Access a database? • Answer: No (Why?) • Question-3: Then, what is it? • You will learn all these concepts in the chapter.

  15. Application #1 Application #2 Application #3 Database containing centralized shared data Database Management System ???

  16. Application #1 Application #2 Application #3 Database containing centralized shared data Database Management System DBMS DBMS manages data resources like an operating system manages hardware resources

  17. Q1: What Is the Purpose of a Database? • As a database user, you play a crucial role in developing database applications for your business or organization. • Organize and keep track of things • Keep track of multiple themes • General rule: • Single theme store in a ____________ • Multiplethemes require a __________ spreadsheet database Fig 5-1 List of Student Grades, Presented in a Spreadsheet

  18. Student Data Form With Multiple Themes Fig 5-2 Student Data Shown in a Form, from a Database

  19. Q2: What is a database? These are terms you’ll need to know when working with databases. A database is a self-describing collection of integrated records A database is an organized collection of logically related data files. (different from the text) Bytes, characters of data, are grouped into columns which are also called fields. The fields are grouped into rows which are also called records. A table is a group of similar rows or records which is also called a file. Fig 5-3 Student Table (also called a file) 5-19

  20. Hierarchy of Data Elements • This figure shows the hierarchy of data elements working from the smallest at the bottom to the largest at the top. Fig 5-4 Hierarchy of Data Elements

  21. Components of a Database:Metadata Describes Structure of Database • A database is more than just a group of tables. It includes tables or files plus the relationships among rows in the tables and metadata that describes the database’s structure. Fig 5-5 Components of a Database

  22. Metadata Fig 5-7 Sample Metadata (in Access) • Metadata are data that describe data and makes databases easy to use. Metadata is always a part of a database. • The Field Name, Data Type, and Description at the top of this diagram are part of the database’s metadata. • The additional field properties at the bottom are also considered metadata. 5-22

  23. What Are Relationships Among Rows? • This diagram shows relationships among rows of different tables. • The Student Number value in the first row of the top table,1325, relates to the same Student Number value in a row in the second table. Fig. 5-6: Example of Relationships Among Rows

  24. What Are Relationships Among Rows? • This diagram shows relationships among rows of different tables. • The Student Number value in the first row of the top table,1325, relates to the same Student Number value in a row in the second table. • Both that Student Number values and that of 4867 (and 1325) in the second table relate to those in the third. Fig. 5-6: Example of Relationships Among Rows

  25. Relationship Special Terms pk • Key • A column or group of columns that identifies a unique row in a table. • Student Number is the key of the Student table. Given a value of Student Number, you can determine one and only one row in Student. Only one student has the number 1325. • Every table must have a (primary) key. • Sometimes more than one column is needed to form a unique identifier. In a table called City, for example, the key would consist of combination of columns (City, State) – form a composite key. • Email_Num is the key of Email Table. • VisitID is the key of Office_Visit Table. pk pk

  26. Relationship Special Terms pk fk • Foreign keys • These are keys of a different (foreign) table than the table in which they reside. • A relational database carries data in the form of tables and uses foreign keys to represent relationships • Relational databases • Relationships among tables are created by using foreign keys. • Relation • Formal name for a table pk pk fk What is (are) the fk in this relation?

  27. Study Questions Q1: What is the purpose of a database? Q2: What is a database? Q3: What are the components of a database application system? Q4: How do database applications make databases more useful? Q5: How are data models used for database development? Q6: How is a data model transformed into a database design? Q7: What is the users’ role in the development of databases? Q8: 2022?

  28. Q3: What Are the Components of a Database Application System? • Applications make database data more accessible and useful. • Users employ a database application that consists of forms, formatted reports, queries, and application programs. • Database management system (DBMS) processes database tables for applications. Fig 5-8 Components of a Database Application System

  29. What Is a Database Management System (DBMS)? • Database developers use the DBMS to create or modify tables, relationships, and other structures in a database. • A DBMS (database management system) program is used to create, process, and administer a database. • Popular DBMS products include: • Oracle from Oracle Corporation (World #1 Database company) • DB2 from IBM • Access from Microsoft (for personal computers) • SQL Server from Microsoft (for large computer systems) • MySQL, an open-source (it was acquired by Oracle) product that’s license-free • Don’t confuse a DBMS, which is a software program, with a database, which is a collection of tables, relationships and metadata. But, they are two different concepts.

  30. Creating the Database and Its Structures • Database developers use the DBMS to create and modify tables, relationships, and other structures in the database. • Below, the developer has added a new column called Response?. This new column has data type Yes/No. Fig 5-9 Adding a New Column to a Table (in Access)

  31. Processing the Database • Four DBMS operations • Read, • _______, • modify, • _______ data • Applications call DBMS in different ways • From a form, when the user enters new or changed data, a computer program behind the form calls the DBMS to make the necessary database changes. • From an application program, the program calls the DBMS directly to make the change. insert delete

  32. Structured Query Language (SQL) • SQL—“see-quell” • International standard language for creating databases and database structures, and processing databases • Used by most popular DBMS • Following SQL statement inserts a new row into the Student table: INSERT INTO Student ([Student Number], [Student Name], HW1, HW2, MidTerm) VALUES (1000, ’Franklin, Benjamin’, 90, 95, 100);

  33. Administering the Database • DBMS provides tools to assist in administration of the database. • Used to set up a security system involving user accounts, passwords, permissions, and limits for processing the database • Backing up database data, adding structures to improve performance of database applications, removing data no longer wanted or needed, and similar tasks • Most organizations dedicate one or more employees to the role of database administration (as database administrator – DBA)

  34. Major Responsibilities of Database Administration Fig 5-10 Summary of Database Administration Tasks

  35. Study Questions Q1: What is the purpose of a database? Q2: What is a database? Q3: What are the components of a database application system? Q4: How do database applications make databases more useful? Q5: How are data models used for database development? Q6: How is a data model transformed into a database design? Q7: What is the users’ role in the development of databases? Q8: 2022?

  36. Multi-User Processing Problem • A database application includes forms, reports, queries, and applications programs available to one or more users. • This figure depicts multi-user database processing. The system must be managed properly to avoid the following problems (called concurrent problems): • Potential update loss because • two users update same data • at the same time • Potential locking problems • Possible data conflicts • How to avoid these • problems? Fig 5-11 Use of Multiple Database Applications

  37. Multi-User Processing Problem

  38. Multi-User Processing Problem (another example) • Lost-update problem • Process A reads a customer record from a file containing account information, including the customer’s account balance and phone number. • Process B now reads the same record from the same file so it has its own copy. • Process A changes the account balance in its copy of the customer record and writes the record back to the file. • Process B—which still has the original stale value for the account balance in its copy of the customer record—updates the customer’s phone number and writes the customer record back to the file. • Process B has now written its stale account balance value to the file, causing the changes made by process A to be lost. (Source: http://en.wikipedia.org/wiki/File_locking)

  39. What Are Forms, Reports, and Queries? • Data entry forms are used to read, insert, modify, and delete data. • Reports are used to show data in a structured context as the example to the right shows. Reports show data in a structured context. Fig 5-12 Example of a Student Report

  40. What Are Forms, Reports, and Queries? • A query form helps the user quickly find answers to questions. In this case, the words “barriers to entry” were found in Baker’s record. Sample query form used to enter phrase for search Sample query results of query operation Fig 5-13 (a) & (b) Sample Query and Results

  41. Why Are Database Application Programs Needed? • Forms, reports, and queries work well for standard functions. However, most applications have unique requirements that a simple form, report, or query cannot meet. • Application programs process logic that is specific to a given business need. • Application programs serve as an intermediary between the Web server and database. • Responds to events, such as when a user presses a submit button; also reads, inserts, modifies, and deletes database data

  42. Four Database Application Programs Running on a Web Server Computer • Database application programs process logic specific to a given business need. For example, a program could track backordered items and hold a customer order until the items are ready to ship. • Application programs enable database processing over the Internet as the figure below shows. Users access the applications programs via a Web server, which in turn accesses a single DBMS and database. Fig 5-14 Four Application Programs on a Web Server Computer

  43. Enterprise DBMS vs. Personal DBMS • Enterprise DBMS • Process large organizational and workgroup databases • Support many, possibly thousands, of users and many different database applications • Support 24/7 operations and can manage databases that span dozens of different magnetic disks with hundreds of gigabytes or more of data • IBM’s DB2, Microsoft’s SQL Server, and Oracle’s Oracle are examples of enterprise DBMS products. • Personal DBMS • Designed for smaller, simpler database applications • Used for personal or small workgroup applications that involve fewer than 100 users (normally fewer than 15), single user

  44. Access: A DBMS and an Application Development Product • Here’s a comparison of an Enterprise DBMS versus a Personal DBMS. Before building a database, developers construct a logical representation of database data called a data model to describe the data and relationships to be stored in database. • Personal DBMS • Smaller, simpler applications • Personal or small workgroup applications • 1 - 100 users • Microsoft Access is both a DBMS and application development product as this figure depicts. • Enterprise DBMS • Large organizational and workgroup databases • Hundred to thousands of users • Many different database applications • 24/7 operations • Oracle, DB2, SQL Server Fig 5-15 Personal Database System

  45. PART II

  46. Study Questions Q1: What is the purpose of a database? Q2: What is a database? Q3: What are the components of a database application system? Q4: How do database applications make databases more useful? Q5: How are data models used for database development? Q6: How is a data model transformed into a database design? Q7: What is the users’ role in the development of databases? Q8: 2022?

  47. How to Design a Database? • Questions: • What tables to create? (e.g., GU database) • What attributes should be included in each table? • How those tables are related to each other? • Data model: is a conceptual representation of data classes (data files) and their relationships. • Relational database: data are organized as two-dimensional tables called relations, which are linked together by sharing the same field.

  48. Q5: How Are Data Models Used for Database Development?Database Development Process A data model is similar to blueprints for a house. It’s alogical representation of database data that describes data and their relationships. Fig 5-16 Database Development Process

  49. User interview & Integrated Model Logical Model (ERD or E/ERD) Implementation (w/Physical or Internal Model) Steps of Database Development … … User view-1 User view-2 User view-3 User view-N … Conceptual Schema (Model)

  50. How are data models used for database development? • Conceptual Design • The process of preparing an abstract model of the database from business perspective, which describes the information needs of end users of the database • Entity-relationship approach => E-R Diagram (ERD) • Relational DBMS: • Transform ERD to tables • Normalization process (you will learn it later) • Physical Design • Describe how data would be physically stored in the data storage • Depends on the DBMS

More Related