cap 503

8/4/2019 Cap 503

1/8

8/4/2019 Cap 503

2/8

Sr. No. (Web adress) (only if relevant to the courses) Salient Features

14 http://database.ittoolbox.com/topics/ General website for DBMS

15 http://sqlcourse2.com/select2.html It is web based SQL DBMS site.

16 http://www.wisegeek.com/what-is-dbms.htm Website containing various articles

17 http://stanford.edu/dept/itss/docs/oracle/10g/server.10 To give details about Distributed and

18 www.oracle.com Oracle is vendor to offer solutions for business

19 www.gossamer-threads.com It is web based SQL DBMS site

20 www.odbms.org Specially dedicated to object

Relevant Websites

Detailed Plan For Lectures

Week Number Lecture Number Lecture Topic Chapters/Sections ofTextbook/otherreference

Pedagogical toolDemonstration/casestudy/images/anmation ctc. planned

Part 1Week 1 Lecture 1 Database Fundamentals:Database

systems,Database Architecture->Reference:1,Chapter1 Section1.11.21.3

ca/regular/cap503/lecture01

Lecture 2 Relat ional Model:Structure of Relat ionaldatabases,fundamental

->Reference :1,chapter2 2.1-2.2


Lecture 3 addit ional and extended relat ional algebraoperations

->Reference :1,chapter2 section 2.3-2.4


Week 2 Lecture 4 SQL: Data Definition,datatypes,schema definition,Basic structure of SQL Queries



Lecture 5 Creating tables,DML operations ->Reference :6,chapter7


Lecture 6 DDL commands for creating and altering ca/regular/cap503/lecture06

Week 3 Lecture 7 Set Operations, Aggregate Functions, NULL values ->Reference :1,chapter3 section 3.4-3.6


Lecture 8 Advanced SQL: Inbui lt functions,Subqueries,Nested subqueries

->Reference :1,chapter3 section 3.7


2 Approved for Autumn Session 2011-12

8/4/2019 Cap 503

3/8

Week 3 Lecture 9 Complex queries,Views ->Reference :1,chapter3 section 3.8-3.9


Week 4 Lecture 10 Joined relations, Integrity constraints ->Reference :1,chapter3 section 3.11 chapter4 section 4.2


Part 2Week 4 Lecture 11 Authorization, DCL Commands ->Reference :1,chapter

4 section 4.3ca/regular/cap503/lecture11

Lecture 12 Embedded SQL,Dynamic SQL ->Reference :1,chapter4 section 4.4-4.5


Week 5 Lecture 13 Embedded SQL, Dynamic SQL ->Reference :1,chapter4 section 4.4-4.5


Lecture 14 Relational Languages: Tuple Relational calculus,Domain relational calculus, Query by Example



Lecture 15 Relational Languages: Tuple Relational calculus,Domain relational calculus, Query by Example


Week 6 Lecture 16 Database design and ER model: Overview ofDesign process, Entity relationship model,constraints



Lecture 17 ER Diagrams, ER Design issues, Weak entity sets,extended ER features ->Reference :1,chapter6 section 6.4- 6.7 ca/regular/cap503/lecture17

Lecture 18 Relational Database Design: Features, AtomicDomains and first normal form,

->Reference :1,chapter7 section 7.1- 7.5


Week 7 Lecture 19 Functional dependency theory decomposition usingfunctional dependencies, decomposition usingMultivalued dependencies,

->Reference :1,chapter7 section 7.6 7.8


Lecture 20 Normalization: Objectives of Normalization, 1NF,2NF.

->Reference :1,chapter7


Lecture 21 Normalization: Objectives of Normalization: 3NF,BCNF, 4NF, 5NF.


MID-TERM

Part 3Week 8 Lecture 22 PL/SQL:Language elements,subprograms and its

types->Reference :6,chapter15


Lecture 23 packages, Cursors-implicit and explicit ->Reference :6,chapter16 chapter 18


Lecture 24 Triggers- row, statement, before, after and instead-of.

->Reference :6,chapter18



8/4/2019 Cap 503

4/8

Week 9 Lecture 25 Transaction Management: Concept of Transaction,Transaction State, Implementation ofatomicity and durability

->Reference:1,chapter15 section15.1 -15.3


Lecture 26 concurrent execution, Serializability, Recoverability ->Reference:1,chapter15 section15.4-15.6


Lecture 27 Concurrency Control: Lock based protocols,Timestamp based protocol

->Reference:1,chapter16 section16.1-16.2


Week 10 Lecture 28 Validation based protocols, Deadlock handling,

Insert and Delete operations, Weak levels ofconsistency

->Reference

:1,chapter16 section16.3-16.8

ca/regular/cap503/lectur

e28

Part 4Week 10 Lecture 29 Validation based protocols, Deadlock handling,

Insert and Delete operations, Weak levels ofconsistency



Lecture 30 recovery with concurrent transactions, buffermanagement, failure with loss of nonvolatile storage



Week 11 Lecture 31 Query Processing: Overview, measures of query

cost, selection operation, sorting, join operation

->Reference



e31

Lecture 32 Query Processing: Overview, measures of querycost, selection operation, sorting, join operation


Lecture 33 Query Optimization: Transformation of relationalexpressions



Week 12 Lecture 34 estimating statistics of expression results ->Reference:1,chapter14 section14.3


Lecture 35 Parallel Databases: I/O parallelism, Interquery

parallelism

->Reference



e35

Lecture 36 Intraquery parallelism,Intraoperation parallelism,Interoperation parallelism




8/4/2019 Cap 503

5/8

Spill OverWeek 13 Lecture 37 SQL injections ->Reference :2,2 ca/regular/cap503/lectur

e37

