lOMoARcPSD|35998605

SYBCA DBMS OLD Papers Solution (2018-2022)

Bachelors of Computer Applications (Yashwantrao Chavan Maharashtra Open

University)

Studocu is not sponsored or endorsed by any college or university

Downloaded by Labdhi Gandhi (labdhigandhi2003@gmail.com)
 lOMoARcPSD|35998605

What are Joins? list down the types of joins?

Joins are a way to combine rows from two or more tables in a relational database based on a related column between
them. There are several types of joins, including:
1. Inner join: returns only the rows that have matching values in both tables.
2. Left join (or left outer join): returns all rows from the left table and the matching rows from the right table. If there is
no match, the result will contain NULL values.
3. Right join (or right outer join): returns all rows from the right table and the matching rows from the left table. If
there is no match, the result will contain NULL values.
4. Full outer join: returns all rows from both tables, and any unmatched rows will contain NULL values.
5. Cross join: returns the Cartesian product of the two tables, meaning it will combine each row from the first table with
every row from the second table.
Describe save point and rollback command WITH EXAPLE?
A savepoint is a point within a transaction where you can rollback to, rather than rolling back the entire transaction. For
example, imagine you are in the middle of a transaction that includes several updates and a delete. If you realize that one
of the updates was incorrect, you can roll back just that update by rolling back to the savepoint before the update was
made, rather than undoing all the changes made in the transaction.
A rollback command is used to undo changes made during a transaction. The changes are undone to the state of the
database at the last savepoint or to the beginning of the transaction if no savepoints were set.
An example of using savepoint and rollback:
BEGIN;
--Make some changes to the database
UPDATE employees SET salary = salary * 1.1 WHERE department = 'sales';
SAVEPOINT update_sales;
UPDATE employees SET salary = salary * 1.05 WHERE department = 'marketing';
SAVEPOINT update_marketing;
--Realize there was an error in the marketing update
ROLLBACK TO update_marketing;
--The changes to the sales department remain
COMMIT;
In this example, a transaction is started with the BEGIN command. The salary of the employees in the sales department is
increased by 10%. A savepoint is set with the SAVEPOINT update_sales command. Then, the salary of the employees in
the marketing department is increased by 5%. But later it was realized that it was an error, so the command ROLLBACK
TO update_marketing was used to undo the changes made to the marketing department, but the changes made to the
sales department remain and are committed.

List out the DML and DDL commands

DML (Data Manipulation Language) commands are used to manipulate data within a database. They include:
1. SELECT: used to retrieve data from one or more tables in a database.
2. INSERT: used to insert new rows of data into a table.
3. UPDATE: used to modify existing data in a table.
4. DELETE: used to delete existing data in a table.
5. MERGE: used to combine the data of two tables into one.
6. CALL: used to invoke a stored procedure.
DDL (Data Definition Language) commands are used to create, modify, and delete database objects such as tables, views,
and indexes. They include:
1. CREATE: used to create a new database object such as a table, view, or index.
2. ALTER: used to modify an existing database object such as a table, view, or index.
3. DROP: used to delete an existing database object such as a table, view, or index.
4. TRUNCATE: used to delete all data from a table, but unlike DELETE it does not generate rollback information and
cannot be undone.
5. RENAME: used to rename an existing database object such as a table, view, or index.

Downloaded by Labdhi Gandhi (labdhigandhi2003@gmail.com)

 lOMoARcPSD|35998605

Short note on Boyce - Codd Normal form (BCNF)

Boyce-Codd Normal Form (BCNF) is a type of normal form used in database design. It is a higher level of normalization
than the Third Normal Form (3NF). A relation is said to be in BCNF if it is in 3NF and any non-trivial functional dependency
X → Y, where X is a proper subset of the relation's attributes, implies that Y is a subset of X.
In simple terms, a relation is in BCNF if and only if every determinant (the attribute or set of attributes on the left side of
a functional dependency) is a candidate key. A relation that is in BCNF has the property that any non-trivial functional
dependency is implied by the candidate keys.
The main benefit of BCNF is that it helps to prevent certain types of data inconsistencies, called anomalies, that can occur
in a database that is not sufficiently normalized. For example, BCNF ensures that if a primary key is updated, the data in
all other columns of the table will be updated accordingly, which helps to maintain data integrity.
It's worth noting that BCNF is not always necessary and it can cause loss of some useful information, In some cases, it is
sufficient to have a database design that is in 3NF or even lower forms of normalization.
Write a short note on Set Operation?
Set operations are a way to combine the results of two or more SELECT statements in a relational database. They include:
1. UNION: combines the results of two SELECT statements and returns only unique rows.
2. UNION ALL: combines the results of two SELECT statements and returns all rows, including duplicates.
3. INTERSECT: returns only the rows that are in both of the SELECT statements.
4. MINUS (or EXCEPT): returns only the rows that are in the first SELECT statement and not in the second.
These set operations are useful for combining data from multiple tables or for comparing data across different sets. They
can be used in combination with other SQL clauses such as WHERE, GROUP BY, and ORDER BY to refine the results.
For example, you can use a UNION operation to combine the results of two SELECT statements that retrieve data from
different tables, and then use a WHERE clause to filter the results.
SELECT column1, column2
FROM table1
WHERE condition1
UNION SELECT column1, column2
FROM table2
WHERE condition2
It's worth noting that set operations are only supported by some relational databases, and the syntax may vary
depending on the specific database management system you are using.
What is the Aggregate function? Describe any 5-aggregate function.
Aggregate functions are used to perform a calculation on a set of values and return a single value. They are commonly
used to summarize data in a query. Some of the most common aggregate functions include:
1. SUM: returns the sum of all values in a given column.
2. COUNT: returns the number of rows in a given column.
3. AVG: returns the average of all values in a given column.
4. MIN: returns the smallest value in a given column.
5. MAX: returns the largest value in a given column.
6. GROUP_CONCAT: returns a concatenated string of all values in a given column.
7. COUNT(DISTINCT): returns the number of unique non-NULL values in a given column.
8. SUM(DISTINCT): returns the sum of unique values in a given column.
These functions are commonly used in combination with the GROUP BY clause to group the results by one or more
columns and apply the aggregate function to each group.
For example, to find the total salary of all employees in a company grouped by department you can use:
SELECT department, SUM(salary)
FROM employees
GROUP BY department;
It's worth noting that aggregate function can be used as column or in the where clause as well, but it depends on the
specific database management system you are using.

Downloaded by Labdhi Gandhi (labdhigandhi2003@gmail.com)

 lOMoARcPSD|35998605

What is entity relationship model and its components?

