Database and Information Management Chapter 7 PDF

Database and Information Management Chapter 7 Computer Application in Management Database 1 and Information Management...

Database and Information Management Chapter 7 Computer Application in Management Database 1 and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia © 2017 by Universiti Utara Malaysia  Learning Objectives ▪ After completing this chapter you will be able to:- ▪ Introduce the basic concepts of database. What are the problems of managing data resources in a traditional file environment? ▪ Explain database system model. What are the major capabilities of database management systems (DBM S), and why is a relational DBMS so powerful? ▪ Explain the concept of database management system. What are the principal tools and technologies for accessing information from databases to improve business performance and decision making? Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 2  Introduction ▪ In IT, data is the most important asset to possess because without it, organizations would cease to function. ▪ It is data that ensures that every system and every process within the organization functions at an optimal level. ▪ Therefore, in all IS, data resources must be organized and structured in logical manner so that they can be accessed easily, processed efficiently, retrieved quickly, and managed effectively. ▪ These data can be managed by using data management. Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 3  Introduction (cont…) ▪ Data management comprises all the disciplines related to managing data as a valuable resource. ▪ An organized collection of data is called a database. ▪ Digital databases are managed using database management systems (DBMS), which store database contents, allowing data creation and maintenance, search and other access. Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 4  The Database System Environment Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel Database and Information Management 5 Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia  Databases, Data, and Information Database Data Information Collection of Collection of Processed data unprocessed data organized in items Organized a manner Text Meaningful that allows Numbers Useful access, retrieval, and Images use of that Audio data Video Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia  Databases, Data, and Information Database and Information Management Management Foundation 2017/2018 7 (DBMS) © 2017 by Universiti Utara Malaysia  Databases, Data, and Information ▪Database software, often called a database management system (DBMS), allows users to: Create a computerized Add, modify, and database delete data Create forms and Sort and retrieve data reports from the data Database and Information Management Management Foundation 2017/2018 8 (DBMS) © 2017 by Universiti Utara Malaysia  Databases, Data, and Information ▪ A database is a logically organized collection of related data designed and built for a specific purpose. ▪ Data is stored hierarchically for easier storage and retrieval. ▪ File (table): collection of related records ▪ Records (row): collections of related fields ▪ Field (column): unit of data containing 1 or more characters ▪ Character [Byte]: a letter number or special character made of bits ▪ Bit: 0 or 1 9 ▪ Database is a Collection of integrated data Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia  Figure 6.1 The Data Hierarchy Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia  The Hierarchy of Data Bit Byte Field Record File + Metadata + Character Index + Database Application metadata Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 11  Databases, Data, and Information ▪ Data is organized in levels ▪ Characters, fields records, and files (Database contains files, file contains records, record contains fields, field contains characters, characters are represented by bytes and bits) Database and Information Management Management Foundation 2017/2018 12 (DBMS) © 2017 by Universiti Utara Malaysia  Databases, Data, and Information ▪ A character is one byte ▪ Numbers, letters, space, punctuation marks, or other symbols ▪ A field is a combination of one or more related characters ▪ Field name ▪ Field size ▪ Data type Database and Information Management Management Foundation 2017/2018 13 (DBMS) © 2017 by Universiti Utara Malaysia  Data Type A classification identifying one of various types of data Data Description Text letters, numbers, or special characters (alphanumeric) Numeric numbers only AutoNumber unique number automatically assigned to each new record Currency dollar and cent amounts or numbers containing decimal values Date month, day, year, and sometimes time Long Text lengthy text entries Yes/No only the values Yes or No (or True or False) Hyperlink Web address that links to document or Web page Attachment OLE Object (Object Linking and Embedding) or BLOB (Binary Large Object) - photograph, audio, video, or document created in other application such as word processing or spreadsheet. Database and Information Management Management Foundation 2017/2018 14 (DBMS) © 2017 by Universiti Utara Malaysia  Databases, Data, and Information ▪ A record is a group of related fields ▪ A primary key (key field) is a field that uniquely identifies each record ▪ A data file is a collection of related records Database and Information Management Management Foundation 2017/2018 15 (DBMS) © 2017 by Universiti Utara Malaysia  Primary Key ▪ Uniquely identifies each record. ▪ Cannot be repeated. ▪ Can be an auto number. ▪ i.e. student id, i/c number, staff id, reference no. Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 16  File / Table ▪ A file is a collection of related records. ▪ If there are 100 employees, then each employee would have a record (e.g. called Employee Personal Details record) and the collection of 100 such records would constitute a file (in this case, called Employee Personal Details file). ▪ Files are integrated into a database. This is done using a Database Management System. Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 17  Traditional Filing System ▪ A file system is a method for storing and organizing files and the data they contain to make it easy to find and access them. ▪ Traditional file based system is basically a file based system, in which we manually handle the files and data such as updating, insertion, deletion, adding new files to database. ▪ i.e. In our own home, we probably have some sort of filing system, which contains receipts, guarantees, invoices, bank statements, and such like. When we need to look something up, we go to the filing system and search through the system starting from the first entry until we find what we want. Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 18  File Processing Systems and Databases File processing system File processing system Database approach Database approach Data are organized, stored, and Database is a Collection of processed in independent files. integrated data (Logically Each department or area within related files and records) organization has own set of Many programs and users can files. share data in database. Each business application Secures data so only authorized designed to use specialized users can access certain data data files containing specific types of data records. Records in one file may not relate to records in any other file. Database and Information Management Management Foundation 2017/2018 19 (DBMS) © 2017 by Universiti Utara Malaysia  File-based Systems HEA Sistem Kuliah Fail Kuliah Data Pensyarah Data Dewan Kuliah Data Pelajar HEP Sistem Aktitvi Fail Aktiviti Data Aktiviti Pelajar Data Pelajar Bendahari Sistem Biasiswa Fail Data Pelajar Biasiswa Data Penaja Each program defines and manages its own data Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 20  Database System HEA Sistem Kuliah Data Dewan Kuliah Data Pensyarah HEP Sistem Aktitvi DBMS Pelajar Data Pelajar Data Aktiviti Bendahari Sistem Biasiswa Data Penaja Many programs and users can share data in database. Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 21  File Processing Systems and Databases How do a database application and a file processing application differ in the way they store data? Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia  File Processing Systems Vs Databases File processing system Database approach Each department has its Programs and users share own set of files. Files data maintained separately. Reduced data redundancy Redundant data Improved data integrity Isolated data Shared data Data inconsistency Easier access Lack of flexibility Reduced development Poor security time Lack of data sharing and availability Database and Information Management Management Foundation 2017/2018 23 (DBMS) © 2017 by Universiti Utara Malaysia  DATA MODEL / DATABASE STRUCTURE Computer Application in Management Database 24 and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia © 2017 by Universiti Utara Malaysia  Data Model A database model determines the information a database will contain and how it will be used and how the items in the database relate to one another. Five common data models Hierarchical database Network database Relational database Multidimensional database Object-oriented database Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 25  Hierarchical Database ▪ Fields or records structured in nodes ▪ Nodes ▪ points connected like branches of an upside-down tree ▪ One parent per node ▪ Parent can have several child nodes ▪ One-to-many relationship Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia  Network Database ▪ Hierarchical node arrangement ▪ Each child node may have more than one parent node (many-to- many relationship) ▪ Pointers ▪ Additional connections between parent and child ▪ Nodes can be reached through multiple paths Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia  Relational Database ▪ More flexible ▪ Data stored in table called a relation ▪ Tables consist of rows and columns ▪ Tables related via a common data item / key field Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia  Relational Database ▪ Most widely used structure ▪ Stores data in tables that consist of rows and columns ▪ Each row (record) has primary key ▪ Each column (column) has unique name ▪ Each table contains data on entity and attributes ▪ Stores data relationships. ▪ Can relate data in one file with data in another, if both files share a common data element. ▪ Uses specialized terminology: Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 29  Relational Database Tables Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia  Operations of a Relational D B MS ▪ Three basic operations used to develop useful sets of data ▪ SELECT ▪ Creates subset of data of all records that meet stated criteria ▪ JOIN ▪ Combines relational tables to provide user with more information than available in individual tables ▪ PROJECT ▪ Creates subset of columns in table, creating tables with only the information specified Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia  Figure 6.5 The Three Basic Operations of a Relational D B MS Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia  Multidimensional Database ▪ Models data as facts, dimensions, or numerical answers for use in the interactive analysis of large amounts of data for decision-making purposes ▪ Allows users to ask questions in colloquial language Good for representing complex relationships ▪ Advantages over relational ▪ Conceptualization ▪ Processing speed Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia  Object-Oriented Database ▪ Objects contain both data and instructions ▪ This model is a multimedia database ▪ Types include web (hypertext) database and hypermedia database, which also includes links ▪ Organize using ▪ Objects ▪ Classes ▪ Attributes ▪ Methods Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia  Databases and the Web ▪ Many companies use the web to make some internal databases available to customers or partners ▪ Typical configuration includes: ▪ Web server ▪ Application server/middleware/CGI scripts ▪ Database server (hosting DBMS) ▪ Advantages of using the web for database access: ▪ Ease of use of browser software ▪ Web interface requires few or no changes to database ▪ Inexpensive to add web interface to system Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia  DATABASE DEVELOPMENET Computer Application in Management Database 36 and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia McGraw-Hill/Irwin © 2017 by Universiti Utara Malaysia  Database Development ▪ Database Administrator (DBA) ▪ In charge of enterprise database development. ▪ Improves the integrity and security of organizational databases. ▪ Uses Data Definition Language (DDL) to develop and specify data contents, relationships, and structure. ▪ Stores these specifications in a data dictionary or a metadata repository. Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 37  Data Dictionary ▪ Known as metadata ⚫ Contains data about each file in database and repository, a each field within those files. "centralized repository of information about data such as meaning, relationships to other data, origin, usage, and format. Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 38  Database Development Process ▪ Database development is a top-down process. Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 39  1. Data Planning ▪ Develop an enterprise model that defines the basic business process of the enterprise. ▪ Output:- Database Development Process ▪ Enterprise model of business process and documentation Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 40  2. Requirement Specification ▪ Define the information needs of end users in a business process. ▪ Output:- Database Development Process ▪ Descriptions of users’ needs may be represented in natural language or using the tools of a particular design methodology Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 41  3. Conceptual Design ▪ An abstract model of a database from the user or business perspective. ▪ A map of concepts and their relationships. ▪ Expresses all information requirements in the form of a Database Development Process high level model. ▪ It describes the things of significance to an organization (entity classes), about which it is inclined/influence to collect information, and characteristics of (attributes) and associations between pairs of those things of significance (relationships). ▪ Example: Entity-relationship modeling Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 42  Conceptual Data Model: Example ▪ Purpose: keep track of customers, orders and books. Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 43  Conceptual Design (cont…) ▪ Entity-relationship modeling: The process of designing a database by organizing data entities to be used and identifying the relationships among them. ▪ Entity-relationship (ER) diagram: Document that shows data entities and attributes and relationships among them. Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 44  Entity- relationship diagram model Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 45  Entity Relationship Diagram Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 46  Entity Relationship Diagram ▪ Entity classes: A grouping of entities of a given type. ▪ Instance: A particular entity within an entity class. Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 47  CUSTOMER: The Entity Class and Two Entity Instances Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 48  Entity Relationship Diagram ▪ Attribute: A property that describes some aspect of entity. ▪ i.e. attributes describes an entity’s characteristics. Sex Programme Religion Residential Hall Contact No Name Place of Birth Student ID Date of Birth Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 49  Entity Relationship Diagram ▪ Identifier: An attribute that identifies an entity instance. Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 50  Entity Relationship Diagram ▪ Relationships: The conceptual linking of entities in a database. ▪ The number of entities in a relationship is the degree of the relationship. Relationships between two items are common and are called binary relationships. Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 51  Relationship ▪ Connection within data; link tables sharing the common field. Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 52  Binary Relationships ▪ There are three types of binary relationships: ▪ 1:1 (one-to-one) relationship: a single-entity instance of one type is related to a single-entity instance of another type. ▪ 1: M (one-to-many) relationship: a single-entity instance of one type is related to many-entity instance of another type. ▪ M:M (many-to-many) relationship: a single-entity instance of one type is related to many-entity of another type and vice versa. Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 53  Entity- relationship diagram model Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 54  4. Logical Design ▪ Translates the conceptual models into the data model of a DBMS. ▪ The user’s view of the data and the software programs that process that data in a database management system. Database Development Process ▪ Output is logical data models, i.e. ▪ Relational, network, hierarchical, object-oriented or multidimensional models. Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 55  5. Physical Design ▪ Determines the data storage structures and access methods. ▪ How data are to be physically stored and accessed on storage devices? ▪ Output is physical data models storage representation Database Development Process and access methods. ▪ Layout that shows how a database is actually arranged on storage devices. ▪ The plan for the actual, physical arrangement and location of data in the direct access storage devices (DASDs) of a database management system. Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 56  Logical and Physical Database Views Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 57  CONCEPT OF DATABASE MANAGEMENT SYSTEM (DBMS) Computer Application in Management Database 58 and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia McGraw-Hill/Irwin © 2017 by Universiti Utara Malaysia  Database Management System (DBMS) ▪ Software that enables users to store, modify, and extract information from a database ▪ The software interface between users (applications) and databases (physical data files). ▪ Separates logical and physical views of data Database Manufacturer Computer Type Access Microsoft Corporation Personal computer, server, PDA DB2 IBM Corporation Personal computer, midrange server, mainframe Informix IBM Corporation Personal computer, midrange server, mainframe Ingres Computer Associates Personal computer, midrange International, Inc. server, mainframe Oracle Oracle Corporation Personal computer, midrange server, mainframe, PDA SQL Server Microsoft Corporation Server Sybase Sybase Inc. Personal computer, midrange server, PDA Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 59  Database Management System (DBMS) 3 Principal Database Components or capabilities of DBMS: ▪ Data Dictionary ▪ Repository that stores the data definitions and descriptions of the structure of the data and the database ▪ DBMS Utilities ▪ Programs that allow you to maintain the database by creating, editing, deleting data, records, and files ▪ Also include automated backup and recovery ▪ Report Generator ▪ Program for producing on-screen or printed readable documents from all or part of a database 60 Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia  Database Management System (DBMS) ▪ A data dictionary contains data about each file in the database and each field in those files Database and Information Management Management Foundation 2017/2018 61 (DBMS) © 2017 by Universiti Utara Malaysia  Database Management System (DBMS) ▪ A DBMS provides several tools that allow users and programs to retrieve and maintain data in the database Query language Query by example Form Report writer Database and Information Management Management Foundation 2017/2018 62 (DBMS) © 2017 by Universiti Utara Malaysia  Database Management System (DBMS) ▪ A query is a request for specific data from the database ▪ A query language consists of simple, English-like statements that allow users to specify the data to display, print, store, update, or delete ▪ Structured Query Language (SQL) is a popular query language that allows users to manage, update, and retrieve data SQL statement SQL statement results Database and Information Management Management Foundation 2017/2018 63 (DBMS) © 2017 by Universiti Utara Malaysia  Database Management System (DBMS) ▪ Most DBMSs include query by example (QBE), a feature that has a graphical user interface to assist users with retrieving data Database and Information Management Management Foundation 2017/2018 64 (DBMS) © 2017 by Universiti Utara Malaysia  Database Management System (DBMS) ▪ A form is a window on the screen that provides areas for entering or modifying data in a database ▪ A report writer allows users to design a report on the screen, retrieve data into the report design, and then display or print the report Database and Information Management Management Foundation 2017/2018 65 (DBMS) © 2017 by Universiti Utara Malaysia  EXAMPLE DATABASE MANAGEMENT SYSTEM (DBMS) Management Foundation 2017/2018 Database 66 Management System (DBMS) Management Foundation 2017/2018 © 2017 by Universiti Utara Malaysia McGraw-Hill/Irwin © 2017 by Universiti Utara Malaysia  MS Access 2013 ▪ Microsoft Access 2013 is a database creation and management program. ▪ MS Access 2013 is a relational model database that is commonly used in commercial database. ▪ Saving a database in MS Access 2013 is done at the beginning of the database. ▪ MS Access files extension: ▪ MS Access 97 – 2003 ~ *.mdb ▪ MS Access 2007 – 2013 ~ *.accdb Database Management System (DBMS) Management Foundation 2017/2018 67 5-67 © 2017 by Universiti Utara Malaysia  MS Access 2013 Objects ▪ Databases in Access are composed of four objects: tables, queries, forms, and reports. ▪ Other objects include: macros and modules Database Management System (DBMS) Management Foundation 2017/2018 68 5-68 © 2017 by Universiti Utara Malaysia  MS Access 2013 Tables ▪ All data in Access is stored in tables, which puts tables at the heart of any database. ▪ Tables are organized into vertical columns and horizontal rows. ▪ In Access, rows and columns are referred to as records and fields. Database Management System (DBMS) Management Foundation 2017/2018 69 5-69 © 2017 by Universiti Utara Malaysia  MS Access 2013 Forms ▪ Forms are used for entering, modifying, and viewing records. ▪ Forms are used to guide people into entering data correctly. ▪ Forms allow to both add data to tables and view data that already exists. Database Management System (DBMS) Management Foundation 2017/2018 70 5-70 © 2017 by Universiti Utara Malaysia  MS Access 2013 Queries ▪ Queries are a way of searching for and compiling data from one or more tables. ▪ It contains: ▪ A question about the database ▪ An answer to the question ▪ Selected records answering the query Database Management System (DBMS) Management Foundation 2017/2018 71 © 2017 by Universiti Utara Malaysia  MS Access 2013 Reports ▪ Reports offer the ability to present your data in print. ▪ MS Access offers the ability to create a report from any table or query. Database Management System (DBMS) Management Foundation 2017/2018 72 5-72 © 2017 by Universiti Utara Malaysia  MS Access 2013 Objects ▪ Every piece of data a query, form, or report uses is stored in one of your database tables ▪ Forms allow to both add data to tables and view data that already exists. ▪ Reports present data from tables and also from queries which, in turn, search for and analyze data within those same tables. Database Management System (DBMS) Management Foundation 2017/2018 73 5-73 © 2017 by Universiti Utara Malaysia  MS Access 2013 Objects (cont…) ▪ When conducted the search, you were entering your search terms into a form that, in turn, created and ran a query based on your request. ▪ When the query finished searching the database's tables for records that matched your search, you were shown a report that drew information from the query and the related tables. Database Management System (DBMS) Management Foundation 2017/2018 74 5-74 © 2017 by Universiti Utara Malaysia  MS Access 2013 Objects (cont…) Database Management System (DBMS) Management Foundation 2017/2018 75 5-75 © 2017 by Universiti Utara Malaysia  Database Management System (DBMS) The End Computer Application in Management Database 76 and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia © 2017 by Universiti Utara Malaysia  What is a Database? Collection of data Database software organized so Database software also called database you can access, allows you to management system retrieve, and (DBMS) use it Create Create forms database and Add, Sort reports change, and and delete retrieve data data Database and Information Management Management Foundation 2017/2018 (DBMS) © 2017 by Universiti Utara Malaysia 77