Database management System

---------------×------------------××--------------------×---------------

Database Management System

---------------×------------------××--------------------×---------------

1. Questions and Answers

2. DBMS Written Notes

3. Notes On SQL

4. Some Commands For Practical Use

5. DBMS Assignments

6. DBMS Textbook

---------------×------------------××--------------------×---------------

Questions And Answers

---------------×------------------××--------------------×---------------

1. What is Data ?

Ans. :- Data is the raw, unprocessed information that serves as the building blocks for information systems.

---------------×------------------××--------------------×---------------

2. Define Database

Ans. :- Database is collection of releated data items stored and managed in structured manner with a computer system. In our words, Data is information that is stored in Database more efficiently and in structured manner.

---------------×------------------××--------------------×---------------

3. Define DBMS

Ans. :- DBMS is known as Database Management System it is collection of software which helps user to create, maintain and manipulate Databases.

---------------×------------------××--------------------×---------------

4. Write Advantages of Database System over File System.

Ans.:-

1. Redundancy can be Reduced.

2. Inconsistency can be avoided.

3. Data can be shared.

4. Centralised control of Data.

5. Data Independence can be provided.

6. Security restrictions can be applied.

7. Standards can be enforced.

8. Integrity can be maintained.

---------------×------------------××--------------------×---------------

5. List application of DBMS

Ans.:-

1. Healthcare industry:- to store information of all staff.

2. Banking and Finance :- to store information of customers and their bank transactions.

3. Telecommunication :- to store information of customers and their incoming and outgoing call records ,sms, etc.

4. Government and Public Services :- To store information of all staff, schemes, records,etc to the government databases. To manage this information.

5. Education :- to store and manage information of students i.e. attendance, marks , credits ,etc.

---------------×------------------××--------------------×---------------

6. List Types of Keys

Ans.:-

Super Key
Composite Key
Candidate Key
Primary Key
Foreign Key

---------------×------------------××--------------------×---------------

7. What is Role of Database Administrator ?

Ans.:- A Database Administrator (DBA) is responsible for maintaining, managing , and ensuring the efficient operations on database system.

The Role of DBA includes :-

1. Database Design and Implementation

2. Database Maintenance

3. Data Security

4. Performing Monitoring and Optimization

5. Data Integrity and Consistency

6. Troubleshooting and Support

7. Database Migration and Updation

8. Backup and Recovery of Data

Overall , Database Administrator plays crucial role for maintaining companies data assets are secure, well managed and available when needed.

---------------×------------------××--------------------×---------------

8. What is Data Model and Enlist it's types.

Ans.:- 1. A Data Model is an overview of an Software system which describes that how data can be represented and how data can be accessed from software system after complete implementation.

2. A Data Model defines data elements and relationships among data elements for a specific system.

3. Different types of Data Model

A. Relational Model

B. Network Model

C. Hierarchical Model

---------------×------------------××--------------------×---------------

9. What is Strong Entity ?

Ans.:- Entity type, which has its key attributes by which we can identify a specific entity uniquely, is called as Strong Entity set.

---------------×------------------××--------------------×---------------

10. What is Weak Entity ?

Ans.:- A Entity type that cannot form distinct keys from its attributes and takes helps from corresponding strong entity is called as Weak Entity set.

---------------×------------------××--------------------×---------------

11. What are Attributes ?

Ans.:- Various Properties that describes an entity are known as Attributes. The attribute value describes each entity becomes a major part of data stored in Database.

---------------×------------------××--------------------×---------------

12. Types of Attributes

Ans.:-

1. Simple and Composite Attribute

2. Single valued and Multi valued attributes

3. Stored and derived attributes

4. Null attributes

5. Key Attributes

---------------×------------------××--------------------×---------------

13. What is Primary Key ?

Ans.:- A Primary Key is a column or group of column in a table that uniquely identify tuple or record (row) in relational table.

Primary key cannot be a null value and it must be unique for each tuple in relational table.

---------------×------------------××--------------------×---------------

14. What is Foreign Key ?

Ans.:-

A Foreign Key is a column or group of column in table that provides a connection between data of two tables.
The tuple in one relation refers only to an existing tuple in one relation.
Foreign Key acts as a cross reference between two tables because it refers to the primary key of another table, so it establishes a link between tables.