An Entity-Relationship (ER) model is a graphical representation of entities and their relationships to each other in a
database. It is used to design and visualize the structure of a database. The main components of an ER model are entities,
attributes, and relationships.
1. Entities: An entity represents a real-world object or concept, such as a customer, an employee, or a product. Entities
are represented by rectangles in an ER diagram.
2. Attributes: Attributes are properties of an entity that describe it. For example, the "name" attribute of a "customer"
entity would describe the customer's name. Attributes are represented by oval shapes in an ER diagram and are
connected to the entity that they describe.
3. Relationships: Relationships describe the associations between entities. For example, a "customer" entity may have a
relationship with a "purchase" entity, indicating that the customer made a purchase. Relationships are represented
by diamond shapes in an ER diagram, and the lines connecting entities represent the relationships between them.
4. Cardinality: The relationship between two entities is described by the cardinality. There are three types of cardinality:
one-to-one, one-to-many and many-to-many.
5. Participation: It represents whether the existence of an entity depends on another entity. It is represented by double
lines.
ER model can be represented in a conceptual level or in a logical level, depending on the scope and the purpose of the
model. An ER model is a powerful tool for database design, as it helps to ensure that the database is structured in a way
that accurately represents the real-world data and relationships that it is meant to store.
Explain about ACID property in DBMS
ACID is an acronym that stands for Atomicity, Consistency, Isolation, and Durability. These are a set of properties that
ensure that database transactions are processed reliably.
1. Atomicity: This property ensures that a transaction is treated as a single, indivisible unit of work. If any part of a
transaction fails, the entire transaction is rolled back, and the database state is returned to what it was before the
transaction began. This ensures that the database is left in a consistent state, even in the event of a failure.
2. Consistency: This property ensures that a transaction brings the database from one valid state to another. The rules
of the database, also known as integrity constraints, must be checked and maintained before and after a transaction.
This ensures that the data stored in the database is always accurate and consistent.
3. Isolation: This property ensures that concurrent transactions do not interfere with each other. Each transaction is
executed as if it is the only transaction taking place, meaning that the changes made by one transaction are not
visible to other transactions until the first transaction is completed. This ensures that the database remains in a
consistent state, even with multiple users accessing it at the same time.
4. Durability: This property ensures that once a transaction is committed, its changes will survive any subsequent
failures, including power loss or system crashes. This is typically achieved through the use of a write-ahead log and/or
database backups.
ACID properties are critical to ensure that a database management system is reliable, consistent and can handle
concurrent access. These properties make sure that a user can trust the data stored in the database and that the
database is not prone to data loss or inconsistencies.

Downloaded by Labdhi Gandhi (labdhigandhi2003@gmail.com)

 lOMoARcPSD|35998605

Difference between. Lossy and Lossless Decomposition.

Lossy and Lossless Decomposition are two types of decomposition used in database design to normalize a relation.
• Lossless Decomposition: The decomposition of a relation into multiple relations (also called decomposition of a
table) such that the original relation can be reconstructed from the decomposed relations with no loss of data is
called Lossless Decomposition. In other words, the join of the decomposed relations will be equal to the original
relation. The join condition is based on the functional dependency of the relation. The decomposition is lossless, if
the join dependency is preserved in the decomposed relations.
• Lossy Decomposition: A decomposition of a relation into multiple relations that results in loss of data is called Lossy
Decomposition. It is also called redundancy elimination. In this decomposition, some data can be lost during the
reconstruction process. Lossy decomposition is used to remove redundant data and improve the performance of a
database.
For example, consider a relation R(A, B, C, D) with functional dependency A->B and B->C, A B C D is the primary key. A
lossless decomposition of R would be R1(A, B) and R2(B, C, D) based on the functional dependency A -> B and B -> C. As
we can see, the original relation can be reconstructed by the join of the decomposed relations and this way we don't lose
any data.
On the other hand, if we decompose R into R1(A, B, C) and R2(C, D), this decomposition is lossy as it will result in the loss
of data when the decomposed relations are joined.
Explain what is Data and Information with example?
Data refers to raw, unorganized facts and figures that can be processed to produce information. Data can be in the form
of numbers, words, images, etc. It can be collected, stored, and analysed to draw conclusions and make decisions.
For example, a spreadsheet containing the names and ages of 100 people is data. It contains facts, but it is no t yet
organized or processed in a way that is meaningful to the user.
Information, on the other hand, is data that has been processed and organized in a way that is meaningful to the user. It
is data that has been transformed into a form that can be understood and used by people.
For example, if the spreadsheet containing the names and ages of 100 people is analysed, and a conclusion is drawn that
the average age of people is 35 years old, this is information. It is data that has been processed and organized in a way
that is meaningful and can be used to make a decision.
In short, data is raw and unorganized facts and figures, while information is data that has been processed and organized
in a way that is meaningful and useful.
Explain the Physical Data Model.
A Physical Data Model (PDM) is a representation of a database's physical storage and organization. It describes how data
will be stored on disk, including details such as the location of files, the structure of tables, the types of indexes used, and
other physical storage considerations.
A PDM includes the following components:
1. Tables: A table is a container for data and is the basic building block of a relational database. Each table is composed
of rows and columns, where rows represent individual records and columns represent the fields or attributes of each
record.
2. Columns: A column is a vertical element in a table that contains data of a specific type. Each column has a name, data
type, and set of constraints.
3. Indexes: An index is a data structure that improves the performance of database operations by providing faster
access to specific data. There are different types of indexes, such as clustered and non-clustered indexes.
4. Constraints: Constraints are used to enforce rules on the data in a table. They ensure that data is consistent and
accurate. There are different types of constraints, such as primary key, foreign key, and check constraints.
5. Partitions: Partitions are a way to divide a table horizontally into smaller, more manageable pieces. This can be useful
for improving performance and managing large amounts of data.
6. File groups: A file group is a collection of one or more files that are used to store data in a database. File groups can
be used to store different types of data, such as indexes and large objects, on different physical storage devices.
A Physical Data Model is used to specify the storage and organization of data on the physical storage media. It is a
detailed representation of the actual database design, and it is used by the DBMS to physically implement the database
on the storage media. It is also used by the Database Administrator to manage and maintain the database.

Downloaded by Labdhi Gandhi (labdhigandhi2003@gmail.com)

 lOMoARcPSD|35998605

define Keys. List the various types of Keys. Explain any one
A key is a specific column or set of columns in a table that is used to uniquely identify each row in the table. Keys play an
important role in maintaining data integrity and establishing relationships between tables in a relational database.
There are several types of keys:
1. Primary key: A primary key is a column or set of columns that uniquely identifies each row in a table. A table can
have only one primary key and it cannot be null.
2. Foreign key: A foreign key is a column or set of columns that references the primary key of another table. It is used to
establish a link between data in two tables and to maintain referential integrity.
3. Alternate key: An alternate key is a unique key that is not the primary key of a table. A table can have multiple
alternate keys.
4. Composite key: A composite key is a key that is made up of two or more columns.
5. Candidate key: A candidate key is a column or set of columns that can be used as a primary key for a table. A table
can have multiple candidate keys.
6. Super key: A super key is a column or set of columns that can be used to uniquely identify a row in a table. A super
key can be a primary key, a foreign key, or a composite key.
One example of a key is the primary key. It is used to uniquely identify each row in a table. For example, in a table called
"Customers", the primary key could be a column called "CustomerID" which is a unique identifier for each customer in
the table. This means that no two customers in the table can have the same CustomerID, and each customer must have a
unique CustomerID. This allows for easy identification and retrieval of specific customer information and also helps to
enforce data integrity by ensuring that there are no duplicate customer records.
Explain single value and multivalve attribute of ER model.

