Database Theory.pdf

28 Mar 2023 Chapter 05: Organizing and Storing...

28 Mar 2023 Chapter 05: Organizing and Storing Principles Data Data management and modelling are key aspects of organizing data and information A well-designed and well-managed database is central to almost all information systems and is an extremely valuable tool in supporting decision- making The number and type of database applications will continue to evolve and yield real business benefits For use with Principles of Business Information Systems, 4e Stair, Reynolds and Chesney: Principles of Business Information Systems, Fourth edition (9781473774605) by Stair, Reynolds & Chesney © Cengage Learning 2021 © 2021 Cengage Learning Database Relational database The centre of almost every information system A relational database is made up of a number of tables A table is made up of a number of records The most common database is the relational Each record is made up of a number of fields database In loose terms, each table stores the data about someone A relational database is a series of related tables, or something of interest to the firm, known as an entity. stored together with a minimum of duplication to Customer is an example achieve consistent and controlled pool of data The fields are the specific items of data stored about an entity (name, data of birth, address, etc.) A record collects all the data about one specific entity, the customer John Smith for example For use with Principles of Business Information Systems, 4e For use with Principles of Business Information Systems, 4e by Stair, Reynolds & Chesney by Stair, Reynolds & Chesney © 2021 Cengage Learning © 2021 Cengage Learning 1  28 Mar 2023 Hierarchy of Data (1) An Example of Hierarchy of Data » Record: a collection of related data fields » File: a collection of related records » Database: a collection of integrated and related files » Hierarchy of data: bits, characters, fields, records, files, and databases For use with Principles of Business Information Systems, 4e For use with Principles of Business Information Systems, 4e by Stair, Reynolds & Chesney by Stair, Reynolds & Chesney © 2021 Cengage Learning © 2021 Cengage Learning Data Entities, Attributes, and Keys Keys and Attributes » Entity: a person, place, or thing for which data is collected, stored, and maintained » Attribute: a characteristic of an entity » Data item: the specific value of an attribute » Primary key: a field or set of fields that uniquely identifies the record For use with Principles of Business Information Systems, 4e For use with Principles of Business Information Systems, 4e by Stair, Reynolds & Chesney by Stair, Reynolds & Chesney © 2021 Cengage Learning © 2021 Cengage Learning 2  28 Mar 2023 Primary Keys Customer Table » Primary key - A field in a table that is unique – each record in that table has a different value in the primary key field. » The primary key is used to uniquely identify each record and to create relationships between tables. » Student ID, Exam ID and Library ID are all primary keys that uniquely identify you Primary keys are used to join tables: ‘Post’ a primary key into another table to join the two (see Figure 5.1 and 5.2) For use with Principles of Business Information Systems, 4e For use with Principles of Business Information Systems, 4e by Stair, Reynolds & Chesney by Stair, Reynolds & Chesney © 2021 Cengage Learning © 2021 Cengage Learning Order Table Relationships Between Tables » Foreign key - When a primary key is posted into another table to create a relationship between the two, it is known as a foreign key. » Referential integrity - means you cannot have an instance of a foreign key before it exists as an instance of a primary key. » Creating relationships prevents the data from being stored in both tables (i.e. data redundancy), thereby reducing costs For use with Principles of Business Information Systems, 4e For use with Principles of Business Information Systems, 4e by Stair, Reynolds & Chesney by Stair, Reynolds & Chesney © 2021 Cengage Learning © 2021 Cengage Learning 3  28 Mar 2023 Linking Data Tables to Answer an Relational Database Model Inquiry For use with Principles of Business Information Systems, 4e For use with Principles of Business Information Systems, 4e by Stair, Reynolds & Chesney by Stair, Reynolds & Chesney © 2021 Cengage Learning © 2021 Cengage Learning Advantages of the Database Disadvantages of the Database Approach Approach 1. Improved strategic use of corporate data 1. More complexity - DBMS can be difficult to set 2. Reduced data redundancy up and operate. Many decisions must be made 3. Improved data integrity correctly for the DBMS to work effectively. 4. Easier modification and updating 2. More difficult to recover from a failure - With 5. Data and program independence a DBMS, a failure can shut down the entire 6. Better access to data and information database. 7. Standardization of data 3. More expensive - DBMS can be more 8. A framework for program development expensive to purchase and operate. 9. Better overall protection of the data 10. Shared data and information resources For use with Principles of Business Information Systems, 4e For use with Principles of Business Information Systems, 4e by Stair, Reynolds & Chesney by Stair, Reynolds & Chesney © 2021 Cengage Learning © 2021 Cengage Learning 4  28 Mar 2023 Designing Relational Databases (1) Designing Relational Databases (2) A database design is also known as a data model One approach to database has 4 stages: or a database schema 1. Identify all entities 2. Identify all relationships between entities 3. Identify all attributes 4. Resolve all relationships It is a list of all the tables in the database, along with all the fields, with any primary and foreign keys identified For use with Principles of Business Information Systems, 4e For use with Principles of Business Information Systems, 4e by Stair, Reynolds & Chesney by Stair, Reynolds & Chesney © 2021 Cengage Learning © 2021 Cengage Learning Designing Relational Databases (3) Designing Relational Databases (4) The first three stages are performed by, for » Once you identify a relationship, there are three example, interviewing staff (managers and users), things you need to document about it: its degree, observing staff at work and reviewing existing cardinality and optionality: documentation » Degree - The number of entities involved in a Relationships between entities are governed by relationship. enterprise (or ‘business’) rules » Cardinality - In a relationship, cardinality is the Resolving a relationship means deciding how to number of one entity that can be related to another implement it – which primary key will be used as a entity. foreign key, or whether to use a linking table (see » Optionality - If a binary relationship is optional for Figure 5.5) an entity, that entity doesn’t have to be related to the other. For use with Principles of Business Information Systems, 4e For use with Principles of Business Information Systems, 4e by Stair, Reynolds & Chesney by Stair, Reynolds & Chesney © 2021 Cengage Learning © 2021 Cengage Learning 5  28 Mar 2023 Entity-Relationship Diagram Types of Binary Relationship (1) Enterprise rules - The rules governing relationships » One-to-one relationship, obligatory on both sides. between entities. Each order must be placed by one and only one » Employee – Passport customer. Each customer can place many orders, but some won’t » Each employee must have one and only one have placed any orders. passport; each passport belongs to one and only one employee. For use with Principles of Business Information Systems, 4e For use with Principles of Business Information Systems, 4e by Stair, Reynolds & Chesney by Stair, Reynolds & Chesney © 2021 Cengage Learning © 2021 Cengage Learning Types of Binary Relationship (2) Types of Binary Relationship (3) » One-to-one relationship, optional on one side. » One-to-one relationship, optional on both sides. » Employee – Company car » Employee – Laptop » Each employee might have zero or only one » Each employee might have one laptop; each company car; each company car is allocated to laptop might belong to one employee (but some one and only one employee. are for general use and therefore won’t belong to anyone For use with Principles of Business Information Systems, 4e For use with Principles of Business Information Systems, 4e by Stair, Reynolds & Chesney by Stair, Reynolds & Chesney © 2021 Cengage Learning © 2021 Cengage Learning 6  28 Mar 2023 Types of Binary Relationship (4) Types of Binary Relationship (5) » One-to-many relationship, many side optional to » One-to-many relationship, many side optional on one side. both sides. » Customer – Order » Student – Elective module » A customer can place many orders; but might » A student might take one elective module; each have placed no orders, each order must be module is taken by many students, but an placed by one and only one customer elective module could be taken by no students. For use with Principles of Business Information Systems, 4e For use with Principles of Business Information Systems, 4e by Stair, Reynolds & Chesney by Stair, Reynolds & Chesney © 2021 Cengage Learning © 2021 Cengage Learning Types of Binary Relationship (6) Database Management Systems » Many-to-many relationship. » A DBMS is a group of programs used as an interface between a database and application » Student – Tutor programs or between a database and the user. » Each tutor teaches one or many students; each » Examples: MS Access, SQL Server, Oracle, student is taught by one or many tutors. MySQL » Capabilities and types of database systems: – Creating and Modifying the Database – Storing and Retrieving Data – Manipulating Data and Generating Reports For use with Principles of Business Information Systems, 4e For use with Principles of Business Information Systems, 4e by Stair, Reynolds & Chesney by Stair, Reynolds & Chesney © 2021 Cengage Learning © 2021 Cengage Learning 7  28 Mar 2023 Creating and Modifying the Database Data dictionary (1) Data definition SQL to define a table Data dictionary - stores meta data, a detailed language (DDL): CREATE DATABASE Lettings; description of all the data used in the database, to collection of instructions achieve the following advantages: and commands used to USE Lettings; – Reduced data redundancy define and describe data CREATE TABLE landlords( – Increased data reliability and relationships in a Firstname CHAR(10), – Assists program development specific database. Surname CHAR(10), Telephone CHAR(10)); – Easier modification of data and information » Structured Query Language (SQL) is a INSERT INTO landlords( ‘John’, ‘Smith’, ‘123456’); DDL. Figure 5.7 SQL as a DDL For use with Principles of Business Information Systems, 4e For use with Principles of Business Information Systems, 4e by Stair, Reynolds & Chesney by Stair, Reynolds & Chesney © 2021 Cengage Learning © 2021 Cengage Learning Storing and Retrieving Data (1) Storing and Retrieving Data (2) One function of a » Two or more people or programs attempting to DBMS is to be an access the same record in the same database at interface between the same time can cause a problem. an application » Concurrency control can be used to avoid this program and the potential problem. One approach is to lock out all database. other application programs from access to a record if the record is being updated or used by another program. For use with Principles of Business Information Systems, 4e For use with Principles of Business Information Systems, 4e by Stair, Reynolds & Chesney by Stair, Reynolds & Chesney © 2021 Cengage Learning © 2021 Cengage Learning 8  28 Mar 2023 SQL to manipulate data Manipulating Data » Data Manipulation Language (DML) - the » Selecting: eliminating rows according to certain commands that are used to manipulate the database criteria – SELECT * FROM EMPLOYEE » Projecting: eliminating columns in a table WHERE JOB_CLASSIFICATION = “C2” » Joining: combining two or more tables » This will output all employees who have a job classification of ‘C2’ » Linking: combining two or more tables through common data attributes to form a new table with The * tells the DBMS to include all columns from the EMPLOYEE table in the results only the unique data attributes For use with Principles of Business Information Systems, 4e For use with Principles of Business Information Systems, 4e by Stair, Reynolds & Chesney by Stair, Reynolds & Chesney © 2021 Cengage Learning © 2021 Cengage Learning Selecting Projecting Original Table Result Table Original Table Result Table SELECT ALL PROJECT PRICE yields yields SELECT only Price < R100 yields PROJECT P_DESCRIPTION and PRICE yields SELECT only P_CODE = 2749274 yields PROJECT P_CODE and PRICE yields For use with Principles of Business Information Systems, 4e For use with Principles of Business Information Systems, 4e by Stair, Reynolds & Chesney by Stair, Reynolds & Chesney © 2021 Cengage Learning © 2021 Cengage Learning 9  28 Mar 2023 Database Output Database Administration (1) After a database has been set up and loaded with data, it » Database administrator (DBA) - the role of the can produce any desired reports database administrator is to plan, design, create, These outputs usually appear in screen displays or hard- copy printouts operate, secure, monitor and maintain databases. The output-control features of a database program allow » Characteristics of a DBA: you to select the records and fields to appear in reports – has a degree in computer science or You can also make calculations specifically for the report management information systems, by manipulating database fields Formatting controls and organization options (such as – works with users to decide the content of the report headings) help you to customise reports and create database flexible, convenient, and powerful information-handling – works with programmers as they build tools applications For use with Principles of Business Information Systems, 4e For use with Principles of Business Information Systems, 4e by Stair, Reynolds & Chesney by Stair, Reynolds & Chesney © 2021 Cengage Learning © 2021 Cengage Learning Database Administration (2) Selecting a DBMS » Data Administrator (DA) - A non-technical Important characteristics of databases to position responsible for defining and consider: implementing consistent principles for a variety of – Database size data issues. – Database cost » Responsibilities of a DA: – Concurrent users – defining and implementing consistent – Performance principles for a variety of data issues – Integration – setting data standards and data definitions – Vendor that apply across all the databases in an organization For use with Principles of Business Information Systems, 4e For use with Principles of Business Information Systems, 4e by Stair, Reynolds & Chesney by Stair, Reynolds & Chesney © 2021 Cengage Learning © 2021 Cengage Learning 10  28 Mar 2023 Database Applications Linking Databases to the Internet Today’s database applications manipulate the Linking databases to the Internet is important for content of a database to produce useful many organizations and people information Semantic Web Common manipulations are searching, filtering, synthesizing, and assimilating the data contained – Developing a seamless integration of traditional in a database using a number of database databases with the Internet applications – Allows people to access and manipulate a number of traditional databases at the same time through the Internet For use with Principles of Business Information Systems, 4e For use with Principles of Business Information Systems, 4e by Stair, Reynolds & Chesney by Stair, Reynolds & Chesney © 2021 Cengage Learning © 2021 Cengage Learning Big Data Data Warehouses (1) » Extremely large and complex data collections Data warehouse: database that collects business information from many sources in the enterprise, – Traditional data management software, covering all aspects of the company’s processes, hardware, and analysis processes are products, and customers. incapable of dealing with them » The data necessary to make sound business decisions is stored in a variety of locations and » Three characteristics of big data: formats. – Volume » This data is initially captured, stored and managed – Velocity by transaction processing systems that are – Variety designed to support the day-to-day operations of the organization. For use with Principles of Business Information Systems, 4e For use with Principles of Business Information Systems, 4e by Stair, Reynolds & Chesney by Stair, Reynolds & Chesney © 2021 Cengage Learning © 2021 Cengage Learning 11  28 Mar 2023 Data Warehouses (2) Data Mining (1) Data mining: information-analysis tool that involves the automated discovery of patterns and relationships in a data warehouse. » Data mining is used extensively in marketing to improve: – customer retention; – identify cross-selling opportunities; – manage marketing campaigns; – market, channel and pricing analysis; and – customer segmentation analysis (especially one- to-one marketing). For use with Principles of Business Information Systems, 4e For use with Principles of Business Information Systems, 4e by Stair, Reynolds & Chesney by Stair, Reynolds & Chesney © 2021 Cengage Learning © 2021 Cengage Learning Data Mining (2) Business Intelligence (1) » Association rules algorithms are used to find Business intelligence (BI): process of associations between items in the data. For gathering enough of the right information in a example, if someone buys eggs, how likely is it timely manner and usable form and analyzing it that they will also buy cheese? to have a positive impact on business strategy, » Data-mining tools help users find answers to tactics, or operations questions they haven’t thought to ask. – Turns data into useful information that is then distributed throughout an enterprise For use with Principles of Business Information Systems, 4e For use with Principles of Business Information Systems, 4e by Stair, Reynolds & Chesney by Stair, Reynolds & Chesney © 2021 Cengage Learning © 2021 Cengage Learning 12  28 Mar 2023 Business Intelligence (2) Distributed databases » Competitive intelligence - One aspect of Distributed database (see Figure 5.12) business knowledge limited to information about – Database in which the data may be spread competitors and the ways that knowledge affects across several smaller databases connected via strategy, tactics and operations. telecommunications devices – Gives corporations more flexibility in how » Counterintelligence - The steps an organization databases are organized and used takes to protect information sought by ‘hostile’ intelligence gatherers. Replicated database – Database that holds a duplicate set of frequently used data For use with Principles of Business Information Systems, 4e For use with Principles of Business Information Systems, 4e by Stair, Reynolds & Chesney by Stair, Reynolds & Chesney © 2021 Cengage Learning © 2021 Cengage Learning Visual, Audio, and Other Database Online Analytical Processing (OLAP) Systems Software that allows users to explore data Databases for storing images from a number of different perspectives Databases for storing sound Virtual database systems: allow different databases to work together as a unified database system Other special-purpose database systems – Spatial data technology: stores and accesses data according to the locations it describes and permits spatial queries and analysis For use with Principles of Business Information Systems, 4e For use with Principles of Business Information Systems, 4e by Stair, Reynolds & Chesney by Stair, Reynolds & Chesney © 2021 Cengage Learning © 2021 Cengage Learning 13