For this course we will be using the LAB exercises given here which are from Silberschatz book.
Solutions to Practice Exercises of Silberschatz book.
| Day |
Date |
Contents |
Slides |
Assignments |
| 1 |
Oct 7 |
Introduction to Databases- topics to be covered |
|
|
| 2 |
Oct 8 |
Relational Databases
Tuple, Domain, Attribute
Keys- candidate key, primary key, super key, foreign key
Relational algebra, SQL, Relational calculus- same power
Examples
Join- equijoin left/right outer join, natural join- minimum and maximum number of elements
Normalisation
1NF- No multi-valued dependency
2NF- No partial dependency
3NF-No transitive dependency BCNF
BCNF, 4NF
Is normalisation good?- additional join operations, expensive, it is good theoretically
Given a relation, can we make BCNF?
Is there an algorithm for this?
It is possible- but we lose something! |
Normalization Notes |
Assignment 1 |
| 3 |
Oct 9 |
Relational Algebra
Join- theta join, equi join, natural join, semi join, outer join
Examples
Division operator
Relational Calculus- Tuple Calculus, Domain Calculus- both have same power, examples
safe and unsafe query |
|
Assignment-2
Assignment-3 |
| 4 |
Oct 10 |
Normalisation revisited- Functional dependency |
|
|
| 5 |
Nov 5 |
Indexing
index- <key, block address>
primary index, clustered index, secondary index
How many clustered index a table can have?- atmost one clusteed index
Sparse index and dense index
primary-dense/ sparse ,clustering- sparse/ dense, secondary-dense |
|
|
| 6 |
Nov 7 |
B tree and B+ tree
B tree- record pointer present in all nodes whereas in B+ tree record pointer present
in leaf nodes only
Indexing- order of leaf and non-leaf node
B+ tree more advantage (no unnecessary block access- in B tree record
pointer present in internal nodes)
Insertion and deletion algorithm
Entity Relationship Model- entity, attributes
Conversion of ER diagram to relational tables- 1:1, m:1, m:n, weak entity
total and partial participation
minimal normalisation satisfied- 2NF because a table formed from an entity with composite key may have a partial dependency |
|
|
