DBMS All Questions and Answers For Preparation with Extra Guided by Everything For Help "Everything For Help" #everythingforhelp

All DBMS Questions for Preparation

1 Mark Questions and Answers:

(i)             Define Record: - A record (tuple or row) is a group of fields within a table that are relevant to a specific entity.

(ii)           Define attribute: - An attribute (column)

(iii)                                Define Degree: - Degree is the total number of attributes/fields/columns of a relation/table.

(iv)                       Define Cardinality: - Cardinality is total number of tuples/rows of relation/table.

(v) Define Foreign Key: - Foreign keys are the columns of a table that points to the primary key of another table.

(vi)                                 Define SQL: - SQL (Structure Query Language) is a database query language used for storing and managing data in Relational Database Management System (RDBMS).

(vii)                    Define Mapping: - Mapping is the number of values of one entity connected to number of values of another entity.

(viii)                  Define DDL: - Data Definition Language (DDL) is a computer language. It is used for creating and modifying the structure of the database objects, such as schemas, tables, views, indexes, etc.

(ix)                       Define DML: - Data Manipulation Language (DML) is a set of syntax elements. It is used to manage the data in the database.

(x) Define Query Processor: - A query processor is a component of a relational database in which data is stored in tables of rows and columns. It complements the storage engine, which writes and reads data to and from storage media.

(xi)                                 Define Primary key: - A primary key is a minimal set of attributes (columns) in a table that uniquely identifies tuples (rows) in that table or One of the candidate keys which has no null values.

(xii)                    Alternate keys: - All the candidate keys except the primary key is known as alternate key.

(xiii)                  Super key: - A super key is a group of single or multiple keys which identifies rows in a table or candidate key + zero or more attributes.

(xiv)                      Define Candidate key: - Minimum set of attributes used to uniquely differentiate records of the table/relation (It is also known as prime attributes).

(xv)                    Define Entity: - An entity is a real-world object that are represented in database. It can be any object, place, person or class.

(xvi)                  Define Tuple: - A single entry in a table is called a tuple or record or row. A tuple in a table/relation represents a set of related data.

(xvii)               Define DCL: - A Data Control Language (DCL) is a syntax similar to a computer programming language used to control access to data stored in a database (Authorization).

(xviii)             Define PL/SQL: - It is a procedural language extension to Structured Query Language. The purpose of PL/SQL is to combine database language and procedural programming language.

(xix)                  Define Null: - The SQL Null is the term used to represent a missing value or Null/NULL is a special marker used in Structured Query Language to indicate that a data value does not exist in the database.

(xx)                     What is Schema: - Description of the database is called the database schema or It is the design of a database.

(xxi)                  Example of RDBMS, DBMS: -

RDBMS	DBMS
SQL Server, Oracle, MySQL, MariaDB, SQLite etc.	dBase, Microsoft Access, LibreOffice Base, FoxPro et.

(xxii)                Define File: - A file is an object on a computer that stores data, information, settings, or commands used with a computer program or A file is a sequence of records stored in binary format.

(xxiii)             Define grant (command): - SQL GRANT is a command used to provide access or privileges on the database objects to the users.

(xxiv)             Define Revoke: - The REVOKE command removes user access rights or privileges to the database objects.

Marks of 2:

(i)               What do you mean by Data independence? : - Data independence is ability to modify a schema definition in one level without affecting a schema definition in the next higher level.

There are two levels of data independence:

1.    Physical Data Independence	2.    Logical Data Independence
Physical Data Independence is the ability to modify the physical schema without requiring any change in application programs.	Logical Data Independence is ability to modify the conceptual schema without requiring any change in application programs.
Modifications at the internal levels are occasionally necessary to improve performance. Possible modifications at internal levels are change in file structures, compression techniques, hashing algorithms, storage devices, etc.	Modification at the logical levels are necessary whenever the logical structures of the database is altered.
Physical data independence separates conceptual levels from the internal levels.	Logical data independence separates external level from the conceptual view.
This allows to provide a logical description of the database without the need to specify physical structures.	Comparatively it is difficult to achieve logical data independence.
Comparatively, it is easy to achieve physical data independence.	Application programs are heavily dependent on logical structures of the data they access. So, any change in logical structure also requires programs to change.

