Relational Database Systems

Question 1

Relational Database Management System

Answer 1

is a type of DBMS (Database Management System) designed specifically for managing relational databases.
→ RDBMS is a subset of DBMS

Question 2

Relational Database

Answer 2

→ A relational database is a type of database that organizes data into tables, which consist of rows and columns.
→ The relational model for databases was introduced by E.F. Codd in the 1970s
→ Applications:
○ Excel
○ Access
○ Microsoft SQL
○ AWS

Question 3

Non-relational Databases

Answer 3

→ also known as NoSQL databases, are a category of database systems that provide storage and retrieval of data in a format other than the traditional relational tables.
→ These databases are designed to handle large volumes of unstructured or semi-structured data
→ Applications:
○ Apache HBase:
* is well-suited for handling large, sparse datasets.
○ IBM Domino:
* is known for its high scalability and is used for collaboration and application development.
○ Oracle NoSQL Database:
* is designed for high-throughput and low-latency applications, emphasizing congruent (consistent) data reading

Question 4

SQL

Answer 4

→ stands for Structured Query Language
→ is a standard programming language designed for managing and manipulating relational databases.
→ Some Database Management Systems:
○ MySQL
○ MS Access
○ PostgreSQL
○ Oracle
○ Sybase
○ SQL Server

Question 5

Why use SQL?

Answer 5

1. Allows users to access and retrieve data from a relational database (RDBMS) using the SELECT statement.
   
   2. Provides commands to describe the structure of the data in a database.
   3. Enables the definition of data structures using Data Definition Language (DDL) commands (such as CREATE TABLE) and manipulation of data within tables using Data Manipulation Language (DML) commands (like INSERT, UPDATE, and DELETE )
   4. Allows the creation and deletion of databases and tables.
   5. Supports the creation and use of functions, views, and stored procedures. (edit 200 rows)
   6. Provides Data Control Language (DCL) commands (GRANT and REVOKE) to set permissions on tables, procedures, and views.

Question 6

Types of SQL

Answer 6

1. Data Definition Language (DDL)
   
   2. Data Manipulation Language (DML)
   3. Data Control Language(DCL)
   4. Transaction Control Language(TCL)
   5. Data Query Language (DQL)

Question 7

Data Definition Language (DDL)

Answer 7

→ deals with the definition and management of the structure of a database.
→ DDL commands allow users to define, modify, and delete database objects such as tables, indexes, and schemas.
○ Create
○ Drop
○ Alter
○ Truncate
(Rename)

Question 8

Data Manipulation Language (DML)

Answer 8

→ deals with the manipulation of data stored in a database.
→ DML commands allow users to insert, update, and delete data in database tables
○ Insert
○ Update
○ Delete

Question 9

Data Control Language (DCL)

Answer 9

→ deals with the control of access to data within a database.
→ DCL commands allow database administrators to grant or revoke permissions and privileges to users or roles, controlling who can perform specific actions on the database objects.
○ Grant
○ Revoke

Question 10

Transaction Control Language(TCL)

Answer 10

→ deals with the control and management of transactions within a database.
→ TCL commands allow users to manage the transactional aspects of database operations.
○ Commit
○ Rollback
○ Savepoint

Question 11

Data Query Language (DQL)

Answer 11

→ deals specifically with the querying and retrieval of data from a relational database.
→ DQL is primarily focused on the SELECT statement, which allows users to retrieve specific information from one or more database tables based on specified conditions.

Question 12

Internal Level/Schemas

Answer 12

→ The internal schema is the lowest level of data abstraction, dealing with the actual representation of the entire database.
→ It focuses on the physical storage of data on the disk in the form of records.
→ It does not deal with physical devices directly but views them as collections of physical pages.
→ Purpose: Describes how data is stored in the database and the actual structures used for storage.

Question 13

Conceptual or Logical Level/Schema

Answer 13

→ Defines all database entities, their attributes, and their relationships.
→ Includes security and integrity information.
→ Data available at this level must be contained in or derivable from the physical level.
→ Purpose:
○ Provides a conceptual view of the entire database, abstracting away details of how data is stored physically.
○ Focuses on defining the structure and relationships in a way that is independent of implementation details.

Question 14

External or View Level/Schema

Answer 14

→ External level is related to the data viewed by specific end-users.
→ Involves external schemas that describe the segment of the database needed for a certain user group.
→ It is closest to the user and hides unnecessary details from specific user groups.
→ Purpose:
○ Presents a specific view of the database tailored to the needs of a particular user group.
○ Allows for data independence by enabling changes to the internal and conceptual levels without affecting users at the external level.

Question 15

Goals

Answer 15

→ Every user should be able to access the same data but able to see a customized view of the data.
→ The user don’t need to deal directly with physical database storage detail.
→ The DBA (Database Administrator) should be able to change the database storage structure without disturbing the user’s views.
→ The internal structure of the database should remain unaffected when changes made to the physical aspects of storage.