Chapter 1: Database Systems PDF

CHAPTER 1 : DATABASE SYSTEMS By Nik Nahdiya Nik Kamaruzaman  Overview Common uses of database systems Introducing the The difference between data and information Database The meaning of a database, various types...

CHAPTER 1 : DATABASE SYSTEMS By Nik Nahdiya Nik Kamaruzaman  Overview Common uses of database systems Introducing the The difference between data and information Database The meaning of a database, various types of database. Database Management System (DBMS) Roles and Advantages of DBMS The importance of database design Why database design in Structural dependence & Data dependence. important Data redundancy Evolution of File System Data Processing File System Problems with File System Data Processing Database System Environment Database Systems Database Management System (DBMS) functions Preparing for Database Professional Career Common Uses of Database System Example 1 When you purchase goods at the local supermarket, a database is accessed The checkout assistant uses a bar code reader to scan each of your purchases The reader is linked to a database application that uses the bar code to find out price of the item from the product database The application then reduces the number of such items in stock and display the price on the cash register. Bar code Linked to a Database Purchase reader scan database to reduce number goods purchases display price of items Common Uses of Database System Example 2 If you are at the University there will be a database system containing information about: yourself the courses you are enrolled in details about your financial aid the classes you have taken in previous years or are taking this year details of all your examination result  Introducing the Database Data Good decisions require good information derived from raw facts Data is managed most efficiently when stored in a database Understanding file system characteristics is important Database Databases evolved from computer file systems Databases solve many of the problems encountered in data management Used in almost all modern settings involving data management: Business, Research and Administration The difference between Data & Information Data Information raw facts (means the data that have been facts that have not yet process (the result of been processed to reveal processing raw data) to their meaning). reveal the meaning. The difference between Data & Information Data Information Building blocks of information Used to reveal meaning in data Requires context to reveal More accurate, relevant, timely meaning information Must be formatted for storage, As a key to a good decision processing and presentation making which lead to Foundation of information organizational survival (bedrock of knowledge)  Introducing the Database Database : shared, integrated computer structure that stores a collection of: End user ○ End-user data: raw facts of interest data to end user ○ Metadata: data about data Provides description of data characteristics and set of Database relationships that link the data Complements and expands value of data Metadata  Database Management System (DBMS) Database management system (DBMS) is a collection of programs that manages structure and controls access to data DBMS is the intermediary between the user and the database Database structure stored as file collection Can only access files through the DBMS  Roles and Advantages of DBMS Minimized data Improved data sharing inconsistency DBMS enables data to be shared same data appear in different places users have better access to more & with different versions better-managed data can be reduced in a properly designed database Better data integration Improved data access wider access to well-managed data it is possible to produce quick answer promotes an integrated view. to ad hoc queries DBMS integrates many users’ views Query (specific request for data of the data manipulation) Ad hoc query (spur of moment question / quick answers to questions)  Roles and Advantages of DBMS Improved decision Increased end-user making productivity better decision, quality information availability of data, transformation to are based from better-managed and usable information will produce improved data access. better productivity. Improve data security more user access the data, the greater the risk. it provides a framework for better enforcement of data privacy Roles and Advantages of DBMS  Types of Database 01 02 03 Number of users Database Locations Expected use / data type 04 05 06 Analytical Database Degree of data XML  Types of Database 1. Classified by the number of users Single-user: ○ Supports only one user at a time ○ Desktop database : Single-user database running on a personal computer Multi-user: ○ Supports multiple users at the same time ○ Workgroup database : Multi-user database that supports a small group of users or a single department (50 usually hundreds)  Types of Database 2. Classified by the database location Centralized database: ○ Supports data located at a single site Distributed database: ○ Supports data distributed across several sites Cloud database: ○ created and maintained using cloud data services that provide defined performance measures for the database 3. Classified by data type: Operational (Transactional) ○ Supports a company’s day-to-day operations General-purpose database: ○ contains a wide variety of data used in multiple disciplines Discipline-specific database: ○ contains data focused on specific subject areas  Types of Database 4. Analytical database: stores historical data and business metrics used exclusively for tactical or strategic decision making Data warehouse: ○ stores data in a format optimized for decision support Online analytical processing (OLAP): ○ tools for retrieving, processing, and modeling data from the data warehouse Business intelligence: ○ captures and processes business data to generate information that support decision making  Types of Database 5. Databases can be classified to reflect the degree to which the data is structured Unstructured data exists in its original (raw) state Structured data results from formatting ○ Structure is applied based on type of processing to be performed Semi structured data: processed to some extent 6. Extensible Markup Language (XML) Represents data elements in textual format  Why Database Design is Important? Database design focuses on design of database structure used for end-user data Designer must identify database’s expected use Well-designed database: ○ Facilitates data management ○ Generates accurate and valuable information Poorly designed database: ○ Causes difficult-to-trace errors  Evolution of File System Data Processing File systems typically composed of collection of file folders, each tagged and kept in cabinet Organized by expected use Contents of each file folder are logically related Manual systems Served as a data repository for small data collections Cumbersome for large collections Evolution of File System Data Processing File system limitations / Problems with File System : ○ Requires extensive programming ○ Cannot perform ad hoc queries ○ System administration is complex and difficult ○ Difficult to make changes to existing structures ○ Security features are likely to be inadequate To convert from manual file system to database system can be done by Data Processing Specialist (DP). Data processing (DP) specialist converted computer file structure from manual system Wrote software that managed the data Designed the application programs Evolution of File System Data Processing Evolution of File System Data Processing Structural & Data Dependence Structural dependence Access to a file depends on its structure Changes in the file structure such as adding or deleting fields requires modification of all programs using that file e.g. adding customer date-of-birth field to CUSTOMER file require changes in all application programs using data from that file Structural independence change file structure without affecting data access Structural & Data Dependence Data dependence Changes in the data storage characteristics affects the application program’s ability to access the data e.g. changing field type from integer to decimal, requires changes in all the programs that access the file. Data independence data storage characteristics do not affect data access  Data Redundancy  File system structure makes it difficult to combine data from multiple sources  Organizational structure promotes storage of same data in different locations  Data stored in different locations is unlikely to be updated consistently  Data redundancy: same data stored unnecessarily in different places  Uncontrolled data redundancy sets the stage for:  Data Inconsistency - exist when different and conflicting version of the same data appear in different places  Referred as lack data integrity.  Data Anomalies - mean abnormality  It happens when all of the required changes in the redundant data are not made successfully  Data Redundancy  Ideally a field value change should be made only at single place.  There are 3 types of data anomalies :  Update Anomalies  Insertion Anomalies  Deletion Anomalies  Database Systems  Database system consists of logically related data stored in a single logical data repository  May be physically distributed among multiple storage facilities  DBMS eliminates most of file system’s problems  Current generation stores data structures, relationships between structures, and access paths  Also defines, stores, and manages all access paths and components  Database Systems Environment  Database system: defines and regulates the collection, storage, management, use of data  Database systems are created and managed at different levels of complexity  Five major parts of a database system: Hardware Software People Procedure Data Database Systems Environment  Database Systems Environment  1. Hardware: all the system’s physical devices  2. Software: three types of software required:  Operating system software  DBMS software  Application programs and utility software  3. People: all users of the database system  System and database administrators  Database designers  Systems analysts and programmers  End users  4. Procedures: instructions and rules that govern the design and use of the database system  5. Data: the collection of facts stored in the database  DBMS Functions Data Dictionary Data Storage Security Management Management Management DBMS stores definitions of DBMS creates and manages DBMS creates a security data elements and complex structures required system that enforces user relationships (metadata) in a for data storage security and data privacy data dictionary Also stores related data entry Security rules determine DBMS looks up required data forms, screen definitions, which users can access the component structures and report definitions, etc database, which items can be relationships accessed, etc.  DBMS Functions Multiuser Access Backup and Recovery Data Integrity Control Management Management DBMS uses sophisticated DBMS provides backup and DBMS promotes and algorithms to ensure data recovery to ensure data enforces integrity rules that concurrent access does not safety and integrity minimizes redundancy and affect integrity Recovery management deals maximizes consistency with recovery of database Data relationships stored in after a failure data dictionary used to Critical to preserving enforce data integrity database’s integrity Preparing for Database Professional Career DATABASE CAREER OPPORTUNITIES JOB TITLE DESCRIPTION SAMPLE SKILLS REQUIRED Database Developer Create and maintain database-based Programming, database fundamentals, SQL applications Database Designer Design and maintain databases Systems design, database design, SQL Database Manage and maintain DBMS and Database fundamentals, SQL, vendor courses Administrator databases Database Analyst Develop databases for decision support QL, query optimization, data warehouses reporting Database Architect Design and implementation of database DBMS fundamentals, data modeling, SQL, environments (conceptual, logical, and hardware knowledge, etc. physical) Database Consultant Help companies leverage database Database fundamentals, data modeling, technologies to improve business database design, SQL, DBMS, hardware, processes and achieve specific goals vendor-specific technologies, etc. Q&A Discussion