In an Entity-Relationship (ER) model, attributes describe the properties of entities. There are two types of attributes,
single-valued and multi-valued.
Single-valued attribute: It is an attribute that has only one value for a given entity. For example, a "customer" entity
might have a single-valued attribute called "name" which would contain a single value such as "John Smith".
Multi-valued attribute: It is an attribute that can have multiple values for a given entity. For example, a "customer" entity
might have a multi-valued attribute called "phone numbers" which would contain multiple values such as "555-555-1212"
and "555-555-1313".
Multi-valued attributes can be represented in an ER diagram using an oval shape connected to the entity with a double
line. It is also represented as a separate relationship with the entity.
Single-valued attributes are represented in an ER diagram using an oval shape connected to the entity with a single line.
It's worth noting that multi-valued attribute can be converted into a separate table with a one-to-many relationship with
the entity. This is done to maintain the normalization and to avoid data redundancy.

Downloaded by Labdhi Gandhi (labdhigandhi2003@gmail.com)

 lOMoARcPSD|35998605

Describe the PL/SQL Architecture

PL/SQL (Procedural Language/Structured Query Language) is a programming language that is specifically designed for the
Oracle Database Management System (DBMS). It is used to create and manage database objects such as stored
procedures, functions, and triggers, as well as to handle data manipulation and transaction control.
The PL/SQL architecture consists of the following components:
1. PL/SQL Engine: The PL/SQL engine is responsible for interpreting and executing PL/SQL code. It is responsible for
parsing the code, checking for errors, and managing the execution of PL/SQL programs.
2. SQL Engine: The SQL engine is responsible for executing SQL statements and returning the results to the PL/SQL
engine. It is also responsible for managing the data stored in the database.
3. PL/SQL Compiler: The PL/SQL compiler is responsible for checking the syntax and semantics of PL/SQL code. It
converts the PL/SQL code into machine-readable code that can be executed by the PL/SQL engine.
4. Database Server: The database server is responsible for managing the data stored in the database. It includes the
storage and retrieval of data, as well as the management of concurrent access to the data.
5. SQLNet: SQLNet is a communication protocol that allows PL/SQL programs to communicate with the database server.
It is responsible for sending SQL statements to the SQL engine and for returning the results to the PL/SQL engine.
The PL/SQL architecture allows for efficient management of the database and allows for the creation of powerful and
efficient database applications. It is designed to work seamlessly with the Oracle Database Management System, and it
provides a wide range of features for data manipulation, transaction control, and error handling.

Explain the following terms: i) Entity ii) Attribute iii) Domain iv) Instance v) Tuple
i) Entity: An entity is a real-world object or concept that is represented in a database. It can be a physical object such as a
person, place, or thing, or it can be a logical object such as an event, process, or relationship. In an Entity-Relationship
(ER) model, entities are represented by rectangles.
ii) Attribute: An attribute is a property of an entity that describes it. It represents a characteristic of the entity, such as its
name, age, or address. In an ER model, attributes are represented by oval shapes and they are connected to the entity
that they describe.
iii) Domain: A domain is the set of possible values that an attribute can have. It specifies the type and format of data that
can be stored in the attribute. For example, if an attribute is "age", the domain would specify that it can only contain
integers.
iv) Instance: An instance is a specific occurrence of an entity or attribute. It is a concrete example of the entity or
attribute in the real world. For example, "John Smith" would be an instance of the entity "customer" with the attribute
"name".
v) Tuple: A tuple is a single row of a table in a relational database. It represents an instance of an entity and contains the
values for each attribute. For example, in a table called "Customers" a tuple might contain the values "John Smith" for the
attribute "name", "35" for the attribute "age", and "123 Main St" for the attribute "address". Each tuple in a table
represents a unique instance of the entity represented by the table.

In SQL, differentiate between:

i) A sub query and join ii) WHERE and HAVING clauses iii) DROP and DELETE
iv) LEFT OUTER JOIN and RIGHT OUTER JOIN
i) A subquery and join: A subquery is a query that is embedded within another query and is used to retrieve data that will
be used by the outer query. A join, on the other hand, is used to combine data from two or more tables based on a
related column between them.
ii) WHERE and HAVING clauses: The WHERE clause is used to filter rows from a table based on a given condition before
the result set is returned. The HAVING clause is used to filter rows from the result set of a query, after the group by
clause is applied to it.
iii) DROP and DELETE: DROP is used to remove an entire table, or a database object such as an index or constraint from
the database. DELETE is used to remove specific rows from a table.
iv) LEFT OUTER JOIN and RIGHT OUTER JOIN: A LEFT OUTER JOIN returns all the rows from the left table and the
matching rows from the right table, and any non-matching rows from the left table will be filled with NULL values. A
RIGHT OUTER JOIN returns all the rows from the right table and the matching rows from the left table, and any non-
matching rows from the right table will be filled with NULL values.

Downloaded by Labdhi Gandhi (labdhigandhi2003@gmail.com)

 lOMoARcPSD|35998605

What is stored procedure in SQL? Write the syntax of stored procedure

A stored procedure is a pre-compiled and reusable program that is stored in a database and can be executed with a single
call. It allows for the encapsulation of complex logic and functionality and can accept input para meters and return output
parameters. Stored procedures are commonly used to perform repetitive or complex tasks such as data validation, data
manipulation, and data retrieval.
The syntax for creating a stored procedure in SQL is as follows:
CREATE PROCEDURE procedure_name
( -- input parameters go here
IN parameter_name data_type, ...
-- output parameters go here
OUT parameter_name data_type, ... )
BEGIN
-- stored procedure logic goes here
SELECT ...
INSERT ...
UPDATE ...
DELETE ...
END;
The stored procedure starts with the keyword CREATE PROCEDURE followed by the name of the stored procedure, then
input and output parameters are defined with their respective names and data types. Inside the BEGIN and END block,
the actual logic of the stored procedure is defined. This can include SQL statements such as SELECT, INSERT, UPDATE, and
DELETE.
To execute the stored procedure, we use the keyword CALL followed by the name of the stored procedure and passing
the values of input parameters.
For example, CALL procedure_name(input_parameter_value, ...)
Stored procedures are useful in situations where you need to run complex queries, encapsulate business logic, or
perform repetitive tasks. They can improve the performance of your database by reducing the amount of code that needs
to be parsed and executed, and they can also increase the security of your application by restricting access to the
underlying tables and data.

State the characteristics of entities.

