CSMSS COP Assignment No.

1 Year: 2024-25
Subject: DMS [313302] Chapter No: 1 Ms. M. G. Jadhav

1. Define the terms i) Data ii) Database iii) DBMS. (W-13, S-15, W-15 ,S-
17,W-18,S-23)
2. Explain the Disadvantages of file processing system. (W-13, S-15 ,W-
18,S-24)
3. State and explain advantages of DBMS over file processing system. (W-14,
W-15, W-16, S-19, W-19,S-22,W-22)
4. List any four applications of DBMS. (W-13, S-15, W-15)
5. Define Instances and schema. (S-14,S-15,S-19,W-22,S-24)
6. Describe data abstractions. (W-17,W-22,S-24)
7. Explain three levels of data abstractions with diagram. (S-14. W-14, S-
15,W-19,S-22)
8. Draw and explain over all structure of DBMS. (W-13,W-14, S-15,W-16,S-
16,S-19,W-22,S-24)
9. List types of data model. (S-16, W-16)
10. Define i) Data Abstraction ii)Data Redundancy.(W-18,S-24)
11. Define Entity. (S-16)
12. Explain strong entity and weak entity.(S-14, W-14, W-16, S-17)
13. Define attributes and explain any 4 type of attributes.(S-16, S-17, W-17)
14. List Symbols used in E-R Diagram. (W-14,S-15,W-17)
15. Draw E-R diagram for Customer branch and Account relationship.(W-
13,W-17)
16. Draw E-R diagram for library management system. (W-14,W-18,S-19,W-
19,S-23)
17. Draw E-R diagram for hospital management system. (S-16,S-24)
18. Draw E-R diagram for airline reservation system.(S-17)
19. Draw E-R diagram for library management system considering issue and
return, fine calculation facility. (S-15)
20. Draw E-R diagram for Customer and loan. (S-17)
21. Distinguish between network model and hierarchical model. (S-19,W-19,S-
19,S-24,W-19,S-24)
CSMSS COP Assignment No. 2 Year: 2024-25
Subject: DMS [313302] Chapter No: 2 Ms. M. G. Jadhav

1. Define table and field. (S-19)

2. List E. F. Codd rules. (W-14, S-19,S-23)
3. Describe basic concept of Relational model. (S-19)
4. Explain any four types of attributes with example.(S-23)
5. Write down any four Dr. E.F Codd's rules.(S-22)
6. Describe primary key and candidate key. (W-13, S-14, W-14, S-15, W-15,
S-17, W-17,W-18,S-23)
7. Define primary key and foreign key.(W-19)
8. Define Normalization, list its types. (W-18, S-19,W-22,S-23)
9. State and explain 1NF and 2NF with example. (S-23,S-24)
10. State and explain 2NF with example. (S-19)
11. State and explain 3NF with example.(W-19)
12. Explain normalization with 1NF, 2NF and 3NF example. (W-13, S-
14, W-14, W-15, S-16, W-17,W-18)
13. Explain strong entity and weak entity.(S-14, W-14, W-16, S-17,W-
19)
14. Explain strong and weak entity set with example. Draw ER diagram
indicating strong and weak entity set. (S-23)
15. List DCL Command. (W-13,W-17,S-19)
16. What is the use of GRANT and REVOKE? (S-15, S-16, W-16, W-
17)
17. Give Syntax and example for CREATE Command. (W-16,W-19)
18. Give Syntax and example for ALTER Command. (W-14, W-16, W-
17,W-19)
19. Describe commit and rollback with syntax and example. (W-18)
20. Give Syntax and example for TRUNCATE Command. (W-15)
21. Explain difference between delete and truncate command with
example.(S-19,S-24)
22. Give Syntax and example for DROP TABLE Command. (W-16, W-
17)
23. Give Syntax and example for RENAME Command. ( W-13, W-16)
24. Explain Integrity constraint. (W-13,W-15,S-16, S-17, W-17)
25. Explain not null and check constraint with example. (S-23)
26. List all DDL Command.(S-14,W-14,S-15,W-18,S-23)
27. Enlist DML commands. (W-19)
28. Explain any four DML commands with syntax and example. (S-23)
29. Describe Save point and roll back command with example .(S-14,W-
14,S-15,S-16,S-17,S-23)
30. Write syntax for creating and Renaming a table. (W-22)
31. Consider the table Student (name, marks, dept, age, place, phone,
birthdate). Write SQL query for following. (S-19)
i)To list students having place as ‘Pune’ or ‘Jalgaon’
ii)To list students having same department(dept) as that of ‘Rachana’
iii) To change marks of ‘Rahul’ from 81 to 96.
iv) To list student name and marks from ‘Computer’ dept.
v) To list student name who have marks less than 40.
vi)To list students who are not from ‘Mumbai;
32. Write SQL queries for following: (S-22)
Create table student with following attributes using suitable data types. Roll
no., as primary key, name, marks as not null and city.
ii) Add column Date of Birth in above student table.
iii) Increase the size of attribute name by 10 in above student table. Change
name of Student table to stud.
33. Write a query to create table emp-details (Emp_ID, name, dept) with
proper data types and Emp_ID as primary key and name as NOT NULL
constrain. ii) Perform all DML command on above created table. (S-24)
CSMSS COP Assignment No. 3 Year: 2024-25
Subject: DMS [313302] Chapter No: 3 Ms. M. G. Jadhav

