Chapter 5: Database Processing


  • Introduction
    • Data component of IT > database processing, data modeling
  • Q5-1: What is the purpose of a database?
    • Keep track of things
    • List of data involving single theme = spreadsheet
    • List that involve data with multiple themes = require database
  • Q5-2: What is a database?
    • Database - collection of integrated records that are self-describing / maintain records / organize & keep track
      • Byte - single character of data
      • Columns (fields)
      • Rows (records)
      • Table (file) - groups of similar rows or records
    • Database - collection of tables/files + relationships among rows + special data (metadata) that describes structure of the database
      • 5 components: form > table > query > report > relationship
      • Input info into form > stored in table (columns & rows) > answer = report > tables joined together through relationships
    • Relationship Among Rows
      • Value in one table relate rows of that table to rows in a second table
      • Key (primary key) - column/group of columns identifying a unique row in a table / uniquely identifies person, object, etc.
      • Foreign key - key of a different (foreign) table; e.g. "student number" in email / office visits table > key for student table / when you pull out primary key from one table for another table to create a relationship
      • Relational databases - databases that represent relationships using foreign keys and carry their data in the form of tables
      • Normalization - breaking down big (poorly structured) table into smaller (well-structured) ones
    • Metadata
      • Database contains a description of its contents within itself; e.g. library has a catalog describing its contents
      • Metadata - data that describes data / structure of the database
      • Transposing - unintentionally making a mistake of entering incorrect info
      • Input mask - make type certain way / restrictions > only letters / numbers
  • Q5-3: What is a Database Management System (DBMS)?
    • Database Management System (DBMS) - software program for creating, processing, and administering a database; usually licensed
      • Note: database vs. DBMS
      • DB2 (IBM) / Access & SQL Server (Microsoft) / Oracle Database (Oracle Corp)
      • MySQL - open source DBMS that is license-free for most applications
    • Creating the database and its structures
      • Database developers use DBMS to create tables, relationships, and other structures > can modify existing table (add row) / create new one
    • Processing the database
      • DBMS provides apps for four processes: read, insert, modify, or delete data
      • Structured Query Language (SQL) - standard, international language for database processing 
        • Need to have access, source, fields, and condition
    • Administering the database
      • Wide variety of activities: set up security system, backing up database data, adding structures, removing unwanted data, etc.
      • Development / operation / backup and recovery / adaptation
  • Q5-4: How do database applications make databases more useful?
    • Database applications - collection of queries, forms, reports, and application programs; serves as intermediary between database data & users
    • Reformats database table > more informative and more easily updated
    • Four elements of database apps & their functions:
      • Forms - view data / update or delete existing data / insert new data
      • Reports - sorting, grouping, filtering, etc. to structure presentation of data
      • Queries - search based on user-provided data values 
      • Application programs - provide special purpose processing (out-of-stock situations), data consistency, and security 
    • Traditional forms, queries, reports, and applications 
      • Users > Users' Computers (^) > Database Servers (DBMS & database)
    • Browser forms, queries, reports, and applications
      • Graphical queries - query criteria created when user clicks on a graphic
    • Multi-user processing - multiple users processing the same database
      • Lost-update problem - e.g. when two people try to buy the same pair of tickets, both sees as available, but only one can check out > locking must be used
  • Q5-5: How are data models used for database development?
    • Database modeling & database design
    • Data model - blueprint / logical representation of database constructed by developers before building the database
      • Describes data and relationships that will be stored in the database
      • Forms, reports, queries > data model (entities and relationships) > database design (tables with foreign keys) > create database
    • What is the entity-relationship data model? 
      • Entity-relationship (E-R) data model - tool for constructing data model; defines entities that will be stored in the database + relationship amongst them
      • Unified Modeling Language (UML) - less popular tool for data modeling
    • Entities 
      • Entity - thing that users want to track (always singular); physical object / logical construct or transaction
        • Order / Customer / Salesperson
      • Attributes - description of characteristics of the entity
        • Order number / order date / subtotal
      • Identifier - attribute/group of attributes associated with only one entity instance
        • Order number > Order / SalespersonName > Salesperson
    • Relationships 
      • Relationships - Student > Advisor > Department 
      • Entity-Relationship (E-R) diagrams - entities are rectangle boxes tied together through lines
      • Crow's Feet - e.g. department may have more than one advisor; shorthand for multiple lines
      • One to one relationship - husband > wife
      • 1:N (one-to-many relationships) - e.g. department > many advisors, but advisor > one department at most // mother or father > children
      • N:M (many-to-many relationships) - e.g. advisor can have many students > one student can have many advisors 
      • Crow's-foot diagram - a version/style of entity-relationship diagram
      • Maximum cardinality - max number of entities that could be involved in a relationship, shown by crow's foot notation
      • Minimum cardinality - constraints on minimum requirements/number of entities required in relationship
        • Advisor has to have at least one student to advise
  • Q5-6: How is a data model transformed into a database design?
    • Database design - process of converting data model into tables, relationships, and data constraints
    • Normalization - conversion of poorly structured table into 2/+ well-structured tables
      • Data integrity problem - e.g. name change isn't correctly made in all rows; hard to spot in databases with millions of rows (Amazon)
        • Incorrect / inconsistent info > lose confidence of users in info > poor reputation for system
  • Q5-7: How can Falcon Security Benefit from a Database System?
    • Quickly query for videos that fit their needs > narrow down topics & criteria
  • Q5-8: 2026?
    • ACID (atomic, consistent, isolated, durable) transactions > either all of a transaction processed or none of it is (atomic)
    • NoSQL DBMS - NotRelational DBMS > new DBMS products supporting very high transaction rates processing relatively simple data structures 
    • NewSQL DBMS - also high levels of transactions, but provide ACID support
    • In-memory DBMS - process databased in main memory
    • Cheap unlimited storage > security more important