Unit 1 – Biological Database Management (804) (IMSC BIOINFORMATICS) Full Notes

This module introduces the fundamental concepts of database systems and SQL. You will learn what a database model is and explore different types of data models, including a detailed understanding of the object-oriented relational model. The content also covers the basics of DBMS and how users interact with it using SQL, including key operations such as creating tables, inserting, updating, and joining data. Additionally, you will study data constraints, SQL sorting and grouping techniques, built-in functions, and essential query writing, along with an overview of SQL security models.

1. What is database model ? Describe various data model and explain the detail about object oriented relational data model.
2. What is SQL ? Write down about the Data Definition Language.
3. What is DBMS ? How to interact with the DBMS ? Explain in detail SQL statements for creating a table, joining two tables, insert data into the table, updating data in a table.
4. What is data constraints ? Explain about various data constraints used in DBMS.
5. What is database management system ? Write down SQL statements for sorting queries, grouping and joining tables. Also discuss about different SQL built-in-function with suitable examples.
6. Write down SQL queries for the following:
   • a) Updating table
   • b) Joining tables
   • c) Retrieval of data from a table
   • d) Write a note on security model of SQL

Database Model

• A database model refers to the structure of a database and determines how the data within the database can be organized and manipulated.
• Data Model gives us an idea that how the final system will look like after its complete implementation.
• It defines the data elements and the relationships between the data elements.
• Data Models are used to show how data is stored, connected, accessed and updated in the database management system.
• Here, we use a set of symbols and text to represent the information so that members of the organisation can communicate and understand it.
• Though there are many data models being used nowadays but the Relational model is the most widely used model.
• Apart from the Relational model, there are many other types of data models about which we will study in details in this blog. Some of the Data Models in DBMS are :
  1. Relational Model
  2. Hierarchical Model
  3. Network Model
  4. Object-Oriented Data Model
  5. Entity-Relationship Model

The most common database model today is the relational model.

Relational Database model (Most Common Model)

• Relational Model is the most widely used model.
• This model was introduced by E.F Codd in 1970, and since then it has been the most widely used database model.
• In this model, the data is maintained in the form of a two-dimensional table.
• All the information is stored in the form of row and columns.
• The basic structure of a relational model is tables. So, the tables are also called relations in the relational model.
• The basic structure of data in the relational model is tables. All the information related to a particular type is stored in rows of that table.
• Hence, tables are also known as relations in the relational model.
• You can design tables, normalize them to reduce data redundancy, and use Structured Query language or SQL to access data from the tables.
• Some of the most popular databases are based on this database model. For example, Oracle, MySQL, etc.
• The relational model consists of multiple tables that bear some relationship with each other. Each table contains attributes that are the key that forms these relationships.

Advantages of Relational Model

• It’s simple and easy to implement.
• Popular database software is available for this database model.
• It supports SQL using which you can easily query the data.
• Flexible
• Scalable
• Precise
• Secure

Disadvantages of Relational Model

• Slow extraction
• High memory consumption

Hierarchical Database Model (Least Common)

• The hierarchical model organizes data into a tree-like structure, where each record has a single parent or root.
• Sibling records are sorted in a particular order. That order is used as the physical order for storing the database.
• This model is good for describing many real-world relationships.
• The hierarchy starts from the Root data, and expands like a tree, adding child nodes to the parent nodes.
• In this model, a child node will only have a single parent node.
• This model efficiently describes many real-world relationships like the index of a book, etc.
• IBM’s Information Management System (IMS) is based on this model.
• For example, consider the hierarchical model of a University :

Advantages of Hierarchical Model

• It’s very easy and quick to navigate through a tree-like structure.
• Changes in the parent node are automatically reflected in child nodes so data integrity is maintained.
• Easy to find the difference.
• Easy addition and deletion of data
• Supports one-to-many relationships

Disadvantages of Hierarchical Model