1. Explain any four string functions with example. (W-13, S-14, S-16, W-16, S-
17, W-17,W-18,W-19,S-23)
2. Explain any four date functions with example. (S-15, W-16, S-17,S-24)
3. Enlist arithmetic and logical SQL operators. (S-22)
4. Explain subquery concept with example. (S-23)
5. Explain set Operators with example. (W-18,W-19,S-24)
6. Explain joins in SQL with examples. (W-18)
7. List the SQL operations and explain range searching operations between and
pattern matching operator ‘like’ with example. (S-19)
8. Explain any four aggregate functions with example. (W-13, S-14,W-18,S-
19,S-23)
9. Explain group by clause with example. (W-13, W-14, S-15,S-16 , S-17, W-
17,W-22)
10. Explain having clause with example. (W-13, S-16 , S-17)
11. Write query using group by and having clause (use students database). (S-
24)
12. Explain order by clause with example. (W-13, W-14, S-15,W-17,W-22)
13. Explain any two types of join with example. (S-23)
14. Explain any four joins if SQL with examples. (S-24)
15. Explain view with example in details. (W-13, S-14, S-15,W-15,S-16 , W-
16,W-19,S-23)
16. Write syntax for creating and dropping views. (S-22)
17. Any Three example of View. (W-13,W-14,W-16)
18. Write and explain syntax for creating view with example. (S-19)
19. Explain Sequence with example in details. (S-13,W-13, S-14, W-14,W-
15,S-16 , W-17)
20. Explain index with types in details. (W-13, S-14,W-14,W-15, S-16, W-16, S-
17, W-17,S-19,W-19
21. Create simple and composite index. Write command to drop above index. (S-
19,S-24)
22. Write and Explain the syntax for creating and dropping indexes with an
example. (S-22)
23. Write and Explain the syntax for creating, Altering and dropping the
sequence. (W-22)
24. Write syntax for creating synonyms with example. (S-19)
25. What are synonyms? Write syntax for creating and dropping a synonym. (S-
14,W-14,W-17,S-24)
26. Consider the following database Employee
(emp_id,emp_name,emp_city,emp_addr,emp_dept,join_date) (W-18)
i) Display the emp_id of employee who live in city ‘Pune’ or ‘Nagpur’
ii) Change the employee name ‘Ayush’ to ‘Ayan’.
iii) Display the total number of employee whose dept is 50.
27. Consider the following schema Depositor (ACC_no, Name, PAN, Balance).
Create a view on Depositor having attributes(ACC_No,PAN) where balance
is greater than 100000. (W-18)
28. Create a sequence (W-18)
i) Sequence name is Seq_1, Start with 1, increment by 1, minimum
value 1, maximum value 20.
ii) Use a seq_1 to insert the values into table Student( ID Number(10),
Name char (20));
iii) Change the Seq_1 max value 20 to 50. iv) Drop the sequence.
29. Write a command to create table student(RNO,name marks, dept) with
proper datatypes and RNo as primary key ii) Write a command to create and
drop sequence. (S-19)
30. Write a command to crate table student (rollno, Stud_name, branch, class,
DOB, City, Contact_no) and write down queries for following: (W-19)
(i) Insert one row into the table
(ii) Save the data
(iii) Insert second row into the table
(iv) Undo the insertion of second row
(v) Create save point S1.
(vi) Insert one row into the table.
31. Consider following schema: EMP (empno, deptno, ename, salary,
designation, join_date, DOB, dept_location). Write down SQL queries for
following: (W-19)
(i) Display employees name & number in decreasing order of salary.
(ii) Display employee name & employee number whose designation is
Manager.
 (iii) Display age of employees with ename.
(iv) Display total salary of all employees.
(v) Display employee names having deptno as 20 and dept_location is
Mumbai

32. Display name of employee who earned lowest salaryConsider the structure
for book table as Book-Master (bookid, bookname, author, no_of copies,
price) Write down SQL queries for following: (W-19,S-24)

(i) Write a command to create Book_master table.

(ii) Get authorwise list of all books.
(iii) Display all books whose price is between 500 & 800.
(iv) Display all books with details whose name start with „D‟.
(v) Display all books whose price is above 700.

