DBMS-Part2

        Q2) Attempt any Four of the following:                                            [16]

     1.     Explain Insert Command with syntax and Example .

SQL INSERT statement is a SQL query. It is used to insert a single or a multiple records in a table.

INSERT INTO table_name  VALUES (value1, value2, value3....);

INSERT INTO STUDENTS (ROLL_NO, NAME, AGE, CITY)  VALUES (1, ABHIRAM, 22, ALLAHABAD); 

3. Write a note on Data Dictionary

A data dictionary contains metadata i.e data about the database. The data dictionary is very important as it contains information such as what is in the database, who is allowed to access it, where is the database physically stored etc.

 The data dictionary in general contains information about the following −

1.                     Names of all the database tables and their schemas.

2.                     Details about all the tables in the database, such as their owners, their security constraints, when they were created etc.

3.                     Physical information about the tables such as where they are stored and how.

4.                     Table constraints such as primary key attributes, foreign key information etc.

5.                     Information about the database views that are visible.

4. What is Join ?Explain types of Join with Example .

JOIN clause is used to combine the records from two or more tables in a database.

Types of SQL JOIN

1.                  INNER JOIN

2.                  LEFT JOIN

3.                  RIGHT JOIN

4.                  FULL JOIN

1. INNER JOIN

In SQL, INNER JOIN selects records that have matching values in both tables as long as the condition is satisfied. It returns the combination of all rows from both the tables where the condition satisfies.

Syntax

1.                  SELECT table1.column1, table1.column2, table2.column1,.... 

2.                  FROM table1  

3.                  INNER JOIN table2 

4.                  ON table1.matching_column = table2.matching_column; 

 

Query

SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT FROM EMPLOYEE  INNER JOIN PROJECT   ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID; 

2. LEFT JOIN

The SQL left join returns all the values from left table and the matching values from the right table. If there is no matching join value, it will return NULL.

3. RIGHT JOIN

In SQL, RIGHT JOIN returns all the values from the values from the rows of right table and the matched values from the left table. If there is no matching in both tables, it will return NULL.

4. FULL JOIN

In SQL, FULL JOIN is the result of a combination of both left and right outer join. Join tables have all the records from both tables. It puts NULL on the place of matches not found.

5. Explain Alter  Command with syntax and Example .

The ALTER TABLE statement in Structured Query Language allows you to add, modify, and delete columns of an existing table. This statement also allows database users to add and remove various SQL constraints on the existing tables.

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

ALTER TABLE ADD Column statement in SQL

ALTER TABLE table_name ADD column_name column-definition; 

ALTER TABLE Cars ADD Car_Model Varchar(20); 

 

ALTER TABLE DROP Column statement in SQL

ALTER TABLE table_name DROP Column column_name ; 

ALTER TABLE Cars DROP COLUMN Car_Color ; 

 

6. Explain advantages and disadvantages of DBMS

    Advantages of DBMS

The advantages of the DBMS are explained below −

·         Redundancy problem can be solved.

In the File System, duplicate data is created in many places because all the programs have their own files which create data redundancy resulting in wastage of memory. In DBMS, all the files are integrated in a single database. So there is no chance of duplicate data.

For example: A student record in a library or examination can contain duplicate values, but when they are converted into a single database, all the duplicate values are removed.

·         Has a very high security level.

Data security level is high by protecting your precious data from unauthorized access. Only authorized users should have the grant to access the database with the help of credentials.

·         Presence of Data integrity.

Data integrity makes unification of so many files into a single file. DBMS allows data integrity which makes it easy to decrease data duplicity Data integration and reduces redundancy as well as data inconsistency.

·         Support multiple users.

DBMS allows multiple users to access the same database at a time without any conflicts.

·         Avoidance of inconsistency.

DBMS controls data redundancy and also controls data consistency. Data consistency is nothing but if you want to update data in any files then all the files should not be updated again.

In DBMS, data is stored in a single database so data becomes more consistent in comparison to file processing systems.

·         Shared data

Data can be shared between authorized users of the database in DBMS. All the users have their own right to access the database. Admin has complete access to the database. He has a right to assign users to access the database.

·         Enforcement of standards

As DBMS have central control of the database. So, a DBA can ensure that all the applications follow some standards such as format of data, document standards etc. These standards help in data migrations or in interchanging the data.

·         Any unauthorized access is restricted

Unauthorized persons are not allowed to access the database because of security credentials.

·         Provide backup of data

Data loss is a big problem for all the organizations. In the file system users have to back up the files in regular intervals which lead to waste of time and resources.

DBMS solves this problem of taking backup automatically and recovery of the database.

Tunability

Tuning means adjusting something to get a better performance. Same in the case of DBMS, as it provides tunability to improve performance. DBA adjusts databases to get effective results.

Disadvantages of DBMS

The disadvantages of DBMS are as follows:

·         Complexity

The provision of the functionality that is expected of a good DBMS makes the DBMS an extremely complex piece of software. Database designers, developers, database administrators and end-users must understand this functionality to take full advantage of it.

Failure to understand the system can lead to bad design decisions, which leads to a serious consequence for an organization.

·         Size

The functionality of DBMS makes use of a large piece of software which occupies megabytes of disk space.

·         Performance

