Database Systems: Design, Implementation, and Management, 14e PDF

Database Systems: Design, Implementation, and Mana...

Database Systems: Design, Implementation, and Management, 14e Module 1: Database Systems Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 1 whole or in part.  Icebreaker: Interview Simulation 1. The class will be broken up into pairs of students. 2. Each pair of students will interview each other to discover interesting or unusual facts 3. Then each pair will introduce each other to the class. 4. Think about connecting a story from your personal experiences to topics that are relevant to this course. Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 2 whole or in part.  Chapter Objectives (1 of 2) By the end of this chapter, you should be able to: 1. Define the difference between data and information 2. Describe what a database is, the various types of databases, and why they are valuable assets for decision making 3. Explain the importance of database design 4. See how modern databases evolved from file systems Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 3 whole or in part.  Chapter Objectives (2 of 2) By the end of this chapter, you should be able to (continued): 5. Understand flaws in file system data management 6. Outline the main components of the database system 7. Describe the main functions of a database management system (DBMS) Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 4 whole or in part.  Why Databases? Figure 1.1 The Pervasive Nature of Databases Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 5 whole or in part.  Data versus Information (1 of 2) Data consists of raw facts − The facts have not yet processed to reveal meaning to the end user Information is the result of processing raw data to reveal the meaning of data − To reveal meaning, information requires context Data is the foundation of information, which is the bedrock of knowledge − Knowledge implies familiarity, awareness, and understanding of information Accurate, relevant, and timely information is the key to good decision making Data management is a discipline that focuses on the proper generation, storage, and retrieval of data Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 6 whole or in part. Data versus Information (2 of 2) Figure 1.2 Transforming Raw Data into Information Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 7 whole or in part.  Introducing the Database A database is a shared, integrated computer structure that stores a collection of the following: − End-user data – that is, raw facts of interest to the end user − Metadata, or data about data, through which the end-user data is integrated and managed  Metadata describes the data characteristics and the set of relationships that links the data found within the database A database management system (DBMS) is a collection of programs that manages the database structure and controls access to the data stored in the database Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 8 whole or in part. Role and Advantages of DBMS (1 of 2) The DBMS presents the end user with a single, integrated view of the data in the database A DBMS provides the following advantages: − Improved data sharing − Improved data security − Better data integration − Minimized data inconsistency − Improved data access − Improved decision making − Increased end-user productivity Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 9 whole or in part. Role and Advantages of DBMS (2 of 2) Figure 1.4 The DBMS Manages the Interaction Between the End User and the Database Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 10 whole or in part.  Types of Databases (1 of 4) A single-user database supports one user at a time − A desktop database is single-user database on a personal computer A multiuser database supports multiple users at the same time − A workgroup database supports a small number of users or a specific department − An enterprise database supports many users across many departments Classification by location − A centralized database supports data located at a single site − A distributed database supports data distributed across different sites − A cloud database is created and maintained using cloud data services Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 11 whole or in part.  Types of Databases (2 of 4) Classification by data type − General-purpose databases contain a wide variety of data used in multiple disciplines − Discipline-specific databases contain data focused on specific subject areas − An operational database is designed to support a company’s day-to-day operations − An analytical database stores historical data and business metrics used exclusively for tactical or strategic decision making and is comprised of two main components:  The data warehouse stores data in a format optimized for decision support  Online analytical processing (OLAP) is a set of tools for retrieving, processing, Coronel, Carlosand modeling and Morris, data Steven, Database from Systems: the Design, data warehouse Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 12 whole or in part.  Types of Databases (3 of 4) Business intelligence describes a comprehensive approach to capture and process business data to generate information that support decision making Databases can be classified to reflect the degree to which the data is structured − Unstructured data exists in its original (raw) state − Structured data is the result of formatting unstructured data to facilitate storage and use − Semistructured data has already been processed to some extent Extensible Markup Language (XML) is a language used to represent data elements in textual format − An XML database supports the storage and management of unstructured XML data Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 13 whole or in part.  Types of Databases (4 of 4) Social media refers to web and mobile technologies that enable “anywhere, anytime, always on” human interaction − Data is captured about end users and consumers that requires the use of specialized database systems The term NoSQL (Not only SQL) is a new generation of DBMS that is not based on the traditional relational database model − These databases are designed to handle an unprecedented volume of data, variety of data types and structures, and velocity of data operations of new business requirements Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 14 whole or in part.  Knowledge Check Activity 1-1 What is a DBMS? Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 15 whole or in part. Knowledge Check Activity 1-1: Answer What is a DBMS? Answer: A DBMS is best described as a collection of programs that manage the database structure and that control shared access to the data in the database. Current DBMSs also store the relationships between the database components; they also take care of defining the required access paths to those components. Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 16 whole or in part.  Why Database Design is Important (1 of 3) Database design refers to the activities that focus on the design of the database structure that will be used to store and manage end-user data Designing appropriate data repositories of integrated information using the two- dimensional table structures found in most databases is a process of decomposition − The integrated data must be decomposed properly into its constituent parts A well-designed database facilitates data management and generates accurate and valuable information A poorly designed database causes difficult-to-trace errors that may lead to poor decision making Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 17 whole or in part. Why Database Design is Important (2 of 3) Figure 1.5 Employee Skills Certification in a Poor Design Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 18 whole or in part. Why Database Design is Important (3 of 3) Figure 1.6 Employee Skills Certification in a Good Design Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 19 whole or in part.  Evolution of File System Data Processing (1 of 2) Manual file systems are accomplished through a system of file folders and filing cabinets With computerized file systems, a data processing (DP) specialist created a computer-based system to track data and produce required reports File System Redux: Modern End-User Productivity Tools − Business users widely use spreadsheet programs such as Microsoft Excel to enter data in a series of rows and columns to manipulate data − A common misuse of spreadsheets is as a substitute for a database Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 20 whole or in part.  Evolution of File System Data Processing (2 of 2) Table Basic File Terminology 1.2 Term Definition Data Raw facts, such as a telephone number, a birth date, a customer name, and a year-to-date (YTD) sales value. Data has little meaning unless it has been organized in some logical manner. Field A character or group of characters (alphabetic or numeric) that has a specific meaning. A field is used to define and store date. Record A logically connected set of one or more fields that describes a person, place, or thing. For example the fields that constitute a record for a customer might consist of the customer’s name, address, phone number, date of birth, credit limit, and unpaid balance. File A collection of related records. For example, a file might contain data about the students currently enrolled at Gigantic University. Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 21 whole or in part.  Problems with File System Data Processing The following problems with file systems challenge the types of information that can be created from data as well as information accuracy: − Lengthy development times − Difficulty of getting quick answers − Complex system administration − Lack of security and limited data sharing − Extensive programming Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 22 whole or in part. Structural and Data Dependence (1 of 2) A file system exhibits structural dependence, which means that access to a file is dependent on its own structure − All file system programs are modified to conform to a new file structure Structural independence exists when you change the file structure without affecting the application’s ability to access the data Data dependence exists when all data access programs are subject to change when data storage characteristics change Data independence exists when data storage characteristics are changed without affecting the program’s ability to access the data Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 23 whole or in part. Structural and Data Dependence (2 of 2) The practical significance of data dependence is the difference between the logical data format (how humans view the data) and the physical data format (how the computer must work with the data) Any program that accesses a file system’s file must tell the computer not only what to do but also how to do it Data dependence makes the file system cumbersome from the point of view of a programmer and database manager Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 24 whole or in part.  Data Redundancy Data redundancy exists when the same data is stored unnecessarily at different places − Database professionals use the term islands of information for scattered data locations − Increases the probability of having different versions of the same data Possible results of uncontrolled data redundancy − Poor data security − Data inconsistency − Data-entry errors − Data integrity problems Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 25 whole or in part.  Data Anomalies A data anomaly develops when not all of the required changes in the redundant data are made successfully The following are three types of anomalies: − Update anomalies − Insertion anomalies − Deletion anomalies Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 26 whole or in part.  Knowledge Check Activity 1-2 What is data redundancy, and which characteristics of the file system can lead to it? Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 27 whole or in part. Knowledge Check Activity 1-2: Answer What is data redundancy, and which characteristics of the file system can lead to it? Answer: Data redundancy exists when unnecessarily duplicated data are found in the database. For example, a customer's telephone number may be found in the customer file, in the sales agent file, and in the invoice file. Data redundancy is symptomatic of a (computer) file system, given its inability to represent and manage data relationships. Data redundancy may also be the result of poorly‑designed databases that allow the same data to be kept in different locations. Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 28 whole or in part.  Database Systems (1 of 2) The database system consists of logically related data stored in a single logical data repository − However, the data might be physically distributed among multiple storage facilities The database’s DBMS eliminates most of the file system’s data inconsistency, data anomaly, data dependence, and structural dependence problems The current generation of DBMS software stores data structures, relationships between those structures, and access paths to those structures − The current generation also defines, stores, and manages all access paths and components Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 29 whole or in part.  Database Systems (2 of 2) Figure 1.9 Contrasting Database and File Systems Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 30 whole or in part.  The Database System Environment (1 of 2) The term database system refers to an organization of components that define and regulate the collection, storage, management, and use of data within a database environment The database system is composed of the following five components: − Hardware − Software − People − Procedures − Data Database solutions must be cost-effective as well as tactically and strategically effective Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 31 whole or in part. The Database System Environment (2 of 2) Figure 1.10 The Database System Environment Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 32 whole or in part.  DBMS Functions (1 of 3) A DBMS performs the following important functions: − Data dictionary management – The DBMS stores definitions of data elements and their relationships in a data dictionary − Data storage management – The DBMS creates and manages the structures required for data storage  Performance tuning ensures efficient performance − Data transformation and presentation – The DBMS transforms entered data to conform to required data structures  Data is formatted to conform to the user’s logical expectations − Security management – The DBMS creates a system that enforces user security and data privacy Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 33 whole or in part.  DBMS Functions (2 of 3) A DBMS performs the following important functions (continued): − Multiuser access control – The DBMS uses sophisticated algorithms to ensure that multiple users can access the database concurrently without compromising its integrity − Backup and recovery management – The DBMS provides backup and data recovery to ensure data safety and integrity  Recovery management deals with the recovery of the database after a failure − Data integrity management – The DBMS promotes and enforces integrity rules, thus minimizing redundancy and maximizing data consistency Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 34 whole or in part.  DBMS Functions (3 of 3) A DBMS performs the following important functions (continued): − Database access languages and application programming interfaces – The DBMS provides data access through a query language  A query language lets the user specify what must be done without having to specify how  Structured Query Language (SQL) is the de facto query language and data access standard supported by the majority of DBMS vendors − Database communication interfaces – A DBMS accepts end-user requests via multiple communication interfaces Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 35 whole or in part.  Managing the Database System: A Shift in Focus Disadvantages of database systems include the following: − Increased costs − Management complexity − Maintaining currency − Vendor dependence − Frequent upgrade/replacement cycles Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 36 whole or in part.  Preparing for Your Database Professional Career Table 1.3 Database Career Opportunities Job Title Description Sample Skills Required Database Developer Create and maintain database-based applications Programming, database fundamentals, SQL Database Designer Design and maintain databases Systems design, database design, SQL Database Administrator Manage and maintain DBMS and databases Database fundamentals, SQL, vendor courses Database Analyst Develop databases for decision support reporting SQL, query optimization, data warehouses Database Architect Design and implementation of database environments (conceptual, DBMS fundamentals, data modeling, SQL, hardware knowledge, etc. logical, and physical) Database Consultant Help companies leverage database technologies to improve business Database fundamentals, data modeling, database design, SQL, DBMS, processes and achieve specific goals hardware, vendor-specific technologies, etc. Database Security Implement security policies for data DBMS fundamentals, database administration, SQL, data security Officer administration technologies, etc. Cloud Computing Data Design and implement the infrastructure for next-generation cloud Internet technologies, cloud storage technologies, data security, Architect database systems performance tuning, large databases, etc. Data Scientist Analyze large amounts of varied data to generate insights, Data analysis, statistics, advanced mathematics, SQL, programming, data relationships, and predictable behaviors mining, machine learning, data visualization Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 37 whole or in part.  Knowledge Check Activity 1-3 What are the main components of a database system? Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 38 whole or in part. Knowledge Check Activity 1-3: Answer What are the main components of a database system? Answer: The basis of this discussion is Section 1-7a, THE DATABASE SYSTEM ENVIRONMENT. Figure 1.10 provides a good bird’s eye view of the components. Note that the system’s components are hardware, software, people, procedures, and data. Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 39 whole or in part.  Summary (1 of 2) Now that the lesson has ended, you should be able to: 1. Define the difference between data and information 2. Describe what a database is, the various types of databases, and why they are valuable assets for decision making 3. Explain the importance of database design 4. See how modern databases evolved from file systems Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 40 whole or in part.  Summary (2 of 2) Now that the lesson has ended, you should be able to (continued): 5. Understand flaws in file system data management 6. Outline the main components of the database system 7. Describe the main functions of a database management system (DBMS) Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 41 whole or in part.