All Questions and Answers of DBMS (Guided) Everything For Help. #everythingforhelp #EverythingForHelp "everything for help"

1 Mark Questions and Answers of DBMS for Exam Preparation:

Define Record: - Records are composed of fields, each of which contains one item of information. -OR- A record is a group of related data held within the same structure.
Define degree: - Degree is the total number of attributes/columns of a relation/table.
Define foreign key: - Foreign keys are the columns of a table that points to the primary key of another table.
Define SQL: - SQL (Structure Query Language) is a database query language used for storing and managing data in Relational Database Management System (RDBMS).
Define mapping: - Mapping is the number of values of one entity connected to number of values of another entity.
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 schema, tables, views, indexes, etc.
Define DML: - Data Manipulation Language (DML) is a set of syntax elements. It is used to manage the data in the database.
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.
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.
Define candidate key: - Minimum set of attributes used to uniquely differentiate records of the table/relation (It is also known as prime attributes).
Define Entity: - An entity is a real-world object that are represented in database. It can be any things Like: object, place, person or class.
Define Tuple: - A single entry in a table is called a tuple or row. A tuple in a table/relation represents a set of related data. -OR- A single row of a table, which contains a single record for that relation is called a tuple.
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).
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.
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.
What is Schema: - Description of the database is called the database schema -OR- It is the design of a database.
Examples of RDBMS, DBMS: -

RDBMS

DBMS

SQL Server, Oracle, MySQL, MariaDB, SQLite etc.

dBase, Microsoft Access, LibreOffice Base, FoxPro et.
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.
Define grant (command): - SQL GRANT is a command used to provide access or privileges on the database objects to the users.
Define REVOKE: - The REVOKE command removes user access rights or privileges to the database objects.

2 Marks Questions and Answers of DBMS for Preparation:

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: (i). Physical Data Independence (ii). Logical Data Independence.
What are three levels of DBMS architecture: -

Three Levels of DBMS Architecture By: - Everything For Help

3. 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: (i). One-to- one (1:1). (ii). One-to-many (1:M). (iii). Many-to-many (M: N). Note: The latter one is correct, it is M: N and not M: M.

4. 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

5. 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. (i) Discretionary Access Control. (ii) Mandatory Access Control. (iii) Role- Based Access Control.

6. 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.

7. 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

8. 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.

9. 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.

10. What are various notations used in E-R diagram: - (a) Entity

(b)Relationship

(c)Attributes

(d)Weak entity

(e)Weak entity Relationship

(f)Multi-valued Attribute Key attribute

(g)Composite Attribute

11. 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

12. 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*/

13. 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)

);

14. 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

15. 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.

5 Marks Questions and Answers of DBMS Preparation for Exam: -

1. 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.

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

(ii) 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.

(iii) 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.

(iv) 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!

(v) 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.

(vi) 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:

Defines the data element.

Helps in the scheduling.

Helps in the control.

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

Helps in the identification of the organizational data irregularity.

Acts as a very essential data management tool.

Provides with a good standardization mechanism.

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

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

Stored Data Manager:

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

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

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

(iv) It controls concurrent access.

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

(vi) 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.

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

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

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.

2. Explain insert, update and delete command with example: -

(i) 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

(ii) 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

(ii) 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.

3. 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

1. 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.

4. 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.

5. 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.

6. 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.

7. 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.

8. 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.

9. 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.

10. 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

10 Marks Questions and Answers of DBMS Preparation for Exam:-

1. Explain various users of DBMS:

Classification of DBMS users (explain various users of DBMS)

We can classify the DBMS users in three broad categories as follows:

• Actors on Scene

• Controllers

• DBMS Workers Behind Scene

Actors on scene

Actors on the Scene: The main aim of database is to provide an environment for storing and accessing data in a more secured and efficient way. Once it is created, it can be used by the user in various ways. Depending on its use, the actors on scene or actual users are classified in following two categories:

• Application Programmers

• End Users

Controllers

The second group of people who actually are the DBMS controllers include database administrator, database designers and system analysts.

DBMS controllers that are classified as

• System Analysts

• Database Designer

• Database Administrator

Workers behind scene

The third category of people involved with a DBMS are those who are the actual workers behind the scene. i.e. those who actually perform the job of implementation of the DBMS. Without their job the DBMS management, creation and even use is not possible. This group of people include

• DBMS system designers

• DBMS system implementer

• Tool Developers

• Operators

• Maintenance Personnel

Explain relational model with constraint keys

• Relational Model represents how data is stored in Relational Databases. A relational database stores data in the form of relations (tables). Consider a relation Student with attributes Roll_No., Name, Address, Phone and Age.

• Key Integrity: Every relation in the database should have at least one set of attributes which defines a tuple uniquely. Those set of attributes is called key. E.g.: Roll_No in Student is a key. No two students can have same roll number. So a key has two properties:

I. It should be unique for all tuples.

II. It can’t have Null values.

Explain DBMS architecture with three levels

Recents in Beach

All Questions and Answers of DBMS (Guided) Everything For Help. #everythingforhelp #EverythingForHelp "everything for help"