DBMS pART-3

Q5) Write short notes on any Two of the following:                    [6]

1. Advantages and Disadvantages of  DBMS.

Advantages of DBMS

·        Controls database redundancy: All the data is stored in one place, and that recorded in the database and hence controls the redundancy in the database.

·      Data sharing: DBMS allows users with authority to share the data in the database with multiple users.

·        Easy Maintenance: The centralized nature of the database helps in the easy maintenance of the data.

·        Reduce time: It reduces the maintenance need and development time.

·        Backup: It automatically backs up data to maintain its integrity in case of failure.

·        Multiple user interfaces: It offers a number of user interface to multiple users.

Disadvantages of DBMS

·        Cost of software and hardware: It requires a number of high powered processors and large size memory to run DBMS.

·        Size: a Large amount of storage size is required to run DBMS efficiently.

·        Complexity: DBMS adds an additional layer of complexity to the data.

·        Higher impact of failure: DBMS faces a higher risk of losing the data since all the data is stored at a single location and a catastrophic failure can wipe it all.

   2. Normalization

·        Normalization is the process of organizing the data in the database.

·        Normalization is used to minimize the redundancy from a relation or set of relations. It is also used to eliminate undesirable characteristics like Insertion, Update, and Deletion Anomalies.

·         Normalization divides the larger table into smaller and links them using relationships.

·         The normal form is used to reduce redundancy from the database table.

 

Normal Form	Description
1NF	A relation is in 1NF if it contains an atomic value.
2NF	A relation will be in 2NF if it is in 1NF and all non-key attributes are fully functional dependent on the primary key.
3NF	A relation will be in 3NF if it is in 2NF and no transition dependency exists.
BCNF	A stronger definition of 3NF is known as Boyce Codd's normal form.
4NF	A relation will be in 4NF if it is in Boyce Codd's normal form and has no multi-valued dependency.
5NF	A relation is in 5NF. If it is in 4NF and does not contain any join dependency, joining should be lossless.

 

3.UNION  Operation 

UNION is used to combine the results of two or more SELECT statements. However it will eliminate duplicate rows from its resultset. In case of union, number of columns and datatype must be same in both the tables, on which UNION operation is being applied.

Example of UNION

The First table,

ID	Name
1	abhi
2	adam

The Second table,

ID	Name
2	adam
3	Chester

Union SQL query will be,

SELECT * FROM First  UNION SELECT * FROM Second;

The resultset table will look like,

ID	NAME
1	abhi
2	adam
3	Chester

4.CARTESIAN PRODUCT operation 

It combines R1 and R2 without any condition. It is denoted by X.

Degree of R1 XR2 = degree of R1 + degree of R2

{degree = total no of columns}

Example

Consider R1 table −

RegNo	Branch	Section
1	CSE	A
2	ECE	B
3	CIVIL	A
4	IT	B

Table R2

Name	RegNo
Bhanu	2
Priya	4

R1 X R2

RegNo	Branch	Section	Name	RegNo
1	CSE	A	Bhanu	2
1	CSE	A	Priya	4
2	ECE	B	Bhanu	2
2	ECE	B	Priya	4
3	CIVIL	A	Bhanu	2
3	CIVIL	A	Priya	4
4	IT	B	Bhanu	2
4	IT	B	Priya	4

5.DML commands

The examples of DML in the Database Management System (DBMS) are as follows −

·               SELECT − Retrieve data from the database.

·               INSERT − Insert data into a table.

·               UPDATE − Update existing data within a table.

·               DELETE − Delete records from a database table.

 

Syntax for DML Commands

The syntax for the DML commands are as follows −

INSERT

Insert command is used to insert data into a table.

The syntax for insert command is as follows −

Syntax

Insert into <table_name> (column list) values (column values);

For example, if we want to insert multiple rows to the Employee table, we can use the following command −

Example

Insert into Employee(Emp_id, Emp_name) values (001, “ bhanu”);

SELECT

Select command is used to retrieve data from the database.

The syntax for the select command is as follows −

Syntax

SELECT * from <table_name>;

DELETE

Delete command is used to delete records from a database table.

The syntax for the delete command is as follows -

Syntax

 Delete from <table_name>WHERE condition;

For example, if we want to delete an entire row of employee id 002, we can use the following command −

Example

DELETE from Employee WHERE Emp_id=002;

UPDATE

Update command is used to update existing data within a table.

The syntax for the update command is as follows -

Syntax

UPDATE <table_name> SET column_number =value_number WHERE condition;

For example, if we want to update the name of the employee having the employee id 001, we can use the command given below −

Example

UPDATE Employee SET Emp_name= Ram WHERE Emp_id= 001;

 

6. Logical File and Physical File

 

Physical File	Logical File
It occupies the portion of memory. It contains the original data.	It does not occupy memory space. It does not contain data.
A physical file contains one record format.	It can contain up to 32 record formats.
It can exist without a logical file.	It cannot exist without a physical file.
If there is a logical file for the physical file, the physical file cannot be deleted until and unless we delete the logical file.	If there is a logical file for a physical file, the logical file can be deleted without deleting the physical file.
CRTPF command is used to make such an object.	CRTLF command is used to make such an object.
Physical files represent the real data saved on an iSeries system and describe how the data is to be displayed to or retrieved from a program.	The logical file represents one or multiple physical files. It also has a description of the records found in one or multiple physical files.
If there is a logical file for a physical file, the physical file can’t be deleted until and unless we delete the Logical file.	If there is a logical file for a physical file, the logical file can be deleted without deleting the physical file.