---------------×------------------××--------------------×---------------

15. Difference Between Logical Data Independence and Physical Data Independence

Ans.:-

---------------×------------------××--------------------×---------------

16. Explain E-R Diagram.

Ans :-

---------------×------------------××--------------------×---------------

17. E-R Notations for various types of Attributes.

Ans.:-

---------------×------------------××--------------------×---------------

18. Draw an E-R Diagram on Library Management System considering issue and return, fine calculation facility also show primary key, weak entity and strong entity.

Ans.:-

---------------×------------------××--------------------×---------------

19. List disadvantages of typical file processing system.

Ans.:-

1. Data redundancy and inconsistency

2. Difficulty in accessing data

3. Data isolation

4. Integrity problems

5. Atomicity problems

---------------×------------------××--------------------×---------------

20. Define Data Abstraction.

Ans.:- Hiding Data structure complexity from end user in different levels is known as Data Abstraction.

To show only essential details and hide unwanted details i.e. how data is arranged in database from user is known as Data Abstraction.

---------------×------------------××--------------------×---------------

21. What is cursor ?

Ans. A cursor is a temporary work area created in the system memory when a SQL statement

is executed.

Types of cursor :-

1. Implicit Cursor

2. Explicit Cursor

---------------×------------------××--------------------×---------------

22. Hierarchical Model vs Network Model

Ans.

Sr. No.	Hierarchical model	Network Model
1.	Hierarchical model is not more popular than network model	Network model is more popular than the hierarchical and relational model.
2.	it does not use client server architecture	It uses client server architecture
3.	One to many relationship is maintained	one to many and many to many relationships is maintained
4.	Hierarchical model is based on tree like structure with one root.	Network model is based on tree like structure with many root.
5.	One child or many children have only one parent	Many Children have many parents
6.	Main application of hierarchical model is in the mainframe database system.	It is upgraded version of hierarchical model so used in network

---------------×------------------××--------------------×---------------

23. What is cursor ?

Ans.:- The oracle engine uses private work for it's internal process in system to execute or to run SQL Commands. This private workspace is known as cursor. The private workspace used for its internal processing of sql commands or to excute sql commands is called as cursor.

---------------×------------------××--------------------×---------------

24. State any two advantages of functions in PL/SQL.

Ans.

Advantages of functions in PL/SQL:

• Work can be divided into smaller modules so that it can be manageable and also enhances the readability of the code.

• It promotes reusability.

• It is secure, as the code is in the database and hides the internal database details from the user.

• It improves performance against running SQL queries multiple times.

---------------×------------------××--------------------×---------------

25. What is instance ?

Ans.:- Collection of relational data stored in database at particular time or movement is called as Instance.

---------------×------------------××--------------------×---------------

26) Describe database backups with it's types.

Ans:

Regular backups are required to protect database and ensure its restoration in case of failure. Various backup types provide different protection to our database. Backing up and restoring data is one of the most important responsibilities of IT professionals Three common types of database backups can be run on a desired system: normal (full), incremental and differential.

i) Normal or Full Backups: When a normal or full backup runs on a selected drive, all the files on that drive are backed up. This, of course, includes system files, application files, user data — everything. Those files are then copied to the selected destination (backup tapes, a secondary drive or the cloud), and all the archive bits are then cleared. Normal backups are the fastest source to restore lost data because all the data on a drive is saved in one location.

ii) Incremental Backups: A common way to deal with the long running times required for full backups is to run them only on weekends. Many businesses then run incremental backups throughout the week since they take far less time. An incremental backup will grab only the files that have been updated since the last normal backup. Once the incremental backup has run, that file will not be backed up again unless it changes or during the next full backup.

iii) Differential Backups: An alternative to incremental database backups that has a less complicated restore process is a differential backup. Differential backups and recovery are similar to incremental in that these backups grab only files that have been updated since the last normal backup. However, differential backups do not clear the archive bit. So a file that is updated after a normal backup will be archived every time a differential backup is run until the next normal backup runs and clears the archive bit.

---------------×------------------××--------------------×---------------

