Database Management Systems Quiz

Database Management Systems (DBMS)

• Entity: A real-world object that needs to be represented in a database. An example is a person, a car, or a book.
• Entity Set: A group of entities of the same type.
• Purpose of DBMS: To manage and organize data efficiently, providing features for storing, retrieving, and manipulating data.
• SQL DDL Commands:
  • CREATE TABLE: Defines the structure of a new table. Syntax: CREATE TABLE table_name (column_name data_type, ...);
  • ALTER TABLE: modifies the structure of an existing table. Syntax: ALTER TABLE table_name ADD column_name data_type;
  • DROP TABLE: Deletes a table and all its data. Syntax: DROP TABLE table_name;
  • CREATE INDEX: Creates an index to speed up data retrieval. Syntax: CREATE INDEX index_name ON table_name (column_name);
  • DROP INDEX: Deletes an index. Syntax: DROP INDEX index_name;
  • TRUNCATE TABLE: Removes all rows from a table but keeps the table structure. Syntax: TRUNCATE TABLE table_name;
• Instance: A specific instance of a database at a given point in time.
• Schema: The logical structure of a database, defining the tables, columns, and relationships, without any actual data.
• Role of Database Manager:
  • Design and Development: Create and modify database structure to meet user requirements.
  • Security and Access Control: Implement security measures to protect sensitive data.
  • Performance Monitoring and Optimization: Ensure the database performs efficiently and effectively.
• Character Handling Functions in SQL:
  • UPPER(): Converts text to uppercase.
  • LOWER(): Converts text to lowercase.
  • SUBSTR(): Extracts a specific substring from a string.
• Purpose of SYSDATE() and ROUND() in SQL:
  • SYSDATE(): Returns the current date and time.
  • ROUND(): Rounds a numeric value to a specified number of decimal places.
• Duties of a Database Administrator (DBA):
  • Database Design and Implementation: Create and maintain the database structure to meet user needs.
  • Security and Access Control: Manage user privileges and ensure data security.
  • Performance Monitoring and Tuning: Analyze and optimize database performance, troubleshooting and resolving issues.
• Attribute: A characteristic or property of an entity. Examples: Name, Age, Address.
• Entity: A distinguishable object in the real world.
• DDL Statements (Data Definition Language):
  • CREATE: Creates tables, indexes, views, etc.
  • ALTER: Alters the structure of database objects.
  • DROP: Deletes database objects.
  • TRUNCATE: Removes all rows from a table.
  • COMMENT: Adds comments to database objects.
• Difference between TRUNCATE and DELETE:
  • Both remove data but differently.
  • TRUNCATE: Faster, removes all rows but keeps the structure, cannot be rolled back.
  • DELETE: Removes specific rows based on conditions, can be rolled back, and takes more time especially with large datasets.

Database System Structure

• Database Users: Interact with the database through applications.
• Database Applications: Software that allows users to access and manipulate database data.
• Database Management System (DBMS): Software responsible for managing the database.
• Database: A collection of related data stored and organized for efficient access.
• Storage Manager: Manages the physical storage of data on the disk.

Relational Database Management Systems (RDBMS)

• Codd's Twelve Rules: Set of principles defining what constitutes a true relational database management system.
• Normalization: Process of organizing data in a database to reduce data redundancy and improve data integrity. It follows normal forms like:
  • First Normal Form (1NF): Eliminates repeating groups of data.
  • Second Normal Form (2NF): Must be in 1NF and all non-key attributes must depend on the entire primary key.
  • Third Normal Form (3NF): Must be in 2NF and all non-key attributes must depend only on the primary key, not other non-key attributes.
  • Boyce-Codd Normal Form (BCNF): Must be in 3NF and all determinants must be candidate keys.
  • Fourth Normal Form (4NF): Must be in 3NF and all multi-valued dependencies must be eliminated.
  • Fifth Normal Form (5NF): Must be in 4NF and all join dependencies must be eliminated.

SQL Transaction Control

• Transaction: A logical unit of work that performs one or more operations on the database.
• COMMIT: Makes all changes made within a transaction permanent.
• ROLLBACK: Reverts all changes made within a transaction.
• SAVEPOINT: Sets a marker point within a transaction to allow reversible rollback to that point.
• SET TRANSACTION ISOLATION LEVEL: Controls the degree of isolation between transactions.

SQL Data Types

• VARCHAR: Variable-length character string. Example: VARCHAR(255) allows a string up to 255 characters.
• CHAR: Fixed-length character string. Example: CHAR(10) always stores a 10-character string, padding with spaces if necessary.
• NUMBER: Represents numeric values. Example: NUMBER(10,2) allows a 10-digit number with 2 decimal places.
• DATE: Represents date values in the format YYYY-MM-DD.
• TIMESTAMP: Represents date and time values in the format YYYY-MM-DD HH24:MI:SS.