• Complex relationships are not supported.
• It does not support more than one parent of the child Node.
• If a parent node is deleted then the child node is automatically deleted.
• Not scalable
• Not flexible
• Difficult to Query
• Slow to search

Network Database Model

• The Network Model is an extension of the Hierarchical model.
• In this model, data is organized more like a graph, and allowed to have more than one parent node.
• In the network database model, data is more related as more relationships are established in this database model.
• Also, as the data is more related, hence accessing the data is also easier and fast.
• This database model uses many-to-many data relationships.
• Integrated Data Store (IDS) is based on this database model.
• This was the most widely used database model before Relational Model was introduced.
• The implementation of the Network model is complex, and it’s very difficult to maintain it.
• The Network model is difficult to modify also.
• For example, let’s look at the network model for a store:

A store has a manager, salespeople, and customers. The store is at the top of the structure because it encompasses the other data elements. The store is a parent element to customer, manager, and salespeople. Additionally, anyone of the three can make an order but only salespeople have access to the stores items.

Advantages of Network Model

• Data can be accessed more quickly than the hierarchical model
• Data integrity is guaranteed because there is a parent/child relationship.
• It supports complex relationships
• It allows more flexibility
• Easy to understand

Disadvantages of Network Model

• Any type of change, such as updating, deletion, or insertion, is extremely complex.
• Expensive
• Inefficient

Object-oriented Model

• The object-oriented database (OODB) model is similar to the relational model in that various tables represent real-life objects.
• In this model, data is stored in the form of objects.
• The behaviour of the object-oriented database model is just like object-oriented programming.
• A very popular example of an Object Database management system or ODBMS is MongoDB which is also a NoSQL database.
• This database model is not mature enough as compared to the relational database model.

Advantages of the Object-oriented Model

• It can easily support complex data structures, with relationships.
• It also supports features like Inheritance, Encapsulation, etc.

Entity-Relationship Model (ER Model)

• An Entity-Relationship model is a high-level data model that describes the structure of the database in a pictorial form which is known as ER-diagram.
• In simple words, an ER diagram is used to represent logical structure of the database easily.
• ER model develops a conceptual view of the data hence it can be used as a blueprint to implement the database in the future.
• Developers can easily understand the system just by looking at ER diagram.

Advantages of the ER Model

• It is easy to understand and design.
• Great visual representation
• Simple to conceptualize
• Integrate with relational or other data models

Disadvantages of Entity-Relationship Model

• No industry standard for notation
• Data manipulation not represented

Object oriented relational model

• An Object relational model is a combination of a Object oriented database model and a Relational database model. So, it supports objects, classes, inheritance etc. just like Object Oriented models and has support for data types, tabular structures etc. like Relational data model.
• It combines the relational and Object-Oriented models, as the name implies.
• This model was developed to bridge the gap between the Object-Oriented and relational models. Many additional capabilities are available, such as the ability to create complicated data types based on our requirements utilising existing data types.
• The issue with this paradigm is that it can become overly complicated and difficult to manage.
• One of the main goals of the Object-Relational data model is to bridge the gap between Object-Oriented practices and the Relational databases common in programming languages like C++, C#, and Java.

History of Object Relational Data Model

Both Relational data models and Object-oriented data models are very useful. But it was felt that they both were lacking in some characteristics and so work was started to build a model that was a combination of them both. Hence, Object relational data model was created as a result of research that was carried out in the 1990’s.

Advantages of Object Relational model

Inheritance – The Object Relational data model allows its users to inherit objects, tables etc. so that they can extend their functionality. Inherited objects contains new attributes as well as the attributes that were inherited.

Complex Data Types – Complex data types can be formed using existing data types. This is useful in Object relational data model as complex data types allow better manipulation of the data.

Extensibility – The functionality of the system can be extended in Object relational data model. This can be achieved using complex data types as well as advanced concepts of object-oriented model such as inheritance.

Disadvantages of Object Relational model

