Ch -5 RDBMS Concept and MYSQL

You are currently viewing Ch -5 RDBMS Concept and MYSQL

Chapter -5 (RDBMS Concept and MYSQL)

Table of Contents

Q. 1) What is the difference between Data and Information?

Key Differences between “data” and “information”:

  1. Processing: Data is raw and unprocessed, while information is processed and organized.
  2. Meaning: Data lacks context and meaning, whereas information is data that has been given meaning through interpretation.
  3. Usage: Data is used as input for processing, while information is the output of this processing and is used for decision-making.

Q. 2) What is Database?

A database is an organized collection of data that is stored and managed in a structured manner, typically using a database management system (DBMS). Databases are designed to efficiently store, retrieve, and manage large amounts of data. They support various operations such as querying, updating, and organizing data to facilitate data management and retrieval.

Q 3) What is RDBMS ? What are advantages and disadvantages of RDBMS?

An RDBMS (Relational Database Management System) is a type of database management system that stores data in a structured format, using rows and columns. It uses SQL (Structured Query Language) for accessing and managing the data.

Advantages of RDBMS

  1. Data Integrity: RDBMS ensures data integrity through constraints like primary keys, foreign keys, and unique constraints. This helps maintain accurate and consistent data.
  2. Data Security: RDBMS provides robust security features, allowing administrators to define user permissions and restrict unauthorized access.
  3. Data Relationships: It allows the establishment of relationships between tables using primary and foreign keys, making it easier to query and analyze related information.
  4. Querying and Reporting: RDBMS supports powerful query languages like SQL, enabling efficient data retrieval and reporting.
  5. Backup and Recovery: RDBMS offers reliable data storage with easy backup and recovery options, ensuring data is not lost.

Disadvantages of RDBMS

  1. Complexity: Setting up and managing an RDBMS can be complex and requires specialized knowledge.
  2. Scalability Challenges: While RDBMS can scale, it may face challenges with horizontal scaling (distributing data across multiple servers) compared to some NoSQL databases.
  3. Cost: Commercial RDBMS solutions can be expensive, both in terms of licensing and maintenance.

Q 4) What is the limitations of Traditional File System?

Traditional file systems have several limitations that RDBMS (Relational Database Management Systems) address effectively. Here are some key limitations of traditional file systems:

  1. Data Redundancy and Inconsistency: In traditional file systems, the same data might be duplicated in multiple files, leading to redundancy. This can cause inconsistencies if one copy of the data is updated but others are not.
  2. Difficulty in Accessing Data: Retrieving specific data from a traditional file system can be cumbersome and inefficient. It often requires writing complex programs to extract the needed information.
  3. Data Isolation: Data is often stored in separate files with different formats, making it difficult to combine and analyze data from multiple sources.
  4. Integrity Problems: Ensuring data integrity (accuracy and consistency) is challenging in traditional file systems. There are limited mechanisms to enforce data integrity rules.
  5. Security Issues: Traditional file systems offer limited security features. Unauthorized access to files can be a significant risk, as there are fewer controls to restrict access.

Q 5) What is the difference between DBMS and Traditional File Oriented System?

What is the difference between DBMS and Traditional File Oriented System?

DBMSTraditional File System
DBMS is software for managing the database.The file system is a way of arranging the files in a storage medium within a computer.
In DBMS there is no redundant data.Redundant data can be present in a file system.
It provides in house tools for backup and recovery of data even if it is lost.It doesn’t provide Inbuilt mechanism for backup and recovery of data if it is lost.
It has more complexity in handling as compared to the file system.It is less complex as compared to DBMS.
DBMS has more security mechanisms as compared to file systems.File systems provide less security in comparison to DBMS.
It has a comparatively higher cost than a file system.It is less expensive than DBMS.
Multiple users can access data at a time.Only one user can access data at a time.
Due to centralized nature data sharing is easyData is distributed in many files. So, it is not easy to share data.
Example are Oracle, SQL ServerExample are Cobol, C++

