Chapter 1 Data Management

The Characteristics of Relational Databases

• The purpose of a database is to help users store and track information efficiently. This can be anything from customer records to inventory details.

• There are two main types of databases:

  • Relational Databases – Organize data into tables with rows and columns.

  • Non-Relational Databases – Store data in formats like key-value pairs, graphs, or documents (used in NoSQL systems).

• When datasets become extremely large, they are referred to as Big Data.

• Relational Databases store data in a structured way using tables (similar to spreadsheets).

  • Rows (also called records) represent individual instances of data.

  • Columns (also called fields) store attributes (properties) of each instance.

• A relational database can contain multiple tables, where each table represents a different entity (such as students, courses, or orders).

• Each row represents a unique occurrence of an entity, and each column represents an attribute of that entity.

• The strength of relational databases is that they store both data and relationships between data.

A Database Has Data and Relationships

• Relational databases are structured to store data while maintaining relationships between rows.

• Each row in a table is identified by a Primary Key, which uniquely identifies it.

• Surrogate Key – A unique identifier automatically assigned by the database system, often used as the Primary Key.

• Composite Key – A Primary Key that consists of multiple columns when a single column isn’t sufficient to uniquely identify a record.

• Foreign Key – A field in one table that links to the Primary Key of another table, creating relationships between tables.

Naming Conventions in This Course

• Table Names: Written in ALL CAPITAL LETTERS (e.g., STUDENT, CLASS, GRADE).

• Column Names: First letter capitalized, and compound names use capital letters for each word (e.g., Term, Section, ClassNumber).

Databases Produce Information

• Data = Raw, recorded facts and figures (e.g., student grades, course numbers).

• Information = Data that has been processed and given meaning (e.g., a GPA calculated from grades).

• Databases store data in a way that allows users to generate meaningful information through processes like summarization, sorting, and calculations.

  • Example: A database storing STUDENT, CLASS, and GRADE data can be used to generate GPA reports.

Database Applications

• Multiuser Database Applications allow multiple users to access and interact with the database simultaneously.

  • Customer Relationship Management (CRM) – Tracks customer interactions and helps businesses manage relationships.

  • Enterprise Resource Planning (ERP) – Integrates data across different departments (finance, HR, supply chain).

• E-commerce companies use databases to track web activity and improve marketing strategies.

• Reporting & Data Mining Applications do not create new data but analyze existing data for business insights and future predictions.

Components of a Database System

A Database System consists of four main components:

1. Users – People who interact with the database (e.g., employees, customers).

2. Database Application – Programs that help users interact with the database.

3. Database Management System (DBMS) – Software that processes and manages the database.

4. Database – The structured collection of data itself.

• The DBMS (Database Management System) is responsible for creating, processing, and administering the database.

• Structured Query Language (SQL) is the standard language used for interacting with relational databases.

• A database is self-describing, meaning it contains metadata (data about data).

Basic Functions of Database Application Programs

• Create and process forms for data entry.

• Allow users to run SQL queries.

• Generate reports for analysis.

• Execute application logic.

• Control application behavior.

Functions of a DBMS

• Create the database and tables.

• Modify data (insert, update, delete).

• Read and retrieve data.

• Enforce constraints to ensure data integrity.

• Control concurrency (handling multiple users accessing data simultaneously).

• Perform backup and recovery to prevent data loss.

The Database

• A Database is a self-describing collection of integrated tables.

• Tables are integrated because they store relationships between rows.

• A database is self-describing because it contains metadata, which provides information about its structure.

NoSQL Databases

• NoSQL ("Not Only SQL") refers to databases that do not use the traditional relational model.

• They are designed for Big Data applications, like Facebook and Twitter, where massive amounts of unstructured data need to be processed quickly.