Specialization and Aggregation

• Specialization: Creating a subtype of an existing entity type with additional attributes. Example: Creating a 'Student' entity type as a specialization of the 'Person' entity type.
• Aggregation: Defining a relationship between an entity and a group of other entities. Example: A 'Department' entity type can aggregate a group of 'Employee' entities.

Granting and Revoking Privileges in SQL

• GRANT: Allows users to grant privileges to other users. Syntax: GRANT privilege ON object TO user;
• REVOKE: Removes privileges granted to users. Syntax: REVOKE privilege ON object FROM user;

Subqueries in SQL

• Subquery: A query nested inside another query.
• Purpose: To retrieve data based on the results of another query.
• Syntax: SELECT column_name FROM table_name WHERE condition (SELECT ... FROM ... WHERE ...);

Data Abstraction

• Physical Level: The lowest level of abstraction, defining how data is physically stored on the disk.
• Conceptual Level: The logical level, defining the entities, attributes, and relationships in the database.
• View Level: The highest level of abstraction, providing simplified views of the data for specific user needs.

String Functions in SQL

• LENGTH(): Returns the length of a string.
• CONCAT(): Joins two strings together.
• SUBSTR(): Extracts a substring from a string.
• REPLACE(): Replaces occurrences of one string with another.

Mapping Cardinalities

• Cardinality: Represents the number of instances of one entity that can be related to the number of instances of another entity.
• One-to-One: 1 entity instance related to 1 instance of another entity.
• One-to-Many: 1 entity instance related to multiple instances of another entity.
• Many-to-One: Multiple entity instances related to 1 instance of another entity.
• Many-to-Many: Multiple entity instances related to multiple instances of another entity.

Transaction Control Language (TCL) Statements in SQL

• COMMIT: Makes changes permanent.
• ROLLBACK: Reverts changes to a previous state.
• SAVEPOINT: Sets a marker point within a transaction to allow rollback to that point.
• SET TRANSACTION ISOLATION LEVEL: Specifies the level of isolation between transactions.

PL/SQL (Procedural Language/SQL)

• PL/SQL Block Structure:
  • DECLARE Section: Declares variables and constants.
  • BEGIN Section: Contains the executable statements.
  • EXCEPTION Section: Handles errors and exceptions.
• PL/SQL Features:
  • Procedural: Allows writing code with procedures, functions, and loops.
  • Data Manipulation: Work with data using SQL commands.
  • Error Handling: Control exceptions.
  • Modular: Organizes code into reusable components.
  • Integration with SQL: Seamlessly integrates with SQL queries.
  • Security: Provides features for controlled access and privilege management.

Procedure Subprogram Unit in PL/SQL

• Characteristics:
  • Reusable: Can be called multiple times.
  • Parameter Handling: Accepts input values and can return output values.
  • No Direct Return Values: Typically perform actions but don't return data directly, use OUT parameters if needed.

Input and Output Statements in PL/SQL

• INPUT: IN parameters pass values to the subprogram.
• OUTPUT: OUT parameters return values from the subprogram.

LOOP Statements in PL/SQL

• LOOP: Endless loop until explicitly exited.
• WHILE LOOP: Executes a block of code while a condition is true. Syntax: WHILE condition LOOP ... END LOOP;
• FOR LOOP: Executes a block of code for a specified number of iterations. Syntax: FOR counter IN [start_value .. end_value] LOOP ... END LOOP;
• EXIT: Exits from a loop.

PL/SQL Subprogram

• A self-contained unit of code that performs a specific function or task.

Reversing a Number in PL/SQL

DECLARE
  num NUMBER := 12345;
  rev NUMBER := 0;
  temp NUMBER;
BEGIN
  temp := num;
  WHILE temp > 0 LOOP
    rev := (rev * 10) + (temp MOD 10);
    temp := temp / 10;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Reversed number: ' || rev);
END;
/

• This code takes a number, extracts each digit from right to left, builds the reversed number, and prints the result.

PL/SQL Numeric Data Types

• NUMBER: Stores numeric values, default precision is 38 digits.
• INTEGER: Stores integer values.
• DECIMAL: Stores numeric values with decimal points, specified precision and scale.
• FLOAT: Stores floating-point numbers with variable precision.
• BINARY_FLOAT: Stores floating-point numbers in binary format (IEEE 754 standard) with 32-bit precision.
• BINARY_DOUBLE: Stores floating-point numbers in binary format (IEEE 754 standard) with 64-bit precision.

CASE and GOTO Statements in PL/SQL