Q 6) Discuss three tier structure of DBMS.

DBMS is collection of inter-related data and set of procedure to access that data. Database systems are made-up of complex data structures. The main purpose of DBMS to provide users with an abstract view of data. To ease the user interaction with database, the developers hide internal irrelevant details from  users. This process of hiding irrelevant details from user is called data abstraction. There are mainly three levels of data abstraction:

  • Physical Level or Internal schema: The Physical level is the lowest level of abstraction that describes how the data are actually stored. At this level, complex low level data structures are defined in detail.
  • Logical Level or Conceptual schema : The Logical Level is the next higher level of abstraction. It describe what data are stored in the database and what relationship exist among those data. The entire database is described in terms of a small number of relatively simple structure. The logical level of abstraction is used by database administrators, who must decide what information is to kept in the database.
  • View Level or External Schema : The View level is the highest level of database. It describes only part of the entire database. Simple structures are used at the logical level but still some complexity remains, as the database may be very large in size. A lot number of users are not concerned with whole of the information, but they want to access only a part of the database.

Q 7) Who is DBA and what are the different roles and responsibilities of DBA?

A Database Administrator (DBA) is a professional responsible for managing, maintaining, and securing a database management system (DBMS). The DBA ensures that the database operates efficiently and securely, supports data integrity, and meets the needs of the organization. Here are the key roles and responsibilities of a DBA:

  1. Database Design and Implementation

Design: Collaborate with developers and architects to design the database schema, including tables, indexes, and relationships.

Implementation: Create and configure databases based on the design specifications.

  1. Performance Monitoring: DBA Continuously monitor database performance using various tools and metrics.
  2. Backup and Recovery: DBA implement and manage regular backup procedures to protect data against loss and also DBA develop and execute recovery plans to restore data in case of hardware failure, data corruption, or other disasters.
  3. Security Management: DBA manage user access and permissions to ensure that only authorized users can access sensitive data and also implement security measures such as encryption, auditing, and monitoring to protect data from unauthorized access and breaches.
  4. Data Integrity and Maintenance: DBA enforce data integrity constraints and validate data to ensure accuracy and consistency. DBA perform routine maintenance tasks such as database cleanup, updating statistics, and managing storage.
  5. Database Upgrades and Patching: DBA Plan and execute database upgrades to new versions to take advantage of new features and improvements.
  6. Documentation: DBA create and maintain comprehensive documentation for database design, configuration, procedures, and troubleshooting.

Q 8) What do you mean by SQL ? What are characteristics of SQL?

SQL (Structured Query Language) is a specialized programming language used for managing and manipulating relational databases. It allows users to perform various operations on the data stored in a database, such as querying, updating, inserting, and deleting data. SQL is essential for interacting with databases like MySQL, SQL Server, Oracle, and others1.

Characteristics of SQL

  • Non-Procedural Language: SQL is a non-procedural language, meaning you specify what you want to do with the data rather than how to do it. This makes it easier to use, especially for those with minimal programming experience.
  • Data Definition and Manipulation: SQL includes commands for defining the structure of the database (Data Definition Language – DDL) and for manipulating the data within the database (Data Manipulation Language – DML).
  • Portability: SQL allows for database portability, meaning you can move databases from one system to another with minimal changes.
  • Ease of Use: SQL is designed to be user-friendly, with a simple syntax that makes it easy to learn and use. Commands are straightforward and can be written in a way that is easy to understand.
  • Support for Multiple Users: SQL can handle multiple users accessing and manipulating the data simultaneously, making it suitable for large-scale applications.
  • Security: SQL provides robust security features, including user authentication and authorization, to protect data from unauthorized access.

Q 9) What are different data types in SQL ? Explain.