Entities are the basic building blocks of an Entity-Relationship (ER) model, they represent the real-world objects or
concepts that are represented in a database. The characteristics of entities include the following:
1. Identifiability: An entity must have a unique identifier or set of identifiers that can be used to distinguish it from
other entities of the same type.
2. Independence: An entity must exist on its own and not be dependent on other entities.
3. Relevance: An entity should be relevant to the problem domain and should represent something that is meaningful
and useful to the system.
4. Persistence: An entity should exist independently of the application that uses it.
5. Concreteness: An entity should represent a real-world object or concept that can be observed or measured.
6. Flexibility: An entity should be able to adapt to changes in the problem domain.
7. Simplicity: An entity should be simple, meaning that it should be easy to understand, use and maintain.
By following these characteristics, the entities in a database can be designed in a way that is ac curate, reliable, and easily
understandable.
explain transaction status by giving an example? in DBMS
In a database management system (DBMS), a transaction is a sequence of operations that are executed as a single,
atomic unit of work. The transaction status refers to the current state of a transaction, such as whether it is ongoing or
has been completed.
For example, consider a banking system where a user initiates a transfer of funds from one account to another. The
transaction would involve several operations, such as checking the balance of the source account, deducting the funds,
and adding them to the destination account. The transaction status would be "ongoing" while these operations are being
executed, and would change to "committed" if all the operations are successfully executed or "aborted" if any of the
operation fail. Once a transaction is committed, the changes made by the transaction become a permanent part of the
database, and if it is Aborted, the changes are rollbacked.

Downloaded by Labdhi Gandhi (labdhigandhi2003@gmail.com)

 lOMoARcPSD|35998605

State the terminology of Transaction.

Transaction is a sequence of one or more database operations that are executed as a single unit of work. The following
are some of the terms commonly associated with transactions:
1. Atomicity: This property ensures that a transaction is treated as a single, indivisible unit of work. If any part of a
transaction fails, the entire transaction is rolled back, and the database state is returned to what it was before the
transaction began.
2. Consistency: This property ensures that a transaction brings the database from one valid state to another. The rules
of the database, also known as integrity constraints, must be checked and maintained before and after a transaction.
3. Isolation: This property ensures that concurrent transactions do not interfere with each other. Each transaction is
executed as if it is the only transaction taking place, meaning that the changes made by one transaction are not
visible to other transactions until the first transaction is completed.
4. Durability: This property ensures that once a transaction is committed, its changes will survive any subsequent
failures, including power loss or system crashes.
5. Commit: A commit is a statement that makes the changes made by a transaction permanent in the database.
6. Rollback: A rollback is a statement that undoes the changes made by a transaction, returning the database to its
state before the transaction began.
7. Savepoint: A savepoint is a point within a transaction where the current state of the database can be "saved" so that
if the transaction needs to be rolled back, it can be rolled back to the savepoint instead of the beginning of the
transaction.
8. Deadlock: A deadlock occurs when two or more transactions are waiting for each other to release a lock, resulting in
a situation where none of the transactions can proceed.
9. Locking: Locking is the mechanism used to prevent multiple transactions from accessing the same data
simultaneously, leading to conflicts. Locks can be applied at different levels such as row-level, page-level, or table-
level.
10. Read consistency: This term refers to the ability of a transaction to read data that is consistent with the state of the
database at the beginning of the transaction. This means that the data read by the transaction will not be af fected by
any other concurrent transactions.

What is normalization? Why we need normalization?

Normalization is the process of organizing data in a relational database so that it is consistent, efficient, and free of dat a
redundancy. The goal of normalization is to minimize data duplication and ensure data integrity, making it easier to
maintain and update the database.
There are several levels of normalization, known as normal forms, which describe the rules and guidelines for organizing
data in a relational database. The most commonly used normal forms are:
1. First Normal Form (1NF) - Each table has a primary key, and all data is atomic (indivisible)
2. Second Normal Form (2NF) - All non-key columns are functionally dependent on the primary key
3. Third Normal Form (3NF) - All non-key columns are not functionally dependent on any non-key columns
Normalization is important for several reasons:
1. Data Integrity: Normalization helps to ensure that data is consistent, accurate, and reliable. This is because it
eliminates data redundancy, which can lead to data inconsistencies and errors.
2. Data Security: Normalized data is more secure as it is less susceptible to data breaches and unauthorized access.
3. Data Modification: Normalization makes it easier to modify the database schema. For example, adding or deleting a
column in a table is much easier and less risky when the table is in a higher normal form.
4. Data Maintenance: Normalization makes it easier to maintain the database, as it reduces the complexity of the data
and makes it easier to understand the relationships between tables.
5. Performance: Normalized databases often perform better than non-normalized ones, as they tend to have fewer
duplicated data and thus require less storage space, thus reducing the query response time.

Downloaded by Labdhi Gandhi (labdhigandhi2003@gmail.com)

 lOMoARcPSD|35998605

What is database? What are the three advantages of DBMS

A database is a collection of data that is organized in a specific way to make it easy to access, manage, and update. A
database management system (DBMS) is a software application that is used to interact with a database and perform
tasks such as adding, modifying, and retrieving data.
The three main advantages of using a DBMS are:
1. Data Integrity: A DBMS enforces rules and constraints to ensure that the data entered into the database is accurate,
consistent, and reliable. This helps to minimize data errors and ensure data integrity.
2. Data Consistency: DBMS ensures that the data is consistent and accurate across the entire database, even if multiple
users are updating and querying the data simultaneously. This helps to minimize data inconsistencies and conflicts.
3. Data Accessibility: A DBMS allows multiple users to access and update the data in the database simultaneously, while
maintaining data consistency and integrity. This makes it easier for users to share and access information, regardless
of their location or device. Additionally, DBMS allows user to access the data even remotely, using the network.
Additionally, DBMS allows user to set security, backup and recovery, it also provide reporting and data analysis
functionality, in-built query language (SQL) and many more features.
What is data model explain any two types of data model
A data model is a conceptual representation of data and the relationships between different data elements. It provides a
blueprint for how data is organized and stored in a database, and is used to design and implement a database
management system (DBMS).
There are several types of data models, but two of the most commonly used ones are:
1. Relational Data Model: A relational data model organizes data into a series of tables, where each table represents an
entity or a relationship. The tables are linked to one another through a system of keys, which allows data to be
related and accessed in a logical and efficient manner. The relational model is based on first-order predicate logic and
its most well-known proposal is the relational model proposed by E.F. Codd.
2. Hierarchical Data Model: A hierarchical data model organizes data into a tree-like structure, where each record is
linked to one or more child records. The hierarchical model is based on the parent-child relationship, and is used to
represent data in a way that is similar to how data is organized in a file system. This model was popular in the 1960s
and 1970s and still used in some systems like IBM's Information Management System (IMS).
Both of the above data model has their own advantages and disadvantages, depending on the nature of the application,
the performance requirements and data complexity. Relational data model is more flexible and can handle complex data
relationships and queries, while Hierarchical data model is easy to implement and maintain, but less flexible.
What is a functional dependency
A functional dependency is a relationship between two sets of attributes in a relational database. It defines the
relationship between a determinant attribute and a dependent attribute. A functional dependency exists when the value
of the determinant attribute uniquely determines the value of the dependent attribute.
For example, consider a table called "Orders" that contains the following attributes: Order ID, Customer ID, and Order
Date. In this table, the Order ID attribute is the primary key, and the Customer ID attribute is the determinant attribute.
The Order Date attribute is the dependent attribute.
The functional dependency between these attributes is: Order ID -> Order Date. This means that the value of the Order ID
attribute uniquely determines the value of the Order Date attribute. In other words, for any given Order ID, there is only
one corresponding Order Date.
Functional dependencies are important in normalization of relational databases, it's used to identify the relation between
the attributes and to maintain the integrity of the data. Based on the functional dependency, tables are normalized and
split into multiple tables, to remove the data redundancy and inconsistencies.
Explain the concept of super key candidate key and a primary key with example
A super key is a set of one or more attributes that can be used to uniquely identify a tuple (row) in a relation (table) in a
database.
A candidate key is a minimal super key, which means it is a set of attributes that can uniquely identify a tuple, but no
proper subset of the key can. It can be one or more.
A primary key is a candidate key that is chosen to be the main identifier of the relation. Only one candidate key can be
primary key.
For example, consider a table called "Students" that contains the following attributes: Student ID, Name, and Address.
The set of attributes (Student ID, Name) is a super key because it can be used to uniquely identify a student. But it's not
minimal because we can use only Student ID as a unique identifier. So, Student ID is a candidate key. The primary key is
chosen to be Student ID.