• CASE Statement: Provides conditional execution based on different cases. Syntax: CASE expression WHEN value1 THEN ... WHEN value2 THEN ... ELSE ... END CASE;
• GOTO Statement: Transfers program execution to a labeled block of code.
• Important Note: Using GOTO can often lead to less readable and maintainable code. Best practices generally advise against using GOTO when possible.

Variable Scope and Visibility in PL/SQL

• Scope: Defines the region of a program where a variable is accessible and valid.
• Visibility: Determines whether other program units can use the variable.
• Local Variables: Declared within a block and only accessible inside that block.
• Global Variables: Defined outside of all PL/SQL blocks and accessible within the current schema.

Comparison Operators in PL/SQL

• =: Equal to.
• !=: Not equal to.
• >: Greater than.
• <: Less than.
• >=: Greater than or equal to.
• <=: Less than or equal to.

Exceptions in PL/SQL

• Exception: An exceptional condition that disrupts the normal flow of program execution.
• Error Handling: Used to prevent the termination of a program due to errors.

User-Defined Exceptions in PL/SQL

• Purpose: Define custom exceptions for specific error handling scenarios.
• Syntax: DECLARE exception_name EXCEPTION; ... WHEN exception_name THEN ... END;

Implicit and Explicit Cursors

• Implicit Cursor: Automatically created by SQL commands like INSERT, UPDATE, and DELETE.
• Explicit Cursor: Created manually by the programmer to control the retrieval and processing of multiple rows of data. Syntax: DECLARE cursor_name CURSOR FOR SELECT ... FROM ...;

Life Cycle of a Cursor

• Open: Opens the cursor and positions it at the first row.
• Fetch: Fetches a row from the cursor into variables.
• Close: Releases resources associated with the cursor.

Triggers in PL/SQL

• Triggers: Stored PL/SQL procedures that are automatically executed in response to certain events or actions on a database table.
• Advantages:
  • Data Integrity: Enforce data consistency and rules.
  • Auditing: Keep track of changes to data.
  • Business Logic: Enforce business rules.

NoSQL (Not Only SQL)

• MongoDB: A popular NoSQL database that uses a document-oriented data model.
• CAP Theorem: A fundamental theorem that states that a distributed database system can only satisfy two out of three properties: Consistency, Availability, and Partition Tolerance.
• Advantages of MongoDB:
  • Scalability: Can handle large amounts of data.
  • Flexibility: Allows for flexible data structures.
  • High Performance: Designed for fast read and write operations.
  • Ease of Use: Has a simple API and is relatively easy to learn.

NoSQL vs RDBMS

• NoSQL:
  • Document-oriented, key-value, graph, or wide column storage.
  • Focus on scalability, flexibility, and performance.
  • Less strict schema, better for handling unstructured data.
  • Ideal for web applications, social media, and big data analytics.
• RDBMS:
  • Tabular data model with structured rows and columns.
  • Focus on transaction integrity, consistency, and data relationships.
  • Strong schema, well-suited for structured data.
  • Ideal for business applications, financial systems, and inventory management.

CRUD Operations in MongoDB

• Create: insertOne(), insertMany(): Creates documents in a collection.
• Read: findOne(), find(): Retrieves documents from a collection.
• Update: updateOne(), updateMany(): Modifies existing documents.
• Delete: deleteOne(), deleteMany(): Deletes documents from a collection.

Key-Value Stores

• Data Model: Simple key-value pairs.
• Examples: Redis, Memcached
• Use Cases: Caching, Session management, in-memory data storage.

Banking System Application

• Table Structure:
  • Savings Account: account_id, account_holder, balance.
  • Current Account: account_id, account_holder, balance.
  • Transaction Journal: transaction_id, transaction_type, from_account, to_account, amount, transaction_date.
• Transaction Logic:
  • Transfer Funds:
    • Begin a transaction.
    • Debit the savings account.
    • Credit the current account.
    • Record the transaction in the Transaction Journal.
    • Commit the transaction or Rollback if there's an error.
• Data Integrity:
  • Using COMMIT/ROLLBACK ensures that all changes are completed successfully, or if any part fails, the database is rolled back to its previous state.

Joining Three Tables in SQL

• Purpose: Combine data from multiple tables based on related keys.
• Syntax (using JOIN): SELECT column1, column2, ... FROM table1 JOIN table2 ON table1.key1 = table2.key2 JOIN table3 ON table2.key2 = table3.key3;
• Example: To get customer information, orders, and order details:
  • SELECT c.cust_name, o.order_id, od.item_name, od.quantity FROM customers c JOIN orders o ON c.cust_id = o.cust_id JOIN order_details od ON o.order_id = od.order_id;