Sub:-DATABASE MANAGEMENT SYSTEM
Question Bank
1. Answer the following
State and explain aggregate functions in SQL with example.
Explain the normalization process.
Explain primary key and foreign key with suitable example.
Explain various DML commands with example.
List various DDL Commands. Explain any one with Example
What is Normalization? Explain 1NF with suitable example
List various DCL Commands. Explain any one with Example.
Explain ALTER Table
Explain Drop and Truncate
Explain Union ,Intersect with Example.
2.Attempt the following :
1.Consider the following entities and their relationship
Room (roomno, desc, rate)
Guest (gno, gname, no_of_days)
The relationship between Room and Guest is One to One (any four)
1.Update the rate of room to 5000 whose type is “AC”.
2.Display the name of guest who is staying 2 days in roomno 101.
3.Insert a row in Room table
4.Find no. of AC rooms
5.Display room details according to its rates in ascending order.
2.Consider the following entities and their relationships.
Emp(eno ,ename ,designation ,salary, Date_Of_Joining)
Dept(dno,dname ,loc)
The relationship between Dept & Emp is one-to-many. (any FOUR)
Add column phone_No into Emp table with data type int.
Delete the details of Employee whose designation is ‘Manager’.
Display the count of employees department wise.
Display the name of employee who is ‘Manager’ of “Account Department”.
Display the names of employees whose salary is greater than 50000 and department is “Quality”.
3 .Attempt the following :
1.In a nursery, the plants are sold to the customers, these plants are flowering and non-flowering only. Nutrients are given to the plant with some quantity. Nutrients include pesticides, watering.
1) Identify all entities
2) Identify all relations
3) Draw ERD.
2.An Insurance agent sells insurance policies to clients.Policies can be of different types such as Vehicle Insurance,Life Insurance ,Accident Insurance etc.The agent collects monthly premiums on the policies in the form of cheques of local bank.Database should provide the various details to the user.
1)Identify all entities
2)Identify all relationship
3)Draw E-R diagram
3.Design database for banking enterprise which recods information about customers, employees of bank. A customer can be depositor or borrower.An employee of the bank can be customer of bank. There are two types of accounts,saving account and current account .
A database should provide following details.
1)Identify all entities
2)Identify all relationship
3)Draw E-R diagram.
4.A reputed general hospital has decided to computerized its operations .In hospital many doctors are working. The patients are admitted to the hospital ,into the room. There they are treated by various doctors. Some times patients have to undergo certain pathological tests ,which are carried out in the labs.
A database should provide following details.
1)Identify all entities
2)Identify all relationship
3)Draw E-R diagram
DBMS
Assignment
1
1. What is Normalization? Explain types of normal form (1NF, 2NF, 3NF) with suitable example.
2. What are different aggregate function in SQL. Explain with
Example.
3. Explain primary key and foreign key with suitable example.
Assignment
2
1. List various DML Commands. Explain Syntax with Example.
2. Difference between drop and truncate with Example
3. Explain ALTER Command with Example.