27) Write down any four Dr. E.F Codd's rules.

Ans:

Dr. E. F. Codd’s Rules:

Rule 1: The information Rule: all data viewed to users

Rule 2: Guaranteed Access Rule: all user get access to database

Rule 3: Systematic treatment of null values: null value should be empty

Rule 4: Dynamic online Catalog: record all transactions in database

Rule 5: Data Sub language rule: use only one language

Rule 6: View updating rule: table and view updated simultaneously

Rule 7: High level insert, delete and update: multiple insert delete update

Rule 8: Physical data independence: hardware change

Rule 9: Logical data independence: structure change

Rule 10: Integrity independence: store correct data

Rule 11: Distribution independence: distributed database

Rule 12: No subversion rule: no version of language used

---------------×------------------××--------------------×---------------

28) State the use of group by and order by clauses.

Ans:

Group by Clause: Group by clause is used to collect the data as multiple records and group them to produce the result.

Syntax:

SELECT column_name, function(column_name)

FROM table_name

WHERE condition

GROUP BY column_name;

Ex: select avg(sal) from emp_details group by deptno;

Order by Clause: To view the data in sorted order, the order by clause is used. By default, the data is sorted in ascending order.

Syntax:

SELECT expressions FROM tables [WHERE conditions] ORDER BY expression [ ASC | DESC ];

Ex: select deptno from emp_details order by deptno; Ex: select deptno from emp_details order by deptno desc; (for descending order)

---------------×------------------××--------------------×---------------

29. Differentiate between Drop and Truncate command

Ans.

---------------×------------------××--------------------×---------------

DBMS Written Notes

---------------×------------------××--------------------×---------------

1. Introduction To Database System

---------------×------------------××--------------------×---------------

Notes On SQL

What is SQL ?

=> SQL stands for Structured Query Language

=> SQL lets you acess and manipulate database

=> SQL become a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organisation For Standardization (ISO) in 1987.

RDBMS

=> RDBMS stands for Relational Database Management System

=> RDBMS is the basis for SQL, and for all Modern Database Systems such as MS SQL Server, IBM DB, Oracle , MySQL , and Microsoft Access.

Some Of The Most Important SQL Commands

SELECT - extracts data from a database

UPDATE - updates data in Database

DELETE - delete data from a database

INSERT INTO - inserts new data into a Database

CREATE DATABASE - creates a new Database

ALTER DATABASE - Modifies a database

CREATE TABLE - creates new table

ALTER TABLE - modifies table

DROP TABLE - deletes a table

CREATE INDEX - creates an index

DROP INDEX - deletes an index

SQL SELECT statement :-

The SELECT statement is used to select data from database.

Example Query :-

SELECT CustomerName, City FROM Customers;

To Select All Columns

Example Query :-

SELECT * FROM Customers;

SQL SELECT DISTINCT Statement :-

The SELECT DISTINCT Statement is used to return only distinct (different) values.

Example :-

SELECT DISTINCT City FROM Customers;

The SQL Where Clause

The WHERE clause is used to filter records.

It is used to extract only those records that fulfill a specified condition.

Example :-

SELECT * FROM Customers WHERE City = 'SATARA' ;

Syntax :-

SELECT Column1, Column2, ColumnN FROM table_name WHERE Condition;

Note :- The WHERE Clause is not only used in SELECT Statements , it is also used in used in update, DELETE, etc.

Operators in The WHERE Clause

Example :- Select all Customers with a Customer Id greater than 80 :-

SELECT * FROM Customers WHERE CustomerID > 80;

Operators :-

= :-- Equal

> :-- Greater than

< :-- Less than

>= :-- Greater than equal to

<= :-- Less than equal to

<> :-- NOT Equal

Between :-- Between or certain range

LIKE :-- Search for pattern

IN :-- To specify multiple possible values for a column

---------------×------------------××--------------------×---------------

DBMS Assignments

---------------×------------------××--------------------×---------------

Assignment No. 1

---------------×------------------××--------------------×---------------

Assignment No. 2

---------------×------------------××--------------------×---------------

Assignment No. 3

---------------×------------------××--------------------×---------------

Assignment No. 4