The object relational data model can get quite complicated and difficult to handle at times as it is a combination of the Object-oriented data model and Relational data model and utilizes the functionalities of both of them.

Advantages of Data Models

• Data Models help us in representing data accurately.
• It helps us in finding the missing data and also in minimizing Data Redundancy.
• Data Model provides data security in a better way.
• The data model should be detailed enough to be used for building the physical database.
• The information in the data model can be used for defining the relationship between tables, primary and foreign keys, and stored procedures.

Disadvantages of Data Models

• In the case of a vast database, sometimes it becomes difficult to understand the data model.
• You must have the proper knowledge of SQL to use physical models.
• Even smaller change made in structure require modification in the entire application.
• There is no set data manipulation language in DBMS.
• To develop Data model one should know physical data stored characteristics.

SQL

• SQL (Structured Query Language) is a programming language used for managing and querying relational databases.
• It provides a standardized way to interact with databases to perform operations such as creating, retrieving, updating, and deleting data.
• This database language is mainly designed for maintaining the data in relational database management system.
• SQL supports ANSI standard.
• SQL is widely used in database management systems (DBMS) such as MySQL, PostgreSQL, Microsoft SQL Server, Oracle, and SQLite, among other.
• SQL is required for the following tasks
  • To create new databases, tables, and views
  • To insert records in a database
  • To update records in a database
  • To delete records from a database
  • To retrieve data from a database

History of SQL

SQL was invented in the 1970s based on the relational data model. It was initially known as the structured English query language (SEQUEL). The term was later shortened to SQL.

Features of SQL

• SQL is used to access data within the relational database.
• It is very fast in extracting large amounts of data very efficiently.
• It is flexible as it works with multiple database systems from Oracle, IBM, Microsoft, etc.
• It helps you manage databases without knowing a lot of coding.

Functions of SQL

It enables the user for :

1. Creation and modification of database structure
2. Accessing and manipulating of database
3. Changing the security setting for the database system
4. Provides authorization for the users to work with the database and table.
5. Inserting ,updating and deleting database content
6. Query the database

SQL Commands

• SQL commands are the fundamental building blocks for communicating with a database management system (DBMS).
• These commands perform various database operations, such as creating tables, inserting data, querying information, and controlling access and security.
• SQL commands can be categorized into different types, each serving a specific purpose in the database management process.

Types of SQL command

DDL (Data definition language)

DDL, which stands for Data Definition Language, is a subset of SQL (Structured Query Language) commands used to define and modify the database structure. These commands are used to create, alter, and delete database objects like tables, indexes, and schemas. The primary DDL commands in SQL include :

CREATE –> This command is used to create a new database object. For example, creating a new table, a view, or a database.

Syntax for creating a table –

CREATE TABLE table_name (column1 datatype, column2 datatype, ...);

ALTER –> This command is used to modify an existing database object, such as adding, deleting, or modifying columns in an existing table.

Syntax for adding a column in a table –

ALTER TABLE table_name ADD column_name datatype;

Syntax for modifying a column in a table –

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

DROP –> This command is used to delete an existing database object like a table, a view, or other objects.

Syntax for dropping a table –

DROP TABLE table_name;

TRUNCATE –> This command is used to delete all data from a table, but the structure of the table remains. It’s a fast way to clear large data from a table.

Syntax –

TRUNCATE TABLE table_name;

RENAME –> Used to rename an existing database object.

Syntax –

RENAME TABLE old_table_name TO new_table_name;

DML (Data manipulation language)

Data Manipulation Language (DML) is a subset of SQL commands used for adding (inserting), deleting, and modifying (updating) data in a database. DML commands are crucial for managing the data within the tables of a database. The primary DML commands in SQL include :

INSERT –> This command is used to add new rows (records) to a table.

Syntax –

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

UPDATE –> This command is used to modify the existing records in a table.

Syntax –

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