SQL supports a variety of data types to handle different kinds of data. These data types can generally be categorized into several main types:

  1. Numeric Data Types
  • INT: Stores integer values. Examples include INTEGER, BIGINT, SMALLINT, and TINYINT, which differ in their range and storage size.
  • DECIMAL or NUMERIC: Stores exact numeric values with a specified precision and scale. For example, DECIMAL(10,2) can store numbers up to 10 digits long with 2 digits after the decimal point.
  1. Character and String Data Types
  • CHAR(n): Stores fixed-length strings. If you insert a string shorter than n, it will be padded with spaces. For example, CHAR(10) will always use 10 characters, including spaces if necessary.
  • VARCHAR(n): Stores variable-length strings. You can store strings up to n characters long. For example, VARCHAR(255) can store strings of any length up to 255 characters.
  1. Date and Time Data Types
  • DATE: Stores dates in the format YYYY-MM-DD.
  • TIME: Stores time values in the format HH:MM:SS.
  • DATETIME: Stores both date and time in the format YYYY-MM-DD HH:MM:SS.

Q 10) Difference between char and varchar data types?

Difference between char and varchar data types?

charvarchar
CHAR is used to store fixed-length strings. If the length of the string is less than the specified length, it is padded with extra spaces to match the defined length.
Example: CHAR(10) will always use 10 bytes of storage, even if the string is shorter.
VARCHAR is used to store variable-length strings. It only uses as much storage as needed for the string, plus a few extra bytes to store the length of the string.
Example: VARCHAR(10) can store up to 10 characters, but if the string is shorter, it only uses the necessary space.
The storage size of CHAR is equal to the defined length. For example, CHAR(10) will always occupy 10 bytes, regardless of the actual string length.The storage size of VARCHAR is the actual length of the string plus 1 or 2 bytes for length information.
CHAR can be faster for fixed-length data because the storage size is consistent, making it easier for the database to manage.VARCHAR can be more efficient in terms of storage space, especially when the length of the data varies significantly.

Q 11) What do you mean by constraints? Explain.

Constraints are rules applied to data tables to enforce the accuracy and reliability of the data stored within the database. These rules help maintain data integrity and consistency by restricting the types of data that can be inserted, updated, or deleted in the database.

Here are some common types of constraints in DBMS:

  1. Primary Key Constraint: A primary key uniquely identifies each record in a table. It must contain unique values and cannot contain NULL values. Each table can have only one primary key, which may consist of a single column or a combination of columns.
  2. Foreign Key Constraint: A foreign key is a column or a set of columns in one table that refers to the primary key in another table. This relationship ensures that the data in the foreign key column matches the data in the primary key column of the related table, maintaining referential integrity between the tables.
  3. Unique Constraint: A unique constraint ensures that all values in a column or a group of columns are unique across the table. Unlike the primary key, a table can have multiple unique constraints, and unique columns can contain NULL values (though only one NULL value is allowed per unique column).
  4. Not Null Constraint: The not null constraint ensures that a column cannot contain any NULL values. This is useful for columns that must always have a value, such as a primary key or any other essential data point.
  5. Check Constraint: A check constraint is used to limit the range of values that can be entered into a column. It specifies a condition that each row in the table must satisfy. For example, a check constraint can ensure that the age column only contains positive numbers.

Default Constraint: A default constraint sets a default value for a column when no value is specified during an insert operation. This helps in ensuring that a column always has some meaningful value.

Q 12) What is the Domain integrity constraint?

The domain integrity constraint is a type of integrity constraint in a database that ensures the validity of data in a column based on its defined data type, format, and range of acceptable values. It enforces rules at the column level, ensuring that the data entered into a column conforms to a specific domain of acceptable values.

For example:

  • A column defined to store dates must only contain valid date values.
  • A column for storing ages might enforce a constraint that only positive integers are allowed.
  • A column for storing email addresses may require a specific format to ensure valid email addresses are entered.

By enforcing domain integrity constraints, the database ensures that the data adheres to the expected type, format, and range, preventing invalid data from being stored and thus maintaining data quality and consistency.

Q 13) What is referential integrity constraint?