(ii)             What are three Levels of DBMS architecture? -

 

1.    External Level	Conceptual Level	Internal Level
It is also called view level. The reason this level is called “view” is because several users can view their desired data from this level which is internally fetched from database with help of conceptual and internal level mapping. The user doesn’t need to know the database schema details such as data structure, table definition etc. user is only concerned about data which is what returned back to the view level after it has been fetched from database (present at the internal level).	It is also called logical level. The whole design of the database such as relationship among data, schema of data etc. are desired in this level. Database constraints and security are also implemented in this level of architecture. This level is maintained by DBA (Database Administrator).	This level is also known as physical level. This level describes how the data is actually stored in the storage devices. This level is also responsible for allocating space to the data. This is the lowest level of the architecture.

Note: External level is the “Top Level” of the Three Level DBMS Architecture.

(iii)          What do you mean by degree of relationship? The degree of relationship (also known as cardinality) is the number of occurrences in one entity which are associated (or linked) to the number of occurrences in another.

There are three degrees of relationship, known as:

1.    One-to-one (1:1)

2.    One-to-many (1:M)

3.    Many-to-many (M: N)

Note: The latter one is correct, it is M: N and not M: M.

(iv)           Difference between Primary key and Foreign key: -

Primary key	Foreign key
Primary key uniquely identifies record in the relation	Foreign key is a field in the relation/table that is primary key in another table/relation.
Does not have null values	Can accept multiple null values
ATMOST ONE primary key in a table	Can have more than one foreign key in a table.

(v)             Different between candidate key and Foreign key: -

Candidate key	Foreign key
It must contain unique values	A Foreign key is a column which is added to create a relationship with another table.
Candidate key may have multiple attributes	It helps us to maintain data integrity and also allows navigation between two different instances of an entity.
Must not contain null values	Every relationship in the model needs to be supported by a foreign key.
It should contain minimum fields to ensure uniqueness
Uniquely identify each record in a table

(vi)           What are various types of access control? - Access control is used to identify an individual who does a specific job, authenticate them, and then proceed to give that individual only the key to the door or workstation that they need access to and nothing more.

Discretionary Access Control	Mandatory Access Control	Role- Based Access Control
Discretionary Access Control is a type of access control system that holds the business owner responsible for deciding which people are allowed in a specific location, physically or digitally. DAC is the least restrictive compared to the other systems, as it essentially allows an individual complete control over any objects they own, as well as the programs associated with those objects. The drawback to Discretionary Access Control is the fact that it gives the end user complete control to set security level settings for other users and the permissions given to the end user are inherited into other programs they use which could potentially lead to malware being executed without the end user being aware of it.	Mandatory Access Control is more commonly utilized in organizations that require an elevated emphasis on the confidentiality and classification of data (ie. Military institutions). MAC doesn’t permit owners to have a say in the entities having access in a unit or facility, instead, only the owner and custodian have the management of the access controls. MAC will typically classify all end users and provide them with labels which permit them to gain access through security with established security guidelines.	It is also known as Rule-Based Access Control, RBAC is the most demanded in regard to access control systems. Not only is it in high demand among households, RBAC has also become highly sought-after in the business world. In RBAC systems, access is assigned by the system administrator and is stringently based on the subject’s role within the household or organization and most privileges are based on the limitations defined by their job responsibilities. So, rather than assigning an individual as a security manager, the security manager position already has access control permissions assigned to it. RBAC makes life much easier because rather than assigning multiple individuals particular access, the system administrator only has to assign access to specific job titles.

(vii)        Write steps to create a view: -

Syntax to Create View:

CREATE VIEW view_name AS

SELECT column1, column2, ….

FROM table_name

WHERE condition;

Example:

CREATE VIEW [Brazil Customers] As

SELECT CustomerName, ContactName

FROM Customers

WHERE Country = “Brazil”;

Note: A view always shows up-to-date data! The database engine recreates the data, using the view’s SQL statement, every time a user queries a view.

(viii)      Name various types of indexes: - The index is a type of data structure. It is used to locate and access the data in a database table quickly.

Types of Index: -

1.    Ordered indices