Performance may not run as fast as desired.

·         Higher impact of a failure

The centralization of resources increases the vulnerability of the system because all users and applications rely on the availability of DBMS, the failure of any component can bring operation to halt.

·         Cost of DBMS

The cost of DBMS varies significantly depending on the environment and functionality provided. There is also the recurrent annual maintenance cost.

7. List various DML commands. Explain any one with an example. 

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;

 

8.Explain Primary Key and Foreign Key with example 

The following table highlights all the important differences between primary key and foreign key −

Key	Primary Key	Foreign Key
Basic	It is used to uniquely identify data in the table.	It is used to maintain relationship between tables.
Null	It can't be NULL.	It can accept the NULL values.
Duplicate	Two or more rows can't have same primary key.	It can carry duplicate value for a foreign key attribute.
Index	Primary has clustered index.	By default, It is not clustered index.
Tables	Primary key constraint can be defined on temporary table.	It can't be defined on temporary tables.

                

A primary key is a column -- or a group of columns -- in a table that uniquely identifies the rows of data in that table. For example, in the table below, CustomerNo, which displays the ID number assigned to different customers, is the primary key.

In simpler words, a foreign key is a set of attributes that references a candidate key. For example, a table called TEAM may have an attribute, MEMBER_NAME, which is a foreign key referencing a candidate key, PERSON_NAME, in the PERSON table.

 

9.Explain the advantages of Network Model over Hierarchical Model.

          Difference between Hierarchical Data Model and Network Data Model :

S. No.	Hierarchical Data Model	Network Data Model
1.	In this model, to store data hierarchy method is used.	In this model, you could create a network that shows how data is related to each other.
2.	It implements 1:1 and 1:n relations.	It implements 1:1, 1:n and also many to many relations.
3.	To organize records, it uses tree structure.	To organize records, it uses graphs.
4.	Records are linked with the help of pointers.	Records are linked with the help of linked list.
5.	Insertion anomaly exits in this model i.e. child node cannot be inserted without the parent node.	There is no insertion anomaly.
6.	Deletion anomaly exists in this model i.e. it is difficult to delete the parent node.	There is no deletion anomaly.
7.	It is used to access the data which is complex and asymmetric.	It is used to access the data which is complex and symmetric.
8.	When update operation is performed, it suffers from inconsistency problem because of the existence of multiple instances of child records.	No such problem exists because of the single occurrence of records while updating.
9.	This model lacks data independence.	There is partial data independence in this model.
10.	Less flexible in comparison to the relational model.	It is flexible.
11.	When you are searching for a record then firstly you need to visit parent record before retrieving a child record.	Searching for a record is easy because of the availability of multiple access paths to reach data item.
12.	Example- IBM’s IMS (Information Management System) implement this model.	Example- Oracle. SQL Server, Sybase DBMS implement this model.

 

10.Explain Basic File operations. 

• Creating a file. Two steps are necessary to create a file.

1.      Space in the file system must be found for the file.

2.      An entry for the new file must be made in the directory.

• Writing a file. To write a file, we make a system call specifying both the name of the file and the information to be written to the file. The system must keep a write pointer to the location in the file where the next write is to take place. The write pointer must be updated whenever a write occurs.
• Reading a file. To read from a file, we use a system call that specifies the name of the file and where (in memory) the next block of the file should be put. The system needs to keep a read pointer to the location in the file where the next read is to take place.

• Because a process is usually either reading from or writing to a file, the current operation location can be kept as a per-process current-file-position pointer.
• Both the read and write operations use this same pointer, saving space and reducing system complexity.
• Repositioning within a file. The directory is searched for the appropriate entry, and the current-file-position pointer is repositioned to a given value. Repositioning within a file need not involve any actual I/O. This file operation is also known as a file seek.
• Deleting a file. To delete a file, we search the directory for the named file. Having found the associated directory entry, we release all file space, so that it can be reused by other files, and erase the directory entry.

Truncating a file. The user may want to erase the contents of a file but keep its attributes. Rather than forcing the user to delete the file and then recreate it, this function allows all attributes to remain unchanged (except for file length) but lets the file be reset to length zero and its file space released

11.Explain Update and Delete Command with syntax and Example

 

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;

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;

 12.What is DBMS? Explain applications of DBMS.

 A Database Management System (DBMS) is software that enables the creation, management, and manipulation of databases. It provides an interface for interacting with databases and includes tools for defining, updating, querying, and administering databases

 Applications of DBMS:

Business and Finance:

Managing customer information, financial records, and transactions.

Facilitating online banking systems.

Education:

Storing student records, grades, and educational resources.

Managing course schedules and curriculum.

Healthcare:

Maintaining patient records, medical history, and treatment plans.

Supporting healthcare management and research.

E-commerce:

Handling product catalogs, inventory, and order processing.

Managing customer accounts and purchase histories.

Human Resources:

Storing employee information, payroll data, and performance records.

Managing recruitment and personnel activities.

Telecommunications:

Managing subscriber information and call records.

Supporting network management and billing systems.

Government and Public Services:

Managing citizen databases, public records, and government services.

Supporting law enforcement and public administration.

Scientific Research:

Storing and analyzing research data.

Collaborating on scientific projects and experiments.