---------------×------------------××--------------------×---------------

Assignment No. 5

---------------×------------------××--------------------×---------------

Some Commands for Practical Use

---------------×------------------××--------------------×---------------

1. show databases;

2. use demo;

3. select * from emp;

output :-

+--------+-----------+---------+-----------+--------+--------+

+--------+-----------+---------+-----------+--------+--------+

| 1 | Demo | 2217 | DBMS | 2023 | 30000 |

| 2 | Akshay | 2222 | DSU | 2024 | 35000 |

| 3 | sudarshan | 2322 | DTE | 2123 | 10000 |

4. desc emp;

output :-

+-----------+--------------+------+-----+---------+-------+

+-----------+--------------+------+-----+---------+-------+

| emp_no | int(11) | NO | PRI | NULL | |

| dept_no | int(255) | YES | | NULL | |

| job_id | int(45) | YES | | NULL | |

| salary | int(200) | YES | | NULL | |

5. desc dept;

output :-

+-----------+--------------+------+-----+---------+-------+

+-----------+--------------+------+-----+---------+-------+

| dept_no | int(11) | NO | PRI | NULL | |

| emp_no | int(255) | YES | MUL | NULL | |

+-----------+--------------+------+-----+---------+-------+

6. desc stud;

output :-

+------------+--------------+------+-----+---------+-------+

+------------+--------------+------+-----+---------+-------+

| rollno | int(11) | NO | PRI | NULL | |

| percentage | int(200) | YES | | NULL | |

+------------+--------------+------+-----+---------+-------+

7. alter table stud add city varchar(50);

8. alter table stud modify studname varchar(200);

9. alter table stud add constraint chk_percent CHECK (percentage<=100);

10. select * from stud;

output :-

+--------+----------+------------+------+

+--------+----------+------------+------+

| 1222 | Demo | 110 | NULL |

| 1322 | rohan | 97 | NULL |

+--------+----------+------------+------+

11. insert into stud values(1323,'rony',120,'Pune');

12. select * from stud;

output :-

+--------+----------+------------+------+

+--------+----------+------------+------+

| 1222 | Demo | 110 | NULL |

| 1322 | rohan | 97 | NULL |

| 1323 | rony | 120 | Pune |

+--------+----------+------------+------+

13. update stud SET city='Karad' where rollno=1222;

14. update stud SET city='Belgaon' where rollno=1322;

15. update stud SET city='Mumbai' where rollno=1323;

16. select * from stud;

output:-

+--------+----------+------------+---------+

+--------+----------+------------+---------+

| 1222 | Demo | 110 | Karad |

| 1322 | rohan | 97 | Belgaon |

| 1323 | rony | 120 | Mumbai |

+--------+----------+------------+---------+

---------------×------------------××--------------------×---------------

1. show databases;

2. create database practical_4;

OUTPUT :-

+--------------------+

| Database |

+--------------------+

| information_schema |

| aryan |

| mysql |

| performance_schema |

| practical_4 |

| test |

+--------------------+

3. use practical_4;

4. create table EMP(EMPNO int PRIMARY KEY, ENAME varchar(255), DNAME varchar(255), JOB varchar(255), HIREDATE DATE, LOC varchar(255));

5. insert into EMP values(7876,'ADAMS','RESEARCH','CLERK',23/05/87,'DALLAS');

6. insert into EMP values(7499,'ALLEN','SALES','SALESMAN',20/02/81,'CHICAGO');

7. insert into EMP values(7698,'SMITH','SALES','MANAGER',01/05/81,'CHICAGO');

8. insert into EMP values(7782,'CLARK','ACCOUNTING','MANAGER',09/06/81,'NEW YORK');

9. SELECT * from EMP;

OUTPUT :-

+-------+-------+------------+----------+------------+----------+

+-------+-------+------------+----------+------------+----------+

+-------+-------+------------+----------+------------+----------+

10. update EMP SET HIREDATE='1981-06-09' WHERE EMPNO=7782;

11. update EMP SET HIREDATE='1987-05-23' WHERE EMPNO=7876;

12. update EMP SET HIREDATE='1981-02-20' WHERE EMPNO=7499;

