CSC Chapter 9 Notes

Chapter 9: Database Systems

9.1 Database Fundamentals

A database is a collection of data that is multi-dimensional in the sense that internal links between its entries make the information accessible from a variety of perspectives.

9.2 Database Management Systems (DBMS)

  • Database Management System (DBMS): A software layer that manipulates a database in response to requests from applications.

  • Distributed Database: A database stored on multiple machines. The DBMS will mask this organizational detail from its users.

  • Data Independence: The ability to change the organization of a database without changing the application software that uses it.

9.3 Reasons for Using Databases

  • Storing Data: To retain important information for future use.

  • Retrieving Data: This involves queries, which are questions answered by accessing the data in a database.

  • Editing: Involves adding, deleting, or changing data.

  • Organizing Data: Structuring information to enhance accessibility.

  • Distributing Data and Information: Allowing data to be shared across different locations.

  • Analyzing Data: Also known as data mining and is significant in the era of big data.

9.4 Database Examples

  • General-purpose DBMS: A database maintained with efficient software that provides flexibility and security.

  • Special-purpose Database Application: An interface designed for specific uses, with tailored capabilities, permissions, and restrictions.

Types of Databases

  1. Directories: E.g., telephone and email directories.

  2. Economic Databases: Related to the economic state of regions, countries, etc.

  3. Financial Databases: Used for storing financial data and transactions.

  4. E-commerce: Databases specifically for online shopping environments.

  5. Sports: Tracking performance, statistics, etc.

  6. Literary and Media Databases: Cataloging books, articles, multimedia elements.

  7. Social Networks: Databases storing user profiles and interactions.

  8. Personal Information Managers: Software that keeps track of personal data and tasks.

Database Terminology

  • Entity: A person, place, thing, event, or condition about which data and information are collected.

  • Attribute: A category of data or information that describes an entity, representing a fact about it.

  • Relationships: Logical connections between entities. For example:
      - A course can have multiple sections.
      - A student can register for multiple sections.
      - A customer may make multiple orders.

  • Records: A specific detail of an individual entity stored in a database, also known as a tuple.

Files, Records, and Fields

  • File (also referred to as Table or Relation): Describes an entity.

  • Records (Tuples): Specific instances of an entity.

  • Fields (Attributes): Associated with data types defining data structure.

  • Schema: The formal definition of a table structure.

  • Primary Key: An attribute guaranteed to have a unique value.

  • Foreign Key: An attribute that creates a link between two tables by referencing the primary key of another table.

9.5 Database Design: E-R Model

  • Entity-Relationship Model: This model expresses entities and how they relate.

  • Relationships are designated by foreign keys that link different tables.

9.6 Accessing Database Data

  • Database Query Engine: Facilitates user access to database data via queries.

9.7 Query Languages

  • Query languages, like programming languages, have specific syntax and are more user-friendly. The standard query language is:
      - SQL (Structured Query Language): Often pronounced as 'sequel'. It is widely used for managing and manipulating relational databases.
      - QBE (Query by Example): This language allows users to create queries based on example criteria.

SQL and Scripting Languages

  • SQL queries can be embedded within programming languages, particularly scripting languages such as PHP, enabling larger tasks to incorporate database access. An example is data-driven web applications.

Query Construction

  • Queries are structured to retrieve data from specified fields in indicated tables, requiring results to satisfy specific search constraints. Boolean operators (logical AND, OR) facilitate constructing multiple search queries.

SQL Query Examples

SQL Query triggers:

SELECT
FROM
WHERE
AND
OR
MONTH(date)

If something asks for the total of something,

SUM(    )
COUNT(*)

  • Basic Queries:
      - SELECT name FROM Customers;
      - SELECT * FROM Customers;
      - SELECT name, phone FROM Customers ORDER BY name;

  • Complex Queries:
      - SELECT * FROM Orders ORDER BY custnum, date;

SQL WHERE Clause Operators

  • Operators include:
      - = (equality)
      - < (less than)
      - > (greater than)
      - <= (less than or equal)
      - >= (greater than or equal)
      - <> (inequality)
      - BETWEEN (for values within a range)
      - IS NULL (for null values)

SQL Practice: Example Queries

  • List the last names of all customers in alphabetical order.

  • List order numbers and dates of orders from April.

  • Identify product names costing more than $50.

  • Select names and product numbers for products priced between $20 and $50.

Summary Functions in SQL

Functions allow retrieving information about data columns:

  • AVG(): Returns the average value of a column's data.

  • COUNT(): Returns the number of rows for a column.

  • MAX(): Returns the maximum value of a column.

  • MIN(): Returns the minimum value of a column.

  • SUM(): Returns the total sum of a column's data.

9.8 Data Mining

  • Data Mining: The process of using computer programs to find patterns or relationships within data. Utilized in applications such as:
      - Detecting fraud, determining purchasing recommendations, etc.

  • Machine Learning: Used to apply algorithms that learn from data, leading to various types of analyses: classification learning, numeric estimation, association learning, clustering, etc.

9.9 Evaluating and Ethical Implications of Data Mining

Finding Patterns in Data

  • Humans have always classified patterns. Data mining automates this, allowing for deeper analysis of data.

Comparison: Data Mining vs. Database Queries

  • Database Queries: Extract factual information ("shallow knowledge").

  • Data Mining: Extracts patterns and hidden knowledge.

Ethical Concerns

  • Unfair Discrimination: Algorithms based on past human decisions can result in bias against demographic groups.

  • Reinforcing Biases: Using historical data without context can lead to repeated mistakes.

  • Lack of Transparency: Algorithms can lack explanation for their outcomes, resembling a “black box.”

  • Privacy: Issues regarding data collection and individual's consent remain significant.