The WHERE clause specifies which records should be updated. Without it, all records in the table will be updated.

DELETE –> This command is used to remove one or more rows from a table.

Syntax –

DELETE FROM table_name WHERE condition;

Like with UPDATE, the WHERE clause specifies which rows should be deleted. Omitting the WHERE clause will result in all rows being deleted.

SELECT –> Although often categorized separately, the SELECT command is sometimes considered part of DML as it is used to retrieve data from the database.

Syntax –

SELECT column1, column2, ... FROM table_name WHERE condition;

The SELECT statement is used to query and extract data from a table, which can then be used for various purposes.

DCL (Data control language)

Data Control Language (DCL) is a subset of SQL commands used to control access to data in a database. DCL is crucial for ensuring security and proper data management, especially in multi-user database environments. The primary DCL commands in SQL include :

GRANT –> This command is used to give users access privileges to the database. These privileges can include the ability to select, insert, update, delete, and so on, over database objects like tables and views.

Syntax –

GRANT privilege_name ON object_name TO user_name;

For example, GRANT SELECT ON employees TO user123; gives user123 the permission to read data from the employees table.

REVOKE –> This command is used to remove previously granted access privileges from a user.

Syntax –

REVOKE privilege_name ON object_name FROM user_name;

For example, REVOKE SELECT ON employees FROM user123; would remove user123’s permission to read data from the employees table.

TCL (Transaction control language)

Transaction Control Language (TCL) is a subset of SQL commands used to manage transactions in a database. Transactions are important for maintaining the integrity and consistency of data. They allow multiple database operations to be executed as a single unit of work, which either entirely succeeds or fails. The primary TCL commands in SQL include :

BEGIN TRANSACTION (or sometimes just BEGIN) –> This command is used to start a new transaction. It marks the point at which the data referenced in a transaction is logically and physically consistent.

Syntax –

BEGIN TRANSACTION;

COMMIT –> This command is used to permanently save all changes made in the current transaction.

Syntax –

COMMIT;

When you issue a COMMIT command, the database system will ensure that all changes made during the current transaction are saved to the database.

ROLLBACK –> This command is used to undo changes that have been made in the current transaction.

Syntax –

ROLLBACK;

If you issue a ROLLBACK command, all changes made in the current transaction are discarded, and the state of the data reverts to what it was at the beginning of the transaction.

SAVEPOINT –> This command creates points within a transaction to which you can later roll back. It allows for partial rollbacks and more complex transaction control.

Syntax –

SAVEPOINT savepoint_name;

You can roll back to a savepoint using ROLLBACK TO savepoint_name;

SET TRANSACTION –> This command is used to specify characteristics for the transaction, such as isolation level.

Syntax –

SET TRANSACTION [characteristic];

TCL commands are crucial for preserving a database’s ACID (Atomicity, Consistency, Isolation, Durability) properties, ensuring that all transactions are processed reliably. These commands play a key role in any database operation where data consistency and integrity are important.

RDBMS terminologies

Attribute – Attributes are the properties that define an entity. e.g.; ROLL_NO, NAME, ADDRESS

Relation Schema – A relation schema defines the structure of the relation and represents the name of the relation with its attributes. e.g.; STUDENT (ROLL_NO, NAME, ADDRESS, PHONE, and AGE) is the relation schema for STUDENT. If a schema has more than 1 relation, it is called Relational Schema.

Tuple – Each row in the relation is known as a tuple. The above relation contains 4 tuples.

Relation Instance – The set of tuples of a relation at a particular instance of time is called a relation instance. Table 1 shows the relation instance of STUDENT at a particular time. It can change whenever there is an insertion, deletion, or update in the database.

Degree – The number of attributes in the relation is known as the degree of the relation. The STUDENT relation defined above has degree 5.

Cardinality – The number of tuples in a relation is known as cardinality. The STUDENT relation defined above has cardinality 4.

