MCM Master of Computer Management Semester 3 Advance DBMSUniversity Syllabus Course and Classes For MCM Semester 3 Advance Database Management Systems
Atlanta Computer Institute Nagpur conducts Tuition Classes for MCM Part 1 First semester MCM Part 1 second semester MCM Part 2 third semester and MCM Part 2 fourth semester for all universities in India . The Following syllabus is of Nagpur University. Final Year Projects Training is also given to MCM Students.
Semester - III
Paper - I: Advance Database Management Systems
UNIT - I
Introduction to Database Management System(DBMS) - Introduction, Why a Database, Characteristic of Data in a Database, Database Management System, Why DBMS, Types of Database Management System, Object-Oriented Model, Object-Relational Model, Deductive/Inference Model, Compression Between the various Database Model. Introduction to Relational Database Management System(RDBMS)- Introduction , RDBMS Terminologies, The Relational Data Structure, Relational Data Integrity, Relational Data Manipulations, Codd‘s Rule. Database Architecture and Data Modeling - Introduction, Conceptual, Physical and Logical Database Model, External or Logical Level. Entity-Relationship Modeling- Introduction, E-R Model, Components of an E-R Model, E-R Modeling Symbols. Data Normalization-Introduction, First Normal Form(1NF), Second Normal Form(2NF), Third Normal Form(3NF), Boyce-Codd Normal Form(BCNF), Fourth Normal Form(4NF), Fifth Normal Form(1NF), Domain-Key Normal Form(DKNF), Renormalizations. Relational Algebra
and Relational Calculus- Relational Algebra, Relational Calculus.
UNIT - II
Introduction to Structured Query Language(SQL) - Introduction, History of SQL, Characteristic SQL, Advantages of SQL, SQL in Action, SQL Data Types and Literals, Types of SQL Commands, SQL Operators, Arithmetic Operators, Compression Operators, Logical Operators, Set Operators, Operators Precedence. Tables, View and Index - Tables, View , Index. Nulls - Introduction, Nulls in Action, When not to Use
Nulls, Effect of Nulls, Null Indicators, Null and Compression Operator, Testing of Nulls, Tests of true, False and Unknown, BETWEEN, LIKE and IN Condition, ALL and ANY Condition, EXITS Condition, ORDERED BY Clause. Query And Subqueries - Query , Subqueries. Aggregate Function - Introduction, General Rule, COUNT() and COUNT(*), SUM(), AVG(), MAX() and MIN(). Insert, Update and Delete Operation -
Introduction, Insert Statement, Bulk Insert of Data, Update Statement, Delete Statement Cursors - Introduction, Cursor Operation, Cursor Positions, Cursor Coding Guideline.
Join And Union - Join , Union.
UNIT - III
Programming with SQL- Introduction, Query Processing, Embedded SQL, Dynamic SQL. Query-By-Example(QBL) - Introduction, Select Query in QBE, Make-Table Query, DELETE Query, UPDATE Query, APPEND Query, QBE and SQL. QUEL-Introduction, Data Definition in QUEL, Data Retrieval in QUEL, Data UPDATE Operation
in QUEL. Triggers - Introduction, What is Trigger?, Types of Triggers, Triggers Syntax, Combining Triggers Types, Setting Inserted Value, Disabling and Enabling Triggers,
Replacing Triggers, Dropping Triggers, Advantages and Limitations of Triggers. Introduction- PL/SQL Blocks, PL/SQL Architecture, SQL Support, PL/SQL Variables, PL/SQL Data Types, PL/SQL Precompilers, Conditional And Sequential Control Statements, Control Statements, Cursors, Iterative Control Statements, PL/SQL Exceptions, PL/SQL Blocks, PL/SQL Triggers, Types Of Triggers, Procedures And Packages.
MCM Syllabus RTMNU
UNIT - IV
Data Ware House and Data Marts - Introduction, Data in the Data Ware House, Data Ware House, Design Issues, OLTP vs. Data Ware House, Configuration of Data Ware House Process, Data Ware House Components, Structure of Data Ware House, Data Ware House Life Cycle, Data Ware House Environment, Data Architecture Data Ware House Operation, How much Data?, Data Integration and Transformation Process. Data
Mining - Introduction, What is Data Mining?, Evaluation of Data Mining, Data Mining Verification vs. Discovery, Tasks Solve by Data Mining, Advantages of Data Mining. On-Line Transaction Processing(OLTP) - Introduction, Designing Criteria OLTP Features, Practical Application of OLTP, Future trends in OLTP. On-Line Analytical Processing(OLAP) - Introduction, OPAP and OPAP, OLAP and Data Ware Housing, Use of OLAP, Benefits of OLAP, Evaluation of OLAP, OLAP Concept and Characteristic, Cood‘s OPLAP Product Evaluation Rules, Different Style of OLAP.
1. Alexis Leon, Mathews Leon, Database Management System, Leaon Vikas.
1. Rini Chakrabarti, Shilbhadra Dasgupta & Subhash K. Shinde, Advance Database Management System, Dreamtech Press.
2. Abraham Silberschatz, Henry F. Korth, S. Sudarshan, Database System Concepts, McGraw-Hill.
3. G. K. Gupta, Database System Concepts, McGraw-Hill.
4. Dr. P. S. Deshpande, SQL & PL/SQL for Oracle 11g, Dreamtech Press.
5. Ivan Bayross, SQL, PL/SQL, BPB Publications.
Practical List of SQL & PL/SQL
1. Write a SQL Query to create a table”employee”:
Field Name Datatype Size
Emp_no varchar2 5
Emp_name varchar2 25
Address varchar2 50
Phone_number number 10
Designation varchar2 15
Salary number 15
1. Display the structure of table.
2. Add qualification field at the end of employee table.
3. Modify the size of the name field 25 to 30.
MCM Syllabus RTMNU Page 40 of 72
4. Display the employee name whose salary is greater than 20,000.
5. Display the employee details whose name starts with ―A‖.
2. Write a SQL Query to create a table “student”:
Field Name Datatype Size Constraint
Roll number 5 primary key
Name varchar2 30 first letter must start with ‗A‘
Address varchar2 30 not null
City varchar2 30
Phone number 11 unique key
Class varchar2 10 All upper letter
Marks number (10, 2) Not null can‘t be 0
1. Display the structure of database and insert 10 records.
2. Display student information for all student in city Pune and Nagpur.
3. Display student information where marks greater than 80 and less than 90.
4. Display student name where first two character of student name ‗An‘.
5. Change student name to Ashish where student roll number A001.
3. Write a SQL Query to create a table “sales_details”:
Field Name Datatype Size
S_id varchar2 8
P_id varchar2 8
P_name varchar2 15
Price number 10
Qty number 8
1. Drop foreign key constraint on column p_no in table sales_details.
2. Add foreign key constraint on column sale_no in table sales_details.
3. Modify the column qty to include not null constraint.
4. Insert 10 records in sale_details.
5. Display p_id and total of quantity qty for each product.
6. Display p_id and total of price for all the products.
4. Write a SQL Query to create a table “customer”:
FieldName Datatype Size
Cust_no varchar2 10
Address varchar2 10
Salary number 10
1. Modify address field with not null.
2. Add city field as it must keep city name Mumbai, Delhi and Kolkata.
3. Add salary field where salary greater than 20,000.
4. Display the structure of table customer.
5. Insert 10 records into the table customer.
6. Display all the customer details who lives in Mumbai and Kolkata.
7. Display all the customer records whose salary>20,000 and salary<30,000.
8. Modify the address field where customer number is ‗C001‘.
5. Write a SQL query to create c_master with fields c_no, name, address, city, state and
Field Name Datatype Size
C_no varchar2 10
Name varchar2 10
Address varchar2 10
State varchar2 20
MCM Syllabus RTMNU Page 41 of 72
City varchar2 20
Pin_code number 10
1. Create sequence which will generate number from 1..999 in ascending order,
with an interval of 1 and in cyclic order.
2. Insert 10 records.
3. Create index on c_master which column name c_no and state.
4. Create view on c_master .
5. Select columns c_no, city which belongs to Nagpur and Mumbai.
6. Write a SQL query to create a syntax seq_order which generating numbers from
1…9999 in ascending will number with an interval of 1 in cyclic order.
Field Name Datatype Size
P_no varchar2 10
P_name varchar2 20
Qty varchar2 10
P_rate varchar2 10
1. Display next value of sequence seq_order.
2. Display current value of sequence seq_order.
3. Insert values in sal_order table must be generated using sal_order sequence.
4. Display all records of sal_order table.
5. Change a cache memory of 50 seq_order sequence having interval 2.
6. Drop sequence.
7. Write a SQL Query to-
1. Create an index employee_index depends on employee table using field name.
2. Create a view depends on employee table.
3. Display the records from the view where city as Delhi and Mumbai.
4. Update the view where employee id is ‗E006‘.
8. Write a SQL query to illustrate numeric function.
1. Sqrt 2. Ceil 3. Power 4. Floor 5. Round
6. Mod 7. Abs 8. Exp 9. Greatest 10. Least
9. Write a SQL query to create tablespace datauser or data where size of file 100MB
extend it by 10MB reach upto 250MB in size. Create user data1 with default tablespace
and temporary tablespace. Create role acc_create with create session, create
user, alter user and assign role to user. Assign profile to user where user should fail after
5 attempt and valid for 3 days. Destroy user data1 and tablespace from system.
10. Write a SQL query for join, inner join, outer join, self join and Cartesian join.
11. Write an algorithm, draw a flowchart and develop a PL/SQL program to check given number is odd or even.
12. Write an algorithm, draw a flowchart and develop a PL/SQL program to check number is reverse or not.
13. Write an algorithm, draw a flowchart and develop a PL/SQL program to check number is palindrome or not.
14. Write an algorithm, draw a flowchart and develop a PL/SQL program to find the number is Armstrong or not.
MCM Syllabus RTMNU Page 42 of 72
15. Write an algorithm, draw a flowchart and develop a PL/SQL program to find the addition of all the number in the given range.
16. Write an algorithm, draw a flowchart and develop a PL/SQL program to find the number is prime or not.
17. Write an algorithm, draw a flowchart and develop a PL/SQL program to calculate factorial of a given number.
18. Write an algorithm, draw a flowchart and develop a PL/SQL program to generate Fibonacci series.
19. Write an algorithm, draw a flowchart and develop a PL/SQL program to insert a new element in a given position in the array.
20. Write an algorithm, draw a flowchart and develop a PL/SQL program to delete the duplicate element from the array.
21. Write an algorithm, draw a flowchart and develop a PL/SQL program to sort the data in ascending order.
22. Write an algorithm, draw a flowchart and develop a PL/SQL program to find reverse of a string.
23. Write an algorithm, draw a flowchart and develop a PL/SQL program to find palindrome of a string.
24. Write an algorithm, draw a flowchart and develop a PL/SQL program to calculate number of char, spaces, words from given string.
25. Write an algorithm, draw a flowchart and develop a PL/SQL program to find largest and smallest element of given array using function concept.
26. Write an algorithm, draw a flowchart and develop a PL/SQL program to print ASCII table.
27. Write an algorithm, draw a flowchart and develop a PL/SQL program to change
sale_price of product_master table where pro_no is ‗C001‘ and insert records with date
on which price was changed last in new_master table whose fields are prod_no, date,
28. Write an algorithm, draw a flowchart and develop a PL/SQL program to accept the employee whose job is programmer and update the salary of the employee. Display how many rows are affected.
29. Write an algorithm, draw a flowchart and develop a PL/SQL program to display
the name, dept, name and salary of first 10 employees getting the highest salary using
30. Write an algorithm, draw a flowchart and develop a PL/SQL program to check whether emp_no of employees exists or not using procedure.
New MCM Syllabus Semester Pattern From 2016 - 17
1. Practical-I : Programming in C & Operating Systems
2. Practical-II : Tally ( ERP 9 ) & MS-Office
2. Core Java
1. Practical-I : Core Java
( i ) PHP & My-SQL
( ii ) VB.Net
( iii ) C#.Net
1. Practical-I : SQL & PL/SQL
2. Practical-II : Electives
2. Electives : (i) Advanced Java
(ii) Android Programming
3. Electives : ( i ) Big Data & Hadoop
( ii ) Software Engineering
( iii )Strategic Management
1. Practical-I : ASP.Net
2. Practical-II : Electives
BCA Bachelor Of Computer Application, BCCA, Bachelor of Commerce & Computer Application BE IT/CS, Information technology/Computer Science MCA, Master of Computer Application MCM, Master of Computer Management Diploma , Polytechnic Others,