Common terms used in SQL

We will discuss about the following terms used in SQL in this exercise. We will discuss more about them individually in the later course. But here it is just a quick introduction about these terms:

1. Database

Definition: A database is a collection of organized data stored and accessed electronically. It is the top-level container that houses various database objects and schemas. A database can contain multiple schemas and objects, such as tables, views, indexes, stored procedures, and more.

Purpose: The primary purpose of a database is to store and manage data efficiently, allowing users to perform various operations such as querying, updating, inserting, and deleting data.

Key Characteristics:

Example: We can create a database in SQL Server using the command shown below:

SQL

CREATE DATABASE SalesDB;

2. DBMS (Database Management System)

A Database Management System (DBMS) is software that interacts with end users, applications, and the database itself to capture and analyze data. A DBMS allows users to create, read, update, and delete data in a database, providing tools to manage the data effectively.

Key Characteristics of a DBMS:

  1. Data Definition: Allows the creation, modification, and deletion of database schemas.
  2. Data Manipulation: Facilitates the retrieval, insertion, updating, and deletion of data.
  3. Data Security: Implements access controls to secure data from unauthorized access.
  4. Data Integrity: Maintains data accuracy and consistency through integrity constraints.
  5. Backup and Recovery: Provides mechanisms for data backup and recovery in case of data loss or corruption.
  6. Concurrency Control: Manages simultaneous data access to ensure consistency.

Example: MySQL, PostgreSQL, etc

3. Schema

Definition: A schema is a logical container within a database that groups related objects together. It acts as a namespace to organize database objects, helping manage permissions and simplify object references.

Purpose: Schemas help in organizing database objects to avoid name conflicts and manage permissions effectively. They can also help in maintaining a modular structure within a database.

Key Characteristics:

Example: We can create a schema in SQL Server using the command shown below:

SQL

CREATE SCHEMA Sales;

4. Object

Definition: An object in SQL refers to any database structure that is created within a schema or database. Common types of database objects include tables, views, indexes, stored procedures, functions, triggers, and sequences.

Purpose: Objects are the fundamental components that store, manage, and interact with data in a database. Each type of object serves a specific purpose in the database's operation and data management.

Key Characteristics:

Example: Creating a table (object) within a schema in SQL Server:

SQL

CREATE TABLE Sales.Customer (
CustomerID INT PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(100)
);

Relationships and Hierarchy

  1. Database:
    • Contains multiple schemas.
    • Is the top-level container.
  2. Schema:
    • Resides within a database.
    • Contains multiple objects.
    • Acts as a namespace and organizational unit.
  3. Object:
    • Resides within a schema.
    • Includes tables, views, indexes, etc.
    • Represents specific data structures and operations.

5. SQL

SQL is a short-form of the structured query language, and it is pronounced as S-Q-L or sometimes as See-Quell. The sql is a language used to communicate with the database.

6. Normalized Vs Denormalized database

Next