Database Systems Overview

Study Notes

Databases and Database Users

A database is an organized data collection, structured for easy access, management, and updates.
Database systems offer advantages over file systems, including reduced redundancy, data integrity, and concurrent access.
Database Administrators (DBA) are responsible for overall database administration, ensuring functionality and security.
Database Designers create the database schema, determining data organization.
End Users interact with the database through applications.

Database System Concepts and Architecture

Database Management Systems (DBMS) use a three-tier architecture:
- Physical Level: Describes data storage on disk.
- Logical Level: Describes stored data and relationships.
- View Level: Defines user interaction with data.
Schema defines the database structure during design.
Data Independence allows schema changes without affecting other levels:
- Physical Independence: Changes to logical schema don't impact physical schema.
- Logical Independence: Changes to view schema don't impact logical schema.

Data Modeling Using Entity-Relationship Model

The Entity-Relationship (ER) Model models database structure.
Entities represent real-world objects, and attributes describe their characteristics.
Relationships define how entities connect.
Cardinality types:
- 1:1: One entity relates to only one other entity.
- 1:N: One entity can relate to many other entities.
- N:M: Many entities can be related to many other entities.
A Primary Key uniquely identifies each entity.
A Foreign Key references a primary key in another table.

The Relational Data Model and Relational Database Constraints

The relational data model organizes information in tables (relations), where each row is a tuple and each column is an attribute.
Relational constraints ensure data integrity:
- Key Constraint: Primary key values must be unique.
- Referential Integrity: Foreign key values must reference valid tuples.
- Domain Constraint: Limits attribute value ranges.

Basic SQL

SQL (Structured Query Language) is the standard for interacting with relational databases.
SQL commands are categorized:
- DML (Data Manipulation Language):
  - SELECT: Retrieves data from a table.
  - INSERT: Adds new records to a table.
  - UPDATE: Modifies existing data.
  - DELETE: Removes records.
- DCL (Data Control Language): Controls database permissions.
- DDL (Data Definition Language): Defines database structure.
  - Example: CREATE TABLE.

Disk Storage, Basic File Structures, Hashing, and Modern Storage Architectures

Database data is stored on hard drives.
Access time is crucial, relying on latency and transfer rate.
Basic file structures:
- Sequential data
- Indexed files
- Hash files
Hashing quickly finds data by assigning a key to a memory address.
Modern storage architectures improve data access efficiency.
SSD (solid-state drives) provide faster access compared to traditional hard drives.

Indexing Structures for Files and Physical Database

Indexes improve access speed to table rows.
An index can be created on one or more columns.
Types of Indexes:
- Hash Indexes: Use a hash function to distribute data evenly across memory.
- B+ trees: Balanced tree structure for efficient searches, inserts, and deletions, ideal for range searches.
- Composite Index: An index using multiple columns for optimized queries with multiple search conditions.

Description

Explore the fundamental concepts of database systems, including the roles of Database Administrators, designers, and end users. Understand the three-tier architecture of Database Management Systems, and learn about data independence and schema design principles. This quiz will test your knowledge on key database concepts and their functionalities.