Introduction to Database Systems

I. File Systems
 Method of organizing and retrieving files from storage mediums (e.g., hard drives, flash drives).
 Disadvantages:
o Lengthy development times for data retrieval tasks.
o Slow report generation and query processing.
o Complex system administration as file numbers expands.
o Security issues and data sharing risks.
o Extensive programming required, leading to high costs and restricted access.

II. Database Systems

 Organized components for the collection, storage, management, and use of data in a database
environment.

Database
 Metadata
o Self-describing data with characteristics and relationships.
 End-user Data
o Raw facts relevant to users.

Five Major Parts of a Database System:

1. Hardware
o Physical devices (e.g., computers, peripherals).
2. Software:
o OS Software - Manages hardware components.
o DBMS Software - Manages the database.
o Application Programs - Access and manipulate data.
3. Peopleware
o Users with specific privileges
1. System Administrators – Oversee the general operations of the database system.
2. Database Administrators (DBAs) – Manages the use and functionality of the
database system.
3. Database Designers and Architects – Designs the structure of the database.
4. System Analyst and Programmers – Design and implement the application programs.
5. End Users – Uses the application programs.
4. Procedures
o Rules governing the database system's design and use.
5. Data
o Collection of information, relationships, and characteristics.

III. Database Models

 Define logical data design for storage and manipulation.
 Provides a representation of the infrastructure offered by a particular database system.

Two Categories of Database Models:

1. Conceptual Model
 Focuses on what is represented.
2. Implementation Model
 Focuses on how data is represented.

Building Blocks of a Database Model:

 Entities
o About which data are to be collected and stored.
o Objects of interest (e.g., person, place).
 Attributes
o Defines and/or important characteristics of an entity
o Characteristics of entities (e.g., student number).
 Relationships
o Associations among entities (e.g., student-teacher).
o Types: One-to-One, One-to-Many, Many-to-Many.
 Constraints
o Rules ensuring data integrity.
Types of Database Models:

 Hierarchical Model
o Used to manage large amounts of data.
o Tree-like structure for managing data.
 Network Model
o Complex relationships, allows multiple parents.
 Relational Model
o Data in two-dimensional tables (rows and columns).
 Entity-Relationship Model (E-R Model)
o Graphical representation of entities and relationships.
 Object-Oriented Model
o Stores data and relationships in objects.

IV. Database Management Systems (DBMS)

 Manages database structure and controls data access.

Functions of a DBMS:

 Data dictionary management.

 Data storage management.
 Data transformation and presentation.
 Security management.
 Access control for multiple users.
 Backup and recovery.
 Data integrity management.
 Data access via query language.

V. Data Dictionaries
 Also called as Metadata Repository.
 Centralized repositories of information about data (metadata).

Two Main Types:

1. Integrated Data Dictionaries

 Part of the DBMS.
2. Standalone Data Dictionaries
 Separate from the DBMS.

Two Classifications:

1. Active Data Dictionaries

 Automatically updated by the DBMS.
2. Passive Data Dictionaries
 Require processing for updates.

Introduction to SQL
I. What is SQL?
 SQL (Structured Query Language) is a database sublanguage used for querying, updating, and
managing relational databases.
 Originated from IBM's Structured English Query Language (SEQUEL) in the 1970s.
 Can be used for interactive queries or embedded in applications for data handling.
 Designed for usability by both technical and non-technical users.

A. Major Components of SQL

1. Data Manipulation Language (DML)

 Allows retrieval, updating, adding, or deleting data in a database.
2. Data Definition Language (DDL)
 Enables the creation and modification of database structures.
3. Data Control Language (DCL)
 Manages security and access controls for the database.

II. Data Definitions

A. Database Manipulation

1. CREATE DATABASE
 Statement used to create a new database.
 2. DROP DATABASE
 Statement used to delete an existing database.

B. Database Table Manipulation

1. CREATE TABLE
 Statement used to create a new table within a database.
2. ALTER TABLE
 Allows modification of table attributes, including adding or deleting columns.
3. DROP TABLE
 Statement used to delete a specified table from the database.

III. Basic Data Retrieval

A. Basic SQL Expression Clauses

 SELECT Clause
o Lists the attributes to be retrieved in the query result.
 FROM Clause
o Specifies the relations (tables) to be scanned for the query.
 WHERE Clause
o Contains predicates involving attributes from the relations listed in the FROM clause.

Basic Data Operations in SQL

I. Summarizing Data
A. What is Aggregation?

 Aggregation refers to methods of summarizing data by combining it into groups.

 Transforms raw data into meaningful information, providing context and patterns relevant for
analysis.

II. Eliminating Duplicates

 Prevent redundancy in query results.
 Use the DISTINCT keyword to remove duplicate rows from the output, placed immediately after the
SELECT keyword.

III. Aggregate Functions

A. Aggregate Function Keywords

 SUM
o Adds values of a specified column.
 AVG
o Calculates the average of values in a specified column.
 MIN
o Finds and returns the lowest value in a column.
 MAX
o Finds and returns the highest value in a column.
 COUNT
o Counts and returns the number of records in a column.

Database Table Manipulation

I. INSERT INTO Statement
 Used to insert new data entries into a database table.
 Forms:
1. Without Column Names
 Simply lists the values to be inserted into their respective fields.
2. With Column Names
 Specifies the columns along with the corresponding values to be inserted.

II. UPDATE Statement

 Updates existing data within a database table.
 The SET clause specifies which field to update and the new value to assign.

III. DELETE Statement

 Deletes rows or data entries from a table.
 Utilizes the WHERE clause to identify which rows to delete.
 Omitting the WHERE clause will result in the deletion of all records in the table.
 Caution is advised, as this action cannot be undone once executed.