Chapter 2 Database Concepts PDF

1 Chapter 2 Database Concepts 1  1 In this chapter, you will learn: The difference between data and information What a database is, about differ...

1 Chapter 2 Database Concepts 1  1 In this chapter, you will learn: The difference between data and information What a database is, about different types of databases, and why they are valuable assets for decision making Why database design is important How modern databases evolved from files and file systems Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel 2  1 In this chapter, you will learn (continued): About flaws in file system data management How a database system differs from a file system, and how a DBMS functions within the database system About data storage and retrieval strategies Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel 3 1 4  Data vs. Information 1 Data Information Raw facts Produced by processing data ○ Raw data - Not yet been Reveals the meaning of data processed to reveal the Enables knowledge creation meaning Should be accurate, relevant and Building blocks of information timely to enable good decision Data management making ○ Generation, storage, and retrieval of data ➔ Accurate, relevant, and timely information is key to good decision making ➔ Good decision making is key to survival in global environment 5 Meaningful Information - example 1 6 Transforming raw data into information - example 1 Database Systems: Design, Implementation, & Management, 12th Edition, Rob & Coronel 7  1 Data Management Trends Paper Computer File- File-based Database based System Data Warehouse And Data mining Read: http://cnx.org/content/m28156/latest/ Sighted 09/05/2014 8  Database Approach 1 Database—shared, integrated computer structure that houses: – End user data: Raw facts of interest to end user – Metadata: Data about data, which end-user data are integrated and managed **stored in one location and able to share with many departments Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel 9  Database Approach 1 DBMS (database management system): – Collection of programs that manages database structure and controls access to data – Possible to share data among multiple applications or users – Makes data management more efficient and effective Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel 10  1 DBMS Makes Data Management More Efficient and Effective End users have better access to more and better- managed data – Promotes integrated view of organization’s operations Better understanding and minimize errors VS individualistic view (the story of the 4 blind man describing an elephant) – Probability of data inconsistency is greatly reduced – Possible to produce quick answers to ad hoc queries Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel 11  1 The DBMS Manages the Interaction Between the End User and the Database Database Systems: Design, Implementation, & Management, 12th Edition, Rob & Coronel 12  1 Types of Databases Single-user: – Supports only one user at a time Desktop: – Single-user database running on a personal computer Multi-user: – Supports multiple concurrent users at the same time Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel 13  1 Types of Databases (continued) Workgroup: – Multi-user database that supports a small group of users or a single department Enterprise: – Multi-user database that supports a large group of users or an entire organization Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel 14  Location of Databases 1 Centralized: – Supports data located at a single site Distributed: Supports data distributed across several site 15  1 Uses of Databases Transactional (or production): – Supports a company’s day-to-day operations Data warehouse: – Stores data used to generate information required to make tactical or strategic decisions Such decisions typically require “data massaging” – Often used to store historical data Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel 16  1 Why Database Design is Important Defines the database’s expected use Different approach needed for different types of databases Avoid redundant data (unnecessarily duplicated) A poorly designed database may lead to poor decision making—and poor decision making can lead to the failure of an organization. Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel 17  1 Manual File Systems Traditionally composed of collection of file folders kept in file cabinet Organization within folders was based on data’s expected use (ideally logically related) System was adequate for small amounts of data with few reporting requirements Finding and using data in growing collections of file folders became time-consuming and cumbersome Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel 18  1 Conversion from Manual File System to Computer File System Could be technically complex, requiring hiring of data processing (DP) specialists DP specialists created file structures, wrote software, and designed application programs Resulted in numerous “home-grown” systems being created Initially, computer files were similar in design to manual files Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel 19 Components of a File 1 Management Information Systems Managing the Digital Firm 9th Edition 20  1 Example of Early Database Design DP specialist wrote programs for reports: – Monthly summaries of types and amounts of insurance sold by agents – Monthly reports about which customers should be contacted for renewal – Reports that analyzed ratios of insurance types sold by agent – Customer contact letters summarizing coverage Additional reports were written as required Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel 21  1 Example of Early Database Design (continued) Other departments requested databases be written for them – SALES database created for sales department – AGENT database created for personnel department Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel 22  1 Evolution of Simple File System As number of databases increased, small file system evolved Each file used its own application programs Each file was owned by individual or department who commissioned its creation Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel 23  File-based System 1 Each of the files in the systems used its own application programs to store, retrieve and modify data Each file was owned by the department that created it. Management Information Systems Managing the Digital Firm 9th Edition 24  1 Problems with File System Data Management Every task requires extensive programming in a third-generation language (3GL) – Programmer must specify task and how it must be done Modern databases use fourth-generation language (4GL) – Allows user to specify what must be done without specifying how it is to be done Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel 25  1 Programming in 3GL Time-consuming, high-level activity Programmer must be familiar with physical file structure As system becomes complex, access paths become difficult to manage and tend to produce malfunctions Complex coding establishes precise location of files and system components and data characteristics Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel 26 Programming in 3GL (continued) 1 Ad hoc queries are impossible Writing programs to design new reports is time consuming As number of files increases, system administration becomes difficult Making changes in existing file structure is difficult File structure changes require modifications in all programs that use data in that file Modifications are likely to produce errors, requiring additional time to “debug” the program Security features hard to program and therefore often omitted Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel 27  1 Structural and Data Dependence Structural dependence – Access to a file depends on its own structure Data dependence – Changes in database structure affect program’s ability to access data – Logical data format How a human being views the data – Physical data format How the computer “sees” the data Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel 28  1 Data Redundancy Data redundancy results in data inconsistency – Different and conflicting versions of the same data appear in different places Errors more likely to occur when complex entries are made in several different files and recur frequently in one or more files Data anomalies develop when required changes in redundant data are not made successfully Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel 29  1 Data Anomalies Unable to perform certain data maintenance due to errors in database design Modification anomalies Occur when changes must be made to existing records Insertion anomalies Occur when entering new records Deletion anomalies Occur when deleting records Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel 30  1 Database vs. File System Problems inherent in file systems make using a database system desirable File system – Many separate and unrelated files Database – Logically related data stored in a single logical data repository Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel 31 Contrasting Database and File Systems 1 Database Systems: Design, Implementation, & Management, 12th Edition, Rob & Coronel 32  1 The Database System Environment Database system is composed of 5 main parts: 1. Hardware 2. Software Operating system software DBMS software Application programs and utility software 3. People 4. Procedures 5. Data Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel 33 The Database System Environment (continued) 1 Database Systems: Design, Implementation, & Management, 12th Edition, Rob & Coronel 34  1 DBMS Functions Performs functions that guarantee integrity and consistency of data – Data dictionary management defines data elements and their relationships – Data storage management stores data and related data entry forms, report definitions, etc. – Data transformation and presentation translates logical requests into commands to physically locate and retrieve the requested data Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel 35  1 DBMS Functions (continued) – Security management enforces user security and data privacy within database – Multi-user access control creates structures that allow multiple users to access the data – Backup and recovery management provides backup and data recovery procedures Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel 36  1 DBMS Functions (continued) – Data integrity management promotes and enforces integrity rules to eliminate data integrity problems – Database access languages and application programming interfaces provides data access through a query language – Database communication interfaces allows database to accept end-user requests within a computer network environment Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel 37  1 Summary Information is derived from data, which is stored in a database To implement and manage a database, use a DBMS Database design defines its structure Good design is important Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel 38  1 Summary (continued) Databases were preceded by file systems Because file systems lack a DBMS, file management becomes difficult as a file system grows DBMS were developed to address file systems’ inherent weaknesses Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel 39  1 Homework – Reading Assignment Physical data storage concepts What are the media and devices? How does it actually work? What are the most common operations on files? Typical files storage methods and its uses How to make access to data faster? How to make storage of data more permanent? Indexing files to facilitate faster records access Reference: Fundamentals of Database Systems, 6th Ed, Elmasri, Navathe - chapters 17,18 40  1 Physical data storage concepts Disk Storage Devices Files of Records Operations on Files Unordered Files Ordered Files Hashed Files – Dynamic and Extendible Hashing Techniques RAID Technology Indexed File Reference: Fundamentals of Database Systems, 6th Ed, Elmasri, Navathe - chapters 17,18 41  1 Operation on Files Typical file operations include: – OPEN: Readies the file for access, and associates a pointer that will refer to a current file record at each point in time. – FIND: Searches for the first file record that satisfies a certain condition, and makes it the current file record. – FINDNEXT: Searches for the next file record (from the current record) that satisfies a certain condition, and makes it the current file record. – READ: Reads the current file record into a program variable. – INSERT: Inserts a new record into the file & makes it the current file record. – DELETE: Removes the current file record from the file, usually by marking the record to indicate that it is no longer valid. – MODIFY: Changes the values of some fields of the current file record. – CLOSE: Terminates access to the file. – REORGANIZE: Reorganizes the file records. For example, the records marked deleted are physically removed from the file or a new organization of the file records is created. – READ_ORDERED: Read the file blocks in order of a specific field of the file. 42