Column – The column represents the set of values for a particular attribute. The column ROLL_NO is extracted from the relation STUDENT.

NULL Values – The value which is not known or unavailable is called a NULL value. It is represented by blank space. e.g.; PHONE of STUDENT having ROLL_NO 4 is NULL.

Relation Key – These are basically the keys that are used to identify the rows uniquely or also help in identifying tables. These are of the following types :

• Primary Key
• Candidate Key
• Super Key
• Foreign Key
• Alternate Key
• Composite Key

Data constraints

• Constraints in DBMS (Database Management Systems) are rules or conditions that are applied to the data within a database to ensure data integrity, consistency, and adherence to business rules.
• They define limitations and requirements that the data must meet, thereby preventing the entry of invalid or inconsistent data.
• Constraints serve as a set of predefined rules that govern the behaviour and relationships of the data in a database, helping to maintain its accuracy and reliability.
• The purpose of constraints is to enforce data quality and prevent data inconsistencies, thereby enhancing the overall data integrity and reliability of the database.
• Constraints define boundaries for data values, relationships between entities, uniqueness requirements, and more. By enforcing constraints, DBMS can ensure that the data conforms to predefined standards and business rules, making the database more robust and reliable.

Types of data constraints

1. NOT NULL CONSTRAINT – This constraint tells that we cannot store a null value in a column. That is, if a column is specified as NOT NULL then we will not be able to store null in this particular column any more.
2. UNIQUE CONSTRAINT – This constraint when specified with a column, tells that all the values in the column must be unique. That is, the values in any row of a column must not repeated.
3. PRIMARY KEY CONSTRAINT – A primary key is a field which can uniquely identify each row in a table. And this constraint is used to specify a field in a table as primary key. Primary key on a column ensures that the values are not null and not duplicate.
4. FOREIN KEY CONSTRAINT – Foreign key on a column refers to the primary key of another table. It prevents operations that would destroy links between tables.
5. CHECK CONSTRAINT – It ensures that the values in a column satisfies a specific conditions/rule given. This constraint helps to validate the values of a column to meet a particular condition. That is, it helps to ensure that the value stored in a column meets a specific condition.
6. DEFAULT CONSTRAINT – This constraint specifies a default value for the column when no value is specified by the user.
7. INDEX CONSTRAINT – It is used to create index which helps to retrieve data from the database quickly.

Benefits of Using Constraints

• Data Integrity – Ensures the accuracy and consistency of the data.
• Reliability – Prevents invalid data from entering the database.
• Performance – Enhances query performance through indexing.
• Security – Enforces rules and restrictions that maintain the quality of the data.

DATA INTEGRITY CONSTRAINT

• Integrity constraints are rules that help to maintain the accuracy and consistency of data in a database.
• They can be used to enforce business rules or to ensure that data is entered correctly.
• Integrity constraints ensure that the data insertion, updating, and other processes have to be performed in such a way that data integrity is not affected.
• They act as guidelines ensuring that data in the database remain accurate and consistent. So, integrity constraints are used to protect databases.
• For example, a simple integrity constraint in DBMS might state that all customers must have a valid email address. This would prevent someone from accidentally entering an invalid email address into the database.
• Integrity constraints can also be used to enforce relationships between tables.
• For example, if a customer can only have one shipping address, then an integrity constraint can be used to ensure that only one shipping address is entered for each customer. Enforcing integrity constraints in SQL can help prevent data inconsistencies and errors, making it easier to manage and query the data

Types of Integrity Constraints

1. Domain Constraints
2. Entity integrity Constraints
3. Referential integrity constraints
4. Key Constraints

Domain Integrity Constraint

• A domain integrity constraint is a set of rules that restricts the kind of attributes or values a column or relation can hold in the database table.
• The data type of domain includes string, character, integer, time, date, currency, etc. The value of the attribute must be available in the corresponding domain.

Entity Integrity Constraint

