For this course we will be using the LAB exercises given here which are from Silberschatz book.
Solutions to Practice Exercises of Silberschatz book.
||Introduction to Databases- topics to be covered
Tuple, Domain, Attribute
Keys- candidate key, primary key, super key, foreign key
Relational algebra, SQL, Relational calculus- same power
Join- equijoin left/right outer join, natural join- minimum and maximum number of elements
1NF- No multi-valued dependency
2NF- No partial dependency
3NF-No transitive dependency BCNF
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!
Join- theta join, equi join, natural join, semi join, outer join
Relational Calculus- Tuple Calculus, Domain Calculus- both have same power, examples
safe and unsafe query
||Normalisation revisited- Functional dependency
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
||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