Downloaded by Labdhi Gandhi (labdhigandhi2003@gmail.com)

 lOMoARcPSD|35998605

Explain structure of DBMS

A database management system (DBMS) is a software application that is used to interact with a database. I t is
responsible for managing the data stored in the database, including creating, updating, and retrieving data.
A DBMS typically has a layered architecture, which is composed of the following main components:
1. Data Storage: The data is stored in a structured format, such as tables, which are organized into rows and columns.
Each table represents a specific entity or relationship, and the columns represent the attributes of the entity or
relationship.
2. Data Definition Language (DDL): The DDL component is responsible for creating and modifying the database schema,
which defines the structure of the data, the relationships between tables, and the constraints and rules that must be
applied to the data.
3. Data Manipulation Language (DML): The DML component is responsible for inserting, updating, and deleting data
from the database. It also includes the query language, such as SQL, which allows users to retrieve data from the
database.
4. Data Control Language (DCL): The DCL component is responsible for managing the access to the data, including
managing user accounts and permissions, and enforcing data security and integrity.
5. Query Optimizer: The query optimizer is responsible for analysing a query and determining the most efficient way to
retrieve the data from the database.
6. Storage Manager: The storage manager is responsible for managing the storage space, reading and writing data, and
providing data backup and recovery.
7. Database Interface: The database interface provides the communication link between the DBMS and the users,
allowing them to interact with the data stored in the database.
In summary, a DBMS is a system that provides an interface to the data stored in a database, and it is composed of several
layers that work together to manage the data, enforce rules and constraints, and provide efficient access to the data. The
main components are Data storage, Data Definition Language, Data Manipulation Language, Data Control Language,
Query Optimizer, Storage Manager and Database Interface.
What is week entity and strong entity by giving an example each?
A weak entity is an entity that cannot be uniquely identified by its own attributes and must rely on a foreign key to
another, related entity called the owner or identifying entity.
For example, consider a database that contains information about students and the courses they are enrolled in. The
"Course" entity is the strong entity, as it can be uniquely identified by its own attributes (e.g. course code, course name,
etc.). The "Enrollment" entity is a weak entity, as it cannot be uniquely identified by its own attributes (e.g. student ID,
course ID, etc.). The Enrollment entity must rely on the foreign key "student ID" to the "Student" entity
(owner/identifying entity), to be uniquely identified.
A strong entity is an entity that has a unique identifier called primary key and can stand alone, it is not dependent on any
other entity.
For example, a "Car" entity is a strong entity. It can be uniquely identified by its own attributes such as VIN number,
make, model, and year. It does not rely on any other entity to be identified, thus it can stand alone.
In summary, a weak entity is an entity that depends on another entity to be uniquely identified and a strong entity is an
entity that has a unique identifier and can stand alone.

Downloaded by Labdhi Gandhi (labdhigandhi2003@gmail.com)

 lOMoARcPSD|35998605

What are different types of users that play different roles in a database environment?
In a database environment, there are several types of users who play different roles and have different levels of access
and privileges. Some of the most common types of users are:
1. Database Administrator (DBA): The DBA is responsible for managing the database and ensuring its performance,
security, and availability. They are responsible for creating and maintaining the database schema, managing user
accounts and permissions, and monitoring and tuning the database.
2. Database Developer: Database Developers are responsible for creating, maintaining and optimizing the database
schema and the stored procedures, triggers and functions that are used to access the data in the database.
3. End User: End users are the individuals or applications that use the data stored in the database. They may include
employees, customers, or other software applications that need to access the data. End users typically have limited
access and privileges, and can only perform a specific set of actions on the data.
4. Data Analyst: Data Analyst are responsible for analysing the data stored in the database, creating reports, and
making data-driven decisions. They have read-only access to the database, and use tools like SQL to extract data,
analyse it and create the reports.
5. System Administrator: System Administrators are responsible for managing the hardware and software that the
database runs on, including the operating system, storage, and network. They ensure that the system is running
optimally and troubleshoot any issues that arise.
6. Security Administrator: Security Administrators are responsible for ensuring that the data stored in the database is
secure. They manage user access and permissions, implement security policies, and monitor the database for any
security breaches.
In summary, a database environment has different types of users with different roles and responsibilities, from managing
the database to analysing the data, from ensuring security to maintaining the hardware and software. Each of them plays
a critical role in the proper functioning of a database.
What is attribute explaining to type of attribute?
An attribute is a characteristic or property of an entity in a database. It is a named column in a table that stores data for a
specific aspect of the entity.
There are two types of attributes:
1. Simple Attribute: Simple attributes are atomic, meaning they cannot be divided into smaller parts. They are also
called scalar attributes. For example, "name", "age", "address" are simple attributes.
2. Composite Attribute: Composite attributes are composed of two or more simple attributes. They are also called
structured attributes. For example, "Full name" which is composed of "first name" and "last name" is a composite
attribute.
In summary, an attribute is a property of an entity in a database, which is represented as a named column in a table.
There are two types of attributes: simple and composite. Simple attributes are atomic and cannot be divided into smaller
parts, while composite attributes are composed of two or more simple attributes.
Explain two phase locking protocol
The two-phase locking protocol (2PL) is a method used to ensure consistency and isolation in a database management
system. It is used to coordinate concurrent access to shared resources by multiple transactions.
The two phases in 2PL are:
1. Growing Phase: In this phase, a transaction acquires locks on the data items it needs to access, but does not release
any locks. This ensures that the transaction has exclusive access to the data items it needs to perform its operations.
2. Shrinking Phase: In this phase, a transaction releases all the locks it acquired during the growing phase. This ensures
that other transactions can now access the data items that were locked by the first transaction.
The two-phase locking protocol ensures that a transaction releases all the locks it acquired in the growing phase, before it
completes. This ensures that a transaction cannot hold on to locks for an extended period of time, and prevents
deadlocks from occurring.
Two-phase locking protocol is widely used in systems that need high consistency and isolation. It is a simple, yet effective
approach to handle concurrent access to shared resources by multiple transactions, and it provides a good balance
between concurrency and consistency.
In summary, the two-phase locking protocol (2PL) is a method used to ensure consistency and isolation in a database
management system. It uses two phases: growing phase, in which a transaction acquires locks on the data items it needs,
and shrinking phase, in which it releases all the locks it acquired. This protocol is widely used in systems that need high
consistency and isolation, and it provides a good balance between concurrency and consistency.

