Database Systems - FYBSc Semester 1

Database Systems Overview

• Database systems serve various purposes, including data storage, retrieval, and management.
• Different views of data facilitate multiple representations of stored information.
• Common data models include hierarchical, network, and relational models, with relational models being the most widely used.
• Key language in database management: SQL (Structured Query Language), used for defining and manipulating data.
• Database users include database administrators, programmers, and end-users.

Relational Model Concepts

• A database schema defines the structure of a database, including tables and their relationships.
• Keys such as primary keys and foreign keys are essential for identifying records uniquely and establishing relationships between tables.
• Relational algebra includes fundamental operations like selection, projection, and join.
• Normalization minimizes redundancy and dependency in database design.

SQL Fundamentals

• Data types in SQL include:

  • Numeric Types: Exact and approximate.
  • Character String Types: char, varchar, etc.
  • Unicode: Uses double storage.
  • Binary Types: Store data as hexadecimal.
  • Date and Time: Formats for storing temporal data.
  • Miscellaneous: Includes CLOB for large objects.

• Types of SQL Constraints:

  • NOT NULL: Ensures columns cannot contain null values.
  • UNIQUE: Guarantees that all values in a column are distinct.
  • PRIMARY KEY: A unique identifier for records in a table, combining NOT NULL and UNIQUE.
  • FOREIGN KEY: Links a record in one table to a record in another.
  • CHECK: Validates values against a specified condition.
  • DEFAULT: Assigns a default value if none is provided by the user.

Transaction Management

• Transactions represent a sequence of operations that execute as a single unit of work, ensuring data integrity.
• Key concepts include:
  • Atomicity: Transactions are all-or-nothing.
  • Durability: Completed transactions remain persistent even in case of system failure.
  • Concurrency Control: Manages simultaneous transaction execution.
• Lock-Based Protocols: Used to manage concurrent access to data.
• Timestamp-Based Protocols: Ensure operations follow timestamps for consistency.

Views in SQL

• Views are virtual tables derived from underlying tables, simplifying complex queries.

• Types of views:

  • Simple Views: Single table, no aggregates or functions.
  • Complex Views: Multiple tables, complex queries, often read-only.
  • Indexed Views: Store data physically to improve performance.
  • Partitioned Views: Combine results from multiple tables.

• Updates on Views:

  • Simple views can often be updated directly.
  • Complex views and indexed views may be read-only or have specific update conditions.

Comparison: Tables vs. Views

• Storage: Tables are physical; views are abstract representations.
• Data Source: Tables hold actual data; views derive data through queries.
• Performance: Direct access to tables is faster; views may involve complex queries.
• Security: Views can restrict data access more precisely than tables.

Example SQL Syntax

• Creating a Table:

  CREATE TABLE Student (
      ID int(6) NOT NULL,
      NAME varchar(10) NOT NULL,
      ADDRESS varchar(20)
  );
  

• Creating a View:

  CREATE VIEW EmployeeNames AS
  SELECT FirstName, LastName FROM Employees;
  

Practical Implementation

• Utilize appropriate constraints when designing tables to maintain data integrity.
• Use views to abstract complexity for end-users while maintaining underlying data structure.
• Understand transaction management to ensure data consistency and reliability in multi-user environments.