13. update EMP SET HIREDATE='1981-05-01' WHERE EMPNO=7698;

14. SELECT * from EMP;

O/P :-

+-------+-------+------------+----------+------------+----------+

+-------+-------+------------+----------+------------+----------+

+-------+-------+------------+----------+------------+----------+

15. insert into EMP values(7839,'KING','ACCOUNTING','PRESIDENT','1981-11-17','NEW YORK'),(7566,'JONES','RESEARCH','MANAGER','1981-04-02','DALLAS'),(7900,'JAMES','SALES','CLERK','1981-12-03','CHICAGO'),(7902,'FORD','RESEARCH','ANALYST','1981-12-03','DALLAS');

16. SELECT * from EMP;

O/P :-

+-------+-------+------------+-----------+------------+----------+

+-------+-------+------------+-----------+------------+----------+

+-------+-------+------------+-----------+------------+----------+

17. DELETE FROM EMP WHERE ENAME='SMITH';

18. SELECT * from EMP;

O/P :-

+-------+-------+------------+-----------+------------+----------+

+-------+-------+------------+-----------+------------+----------+

+-------+-------+------------+-----------+------------+----------+

19. UPDATE EMP SET JOB='MANAGER' WHERE ENAME='ADAMS';

20. SELECT * from EMP;

O/P :-

+-------+-------+------------+-----------+------------+----------+

+-------+-------+------------+-----------+------------+----------+

+-------+-------+------------+-----------+------------+----------+

---------------×------------------××--------------------×---------------

Practical No. 2

SQL> create table emp(empno number(4),ename varchar(10),job number(9),mgr number(4),hiredate

date,sal number(7,2),comm number(7,2),deptno number(2));

SQL> alter table emp modify empno primary key;

SQL> desc emp;

SQL> insert into emp values(1111,'Ayush',118619,2217,TO_DATE('2005/08/26','yyyy/mm/dd'),30000.50,902.56,23);

SQL> select * from emp;

SQL> insert into emp values(2222,'zoya',118,3217,'10-Oct-07',30000.50,902,22);

SQL> select * from emp;

SQL> insert into dept values(23,'Computer','Karad');

SQL> insert into dept values(22,'Computer','Malvan');

SQL> select * from dept;

---------------×------------------××--------------------×---------------

Practical No. 3

SQL> create table EMP(emp_no number Primary key,e_name varchar(10),dept_no number(4),dept_name varchar(10),job_id number(4),salary number(6));

SQL> insert into emp values(1111,'aryan',2023,'Computer',1001,20000);

SQL> insert into emp values(2222,'rohan',3423,'HR',1031,30000);

SQL> insert into emp values(3333,'pranav',3453,'HR',1031,35000);

SQL> insert into emp values(4444,'aakash',3555,'Sales',1431,45000);

SQL> select * from emp;

SQL> drop table emp;

SQL> create table emp(empno number primary key,empname varchar(10),salary number(6),phoneno

number(10));

SQL> insert into emp values(1111,'aryan',20000,3040506010);

SQL> insert into emp values(2222,'pranav',23333,1020304050);

SQL> insert into emp values(3333,'john',30000,1098765432);

SQL> select * from emp;

SQL> create table dept(deptno number primary key,empno number, deptname varchar(20),location

varchar(20),constraint fk_empno foreign key (empno) references emp(empno));

SQL> insert into dept values(1000,1111,'Computer','Karad');

SQL> insert into dept values(2000,2222,'Electronics','Karad');

SQL> insert into dept values(3000,3333,'Electrical','Karad');

SQL> select * from dept;

SQL> rename emp to emp_table;

SQL> select * from emp_table;

SQL> alter table dept modify location varchar(15);

SQL> desc dept;

SQL> truncate table dept;

SQL> select * from dept;

SQL> drop table dept;

SQL> drop table emp_table;

---------------×------------------××--------------------×---------------

QUESTION PAPERS AND MODEL ANSWERS PREVIOUS YEARS

Winter 2018

---------------×------------------××--------------------×---------------

MSBTE DBMS PAPERS DOWNLOAD

---------------×------------------××--------------------×---------------

Informative Tech

Database management System