LESSON PLAN FOR THEORY COURSES (15 weeks * 3 Hours = 45 Classes)
SN
1
|
T/L
T
|
Topics for Delivery
Overview of the database
management system
|
Skills
Identify the
elementary concepts of
File Management
systems, DBMS and
Data Models.
|
Competency
CO1
|
2
|
T
|
Database system Vs file system
|
3
|
T
|
Database system concept and architecture
|
4
|
T
|
Data model schema and instances
|
5
|
T
|
Data model schema and instances
|
6
|
L
|
Draw an E-R diagram and convert
entities and relationships to a
relation table for a given scenario.
|
7
|
T
|
Data independence
|
Ability to gain RDBMS knowledge and SQL queries.
|
CO1/CO2
|
8
|
T
|
Database language and interfaces,
|
9
|
L
|
Installing MySQL & Xampp on the windows machine and creating the first Database.
|
10
|
T
|
DDL, DML, DCL
|
11
|
L
|
Viewing all databases, Creating a
Database, Viewing all Tables in a
Database, Creating simple tables
|
12
|
T
|
Overall Database Structure
|
13
|
T
|
RDBMS concepts, Integrity constraints, entity integrity, referential integrity
|
14
|
L
|
Inserting/Updating/Deleting Records
in Table Saving (Commit,), Undoing
(rollback)
|
15
|
T
|
Key constraints, Domain constraints
|
16
|
T
|
Relational algebra
|
Competence to formulate Relational Algebra & Relational Calculus queries
|
CO1
|
17
|
T
|
Relational Calculus
|
18
|
T
|
SQL data type and literals. Types of SQL commands
|
Describe the
working of SQL with
different clauses & designing keys & constraints.
|
CO2
|
19
|
T
|
Characteristics of SQL, advantages of SQL
|
20
|
T
|
Creating Tables (Key/Domain)
Creating Tables with Referential Integrity
|
21
|
L
|
Dropping/Truncating/Renaming
Table, Adding a column,
Changing, column data type, size,
Dropping column
|
22
|
T
|
SQL operators and their procedure
|
Writing & Evaluating SQL queries based on tables, views, Indexes, Aggregate Functions and joins
|
CO2
|
23
|
T
|
Tables, views, and indexes.
|
24
|
T
|
Aggregate functions
|
25
|
L
|
Queries with Aggregate functions
Queries with group by and having
Queries involving- Date, String,
|
26
|
T
|
Insert, update, and delete operations
|
27
|
T
|
Joins, Unions, Intersection, Minus
|
28
|
L
|
Inner & Outer Join With
IN/EXISTS clause
|
29
|
T
|
Cursors, Triggers
|
Ability to formulate Advanced SQL queries and understand the basics of DB design.
|
CO2/CO3
|
30
|
T
|
Procedures in SQL/PL SQL
|
31
|
T
|
Functional dependencies
|
32
|
L
|
Creating, Dropping & Selecting
data from a view
|
33
|
T
|
Normal forms, first & second
|
34
|
T
|
Normal forms, Third & BCNF
|
35
|
T
|
Dependency Preservations
|
Applying DB design concepts using Dependencies, Lossless design, and understanding the basics of Transaction processing
|
CO3
|
36
|
T
|
Lossless join decompositions
|
37
|
L
|
Pl/SQL program using FOR loop to insert ten rows into a database table.
|
38
|
T
|
Normalization using FD, MVD, and JDs
|
39
|
T
|
Transaction system, ACID properties
|
40
|
T
|
Testing of serializability, serializability of schedules
|
41
|
T
|
Conflict & view serializable schedule
|
Classify and performing Recoverability & deadlocks tests in transactions
|
CO4
|
42
|
L
|
For a given set of related tables performs
a. Begin Transactions
b. End Transaction
|
43
|
T
|
Recoverability, Recovery from transaction failures
|
44
|
T
|
Log-based recovery and checkpoints
|
45
|
T
|
Deadlock handling in Transaction processing
|
46
|
T
|
Distributed Databases, distributed data storage & Directory system
|
47
|
T
|
Concurrency control, Locking Techniques for concurrency control
|
Developing knowledge of different Concurrency protocols.
|
CO4
|
48
|
L
|
For a given set of related tables performs
a. Create roles
b. Assign Privileges
c. Revoke Privileges
|
49
|
T
|
Time stamping protocols for concurrency control
|
50
|
T
|
Validation-based protocol, multiple granularities
|
51
|
T
|
Multi-version schemes, Recovery with the concurrent transaction
|
52
|
L
|
Write Trigger to insert the deleted rows of table1 to table2.
|
|
53
|
T
|
Case study of Oracle & MySQL
|
Capturing the understanding about recent tools and technologies of the domain
|
CO1/CO2
|
54
|
T
|
Case study NoSQL (MongoDB)
|
55
|
L
|
Given the table EMPLOYEE (EmpNo,
Name, Salary, Designation, DeptID) write a
Cursor to select the five highest-paid
Employees from the table.
|
56
|
T
|
Discussion on some of the latest DB products available in the market
|
57
|
T
|
Discussion of some latest papers published in IEEE Transactions and ACM transactions
|
58
|
T
|
Discussion of some latest papers published in Web of Science and SCOPUS-indexed journals
|
59
|
L
|
Using the Xampp server, create
usernames/passwords & connect it to
MySQL.
|
60
|
T
|
Study of advances, the latest trends in the course, and the latest applications of the domain.
|
LAB PLAN FOR THEORY COURSES (15 weeks * 2 Hours = 30 Classes)
|
1
|
L
|
Draw an E-R diagram and convert entities and relationships to a relation table for a
given scenario.
(Two assignments shall be carried out i.e., consider two different scenarios (e.g. bank,
College)
|
2
|
L
|
Installing MySQL & Xampp on the windows machine and creating the first Database.
|
3
|
L
|
Perform the following on MySQL:
a. Viewing all databases, Creating a Database,
b. Viewing all Tables in a Database, Creating simple tables
|
4
|
L
|
Perform the following on MySQL:
a. Inserting/Updating/Deleting Records in Table
b. Saving (Commit,) and Undoing (rollback)
|
5
|
L
|
Perform the following:
a. Altering a Table, Dropping/Truncating/Renaming Tables.
b. Adding a column, Changing column data type, size
c. Dropping a column
|
6
|
L
|
Perform the following:
a. Creating Tables (With and Without Constraints (Key/Domain)
b. Creating Tables (With Referential Integrity Constraints)
|
7
|
L
|
For a given set of relation schemes, create tables and perform the following Queries:
a. Simple Queries
b. Queries with Aggregate functions (Max/Min/Sum/Avg/Count)
c. Queries with Aggregate functions (group by and having clause)
d. Queries involving- Date Functions, String Functions, Math Functions
|
8
|
L
|
For a given set of relation schemes, create tables and perform the following Queries:
Inner Join
Outer Join Subqueries- With IN clause, With EXISTS clause
|
9
|
L
|
. For a given set of related tables perform the following: -
a. Creating Views
b. Dropping views
c. Selecting from a view
|
10
|
L
|
Write a Pl/SQL program using FOR loop to insert ten rows into a database table.
|
11
|
L
|
Given the table EMPLOYEE (EmpNo, Name, Salary, Designation, DeptID) write a
cursor to select the five highest-paid employees from the table.
|
12
|
L
|
For a given set of related tables perform the following:
a. Begin Transactions
b. End Transaction
|
13
|
L
|
For a given set of related tables perform the following:
a. Create roles
b. Assign Privileges
c. Revoke Privileges
|
14
|
L
|
Write Trigger to insert the deleted rows of table1 to table2.
|
15
|
L
|
Using Xampp server, create usernames/passwords & connect it to MySQL community
Edition
|
Share with your friends: |