A referential integrity constraint is a rule in a database that ensures the validity and consistency of relationships between tables. It is enforced through the use of foreign keys, which are columns in a table that link to the primary key of another table. The primary purpose of a referential integrity constraint is to maintain consistent and valid relationships between the rows in the two tables. For example, if you have a “Students” table with a primary key “StudentID” and an “Enrollments” table with a foreign key “StudentID,” referential integrity ensures that every “StudentID” in the “Enrollments” table corresponds to a valid “StudentID” in the “Students” table.

This constraint helps maintain data accuracy and prevents orphaned records, ensuring that relationships between tables are valid and consistent.

Q 14) What do you mean by DML and DDL ? Explain.

DML (Data Manipulation Language) and DDL (Data Definition Language) are two categories of SQL (Structured Query Language) commands used to interact with databases. They serve different purposes in managing and manipulating database data and structure.

Data Manipulation Language (DML)

DML is a subset of SQL used for managing data within database tables. DML commands allow users to perform operations such as inserting, updating, deleting, and querying data. Key DML commands include:

  • INSERT: Adds new records to a table.
  • UPDATE: Modifies existing records in a table.
  • DELETE: Removes records from a table.
  • SELECT: Retrieves data from one or more tables, allowing users to query specific information.

DML operations are essential for day-to-day data management tasks, enabling users to manipulate the data stored in a database.

Data Definition Language (DDL)

DDL is a subset of SQL used for defining and managing the structure of database objects, such as tables, indexes, and schemas. DDL commands affect the schema or structure of the database rather than the data itself. Key DDL commands include:

  • CREATE: Creates new database objects, such as tables, indexes, or views.
  • ALTER: Modifies the structure of existing database objects, such as adding or dropping columns from a table.
  • DROP: Deletes database objects, removing them permanently from the database.
  • TRUNCATE: Removes all records from a table but retains the table structure for future use.

Q 15) What do you mean by DCL and TCL ? Explain.

In SQL, DCL (Data Control Language) and TCL (Transaction Control Language) are two important sublanguages used for different purposes:

Data Control Language (DCL)

DCL is used to control access to data within the database. It includes commands that grant or revoke permissions to users, ensuring that only authorized users can perform certain actions. The primary DCL commands are:

  • GRANT: Gives specific privileges to users or roles. For example, GRANT SELECT ON Employees TO User1; allows User1 to perform SELECT operations on the Employees table.
  • REVOKE: Removes previously granted privileges from users or roles. For example, REVOKE SELECT ON Employees FROM User1; removes the SELECT permission from User1 on the Employees table.

Transaction Control Language (TCL)

TCL is used to manage transactions within the database, ensuring data integrity and consistency. Transactions are sequences of operations performed as a single logical unit of work. The primary TCL commands are:

  • COMMIT: Saves all changes made during the current transaction. For example, COMMIT; ensures that all changes made in the transaction are permanently saved to the database.
  • ROLLBACK: Undoes all changes made during the current transaction. For example, ROLLBACK; reverts the database to its state before the transaction began.
  • SAVEPOINT: Sets a point within a transaction to which you can later roll back. For example, SAVEPOINT Savepoint1; creates a savepoint named Savepoint1.
  • SET TRANSACTION: Sets the properties for the current transaction, such as isolation level. For example, SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; sets the isolation level to SERIALIZABLE.

Q 16) What do you mean by table? How can you create a table in SQL?

In SQL, a table is a fundamental structure within a relational database that stores data in rows and columns. Each row represents a unique record, and each column represents a specific attribute of the data.

Creating a Table in SQL

To create a table in SQL, you use the CREATE TABLE statement. This statement specifies the table’s name and defines its columns, including the data types and any constraints like primary keys or not null constraints.

Suppose we want to create a table named Employees with the following columns: EmployeeID, FirstName, LastName, Email, and HireDate.

CREATE TABLE Employees (

   EmpID INT PRIMARY KEY,  

    FirstName VARCHAR(50) NOT NULL,

    LastName VARCHAR(50) NOT NULL,

    Email VARCHAR(100),

    HireDate DATE

);

