Using MIS 10 th Edition Chapter 5 Database
Using MIS 10 th Edition Chapter 5 Database Processing Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -1
“We Don’t Have a Way to Track the Data About the Videos. ” • Falcon Security stores sequentially numbered digital video files in separate directories for each client. • Tracking down exact footage of when equipment was stolen means searching hundreds of video files. • Need database to track video files. • Mongo. DB for tracking video files? Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -2
Study Questions Q 5 -1 What is the purpose of a database? Q 5 -2 What is a database? Q 5 -3 What is a database management system (DBMS)? Q 5 -4 How do database applications make databases more useful? Q 5 -5 How are data models used for database development? Q 5 -6 How is a data model transformed into a database design? Q 5 -7 How can Falcon Security benefit from a database system? Q 5 -8 2027? Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -3
Why Use A Database? Q 5 -1 What is the purpose of a database? • Organize and keep track of things. • Keep track of multiple themes. • General rule: – Single theme - store in a spreadsheet. – Multiple themes - use a database. – What's a theme? § Ex: student grades, student emails, student office visits. Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -4
A List of Student Grades Presented in a Spreadsheet – Single Theme Q 5 -1 What is the purpose of a database? Figure 5 -1 A List of Student Grades Presented in a Spreadsheet Source: Microsoft Excel 2016 Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -5
Student Data Form for a Database Application Q 5 -1 What is the purpose of a database? Figure 5 -2 Student Data Shown in a Form from a Database Source: Microsoft Access 2016 Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -6
Q 2 What is a Database? Q 5 -2 What is a database? Figure 5 -3 Student Table (also called a file) Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -7
Hierarchy of Data Elements Q 5 -2 What is a database? Figure 5 -4 Hierarchy of Data Elements Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -8
Components of a Database Q 5 -2 What is a database? Figure 5 -5 Components of a Database Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -9
Example of Relationships Among Rows Q 5 -2 What is a database? Figure 5 -6 Example of Relationships Among Rows Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -10
Sample of Access Metadata Q 5 -2 What is a database? Figure 5 -7 Sample Metadata (in Access) Source: Microsoft Access 2016 Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -11
Querying Inequality? Ethics Guide • Mary. Ann has a data mart. • Business professional majored in HR, now "expert" in SQL. • Uses SQL to do job faster and better. • Examined data, saw possible discriminatory pattern. • What would you do from categorical imperative and utilitarian perspectives? Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -12
Querying Inequality? (cont’d) Ethics Guide • Queries could reveal all sorts of patterns and trends. § Be sure what answers you want before starting query. • How strongly do you feel about social and personal responsibility, considering your needs and those of your family? • How important is social responsibility posture of an employer to you? § Is that something to add to your criteria for a job search? Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -13
Slick Analytics So What? • CIOs are looking to merge the storage and analysis of cloud-based data into one synergistic operation. • Laredo Petroleum – Old approach used numerous spreadsheets and manual calculations. – Value of data diminished due to the time it took to analyze the data. – New approach uses cloud storage and cloud analytics. Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -14
Slick Analytics (cont’d) So What? • Laredo Petroleum – Needs to know when it should clean chemical deposits in its wells. – Cloud storage and analytics improved scalability, robust data analysis, and data accessibility. • Cloud analytics will grow by 46 percent through 2020. • Security and privacy concerns are drawbacks to cloud services. Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -15
Database Management System (DBMS) Q 5 -3 What is a database management system (DBMS)? • Program to create, process, administer a database. • Licensed from vendors § IBM, Microsoft, Oracle, and others. – DB 2, Access, SQL Server, Oracle Database. • Open source § My. SQL: License-free for most applications. Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -16
Processing the Database Q 5 -3 What is a database management system (DBMS)? • DBMS Process Operations – Read – Insert – Modify – Delete data Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -17
Processing the Database (cont’d) Q 5 -3 What is a database management system (DBMS)? • Structured Query Language - SQL (see-quell) § International standard § Used by nearly all DBMS • SQL Example • INSERT INTO Student – ([Student Number], [Student Name], HW 1, HW 2, Mid. Term) • VALUES (1000, ‘Franklin, Benjamin’, 90, 95, 100) Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -18
Adding a New Column to a Table (in Access 2016) Q 5 -3 What is a database management system (DBMS)? Figure 5 -8 Adding a New Column to a Table (in Access) Source: Microsoft Access 2016 Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -19
Administering the Database Q 5 -3 What is a database management system (DBMS)? • Set up security system, user accounts, passwords, permissions, limits for processing. • Limit user permissions. • Back up database, improve performance of database applications, remove unwanted data. Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -20
Summary of Database Administration Tasks Q 5 -3 What is a database management system (DBMS)? Category Development Database Administration Task Description Create and staff DBA function Size of DBA group depends on size and complexity of database. Groups range from one part-time person to small group. Form steering committee Consists of representatives of all user groups. Forum for community-wide discussions and decisions. Specify requirements Ensure that all appropriate user input is considered. Validate data model Check data model for accuracy and completeness. Evaluate application design Verify that all necessary forms, reports, queries, and applications are developed. Validate design and usability of application components. Figure 5 -9 Summary of Database Administration (DBA) Tasks Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -21
Summary of Database Administration Tasks (cont’d) Q 5 -3 What is a database management system (DBMS)? Category Operation Backup and Recovery Adaptation Database Administration Task Description Manage processing rights and responsibilities Determine processing rights/restrictions on each table and column. Manage security Add and delete users and user groups as necessary; ensure that security system works. Track problems and manage resolution Develop system to record and manage resolution of problems. Monitor database performance Provide expertise/solutions for performance improvements. Manage DBMS Evaluate new features and functions. Monitor backup procedures Verify that database backup procedures are followed. Conduct training Ensure that users and operations personnel know and understand recovery procedures. Manage recovery process. Set up request tracking system Develop system to record and prioritize requests for change. Manage configuration change Manage impact of database structure changes on applications and users. Figure 5 -9 Summary of Database Administration (DBA) Tasks Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -22
Forms, Queries, Reports, and Applications Q 5 -4 How do database applications make databases more useful? Forms View data; insert new, update existing, delete existing data. Queries Search using values provided by user. Reports Structured presentation of data using sorting, grouping, filtering, other operations. Application programs Provide security, data consistency, special purpose processing, e. g. , handle out-of-stock situations. Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -23
Users Accessing Databases Q 5 -4 How do database applications make databases more useful? Figure 5 -10 Components of a Database Application System Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -24
Example of a Student Report Q 5 -4 How do database applications make databases more useful? Figure 5 -11 Example of a Student Report Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -25
Query Example Q 5 -4 How do database applications make databases more useful? Figure 5 -12 A Sample Query Form Used to Enter Phrase for Search Figure 5 -12 B Sample Query Results of Query Operation Source: Microsoft Access 2016 Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -26
Browser Forms, Reports, Queries, and Applications Q 5 -4 How do database applications make databases more useful? Figure 5 -13 Four Application Programs on a Web Server Computer Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -27
Account Creation Browser Form Q 5 -4 How do database applications make databases more useful? Figure 5 -14 Account Creation Browser Form Source: Microsoft Corporation Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -28
Browser Report Q 5 -4 How do database applications make databases more useful? Figure 5 -15 Browser Report Source: Microsoft Corporation Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -29
Graphical Query: User Clicks on Video Icon to Find All Videos from that Location Q 5 -4 How do database applications make databases more useful? Figure 5 -16 Graphical Query: User Clicks on Video Icon to Find All Videos from That Location Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -30
Multiuser Processing Problem Q 5 -4 How do database applications make databases more useful? 1. Andrea reads pedal record showing balance of 2 pedals. 1 3. Andrea puts both in shopping baskets, but delays checking out. 2 2. Jeffrey reads same record showing balance of 2 pedals. 3 5. Andrea proceeds to checkout. Someone will be disappointed. 4 5 4. Jeffrey puts both in shopping basket and checks out before Andrea. Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -31
Q 5: How Are Data Models Used for Database Development? Q 5 -5 How are data models used for database development? Figure 5 -17 Database Development Process Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -32
What Is the Entity-Relationship Data Model? Q 5 -5 How are data models used for database development? Entities • Something to track. – Order, customer, salesperson, item, volunteer, donation Attributes • Describe characteristics of entity. – Order. Number, Customer. Number, Volunteer. Name, Phone. Number Identifier • Uniquely identifies one entity instance from other instances – Student_ID_Number Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -33
Student Data Model Entities Q 5 -5 How are data models used for database development? Figure 5 -18 Student Data Model Entities Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -34
Example of Department, Adviser, and Student Entities and Relationships (cont’d) Q 5 -5 How are data models used for database development? Figure 5 -19 Example of Department, Adviser, and Student Entities and Relationships Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -35
Sample of Relationships―Version 1 Q 5 -5 How are data models used for database development? Crow’s Feet 1: N One department may have many advisers, but an adviser may be in only one department N: M An Adviser may have many students, and one student may have many advisers Figure 5 -20 Sample Relationships Version 1 Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -36
Sample of Relationships─Version 2 Q 5 -5 How are data models used for database development? “Crow’s Foot” N: M A department has many advisors, and an advisor may advise for more than one department 1: N A student has only one advisor, but an adviser may advise many students Figure 5 -21 Sample Relationships Version 2 Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -37
Crow’s-Foot Diagram Version Q 5 -5 How are data models used for database development? Maximum cardinality─maximum number of entities in a relationship. Vertical bar on a line means at least one entity required. Minimum cardinality—minimum number of entities in a relationship. Small oval means entity is optional; relationship need not have entity of that type. Figure 5 -22 Sample Relationships Showing Both Maximum and Minimum Cardinalities Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -38
Q 6: How Is a Data Model Transformed into a Database Design? Q 5 -6 How is a data model transformed into a database design? • Normalization § Converting poorly structured tables into two or more well-structured tables. – Goal § Construct tables with single theme or entity. – Purpose § Minimize data integrity problems. Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -39
Data Integrity Problems Q 5 -6 How is a data model transformed into a database design? • Data integrity problems. § Incorrect or inconsistent information. § Users lose confidence in information. § System gets a poor reputation. • Can only occur if data are duplicated. Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -40
Poorly Designed Employee Table Causes Data Integrity Problem Q 5 -6 How is a data model transformed into a database design? Figure 5 -23 A Poorly Designed Employee Table Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -41
Two Normalized Tables Q 5 -6 How is a data model transformed into a database design? Figure 5 -24 Two Normalized Tables Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -42
Summary of Normalization Q 5 -6 How is a data model transformed into a database design? Figure 5 -25 Transforming a Data Model into a Database Design Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -43
Representing 1: N Relationships Q 5 -6 How is a data model transformed into a database design? Figure 5 -26 Representing a 1: N Relationship Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -44
Representing an N: M Relationship: Strategy for Foreign Keys Q 5 -6 How is a data model transformed into a database design? Figure 5 -27 Representing an N: M Relationship Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -45
Users’ Role in the Development of Databases Q 5 -6 How is a data model transformed into a database design? • Final judges. • Thorough review of data model. § Entities must contain all the data users need to do their jobs. § Must accurately reflect their view of the business. • Take it seriously. • Devote time. Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -46
A Database System At Falcon Security Q 5 -7 How can Falcon Security benefit from a database system? • Find videos by querying their characteristics. § “Which videos do we have of the Beresford Building in October 2014, shot from 3, 000 feet or less? ” • Choices § Store videos on a file server and keep metadata about each video in a relational database to query with SQL. § Use No. SQL Mongo. DB. Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -47
Falcon Security Chooses Option 1 Q 5 -7 How can Falcon Security benefit from a database system? • Use Access to store metadata. • Less risky: uses known technology. • Creates E-R diagram. • Decide to keep design simple at first. Figure 5 -28 E-R Diagram for Falcon Security’s Database Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -48
Database Processing in 2027? Q 5 -8 2027? • Volume of database continues to grow. • Cheap, unlimited storage, greater processing speeds in relational databases. • Security becomes more important. • Many No. SQL, New. SQL, and in-memory databases exist in commerce. Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -49
ACID Transactions Q 5 -8 2027? • Atomic, Consistent, Isolated, Durable transactions. • Critical to traditional commercial applications. • New Internet applications (Twitter) don’t need ACID. Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -50
New Categories of DBMS Q 5 -8 2027? • No. SQL DBMS (Not. Relational DBMS) • Supports very high transaction rates, processing relatively simple data structures, • Replicated on many servers in the cloud, without ACID transaction support. • Mongo. DB, Cassandra, Bigtable, and Dynamo. Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -51
New Categories of DBMS (cont’d) Q 5 -8 2027? • New. SQL DBMS – Process very high levels of transactions, like No. SQL DBMS, but provide ACID support. – May or may not support relational model. – Current hotbed of development. • In-memory DBMS using SQL extension – SAP HANA, Tableau. – High volume ACID transaction support with complex relational query processing. Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -52
Database Processing in 2027? (cont’d) Q 5 -8 2027? • Keep abreast of developments. • Watch from investor’s perspective. • New opportunities and career paths will develop around these new DBMS products. • Separate yourself from the competition when it comes to job interviews. Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -53
Big Data…Losses Security Guide • Many companies are focusing on perfecting powerful Big Data tools. • They spend little effort securing the data they collect. • Information security is neglected for the sake of functionality and convenience. • Security audit showed more than 39, 000 No. SQL databases are exposed. • 1. 1 petabytes of this data were available online. Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -54
Big Data…Losses (cont’d) Security Guide • Information. Week Survey: – Poor database security practices at 20 percent of respondents. 1. Databases containing sensitive information are not secured. 2. Data breaches have occurred or it cannot be confirmed breaches have not occurred. 3. Security evaluations are not regularly conducted on respondents’ databases. – Securing data is as important as accuracy and reporting capabilities. Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -55
Database Engineer Career Guide Jacob Case at Overstock. com Q. What attracted you to this field? A. “I still remember my first database class. I just had a feeling that it was important because I knew businesses derive information and critical insights from data, and these insights are used to make critical business decisions. I knew that if I became proficient with managing and analyzing data I would always have a job. ” Q. What advice would you give to someone who is considering working in your field? A. “Don't be intimidated by the idea of working in the field of information systems. If you are committed to learning, are teachable, are a great team player, and have a good attitude, you will learn a tremendous amount. ” Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -56
Active Review Q 5 -1 What is the purpose of a database? Q 5 -2 What is a database? Q 5 -3 What is a database management system (DBMS)? Q 5 -4 How do database applications make databases more useful? Q 5 -5 How are data models used for database development? Q 5 -6 How is a data model transformed into a database design? Q 5 -7 How can Falcon Security benefit from a database system? Q 5 -8 2027? Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -57
Dean's Piano Database Case Study • Certified piano tuner and technician repairing and restoring pianos for many years. • Clown entertainer at children’s parties. Figure 5 -30 Deano the Clown Source: Dean Petrich Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -58
Pianos in Storage Case Study Figure 5 -31 Pianos in Storage Source: David Kroenke Figure 5 -32 Pianos in Tent Source: David Kroenke Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -59
Columns in the Piano Table Case Study Figure 5 -33 Columns in the Piano Table Source: Microsoft Corporation Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -60
Query Design and Result Case Study Figure 5 -34 Example Query Source: Microsoft Corporation Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -61
Results from Query Case Study Figure 5 -35 Results from Query in Figure 5 -34 Source: Microsoft Corporation Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -62
Piano Sound Quality by Building Case Study Figure 5 -36 Piano Sound Quality by Building Source: Microsoft Corporation Copyright © 2018, 2017, 2016 Pearson Education, Inc. All Rights Reserved 5 -63
- Slides: 64