2.    Primary index

3.    Dense index

4.    Sparse index

5.    Clustering index

6.    Secondary Index

(ix)           Different between trivial and non-trivial dependencies: -

Trivial Dependency	Non-Trivial Dependency
If an FD X        Y holds where Y subset of X, then it is called a trivial FD. Trivial FDs are always hold.	If an FD X       Y holds where Y is not subset of X, then it is called non-trivial FD.
	Completely non-trivial dependency: If an FD X        Y holds where X intersect Y=ᵩ, is said to be completely non-trivial FD.

(x)             What do you mean by decomposition? - Decomposition is the process of dividing the normal form into tables for remove the anomaly. Decomposition makes easy to find the data in database.

(xi)           What are various notations used in E-R diagram? –

(a) Entity

(b)                        Relationship

(c) Attributes

(d)                        Weak entity

(e)Weak entity Relationship

(f)   Multivalued Attribute Key attribute

(g) Composite Attribute

(xii)        What are various issues of data security?

(a) Access control

(b)     Auditing

(c) Authentication

(d)     Encryption

(e)Integrity Controls

(f)   Backups

(g) Application security

(h)     Database security

(xiii)      Write commit and rollback commands: - Commit – To make the changes done in a transaction permanent issue the COMMIT statement. The Syntax of COMMIT Statement is

COMMIT [WORK] [COMMENT ‘your comment’]

Here WORK is Optional

Example: - insert into emp (empno,ename,sal) values (101;Abd;2300);

Commit;

Rollback – To rollback the changes done in a transaction give rollback statement. Rollback restore the state of the database to the last commit point.

Example:

delete from emp;

rollback; /*undo the changes*/

(xiv)      Write create table statement with example: - The CREATE TABLE statement is used to create a new table in a database.

Syntax –

CREATE TABLE table_name (

column1 datatype,

column2 datatype,

column3 datatype,

…..

);

The column parameters specify the names of the columns of the table.

The datatype parameter specifies the type of data the column can hold (e.g. varchar, integer, date, etc.)

Example:

CREATE TABLE Persons (

PersonID int,

LastName varchar (255),

FirstName varchar (255),

Address varchar (255),

City varchar (255)

);

(xv)        What are used of alter command? – The SQL ALTER TABLE command is used to modify the definition (structure) of a table by modifying the definition of its columns. The ALTER command is used to perform the following functions:

(a) Add, drop, modify table columns

(b)                        Add and drop constraints

(c) Enable and Disable constraints

(xvi)      What are big data activities? – Big data is a term that describes the large volume of data – both structured and unstructured – that inundates a business on day-to-day basis.

Marks of 5:

(i)               Compare conventional system and data base system (dbms): -

(ii)             Explain structure of DBMS: - Applications: It can be considered as a user-friendly web page where the user enters the requests. Here he simply enters the details that he needs and presses buttons to get the data.

End User: They are the real users of the database. They can be developers, designers, administrator or the actual users of the database.

DDL: Data Definition Language (DDL) is a query fired to create database, schema, tables, mappings etc. in the database. These are the commands used to create the objects like tables, indexes in the database for the first time. In other words, they create structure of the database.

DDL Compiler: This part of database is responsible for processing the DDL commands. That means these compiler actually breaks down the command into machine understandable codes. It is also responsible for storing the meta data information like table name, space used by it, number of columns in it, mapping information etc.

DML Compiler: When the user inserts, deletes, updates or retrieves the record from the database, he will be sending request which he understand by pressing some  buttons. But for the database to work/understand the request, it should be broken down to object code. This is done by this compiler. One can imagine this as when a person is asked some question, how this is broken down into waves to reach the brain!

Query Optimizer: When user fires some request, he is least bothered how it will be fired on the database. He is not all aware of database or its way of performance. But whatever be the request. It should be efficient enough to fetch, insert, update or delete the data from the database. The query optimizer decides the best way to execute the user request which is received from the DML compiler.

Data Files: It has the real data stored in it. It can be stored as magnetic tapes, magnetic disks or optical disks.

Data Dictionary: It contains all the information about the data base. As the name suggests, it is the dictionary of all the tables, view, materialized views, constraints, indexes, triggers etc.