In this example:

  • EmpID is defined as an integer (INT) and is the primary key (PRIMARY KEY) of the table, ensuring that each employee has a unique identifier.
  • FirstName and LastName are variable character fields (VARCHAR) with a maximum length of 50 characters and are marked as NOT NULL, meaning they must have a value.
  • Email is also a VARCHAR field, but with a maximum length of 100 characters, and it can contain null values.
  • HireDate is a date field (DATE) that stores the date an employee was hired.

Q 17) What is the function of SELECT command? Explain.

The SELECT command in SQL is used to retrieve data from one or more tables in a database. It allows you to specify which columns of data you want to retrieve and from which table. The data returned by a SELECT query is stored in a result set, which can be displayed or further processed.

Key Components

  • SELECT: Specifies the columns you want to retrieve.
  • FROM: Specifies the table from which to retrieve the data.
  • WHERE: (Optional) Specifies conditions that the data must meet to be included in the result set.

Examples

  1. Retrieve Specific Columns:

SELECT FirstName FROM Students;

This query retrieves the FirstName column from the Students table.

  1. Retrieve All Columns:

SELECT * FROM Students;

This query retrieves all columns from the Students table.

  1. Retrieve Data with Conditions:

SELECT FirstName , LastName FROM Employees WHERE Department = ‘Sales’;

This query retrieves the FirstName and LastName of employees who work in the Sales department.

Q 18) How can you insert rows in table?

To insert rows into a table in SQL, you use the INSERT INTO statement. This statement allows you to add new records to a table by specifying the values for each column.

Basic Syntax

The basic syntax for the INSERT INTO statement is:

INSERT INTO table_name (column1, column2, column3, …)

VALUES (value1, value2, value3, …);

Examples

Suppose we  have a table named Students with columns RollNo , FirstName, LastName, and Email. To insert a single row into this table, we would use:

INSERT INTO Students (RollNo, FirstName, LastName, Email)

VALUES (41, ‘Vishal’, ‘Uttam’, ‘codewithvishal41@gmail.com’);

This command inserts a new record into the Students table with the specified values for each column.

Q 19) What is the use of DISTINCT clause in SELECT statement?

The DISTINCT clause in a SELECT statement is used to remove duplicate rows from the result set, ensuring that only unique records are returned. It is particularly useful when we want to retrieve a list of distinct values or avoid redundant data in our query results.

Syntax

The basic syntax for using the DISTINCT clause is:

SELECT DISTINCT column1, column2, …FROM table_name;

Suppose we have a table named Customers with a column Country. If we want to get a list of all unique countries where our customers are located, we would use:

SELECT DISTINCT Country FROM Customers;

This query will return each country only once, even if there are multiple customers from the same country.

Q 20) What is the purpose of WHERE clause with SELECT statement?

The WHERE clause in a SQL SELECT statement is used to filter records and retrieve only those rows that meet specific conditions. It allows you to specify criteria that the data must satisfy to be included in the result set.

Example

Suppose we have a table named Employees and we want to retrieve the records of employees who work in the “Sales” department:

SELECT FirstName, LastName

FROM Employees

WHERE Department = ‘Sales’;

In this example, the WHERE clause filters the results to include only those employees whose Department is “Sales”.

Q 21) What is the function of ORDER BY clause ? Explain.

The ORDER BY clause in SQL is used to sort the result set of a query by one or more columns. It allows us to arrange the data in either ascending (default) or descending order, making it easier to analyze and interpret the results.

Basic Syntax

SELECT column1, column2, … FROM table_name

ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], …;

  • ASC: Sorts the data in ascending order (smallest to largest, A to Z). This is the default order.
  • DESC: Sorts the data in descending order (largest to smallest, Z to A).

Example

We can sort by multiple columns, with each column providing a level of sorting. For example, we might first sort by one column and then by another column to resolve ties.

Example:

SELECT * FROM Employees