Downloaded by Labdhi Gandhi (labdhigandhi2003@gmail.com)

 lOMoARcPSD|35998605

What is De-normalization?
De-normalization is the process of intentionally introducing redundancy in a normalized database. This is done to
improve the performance of certain types of queries, by reducing the number of joins that are required to retrieve the
data.
De-normalization is a trade-off between performance and data integrity. While it can improve query performance, it can
also lead to data inconsistencies and make it more difficult to update the data.
There are different ways to de-normalize a database, but some common methods include:
1. De-normalizing by adding redundant data: This is the most common method of de-normalization. It involves adding
a copy of data from one table to another table, so that the data can be retrieved with fewer joins.
2. De-normalizing by creating a denormalized view: This method involves creating a virtual table that combines data
from multiple tables, so that it can be queried as if it were a single table.
3. De-normalizing by creating a summary table: This method involves creating a new table that contains pre-
aggregated data, so that it can be queried more quickly.
De-normalization is usually done in situations when the performance is critical and the high read-write ratio is present
and the number of joins required to retrieve the data is high. However, it's important to consider the trade-off between
performance and data integrity, also it's important to monitor the database performance after de-normalization, as it can
lead to data inconsistencies and update difficulties.
What is SQL
SQL (Structured Query Language) is a programming language designed for managing and manipulating relational
databases. It is used to insert, update, retrieve, and delete data in a database. SQL is based on the relational model,
which organizes data into a series of tables, where each table represents an entity or a relationship. The tables are linked
to one another through a system of keys, which allows data to be related and accessed in a logical and efficient manner.
SQL commands are used to interact with the data stored in a relational database.
Some common SQL commands include:
1. SELECT: Used to retrieve data from one or more tables in the database.
2. INSERT: Used to add new data to a table in the database.
3. UPDATE: Used to modify existing data in a table in the database.
4. DELETE: Used to remove data from a table in the database.
5. CREATE: Used to create a new table, view, or index in the database.
6. ALTER: Used to modify the structure of an existing table, view, or index in the database.
7. DROP: Used to delete an existing table, view, or index in the database.
SQL is widely used for managing data in relational databases, and it's supported by most relational databases like MySQL,
Oracle, PostgreSQL, SQL Server, and many others. SQL is not only used for managing data, but also for managing the
database structure and users.

Explain following operation on transaction. Read item(X), write item(X).

A transaction is a sequence of operations that are executed as a single, atomic unit of work. In a database management
system, a transaction is used to ensure that the data remains in a consistent state, even in the presence of concurrent
access by multiple users or applications.
The two operations that you have mentioned, "read item(X)" and "write item(X)", are operations that can be performed
within a transaction.
1. Read item(X): This operation is used to retrieve the current value of a specific item (X) from the database. The item
(X) can be a row, a column, or a specific value in the database. This operation is also known as a read-only operation,
as it does not modify the data in the database.
2. Write item(X): This operation is used to update or modify the value of a specific item (X) in the database. This
operation can also be used to insert new data into the database. This operation is also known as a write operation, as
it modifies the data in the database.
Both of these operations are executed as part of a transaction, which ensures that the data remains in a consistent state.
For example, if a transaction reads an item and then writes a new value to the same item, other transa ctions will not be
able to see the updated value until the first transaction is committed.
In summary, "read item(X)" and "write item(X)" are operations that can be performed within a transaction. A "read
item(X)" operation retrieves the current value of a specific item (X) from the database, while a "write item(X)" operation
updates or modifies the value of a specific item (X) in the database. The execution of these operations is part of a
transaction, which ensures that the data remains in a consistent state, even in the presence of concurrent access.

Downloaded by Labdhi Gandhi (labdhigandhi2003@gmail.com)

 lOMoARcPSD|35998605

Give any four differences between DBMS and RDBMS

1. Data Model: A DBMS (Database Management System) can use any data model, such as hierarchical, network, or
object-oriented, while an RDBMS (Relational Database Management System) uses the relational model.
2. Data Structures: A DBMS can handle a variety of data structures, such as lists, trees, and graphs, while an RDBMS
only handles tables and relationships between tables.
3. Data Integrity: A DBMS may not provide the same level of data integrity and consistency as an RDBMS, which
enforces data constraints and rules to maintain consistency and accuracy of the data.
4. Query Language: A DBMS may not support a standardized query language like SQL (Structured Query Language)
which is the standard query language for RDBMS.
In summary, DBMS and RDBMS are different in terms of the data model they use, the data structures they handle, the
level of data integrity they provide and the query language they support. RDBMS is a type of DBMS that uses the
relational data model, it handles tables and relationships between tables, provides a high level of data integrity and
consistency and supports SQL as a query language.
what is meta data
Metadata is data that provides information about other data. It is often used to describe the structure, content, and
context of data stored in a database.
In the context of a database management system, metadata is data that describes the structure of the database, such as
the tables, columns, indexes, and relationships between tables. It also includes information about the data stored in the
tables, such as data types, constraints, and defaults.
Examples of metadata include table and column names, data types, primary keys, foreign keys, constraints, and indexes.
It also includes information about the database itself, such as the version, the date it was created, and the names of the
users who have access to it.
Metadata is important because it allows the database management system to understand the structure and content of
the data, and to enforce constraints and rules that ensure the data is consistent and accurate. It also allows users to
understand the data and its context, and to navigate and query the data more easily.
Explain type of relationship
In a relational database, a relationship is a connection between two or more tables that defines how data is related.
There are several types of relationships that can exist between tables in a database:
1. One-to-one (1:1) relationship: This type of relationship exists when one record in a table is related to one and only
one record in another table, and vice versa. For example, a "Driver" table might have a 1:1 relationship with a
"License" table, where each driver is associated with a unique license.
2. One-to-many (1:N) relationship: This type of relationship exists when one record in a table is related to one or more
records in another table, but each record in the second table is related to only one record in the first table. For
example, a "Department" table might have a 1:N relationship with an "Employee" table, where each department can
have many employees, but each employee belongs to only one department.
3. Many-to-many (M:N) relationship: This type of relationship exists when multiple records in one table are related to
multiple records in another table, and vice versa. For example, a "Student" table might have a M:N relationship with
a "Course" table, where each student can take multiple courses, and each course can have multiple students.
4. Self-referencing relationship: This type of relationship exists when a table has a relationship with itself. For example,
an "Employee" table might have a self-referencing relationship where each employee has a manager, and the
manager is also an employee in the same table.
Define with example and syntax group: by clause.
The GROUP BY clause is used in SQL to group rows in a query result set based on one or more columns. It is typically used
in conjunction with aggregate functions, such as SUM, COUNT, AVG, etc., to group data by a specific column or set of
columns and then perform calculations on the grouped data.
For example, consider a table named "SALES" that contains information about products sold, including the product name,
the quantity sold, and the price.
To find the total quantity sold for each product, we can use the following query:
SELECT product_name, SUM(quantity)
FROM SALES
GROUP BY product_name;
The query above retrieves the product name and the total quantity sold for each product. The GROUP BY clause groups
the rows in the result set by the product name, and the SUM () function calculates the total quantity sold for each group.