Function of Data Dictionary:

a)    Defines the data element.

b)   Helps in the scheduling.

c)     Helps in the control.

d)   Permits the various users who know which data is available and how can it be obtained.

e)    Helps in the identification of the organizational data irregularity.

f)      Acts as a very essential data management tool.

g)    Provides with a good standardization mechanism.

h)   Acts as the corporate glossary of the ever-growing information resource.

i)       Provides the report facility, the control facility along with the excerpt facility.

Stored Data Manager:

1)    This is also known as Database Control System. It is one the main central system of the database. It is responsible for various tasks.

2)    It converts the requests received from query optimizer to machine understandable form.

3)    It makes actual request inside the database. It is the fetching the exact part of the brain to answer.

4)    It controls concurrent access.

5)    If there is multiple users accessing the database at the same time, it makes sure, all of them see correct data.

6)    It helps to maintain consistency and integrity by applying the constraints. That means, it does not allow inserting / updating / deleting any data if it has child entry. Similarly it does not allow entering any duplicate value into database tables.

7)    It guarantees that there is no data loss or data mismatch happens between the transactions of multiple users.

8)    It helps to backup the database and recover data whenever required.

9)    Since it is a huge data base and when there is any unexpected exploit of transaction, and reverting the changes are not easy, so it maintains the backup of all data, so that is can be recovered.

 

(iii)          Explain insert, update and delete command with example: - Insert Command: To add data to a table the Insert command is used. The syntax of the Insert command is given below:

Insert into table-name values (data, data …)

Example:

Suppose you have the following fields in the table names as Employee as followers:

Dept_No

Dept_Name

Emp_No

Emp_Name

Now use the following query to insert values to these fields in table.

Insert into Employee values (10, ‘Management’, ‘E01’,’John Abraham’);

Dept_No	Dept_Name	Emp_No	Emp_Name
10	Management	E01	John Abraham

Update Command: To update a table or row or column in the table we use the update command. The syntax of update command is given below:

Update table-name set column-name = value where condition;

Example:

Suppose we have a table that is named as employee.

Dept_No	Dept_Name	Emp_No	Emp_Name
10	Management	E01	John Abraham
20	Management	E02	Tim
30	Finance	E10	AbdurRahman

To update the table we are going to use the following query:

Update employee set Emp_No = E04 where Dept_No = 30;

This query will produce the following result:

Dept_No	Dept_Name	Emp_No	Emp_Name
10	Management	E01	John Abraham
20	Management	E02	Tim
30	Finance	E04	AbdurRahman

Delete Command:

To delete a table row or some data from a table in the database the delete command is used. The syntax of delete command is as follows:

Delete from table-name;

Example:

To delete all records use the delete command. This will delete the attributes, indexes etc. of the table but the table as a whole will not be deleted. To deleted all records from the table use the following SQL statement:

Delete from Employee;

This query will delete all the records from the table named as Employee. This change will be permanent and cannot be recovered.

(iv)           What are various types of joins? – In SQL server we have only three types of joins. Using these joins we fetch the data from multiple tables based on condition

1.    Inner Join: - Inner join returns only those records/rows that match/exists in both the tables.

Syntax for inner join is as

Select * from table_1 as t1 inner join table_2 as t2 on t1. IDocl=t2.IDcol

 

2.    Outer Join: - This type of join returns all rows from one table and only those rows from a secondary table where the joined fields are equal (join condition is met).

Left Outer Join: The left outer join returns all records from the left table and the matched records from right table.

 

Right Outer Join: The right outer join returns all records from right table and the matched records from the left table.

 

Full Outer Join: The full outer join returns all records when there is a match in either left or right table.

 

(v)             Differentiate between authentication and authorization: Authentication: - Authentication confirms who you are. For example, you can login to your Unix server using ssh client, or access the server using POP3 and SMTP email client Typically, PAM (Pluggable Authentication Modules) is used as a lower-level application programming interface (API), which allows programs that rely on authentication to be written independently of the underlying authentication schema.

