Chapter 1_Applications of Database_v1.pdf

Chapter 1 Applications of Database 1  A business organisation that generates lots of transactional data 2 (From the business perspective) – What are the important information to know? Which are the top 100 selling products? Top 100 most profitable product...

Chapter 1 Applications of Database 1  A business organisation that generates lots of transactional data 2 (From the business perspective) – What are the important information to know? Which are the top 100 selling products? Top 100 most profitable products? What is the contribution(%) of each product to total profit? Which items are always bought together? What would be the programming effort required to write programs to answer the above question? If you have a database system, all these questions could be answered rather quickly and easily. 3 (From the technical implementation perspective) – system must be efficient in supporting requirements Amount of transaction per unit time? No. of concurrent user support? Duration of data storage? Data warehousing requirement? Database distribution strategy? (and much more…) If you have a DBMS, all these questions could be implemented. 4  Lesson Outline Applications of database – DBMS in an enterprise system context – DBMS in application development environment 5  Lesson Objectives Examples of database applications Definitions of terms History and development of database processing Purpose of database applications 6  Importance of Databases to Economy Expanding use of databases in retail sales – Walmart, retail sales information tracking – Walmart worldwide no. of stores – 10,586 stores and clubs in 24 countries (2022) – 1 million transaction per hour (how many per second?) Examples of analyses – information for decision making – Sales of items Comparisons between daily totals of items sold and items in inventory Seasonal variations in sales of specific and similar items Relative sales of similar items with different features – Market-basket collections (all items in a single purchase) Average and variation in total purchase amount Average and variation in number and price of items Correlation between sales of items in a single purchase – Customer analysis Behavior of average customer Preferences of individual customers 7  Applications of database DBMS in an enterprise system context 8 Application Areas of Database System Airlines and railways: Airlines and railways use online databases for reservation, and for displaying the schedule information. Banking: Banks use databases for customer inquiry, accounts, loans, and other transactions. Education: Schools and colleges use databases for course registration, result, and other information. Telecommunications: Telecommunication departments use databases to store information about the communication network, telephone numbers, record of calls, for generating monthly bills, etc. Credit card transactions: Databases are used for keeping track of purchases on credit cards in order to generate monthly statements. E-commerce: Integration of heterogeneous information sources (for example, catalogs) for business activity such as online shopping, booking of holiday package, consulting a doctor, etc. Health care information systems and electronic patient record: Databases are used for maintaining the patient health care details. Digital libraries and digital publishing: Databases are used for management and delivery of large bodies of textual and multimedia data. Finance: Databases are used for storing information such as sales, purchases of stocks and bonds or data useful for online trading. Sales: Databases are used to store product, customer and transaction details. Human resources: Organizations use databases for storing information about their employees, salaries, benefits, taxes, and for generating salary checks. 9 Examples of Database Applications Purchases from the supermarket – https://myaeon2go.com/ Purchases using your credit card – www.amazon.com Booking a holiday at the travel agents – www.expedia.com.my Using the local library – https://pnm.overdrive.com/ Property management – www.knightfrank.com.my Banking transaction – www.maybank2u.com.my 10  Main use of DBMS in Organizations Enterprise applications – Enterprise resource planning (ERP) systems Enterprise Resource Planning (ERP) system integrates systems across an enterprise to streamline workflow, share information among different departments, and provide insight into a business’s operations. 11  Main use of DBMS in Organizations Enterprise applications – Data warehousing implementations Database used for reporting and data analysis. Integrating data from one or more disparate sources creates a central repository of data, a data warehouse Source: Stoneridge software,2019 12  Main use of DBMS in Organizations Enterprise applications – Big Data Analytics The process of collecting, organizing and analyzing large sets of data to discover patterns and useful information 13  Initial Vocabulary Data: raw facts about things and events Information: transformed data that has value for decision making Essential to organize data for retrieval and maintenance Which are the top 100 selling products? Increase prices all food product by 10% due to increase in price of sugar 14  Database Characteristics Persistent – Lasts a long time (not transient) – Lasts longer than the execution of a computer program – Data is stored to a persistent media once a transaction is completed (committed) so that it can be retrieved when needed Inter-related – Stores entities and relationships among the entities – Entity: cluster of data about a topic (customer, student, loan) – Relationship: connection among entities Shared – Multiple uses: hundreds to thousands of data entry screens and reports – Multiple users: many people simultaneously use a database 15 University Database 16 Water Utility Database 17  Database Technology Evolution Database Systems: Design, Implementation, & Management, 12th Edition, Rob & Coronel Additional Reading: What is DBMS Advantages and Disadvantages of DBMS.htm 18  Purpose of Database System Prior to the availability of DBMSs, database applications were built on top of file systems – coded from the ground up. Drawbacks of this approach: – Difficult to reprogram sophisticated processing, i.e., concurrency control, backup and recovery, security – Re-inventing the wheel can be expensive and error-prone “We need a truck, lets design and build our own truck.” i.e. Need to rewrite code for a program/system that is already working but not compatible with the current system This leads to: Problem for the far base system – Data redundancy and inconsistency – Multiple files and formats – A new program to carry out each new task – Integrity constraints (e.g. account balance > 0) become embedded throughout program code, etc. Database systems offer proven solutions for the above problems. 19  Applications of database DBMS in application development environment 20  The Range of Database Applications Personal databases 21  The Range of Database Applications Two-tier Client/Server databases 22  The Range of Database Applications Multitier Client/Server databases 3-tier 4-tier 23  Applications, the DBMS, and SQL Applications are the computer programs that users work with. The Database Management System (DBMS) is a software suite that creates, processes, and administers databases. Structured Query Language (SQL) is an internationally recognized standard database language that is used by all commercial DBMSs. 24  Database Applications A DBMS has features to: (you will learn some of these features in the lab practical and in other courses) 25 Database Applications—Forms A very useful reporting tool 26  Database Applications—Queries SELECT LastName, FirstName, EmailAddress FROM STUDENT WHERE StudentNumber > 2; Very simple English-like phrases that can be easily understood even by the end-users 27  Database—Reports Reports in a variety of formats can be created in an ad-hoc manner 28  Summary Examples of database applications Definitions of terms History and development of database processing Purpose of database applications Please refer to reference books and additional reading materials for more knowledge and information. 29