Downloaded by Labdhi Gandhi (labdhigandhi2003@gmail.com)

 lOMoARcPSD|35998605

what is the advantage of PL-SQL?

PL/SQL (Procedural Language/Structured Query Language) is a programming language that is used to create and manage
database applications. Some advantages of using PL/SQL include:
Improved performance: PL/SQL code is precompiled and stored in the database, which means it can be executed faster
than interpreted languages, such as SQL.
1. Better security: PL/SQL allows developers to create procedures, functions, and triggers that can be stored in the
database, which can be used to enforce security rules and constraints.
2. Increased scalability: PL/SQL allows developers to create large, complex applications that can handle a large number
of users and transactions.
3. Enhanced developer productivity: PL/SQL provides a rich set of built-in functions and data types that allow
developers to write more efficient and less error-prone code.
4. Better support for complex business logic: PL/SQL allows developers to create powerful, flexible, and reusable code
that can handle complex business logic, such as conditional statements, loops, and exception handling.
5. Integration with the Oracle Database: PL/SQL is a proprietary language that is tightly integrated with the Oracle
Database, which makes it easier to manage and maintain the database.
In summary, PL/SQL is a programming language that is used to create and manage database applications. It has several
advantages, such as improved performance, better security, increased scalability, enhanced developer productivity,
better support for complex business logic and tight integration with the Oracle Database.
What is DDL expanded to DDL command with example?
DDL stands for Data Definition Language. It is a subset of SQL that is used to define the structure of a database, such as
creating, altering and dropping tables, indexes, and other database objects.
The main DDL commands are:
1. CREATE: This command is used to create new tables, indexes, and other database objects. For example, the following
SQL statement creates a new table named "EMPLOYEES":
CREATE TABLE EMPLOYEES
( ID INT PRIMARY KEY,
NAME VARCHAR(255),
AGE INT );
2. ALTER: This command is used to modify the structure of existing tables, indexes, and other database objects. For
example, the following SQL statement adds a new column named "SALARY" to the "EMPLOYEES" table:
ALTER TABLE EMPLOYEES ADD SALARY INT;
3. DROP: This command is used to delete existing tables, indexes, and other database objects. For example, the
following SQL statement deletes the "EMPLOYEES" table:
DROP TABLE EMPLOYEES;
What is data constraint? Explain check constraint with example?
A data constraint is a rule or condition that is used to limit the values that can be stored in a database. It is used to ensure
the integrity and accuracy of the data in the database. A check constraint is a type of data constraint that is used to limit
the values that can be entered into a specific column or set of columns in a table. It is used to ensure that the data
entered into the table meets certain conditions or requirements.
For example, consider a table named "EMPLOYEES" with a column named "Age", where you want to ensure that the age
entered is between 18 and 60. The check constraint can be added as follows:
ALTER TABLE EMPLOYEES
ADD CONSTRAINT chk_Age CHECK (Age BETWEEN 18 AND 60);
Another example, a table named "PRODUCTS" with a column named "Stock" and you want to ensure that the stock is
greater than zero. The check constraint can be added as follows:
ALTER TABLE PRODUCTS
ADD CONSTRAINT chk_Stock CHECK (Stock > 0);
In summary, a data constraint is a rule or condition that is used to limit the values that can be stored in a database. A
check constraint is a type of data constraint that is used to limit the values that can be entered into a specific column or
set of columns in a table. It is used to ensure that the data entered into the table meets certain conditions or
requirements. It's added by using ALTER statement and adding a constraint with a name chk_ and the column name, then
it's followed by CHECK clause and the condition.

Downloaded by Labdhi Gandhi (labdhigandhi2003@gmail.com)

 lOMoARcPSD|35998605

What is stored procedure in SQL write the syntax for stored procedure?
A stored procedure is a pre-compiled, reusable piece of code that is used to perform a specific task or set of tasks in a
database management system. The syntax for creating a stored procedure in SQL (Structured Query Language) varies
depending on the type of database you are using, but the basic format is similar across most relational database
management systems.
The basic syntax for creating a stored procedure in SQL is as follows:
CREATE PROCEDURE procedure_name
( [parameter1 data_type,] [parameter2 data_type,]
... )
BEGIN --SQL statements
END;
For example, to create a stored procedure named "usp_InsertProduct" that takes two parameters (product_name and
product_price) and insert the product into the "Products" table, the following SQL statement can be used:
CREATE PROCEDURE usp_InsertProduct
(@product_name varchar(255), @product_price float)
BEGIN
INSERT INTO Products (product_name, product_price)
VALUES (@product_name, @product_price);
END;
After creating the stored procedure, it can be executed by calling the procedure name with passing the parameter values,
for example:
EXEC usp_InsertProduct 'product name', 12.5
In summary, A stored procedure is a pre-compiled, reusable piece of code that is used to perform a specific task or set of
tasks in a database management system. The basic syntax for creating a stored procedure in SQL is as follows: "CREATE
PROCEDURE procedure_name ( [parameter1 data_type,] [parameter2 data_type,] ... ) BEGIN --SQL statements END;",
where procedure_name is the name of the stored procedure, parameter1, parameter2, etc. are the names and data
types of the parameters that the stored procedure takes as input, and the SQL statements are the code that the stored
procedure will execute when it is called.
write a short note on primary key with syntax and example.
A primary key is a column or set of columns in a table that is used to uniquely identify each row in the table. It is a special
type of unique key and it cannot contain null values. The main characteristics of a primary key are that it must be unique
and not null. Primary keys are used to enforce referential integrity, which means that they are used to prevent duplicate
values and ensure that each row in the table can be uniquely identified.
The syntax for creating a primary key in SQL is as follows:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ...);
For example, consider a table named "EMPLOYEES" with columns "ID", "NAME", and "AGE". To set the "ID" column as the
primary key for the "EMPLOYEES" table, the following SQL statement can be used:
ALTER TABLE EMPLOYEES
ADD CONSTRAINT pk_Employees PRIMARY KEY (ID);
In summary, A primary key is a column or set of columns in a table that is used to uniquely identify each row in the table,
it's unique and not null. It's used to enforce referential integrity and prevent duplicate values.
Any five data type in SQL.
INT or INTEGER: This data type is used to store whole numbers. It can typically store values between -2147483648 and
2147483647.
1. VARCHAR or CHAR: These data types are used to store character strings of varying lengths. The VARCHAR data type
stores strings of variable length, while the CHAR data type stores strings of fixed length.
2. DATE: This data type is used to store date values. It stores the date in the format YYYY-MM-DD.
3. FLOAT: This data type is used to store floating-point numbers. It can store values with decimal points, such as 3.14.
4. BOOLEAN: This data type is used to store Boolean values, which can be either true or false.
5. DECIMAL: This data type is used to store decimal numbers; it can store values with decimal points and precision can
be set.
These are some commonly used data types in SQL. Depending on the database management system, there may be
additional data types available.

