Concise Summary of Database Concepts and SQL

Basics of Databases

  • Definition of a Database: A structured collection of related information.

  • File-Based System: A system of files and application programs manipulating them.

  • Limitations:

    • Difficulties in querying.

    • Data redundancy and dependency issues.

    • Slow development and high maintenance costs.

Components of a Database System

  • Components:

  • Users

  • Database Application

  • DBMS (Database Management System)

  • Database

  • Roles:

  • Database Administrator (DBA): Manages database resources.

  • Developers: Create applications interfacing with databases.

  • End-users: Interact with databases through applications.

Database Management System (DBMS)

  • Functionality:

  • Stores, retrieves, and updates data.

  • Provides a logical view of data.

  • Supports concurrency control and recovery services.

  • Advantages:

  • Reduces data duplication.

  • Simplifies data retrieval and management.

  • Enhances data security.

  • Disadvantages:

  • Complexity and high cost.

Database Structure

  • Normalization: Process to eliminate redundancy in databases designed to meet the Third Normal Form (3NF).

  • Normal Forms: 0NF, 1NF, 2NF, 3NF.

  • Keys: Unique identifiers for records in tables (e.g., primary and concatenated keys).

  • Relationships: Defined connections between tables (e.g., one-to-many relationships).

Entity-Relationship Model

  • E-R Model: Consists of entities, relationships, and attributes, visualized using E-R diagrams.

  • Functional Dependencies: Rules between attributes where one attribute uniquely determines another.

SQL (Structured Query Language)

  • Purpose: Language for managing relational databases.

  • Basic Statements:

  • SELECT, INSERT, UPDATE, DELETE for data manipulation.

  • Aggregate functions: COUNT, SUM, AVG, MIN, MAX for summary calculations.

  • Grouping Data: Use of GROUP BY for aggregation results.

  • Joins: Facilitate retrieval of related data across multiple tables.

  • SQL Example: Queries to fetch and manipulate data from tables; e.g., counting employees per branch.

Summary of SQL Queries

  • SELECT Statement Examples:

  • SELECT * FROM Staff;

  • SELECT COUNT(*) FROM Staff WHERE salary > 10000;

  • INSERT INTO Staff VALUES (‘SG16’, ‘Alan’, ‘Brown’, ‘Assistant’, ‘M’, Date‘1957-05-25’, 8300, ‘B003’);

  • UPDATE Staff SET position = ‘Manager’, salary = 18000 WHERE staffNo = ‘SG14’;

This concise summary covers basic concepts of databases, their structure, functions of DBMS, and introductory SQL queries.