• The entity integrity constraint states that primary key value can’t be null.
• This is because the primary key value is used to identify individual rows in relation and if the primary key has a null value, then we can’t identify those rows.
• A table can contain a null value other than the primary key field.
• There are primarily two types of integrity constraints that help us in ensuring the uniqueness of each row, namely, UNIQUE constraint and PRIMARY KEY constraint.
• The unique key helps in uniquely identifying a record in the data table. It can be considered somewhat similar to the Primary key as both of them guarantee the uniqueness of a record. But unlike the primary key, a unique key can accept NULL values and it can be used on more than one column of the data table.

Referential Integrity Constraint

• A referential integrity constraint is specified between two tables.
• In the Referential integrity constraints, if a foreign key in Table 1 refers to the Primary Key of Table 2, then every value of the Foreign Key in Table 1 must be null or be available in Table 2.
• This makes sure that if a foreign key exists in a table relationship then it should always reference a corresponding value in the second table or it should be null.

Key Constraints

• Keys are the entity set that is used to identify an entity within its entity set uniquely.
• There are a number of key constraints in SQL that ensure that an entity or record is uniquely or differently identified in the database. There can be more than one key in the table but it can have only one primary key.

Some of the key constraints in SQL are :

• Primary Key Constraint
• Foreign Key Constraint
• Unique Key Constraint

Use of Data integrity constraints

• Maintain the quality of information in a database.
• Ensure that data insertion, updating, and other processes are performed in such a way that data integrity is not affected.
• Guard against accidental damage to the database.
• Restrict the types of information that can be entered into a table.
• Ensure accuracy and consistency of data in a relational database.
• Enforce business rules or ensure that data is entered correctly.

Explain in detail SQL statements for creating a table, joining two tables, insert data into the table, updating data in a table.

Creating a Table

The CREATE TABLE statement is used to create a new table in a database. Here’s a basic example :

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    BirthDate DATE,
    HireDate DATE,
    Salary DECIMAL(10, 2),
    DepartmentID INT
);

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(100)
);

Insert Data

INSERT INTO Departments (DepartmentID, DepartmentName)
VALUES (1, 'HR'), (2, 'IT'), (3, 'Finance');

INSERT INTO Employees (EmployeeID, FirstName, LastName, BirthDate, HireDate, Salary, DepartmentID)
VALUES
(1, 'John', 'Doe', '1980-01-01', '2020-01-01', 60000.00, 1),
(2, 'Jane', 'Smith', '1985-05-05', '2019-03-15', 70000.00, 2);

Join tables

SELECT Employees.EmployeeID, Employees.FirstName, Employees.LastName,
Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Update data

UPDATE Employees
SET Salary = 65000.00
WHERE EmployeeID = 1;

Write down SQL statements for sorting queries, grouping and joining tables. Also discuss about different SQL built-in-function with suitable examples.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    BirthDate DATE,
    HireDate DATE,
    Salary DECIMAL(10, 2),
    DepartmentID INT
);

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(100)
);

INSERT INTO Departments (DepartmentID, DepartmentName)
VALUES (1, 'HR'), (2, 'IT'), (3, 'Finance');

INSERT INTO Employees (EmployeeID, FirstName, LastName, BirthDate, HireDate, Salary, DepartmentID)
VALUES
(1, 'John', 'Doe', '1980-01-01', '2020-01-01', 60000.00, 1),
(2, 'Jane', 'Smith', '1985-05-05', '2019-03-15', 70000.00, 2);

Sorting Queries

To sort query results, you use the ORDER BY clause. You can sort the results in ascending (ASC) or descending (DESC) order.

SELECT FirstName, LastName, Salary
FROM Employees
ORDER BY Salary DESC;

In this example, the results are sorted by the Salary column in descending order.

Grouping Data

To group rows that have the same values in specified columns, we use the GROUP BY clause