33. Display all books whose number of copies are less than 10. Write the SQL
queries for following EMP table. Emp (empno, deptno, ename, salary,
designation, city.) (W-22)
i) Display average salary of all employees.
ii) Display names of employees who stay in Mumbai or Pune.
iii) Set the salary of employee 'Ramesh' to 50000.
iv) Display names of employees whose salaries are less than 50000.
v) Remove the Record of employees whose deptno is 10.
vi) Remove the column deptno from EMP table.
34. Write SQL queries for following. Consider table stud (roll no, name, subl,
sub2, sub3) (W-22)
i) Display names of student who got minimum mark in subl.
ii) Display names of students who got above 40 marks in sub2.
iii) Display count of Students failed in sub2.
iv) Display average marks of subl of all students.
v) Display names of students whose name start with 'A' by arranging
them in ascending order of subl marks.
vi) Display student name whose name ends with h' and subject 2 marks
are between 60 to 75.
CSMSS COP Assignment No. 4 Year: 2024-25
Subject: DMS [313302] Chapter No: 4 Ms. M. G. Jadhav

1. State advantages of PL/SQL. (W-14, S-15, W-15, S-16,W-18,S-22)

2. Explain function in PL/SQL with example. (W-18,S-23)
3. Explain block structure of PL/SQL. (W-13, S-14, W-15,S-16 , W-16, S-17,W-
18,S-19,S-23)
4. State syntax of while loop command. (S-23)
5. What are Types of PL/SQL? (S-15)
6. State any four PL/SQL data types. (S-19)
7. Define Cursor. List the two types of cursor.(W-18,S-19
8. Write step by step syntax to create, open and close cursor in PL/SQL. (W-
18,W-22,S-23,S-24)
9. Explain implicit and explicit cursors.
10. Explain control structure of PL/SQL with example. (W-13, W-15, S-15,W-
19)
11. Explain loop in PL/SQL in details with example. (W-13, W-14.W-16 , W-
17)
12. Write a PL/SQL program to print number from 1 to 15 and 50 to 60 using
for loop. (S-14,S-17)
13. Explain the exception handling with its two types. (W-13, W-14,W-15,W-
16,S-17 , W-17,W-18,S-19,W-19)
14. Write a PL/SQL program which handles divide by zero exception. (S-15)
15. Write a PL/SQL code to check whether specified employee is present in
Emp table or not. Accept empno from user. If employee does not exist display
message using exception handling. (W-22)
16. Write a PL/SQL procedure to calculate factorial of a given number. (S-
15,W-15)
17. Differentiate between function and procedure. (S-15,S-24)
18. Explain Trigger with example in details. (W-13, S-14, W-14,S-15 ,S-16 W-16,
S-17,W-17,W-22,S-23)
19. Write a trigger which invokes on deletion of record on emp table. (W-22)
20. Define database trigger. How to create and delete trigger?(W-19,S-24)
21. State the use of database trigger and also list types of trigger. (S-19,S-24)
22. Write a PL/SQL to display the largest of three numbers. (W-13)
 23. Write a PL/SQL to square of any number. (W-14)
24. Write a PL/SQL program to print n even numbers using For Loop. (W-19)
25. Write a PL/SQL program to calculate factorial of a given number. (S-19)
26. Write a PL/SQL program which accepts the customer_ID from the user. If the
enters an invalid ID then the exception invalid_id is raised using exception
handling. (W-18)
27. Write a PL/SQL code to print reverse of a number. (W-22,S-23)
28. Write a PL-SQL program to print odd numbers from 1 to 10. (S-23)
29. Write PL/SQL code to print largest number from three numbers (Accept 3
numbers from user) (S-24)
30. Write PL/SQL program to print number of employee working in specified
department consider emp table and accept dept-no. from user. (S-24)
CSMSS COP Assignment No. 5 Year: 2024-25
Subject: DMS [313302] Chapter No: 5 Ms. M. G. Jadhav

1. Explain the database security with its requirements. (W-13,W-16,W-18)

2. Explain the four roles of database administrator. (W-18,W-22)
3. Explain Types of DBMS users. (S-14,S-17)
4. Describe Grant and Revoke Commands. (S-15, S-16, W-16, W-17,S-23
5. Describe database backups with it‟s types. (W-19)
6. Explain recovery techniques with example. (S-19,W-22,S-24)
7. Explain the most common types of database failures. (S-23,S-24)
8. Explain ACID properties of transaction. (S-15,W-15,S-16,W-17,W-18,W-
19,W-22,S-23)
9. Explain state of transaction with neat diagram. (S-14,S-15,S-16,S-17,W-17,S-
19)
10. i) create user ‘Rahul’ (W-18)
ii) grant create, select, insert, update, delete, drop privilege to ‘Rahul’
iii) Remove the select privilege from user ‘Rahul’.
11. Write SQL command for following. (S-24)
i. Create user
ii. Grant privileges to user.
iii. Remove privileges from user.

12. Describe database privileges. Write down the procedure for granting &
revoking privileges in database objects to the users. (W-19,S-24)

13. Write SQL queries for following. (W-22)

i. Create user named 'user1' having Password '1234
ii) Assign 'insert' and update' Privilege to 'userl".
ii. Remove update Privilege assigned to the userl.
iii. Assign the resource Pemission to userl