ORDER BY Department ASC, LastName DESC;

This query sorts employees first by Department in ascending order, and then by LastName in descending order within each department.

Q 22) Discuss the use of AND, OR and NOT operators in SQL?

In SQL, the AND, OR, and NOT operators are used to combine multiple conditions in the WHERE clause of a SELECT, UPDATE, DELETE, or other SQL statements. These logical operators help refine your queries to retrieve or manipulate data based on complex criteria.

  1. AND Operator: The AND operator is used to combine multiple conditions, requiring all specified conditions to be true for a row to be included in the result set.

Example: To find teachers in the ‘PGT’ Category with a salary greater than Rs. 30,000:

SELECT * FROM Teachers WHERE Category = ‘PGT’ AND Salary > 30000;

This query retrieves teachers who satisfy both conditions: being in the ‘PGT’ Category and having a salary above Rs. 30,000.

  1. OR Operator: The OR operator is used to combine multiple conditions, where at least one of the specified conditions must be true for a row to be included in the result set.

Example: To find teachers who are either in the ‘PGT’ Category or have a salary greater than Rs. 30,000:

SELECT * FROM Teachers WHERE Category = ‘PGT’ OR Salary > 30000;

This query retrieves teachers who meet either condition: being in the ‘PGT’ Category or having a salary above Rs 30,000.

  1. NOT Operator: The NOT operator negates a condition, meaning that it selects rows where the specified condition is false.

Example: To find employees who are not in the ‘PGT’ Category:

SELECT * FROM Teachers WHERE NOT Category = ‘PGT’;

This query retrieves teachers whose category is not PGT.

Q 23) Why BETWEEN operator is used?

The BETWEEN operator in SQL is used to filter the result set within a specified range. It is particularly useful for selecting rows where a column’s value falls between two given values, inclusive of the boundary values.

Example

Suppose we have a table named FEES and we want to retrieve fees deposit between April 1, 2024, and Augusr 31, 2024:

SELECT FeesID, FeesDate, StudentID FROM FEES

WHERE FeesDate BETWEEN ‘2024-04-01’ AND ‘2024-08-31’;

In this example, the query returns all fees deposit where the FeesDate is between April 1, 2024, and August 31, 2024, inclusive of both dates.

Using the BETWEEN operator makes our queries more readable and concise when filtering data within a specific range.

Q 24) How can you remove a table in database?

To remove a table from a database, we use the DROP TABLE statement. This command deletes the table and all of its data permanently, so it should be used with caution.

Syntax

The basic syntax for the DROP TABLE statement is:

DROP TABLE table_name;

  • table_name: The name of the table you want to remove.

Examples

  • Removing a Single Table:

DROP TABLE Students;

This command deletes the Students table along with all its data and structure.

  • Removing Multiple Tables:

We can also remove multiple tables in a single DROP TABLE statement by listing them, separated by commas:

DROP TABLE Students, Teachers;

Q 25) How can you create users in SQL?

Creating users in SQL involves defining new user accounts that can access and interact with a database. The process varies depending on the SQL database management system (DBMS) you are using. Here’s a example of how to create users in MySQL databases:

To create a new user in MySQL, we use the CREATE USER statement-:

Create a User:

CREATE USER ‘username’@’host’ IDENTIFIED BY ‘password’;

  • ‘username’ is the name of the new user.
  • ‘host’ specifies the host from which the user can connect (e.g., ‘localhost’ or ‘%’ for any host).
  • ‘password’ is the user’s password.

Example :

CREATE USER ‘vishal@localhost’ IDENTIFIED BY ‘vishal1234’

Q 26) How can you delete user accounts in SQL?

Deleting user accounts in SQL varies depending on the database management system (DBMS) you are using. Here are examples for MySQL:

In MySQL, we can delete a user using the DROP USER statement. Similar to SQL Server, we can specify the host if needed:

DROP USER ‘username’@’host’;

Example:

DROP USER ‘vishal’@’localhost’;