SELECT DepartmentID, COUNT(*) AS NumberOfEmployees
FROM Employees
GROUP BY DepartmentID;

In this example, the query groups employees by DepartmentID and counts the number of employees in each department

Joining tables

SELECT Employees.EmployeeID, Employees.FirstName, Employees.LastName,
Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Built in functions

Aggregate Functions

1. COUNT() : Returns the number of rows.

SELECT COUNT(*) FROM Employees;

2. SUM() : Returns the sum of a numeric column.

SELECT SUM(Salary) FROM Employees;

3. AVG() : Returns the average value of a numeric column.

SELECT AVG(Salary) FROM Employees;

4. MIN() : Returns the smallest value in a column.

SELECT MIN(Salary) FROM Employees;

5. MAX() : Returns the largest value in a column.

SELECT MAX(Salary) FROM Employees;

String Functions

1. CONCAT() : Concatenates two or more strings.

SELECT CONCAT('FirstName, ' ', LastName') AS FullName FROM Employees;

2. LENGTH() : Returns the length of a string.

SELECT LENGTH(FirstName) FROM Employees;

3. UPPER() : Converts a string to uppercase.

SELECT UPPER(FirstName) FROM Employees;

4. LOWER() : Converts a string to lowercase.

SELECT LOWER(FirstName) FROM Employees;

Write down SQL queries for the following

1. Updating table

To update a table, you use the UPDATE statement. For example, if you have a table named “employees” and you want to update the salary of an employee with “employee_id” 1 :

UPDATE employees
SET salary = 60000
WHERE employee_id = 1;

2. Joining tables

To join tables, you use the JOIN statement. For example, if you have two tables named “employees” and “departments” and you want to join them on a common column “department_id” :

SELECT employees.employee_id, employees.name, departments.department_name
FROM employees
JOIN departments ON employees.department_id = departments.department_id;

3. Retrieval of data from a table

To retrieve data from a table, you use the SELECT statement. For example, if you want to retrieve all columns from the “employees” table then the query will be :

SELECT * FROM employees;

4. Write a note on security model of SQL

The security model of SQL (Structured Query Language) is designed to protect data integrity and privacy while ensuring that authorized users can perform their required tasks. This model typically includes several key components :

Authentication – Authentication verifies the identity of a user or entity trying to access the database. This is usually achieved through a combination of :

• Usernames and Passwords – The most common method where users must provide a valid username and password to gain access.
• Multi-Factor Authentication (MFA) – Enhances security by requiring two or more verification methods, such as a password plus a code sent to a user’s phone.

Authorization – Authorization determines what an authenticated user is allowed to do within the database. This involves defining user permissions and roles :

• Roles – Grouping of privileges that can be assigned to users. For example, a “Database Administrator” role might include all permissions, while a “Read-Only User” role might only include select permissions.
• Privileges – Specific rights to perform certain actions, such as SELECT, INSERT, UPDATE, DELETE, and EXECUTE on database objects like tables, views, and procedures.

Access Control – Access control mechanisms are used to enforce authorization policies :

Discretionary Access Control (DAC) – Based on user privileges. Each object (like a table) has an owner who can grant or revoke access to other users.

Mandatory Access Control (MAC) – Based on predefined policies determined by the system, rather than individual user decisions. Often used in highly secure environments.

Role-Based Access Control (RBAC) – Access decisions are based on the roles assigned to users, simplifying the management of permissions.

Encryption – Encryption protects data by making it unreadable to unauthorized users :

Data-at-Rest Encryption – Protects stored data using methods such as Transparent Data Encryption (TDE).

Data-in-Transit Encryption – Protects data being transmitted over networks using protocols like SSL/TLS.

Auditing and Monitoring – Regular auditing and monitoring help detect and respond to security incidents :

Audit Logs – Record user activities, such as logins and executed queries, for forensic analysis and compliance.

Intrusion Detection Systems (IDS) – Monitor database activity to identify and alert on suspicious behavior.