Downloaded by Labdhi Gandhi (labdhigandhi2003@gmail.com)

 lOMoARcPSD|35998605

Explain hierarchical model with example

The hierarchical data model is a data model that organizes data in a tree-like structure, where each record is represented
as a node in the tree. The nodes are linked together through parent-child relationships, where each parent node can have
multiple child nodes. The hierarchical data model is often used to represent hierarchical relationships between data, such
as the relationships between employees and managers in an organization.
An example of a hierarchical data model could be a company organizational chart. The CEO of the company would be at
the top of the hierarchy, with the department managers and their respective employees below them. Each department
manager is a child of the CEO, and each employee is a child of their respective department manager.

In this example, the CEO is the parent of the two department managers, and the department managers are parents of the
employees. The employees are the children of the department managers. The hierarchical data model is useful for
representing data that has a natural hierarchical structure, but it can be less flexible than other data models when it
comes to querying and manipulating the data.
In summary, the hierarchical data model is a data model that organizes data in a tree-like structure, where each record is
represented as a node in the tree. It's useful for representing data that has a natural hierarchical structure, but it can be
less flexible than other data models when it comes to querying and manipulating the data. An example of a hierarchical
data model is a company organizational chart, where the CEO is at the top of the hierarchy, with the department
managers and their respective employees below
Explain the procedure for Creating Views. With Suitable Example
A view is a virtual table that is based on the result of an SQL statement. It is a way to present a subset of the data in a
table or multiple tables in a specific way. Creating views can simplify the process of querying data, as it allows you to
access the data in a specific format without having to rewrite the underlying SQL statement every time.
The procedure for creating views in SQL is as follows:
1. Create a SELECT statement that retrieves the desired data from one or more tables.
2. Use the CREATE VIEW statement to create a view and give it a name.
3. Specify the SELECT statement as the SELECT statement for the view.
For example, consider a table named "EMPLOYEES" that contains columns for "ID", "NAME", "SALARY", and
"DEPARTMENT". To create a view that displays the name and salary of employees in the "SALES" department, the
following SQL statement can be used:
CREATE VIEW Sales_Employees AS
SELECT NAME, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT = 'SALES';
The above statement creates a view named "Sales_Employees" that contains the name and salary of all employees in the
"SALES" department. Now, you can query the view just like a table, for example:
SELECT * FROM Sales_Employees;

Downloaded by Labdhi Gandhi (labdhigandhi2003@gmail.com)

 lOMoARcPSD|35998605

Explain procedure for creating Snapshots with example

A snapshot is a read-only copy of a table or a set of data that is saved at a specific point in time. It is used to create a
backup of the data that can be used for reporting or analysis purposes, without affecting the performance of the original
table. The procedure for creating a snapshot in SQL varies depending on the database management system you are using,
but generally involves the following steps:
• Create a SELECT statement that retrieves the desired data from the original table.
• Use the CREATE SNAPSHOT statement to create a snapshot and give it a name.
• Specify the SELECT statement as the SELECT statement for the snapshot.
For example, consider a table named "EMPLOYEES" that contains columns for "ID", "NAME", "SALARY", and
"DEPARTMENT".
To create a snapshot of the "EMPLOYEES" table and name it "EMPLOYEES_SNAPSHOT",
the following SQL statement can be used:
CREATE SNAPSHOT EMPLOYEES_SNAPSHOT AS
SELECT ID, NAME, SALARY,
DEPARTMENT FROM EMPLOYEES;
The above statement creates a snapshot named "EMPLOYEES_SNAPSHOT" that contains all the data from the
"EMPLOYEES" table at the time the snapshot was created. Now, you can query the snapshot just like a table, for example:
SELECT * FROM EMPLOYEES_SNAPSHOT;
Please note that the above example is just a demonstration of the process of creating a snapshot, the actual syntax and
procedure for creating a snapshot may vary depending on the specific database management system you are using.
Explain Referential Integrity Constraint
Referential integrity constraint is a set of rules that are used to maintain consistency and accuracy of data in a relational
database. It is used to ensure that the values in one table match corresponding values in another table. There are two
types of referential integrity constraints: foreign key constraints and primary key constraints.
Foreign key constraints are used to ensure that the values in a foreign key column match the values in a primary key
column of another table. For example, consider two tables, "EMPLOYEES" and "DEPARTMENTS". The "EMPLOYEES" table
has a column "DEPARTMENT_ID" that is a foreign key referencing the "DEPARTMENTS" table's primary key column
"DEPARTMENT_ID". The foreign key constraint ensures that the values in the “DEPARTMENT_ID" column of the
"EMPLOYEES" table match the values in the "DEPARTMENT_ID" column of the "DEPARTMENTS" table.
Primary key constraints are used to ensure that the values in a primary key column are unique and not null. For example,
consider a table "EMPLOYEES" has a column "ID" that is a primary key. The primary key constraint ensures that the values
in the "ID" column are unique and not null for each employee.
Referential integrity constraints are enforced by the database management system, which ensures that any data entered
into the table matches the defined rules. It helps to maintain consistency and accuracy of data, and prevents data from
becoming orphaned.
Explain procedure for creating Index with example
An index is a database object that is used to improve the performance of queries. It allows the database management
system to quickly locate and retrieve specific rows from a table, based on the values in one or more columns. The
procedure for creating an index in SQL varies depending on the database management system you are using, but
generally involves the following steps:
1. Choose the table and the column(s) on which you want to create an index. 2. Use the CREATE INDEX statement to
create an index and give it a name. 3. Specify the table and the column(s) that the index should be created on.
For example, consider a table named "EMPLOYEES" that contains columns for "ID", "NAME", "SALARY", and
"DEPARTMENT". To create an index on the "NAME" column of the "EMPLOYEES" table,
the following SQL statement can be used:
CREATE INDEX name_index
ON EMPLOYEES (NAME);
The above statement creates an index named "name_index" on the "NAME" column of the "EMPLOYEES" table. Now,
when you run a query that filters on the "NAME" column, the database management system will use the index to quickly
locate the matching rows.
You can also create composite index on multiple columns. For example, if you have a table named "ORDERS" with
columns "CUSTOMER_ID" and "ORDER_DATE", you can create an index on both columns as follows:
CREATE INDEX order_index
ON ORDERS (CUSTOMER_ID, ORDER_DATE);

Downloaded by Labdhi Gandhi (labdhigandhi2003@gmail.com)