RDBMS Overview and Advantages

Relational Database Management Systems (RDBMS)

• A database is an organized collection of structured information, typically stored electronically.
• File-based systems are used to maintain data files, but they are not very efficient.
• Limitations of file-based systems include inability to store large amounts of data, redundant data, lack of security, and difficulty sharing data among multiple users.
• Database Management Systems (DBMS) are software systems designed to manage and organize data.
• RDBMS store data in tables, enabling efficient data access, integrity, and security, as well as concurrent access and crash recovery.
  • Examples include Oracle, SQL Server, MySQL, MS Access, DB2.

Advantages of DBMS

• Efficient data access using various techniques for storage and retrieval.
• Data integrity and security enforced by DBMS.
• Concurrent access allowing multiple users to use the database simultaneously.
• Crash recovery enabling restoration of the database in case of system failure.

Disadvantages of DBMS

• Size: DBMS software is large, occupying significant disk space.
• Performance: Performance may not be as fast as desired.

Keys in DBMS

• Keys are attributes or sets of attributes to identify a specific row (tuple) in a table.
  • Candidate Key: Minimal set of attributes that uniquely identify a tuple. A relation can have more than one candidate key.
  • Primary Key: A selected candidate key to uniquely identify each row in a table. A relation cannot have more than one primary key. It must be unique and cannot contain NULL values.
  • Alternate Key: A candidate key other than the primary key.
  • Composite Key (Composite Primary Key): Created by combining two or more columns to uniquely identify each row.
  • Super Key: A group of single or multiple keys that uniquely identify rows in a table.

Types of SQL Commands

• DDL (Data Definition Language): Used to define the structure of a database.
  • CREATE TABLE - Creates a table structure.
  • ALTER TABLE - Modifies an existing table (add/drop/modify columns).
  • DROP TABLE - Deletes a table.
  • TRUNCATE TABLE - Deletes all rows from a table but maintains the table structure.
• DML (Data Manipulation Language): Used to manipulate data within a database.
  • INSERT INTO - Inserts data into a table.
  • UPDATE - Modifies existing data.
  • DELETE - Removes data from a table.

Data Query Language (DQL)

• Fetching data from a database.
  • SELECT - Views data from tables based on specific conditions (e.g., WHERE clause).

Data Control Language (DCL)

• Managing user permissions and privileges.
  • GRANT - Grants access privileges.
  • REVOKE - Revokes access privileges.

Transaction Control Language (TCL)

• Managing transactions for database operations.
  • COMMIT - Saves all transactions.
  • ROLLBACK - Reverses any uncommitted transactions.
  • SAVEPOINT - Creates a temporary checkpoint for transactions to be rolled back to.

Clauses in SQL

• WHERE: Retrieves specific data based on conditions.
• ORDER BY: Sorts records based on specified criteria.
• GROUP BY: Groups rows with the same values in the specified columns.
• HAVING: Filters grouped results

SQL Functions (Single row)

• UPPER(): Converts string to uppercase
• LOWER(): Converts string to lowercase
• CONCAT(): Concatenates strings
• LENGTH(): Returns length of a string
• SUBSTR(): Extracts a portion of a string

SQL Functions (Aggregate)

• COUNT(): Counts the number of rows in a table.
• SUM(): Calculates the sum of numeric values.
• AVG(): Computes the average of numeric values.
• MAX(): Finds the maximum value.
• MIN(): Finds the minimum value.

Data Types in SQL

• CHAR(Size): Fixed-length character string.
• VARCHAR(Size): Variable-length character string.
• INT(size): Integer value.
• DECIMAL(size, d): Fixed-point number.
• DATE: Date format.

This quiz covers the essentials of Relational Database Management Systems (RDBMS), detailing the structure, benefits, and examples of DBMS. It highlights the limitations of file-based systems and the efficiency of RDBMS in managing data with integrity, security, and concurrent access.