Lecture 38 Distributed daatbases, Packages examples ->Reference :5,5->Reference :6,6


Details of homework and case studies

Homework No. Objective Topic of the Homework Nature of homework(group/individuals/field

work

Evaluation Mode Allottment /submission

Week

Test 1 To enhance thebasics ofdatabases

Database Fundamentals: Database systems, DatabaseArchitectureRelational Model, Structure of Relational databases, fundamental,additional and extended relational algebra operationsSQL: Data Definition, datatypes, schema definition, Basicstructure of SQL Queries, Creating tables, DML operations, DDLcommands for creating and altering, Set Operations, AggregateFunctions, NULL values

Individual Checking 2 / 3

Test 3 To enhance theskills in someadvance topics indatabases

PL/SQL Language elements, subprograms and its types,packages, Cursors-implicit and explicit, Triggers- row, statement,before, after and instead-of.Transaction Management: Concept of Transaction, TransactionState, Implementation of atomicity and durability, concurrentexecution, Serializability, Recoverability.Concurrency Control: Lock based protocols, Timestamp basedprotocols, Validation based protocols, Deadlock handling, Insertand Delete operations, Weak levels of consistencyRecovery system: Failure classification, storage structure,recovery and atomicity, log-based recovery, recovery withconcurrent transactions, buffer management, failure with loss ofnon-volatile storage

Query Processing: Overview, measures of query cost, selectionoperation, sorting, join operationQuery Optimization: Transformation of relational expressions,estimating statistics of expression results.Parallel Databases: I/O parallelism, Interquery parallelism,Intraquery parallelism, Intraoperation parallelism, Interoperationparallelism



8/4/2019 Cap 503

6/8

Test 2 To enhance thebasics ofdatabases

Advanced SQL: Inbuilt functions, Subqueries, Nested subqueries,Complex queries, Views, Joined relations, Integrity constraints,Authorization, DCL Commands, Embedded SQL, Dynamic SQLRelational Languages: Tuple Relational calculus, Domainrelational calculus, Query by ExampleDatabase design and ER model: Overview of Design process,Entity relationship model, constraints, ER Diagrams, ER Designissues, Weak entity sets, extended ER featuresRelational Database Design: Features, Atomic Domains and firstnormal form, Functional dependency theory decomposition usingfunctional dependencies, decomposition using Multivalued

dependencies,Normalization: Objectives of Normalization, 1NF, 2NF, 3NF,BCNF, 4NF, 5NF.


Scheme for CA:out of 100*

Component Frequency Out Of Each Marks Total Marks

Test 2 3 10 20

Total :- 10 20

* In ENG courses wherever the total exceeds 100, consider x best out of y components of CA, as explained in teacher's guide available on theUMS

*Each experiment of the lab will be evaluated using following relative scheme:

Component % of Marks

WR 20

VIVA 30

J/E 50

List of experiments :-

LectureNumber

Lecture Topic Pedagogical Tools Or Equipment Planned lab Manual

practical 1 To implement different types of DDL statements inSQL

LCD,Laptop,oracle 9i( for SQL) Not Applicable


8/4/2019 Cap 503

7/8

practical 2 To implement different types of DML statements inSQL


practical 3 To implement different types of DCLstatements in SQL LCD,Laptop,oracle 9i( for SQL) Not Applicable

practical 4 To implement different types of DCLstatements in SQL LCD,Laptop,oracle 9i( for SQL) Not Applicable

practical 5 To use constraints in SQL LCD,Laptop,oracle 9i( for SQL) Not Applicable

practical 6 clause using where,order by LCD,Laptop,oracle 9i( for SQL) Not Applicable

practical 7 clause using between LCD,Laptop,oracle 9i( for SQL) Not Applicable

practical 8 clause using like LCD,Laptop,oracle 9i( for SQL) Not Applicable

practical 9 clause using group by, having etc LCD,Laptop,oracle 9i( for SQL) Not Applicable

practical 10 To implement character functions available in SQL LCD,Laptop,oracle 9i( for SQL) Not Applicable

practical 11 To implement Numeric functions available in SQL LCD,Laptop,oracle 9i( for SQL) Not Applicable

practical 12 To implement Date Conversion functions available inSQL


practical 13 To implement different in-built functions LCD,Laptop,oracle 9i( for SQL) Not Applicable

practical 14 To implement different user defined Functions LCD,Laptop,oracle 9i( for SQL) Not Applicable

practical 15 To run the various query commands of SQL LCD,Laptop,oracle 9i( for SQL) Not Applicable

practical 16 To run the various correlated queries using commandsof SQL


practical 17 To run the various nested queries using commands ofSQL


practical 18 To run the various correlated queries using commandsof SQL


Mid Termpractical 19 To create views in PL/SQL LCD,Laptop,oracle 9i( for SQL) Not Applicable

practical 20 To write programs using control structures of PL/SQLlike If-else statements


practical 21 To write programs using control structures of PL/SQL

like do-while


practical 22 To write programs using control structures of PL/SQLlike for statements


practical 23 To write programs using control structures of PL/SQLlike while Statements


practical 24 implementation of Cursors LCD,Laptop,oracle 9i( for SQL) Not Applicable

practical 25 implementation of Cursors LCD,Laptop,oracle 9i( for SQL) Not Applicable


8/4/2019 Cap 503

8/8

practical 26 Implementation of Procedures LCD,Laptop,oracle 9i( for SQL) Not Applicable

practical 27 Implementation of Procedures LCD,Laptop,oracle 9i( for SQL) Not Applicable

practical 28 Implementation of triggers LCD,Laptop,oracle 9i( for SQL) Not Applicable



Spill Overpractical 31 Recovery Techniques LCD,Laptop,oracle 9i( for SQL) Not Applicable


cap 503

Documents