370 likes | 801 Views
Chapter 5. Database Processing. Study Questions. Q1 What is the purpose of a database?. Q2 What does a database contain?. Q3 What is a DBMS, and what does it do?. Q4 What is a database application?. Q5 What is the difference between an enterprise DBMS and a personal DBMS?.
E N D
Chapter 5 Database Processing
Study Questions Q1 What is the purpose of a database? Q2 What does a database contain? Q3 What is a DBMS, and what does it do? Q4 What is a database application? Q5 What is the difference between an enterprise DBMS and a personal DBMS? How does the knowledge in this chapter help FlexTime and you?
Relationships Among Records Database has multiple tables (one for each theme) Values in one table may relate to records in other tables Field(s) that uniquely identify a record in a table Relational database Primary or Unique key Each table must have a key Foreign key • A non-primary key in one table that is related to a primary key of a different table
Relationships Among Records in Three Tables Figure 5-6
Metadata Database: Self-describing collection of integrated records • Metadata • Data that describes data • Makes databases more useful • Makes databases easier to use • Describes data by: • Data type: text, number, date, etc. • Field name • Field properties
Q3: What Is a DBMS and What Does It Do? Database management system (DBMS) • Program that creates, processes, and administers databases • Examples: DB2, Microsoft Access, SQL Server, Oracle, MySQL (open-source DBMS) Database Collection of tables, relationships, and metadata DBMS A software program
Components of a Database Application System This diagram shows how we use a business website or a web application connected to a database.
Processing the Database Four DBMS operations • Read, Insert, Modify, and Delete data Structured Query Language (SQL) • International standard language for processing databases Example SQL statement • INSERT INTO Student • ([Student Number], [Student Name], HW1, HW2, MidTerm) • VALUES (1000, ‘Franklin, Benjamin’, 90, 95, 100) If Neil knew SQL, he could formulate his own queries against the database.
Administering the Database DBMS security features to set up user accounts, passwords, permissions, processing limits Permissions—setting data access rights for specific users or groups of users Database backup and replication, adding structures, removing unneeded data
Q4: What Is a Database Application? Multi-tier architecture; middle-tier
Forms, Reports, and Queries Forms • Used to read, insert, modify, and delete data (record-based) Reports • Show data in structured context • May compute values such as Totals, within a report Queries • Are a means of getting answers from database data
Example of a Database Query and Query Result Query result Query form Report
Database Application Programs Internet database processing to: Connect web server and database Processing logic for a specific business need Respond to events Read, insert, modify, delete data
Four Database Application Programs Running on a Web Server Via Internet
Multiuser Processing Considerations Lost-update problem Occurs when two or more transactions select same record at same time, then update it based on values first received. Each transaction is independent. Last update overwrites updates previously made by others. 10-5 = 5 5, 7 or 3? 10-3 = 7
Multi-User Processing Problem: Lost-update problem Process A reads customer record from file containing customer’s account balance. Process A updates account balance in its copy of customer record and writes record to file. Process B writes stale account balance value to file, causing changes made by Process A to be lost. 1 2 3 4 5 Process B reads same record from same file now has its own copy. Process B has original stalevalue for account balance. Updates customer’s phone number and writes customer record to file.
Preventing Lost Update Problem • Locking • Prevents another user or process to open a record currently being used by another user or process.
Q5: What Is the Difference Between an Enterprise DBMS and a Personal DBMS? • Enterprise DBMS • Processes large organizational and workgroup databases • Supports many users (thousands plus) • Examples: DB2, SQL Server, Oracle, MySQL • Personal DBMS • Designed for smaller, simpler database applications • Supports fewer than 100 users (mostly 1–10 users) • Examples: Access, dBase, FoxPro, R-Base
The Database Allocation Approach Distributed Databases There are alternative ways of distributing a database. The central database can be partitioned (a) so that each remote processor has the necessary data to serve its own local needs. The central database also can be replicated (b) at all remote locations. Each is responsible for their own data sources.
Big Data • Data in petabyte and exabyte range (billions to trillions of records from different sources) • Megabyte (106), terabyte (1012), petabyte(1015), exabyte (1018) • Twitter generates over 8 terabytes of data daily • Big data is capable of revealing more patterns and trends than smaller data sets • Big data need new technologies and tools to derive business values.
In-memory Computing • Way to facilitate big data analysis • Relies on computer RAM for data storage • Eliminates bottlenecks from retrieving and reading data in a disk-based database • Dramatically shortens query response times • Possible to amount large data sets to the size of a data mart or a small data warehouse to reside in memory. • Complex business calculations can complete within seconds • Leading commercial products: SAP’s HANA, Oracle Exalytics
Ethics Guide: Nobody Said I Shouldn’t Chris employed to configure computers, manage network, maintaining servers and making database backups. Mentioned it to Jason on Friday Chris was fired Monday morning Queried SQL Server metadata Discovered one order-entry entry clerk (Jason) gave large discounts to a buyer that no one else did Located tables with order data, customers, salespeople Made unauthorized copy of one database backup
Guide: No, Thanks, I’ll Use a Spreadsheet Databases take time to build Complicated to operate Need IS people to create it and keep it running Spreadsheets may be a better option in some cases Shares data that you may not want to expose Is the car salesman’s arguments against keeping his business data in a database valid? Would it be better for employer if all its salespeople used a database?
Active Review Q1 What is the purpose of a database? Q2 What does a database contain? Q3 What is a DBMS, and what does it do? Q4 What is a database application? Q5 What is the difference between an enterprise DBMS and a personal DBMS? How does the knowledge in this chapter help FlexTime and you?