Authorization: - Authorization is the process to confirm what you are authorized to perform. For example, you are allowed to login to your Unix serer via ssh client, but you are not allowed to browser / data2 or other file systems. Authorization occurs after Authorization can be controlled at the level of file system or use a variety of configuration options such as application level chroot. Normally, the connection attempt should be good authentication and authorization by the system. You can attempt are either accepted or rejected with the help of two factors.

(vi)           Explain BCNF in brief: - Boyce-Codd Normal Form (BCNF) is a normal form used in database normalization. It is a slightly stronger version of the third normal form (3NF). BCNF was developed in 1975 by Raymond F.Boyce and Edgar F. Codd to address certain types of anomalies not dealt with by 3NF as originally defined.

If a relational schema is in BCNF then all redundancy based on functional dependency has been removed, although other types of redundancy may still exist. A relational schema R is in Boyce-Codd Normal Form if and only if for every one of its dependency’s X        Y, at least one of the following conditions hold.

·       X        Y is a trivial functional dependency (Y        Y)

·       X is a super key for schema R.

(vii)        What are various normal forms of DBMS? Normalization of a Database is achieved by following a set of rules called ‘forms’ in creating the database.

·       The words normalization and normal form refer to the structure of a database.

·       Normalization was developed by IBM researcher E.F. Codd In the 1970s.

·       Normalization increase the clarity in organizing data in database.

Database Normalization Process is divided into following the normal form:

·       First Normal Form: - Each column is unique in 1NF. Example: Sample Employee table, it displays employees are working with multiple departments

Employee	Age	Department
AbdurRahman	20	Marketing, Sales
Luqman	25	Quality Assurance
Alex	36	Human Resource

·       Second Normal Form (2NF): - The entity should be considered already in 1NF, and all attributes within the entity should depend solely on the unique identifier of the entity.

·       Third Normal Form (3NF): - The entity should be considered already in 2NF, and no column entry should be dependent on any other entry (value) other than the key for the table. If such an entity exists. Move it outside into a new table. 3NF is achieved considered as the database is normalized.

·       Boyce-Codd Normal Form (BCNF): - 3NF and all tables in the database should be only one primary key.

·       Fourth Normal Form (4NF): - Tables cannot have multi-valued dependencies on a Primary key.

·       Fifth Normal Form (5NF): - A composite key shouldn’t have any cyclic dependencies.

(viii)      Explain use of conditional expression in select command with example: -

(ix)           Different between 3NF and BCNF.

Basis For Comparison	3NF	BCNF
Concept	No non-prime attribute must be transitively dependent on the candidate key.	For any trivial dependency in a relation R say X->Y, X should be a super key of relation R.
Dependency	3NF can be obtained without sacrificing all dependencies.	Dependencies may not be reserved in BCNF.
Decomposition	Lossless decomposition can be achieved in 3NF.	Lossless decomposition is hard to achieve in BCNF.

(x)             Explain multiple valued dependencies: - According to database theory, a multivalued dependency is a full constraint between two sets of attributes in a relation. A multivalued dependency is a special case of a join dependency, with only two sets of values involved, i.e. it is a binary join dependency.

(xi)           What are Various mapping constraints: - There are two main types of mapping constraints

(a) Mapping Cardinalities in DBMS: - The mapping cardinalities or the cardinality ratio represents the number of relationship instances that an entity can participate in.

(b)     Existence Dependencies: - Existence dependencies are another  important class of mapping constraints in dbms. If the existence of entity X depends on the existence of entity Y, the X is said to be existence dependent as Y. So, operationally if Y is deleted, so is X.  Entity Y is said to be a Dominant Entity and S is said to be a Subordinate entity.

(xii)        What is trigger and explain its characteristics: - Trigger is an SQL procedure that initiates an action when an event (inset/delete/update) occurs. They are stored and managed by DBMS. A trigger cannot be called or executed: The DBMS automatically fires the trigger as a result of data modification to the associated table. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. Each trigger is attached to a single, specific table in the database.

Types of Triggers:

Depending upon, when a trigger is fired, it may be classified as:

·       Statement-level trigger

·       Row-level trigger

·       Before trigger

·       After triggers

Marks of 10:

(i)               Explain various users of DBMS.

(ii)             Explain database administrator

(iii)          Explain relational model with constraint with keys.

(iv)           Explain DBMS architecture with three levels