DBMS Architecture Overview

DBMS Architecture

• DBMS Architecture represents the design of a Database Management System.
• Composed of three main types: Single Tier, Two Tier, and Three Tier Architecture.

Single Tier Architecture

• All components (client, server, database) reside on the same machine.
• Simplifies the system as it does not require intermediate communication.

Two Tier Architecture

• Client application communicates directly with the database.
• Allows multiple clients to connect to a single database without an intermediary.

Three Tier Architecture

• Includes a client, a server, and a database, enabling indirect communication.
• Clients do not communicate directly with the database but through a server.
• Utilizes Structured Query Language (SQL) for communication.

ANSI-SPARC Architecture

• Refers to the Three Schema Architecture.
• Implemented by the American National Standards Institute (ANSI) and the Standards Planning and Requirements Committee (SPARC).

SQL Commands

• SQL commands are instructions for communicating with a database.
• Commands are categorized into Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), Transaction Control Language (TCL), and Data Query Language (DQL).

Data Definition Language (DDL)

• Creates, alters, and removes database objects.
• Key Commands:
  • CREATE: Used for creating tables or databases.
  • ALTER: Used to modify existing tables.
  • DROP: Permanently deletes tables or databases.
  • TRUNCATE: Removes all records from a table without deleting the structure.

Data Manipulation Language (DML)

• Manages data in existing databases but does not change the structure.
• Key Commands:
  • INSERT: Adds new records to a table.
  • UPDATE: Modifies existing records.
  • DELETE: Removes records from a table.

Data Control Language (DCL)

• Manages user permissions.
• Key Commands:
  • GRANT: Assigns access privileges to users.
  • REVOKE: Removes access privileges from users.

Transaction Control Language (TCL)

• Controls transactions within a database.
• Key Commands:
  • COMMIT: Saves all changes made in a transaction.
  • ROLLBACK: Undoes changes made in the transaction.
  • SAVEPOINT: Sets a point within a transaction to which you can later roll back.

Database Constraints

• Rules that enforce limits on data in a table.
• Examples of constraints:
  • NOT NULL: Ensures a field cannot be empty.
  • UNIQUE: Guarantees all values in a column are different.
  • DEFAULT: Sets a default value for a column if no value is provided.
  • AUTO INCREMENT: Automatically generates a unique value for new records.

Keys in Database Management

• Different types of keys used to uniquely identify records:
  • Super Key: A combination of attributes that can uniquely identify a tuple.
  • Candidate Key: A minimal super key.
  • Primary Key: A selected candidate key used to uniquely identify records.
  • Foreign Key: An attribute linked to the primary key of another table.

Difference between DDL and DML

• DDL: Focuses on database structure creation and modification.
• DML: Responsible for managing and manipulating existing data within the tables.

Example SQL Operations

• Inserting new records:
  • INSERT INTO student_details (stud_id, f_name, l_name, nic, address, age, subject, marks) VALUES ('S001', 'Sandun', 'Perera', '957414152V', '50,1st lane, kottawa', 28, 'Maths', 85);
• Updating records:
  • UPDATE student_details SET marks = '95' WHERE stud_id = 'S001';
• Deleting records:
  • DELETE FROM student_details WHERE stud_id = 'S010';
• Querying specific data:
  • SELECT f_name, l_name, nic, age FROM student_details WHERE stud_id = 'S002';