DATABASES

DATABASES

1. NORMALISATION

   • Three main issues:
     • Insertion Anomalies
     • Deletion Anomalies
     • Modification Anomalies
     • These issues are a result of poor design.
   • A formal technique for analysing relations based on their primary key and functional dependencies.
     • Analyse each relation using a set of rules, if a relation meets a rule, decompose the relation to two or more relations.
   • Functional Dependencies
     • SEE APPROPRIATE INDEX CARDS
   • 4 types we look at:
     • 0NF
     • 1NF
     • 2NF
     • 3NF

2. RELATIONSHIPS

   • Entities
     • SEE ENTITIES INDEX CARDS
   • Attributes
     • SEE ATTRIBUTES INDEX CARDS
   • Keys
     • Primary
       • SEE PRIMARY KEY INDEX CARD
     • Foreign
       • SEE FOREIGN KEY INDEX CARD
     • Composite
       • SEE COMPOSITE KEY INDEX CARD
   • A named significant association that links two entities
     • Binary assocation
   • Cardinality (number in a set)
     • One-To-One
       • SEE 1:1 INDEX CARDS
     • One-To-Many
       • SEE 1:M INDEX CARDS
     • Many-To-Many
       • SEE M:N, LINK TABLE & RESOLVING RELATIONSHIPS INDEX CARDS
   • Remove redundant relationships
   • Grids
     • SEE RELATIONSHIP GRIDS INDEX CARDS
   • Optionality
     • Mandatory
       • SEE OPTIONALITY INDEX CARDS
     • Optional
       • SEE OPTIONALITY INDEX CARDS
     • SEE OPTIONALITY INDEX CARDS
   • Problems with ER models
     • Due to poor understanding of relationships.
     • Fan Traps
       • SEE FAN TRAP INDEX CARDS
     • Chasm Traps
       • SEE CHASM TRAP INDEX CARDS
     • SEE PROBLEMS WITH ER MODELS INDEX CARDS

3. SECURITY

   • Reasons for Security
     • SEE APPROPRIATE INDEX CARD
   • 3 issues
     • SEE APPROPRIATE INDEX CARD
   • Types of Security
     • SEE APPROPRIATE INDEX CARD
   • Security
     • Effective Security
       • SEE APPROPRIATE INDEX CARDS
     • Data Protection Act
     • System Requirements
       • SEE APPROPRIATE INDEX CARDS
     • Controlling Access
       • SEE APPROPRIATE INDEX CARDS
     • Change appearance
       • SEE APPROPRIATE INDEX CARD

4. TRANSACTIONS

   • They are a collection of operations which perform a single logical function within the database application.
     • Commit
     • Rollback
   • Ideal Properties: ACID
     • Atomicity
     • Consistency
     • Isolation/Independence
     • Durability
   • Recovery
     • The act of restoring a broken database to a usable state following an event that has rendered it inoperative or inconsistent
     • DBMS must ensure either all operations in a transaction execute successfully OR the transaction has no effect on the database.
     • How to recover.
       • SEE INDEX CARDS
   • Computer systems fail due to:
     • SEE FAILURE INDEX CARD
   • Checkpoints
     • SEE INDEX CARD
   • Deferred Update
     • SEE INDEX CARD
   • Immediate Update
     • SEE INDEX CARD

5. DESIGN

   • Conceptual
   • DBMS Selection
   • Logical
   • Physical
   • Good Data Models
     • SEE APPROPRIATE INDEX CARDS
   • Essential Concepts
     • SEE APPROPRIATE INDEX CARDS

6. STORAGE

   • Primary Storage
     • SEE PRIMARY STORAGE INDEX CARD
   • Secondary Storage
     • SEE SECONDARY STORAGE INDEX CARD
     • Magnetic Disks
       • SEE INDEX CARD
   • Files
     • Logically stored collection of related records
       • SEE INDEX CARD FOR FILE ORGANISATION
         • SEE INDEX CARD FOR ORDERED AND HEAPED FILES
   • Records
     • Logically related fields, elements or information.
   • Blocks
     • Smallest amount of data that can be transferred from the primary memory (RAM) to secondary memory (disc).
     • 3 records
     • How can files be allocated to blocks?
       • SEE BLOCK ALLOCATION INDEX CARDS

7. INDEXED FILES

   • Blocked Records
   • "A table or other data structure that is used to determine the location of rows in a table (relation) that satisfy a condition."
   • SEE INDEX CARDS FOR EXAMPLES
   • The types are: Full (Dense) Index, Partial (Sparse) Index, Partial Block Index, Multi-level Index.
     • SEE INDEX CARDS FOR EXAMPLES
     • SEE INDEX CARDS FOR PROS/CONS
     • SEE INDEX CARDS FOR GUIDELINES

8. DIRECT ACCESS & HASHING

   • Hashing
     • The implementation methods
       • Direct Mapping
       • Directory Lookup
       • Hash Functions
     • SEE HASHING INDEX CARDS
   • Direct Mapping
     • SEE INDEX CARD
   • Address & Key Spaces
     • SEE INDEX CARD
   • Directory Lookup
     • SEE INDEX CARD
   • Hash Functions
     • SEE INDEX CARD
     • Collisions
       • A problem when two records cannot be placed at the same location.
       • 3 solutions
         • Linear probing
           • SEE INDEX CARD
         • Buckets & Overflow
           • SEE INDEX CARD
           • Bucket is a set of records stored in one memory location
             • Each bucket is the size of a block.
         • Multiple hashing
   • SEE INDEX CARD

9. RELATIONAL ALGEBRA

   • Relation
     • SEE INDEX CARD
   • Tuple
     • SEE INDEX CARD
   • Attribute
     • SEE INDEX CARD
   • Domain
     • SEE INDEX CARD
   • Projection
     • SEE INDEX CARD
   • Restriction/Selection
     • SEE INDEX CARD
   • Rename
     • SEE INDEX CARD
   • Cartesian Product
     • SEE INDEX CARD
   • Join
   • Union
     • Adding together tuples (rows) in relations to create a third relation.
     • SEE INDEX CARD
   • Intersection
     • Extracting the tuples that are common in two relations to create a third relation.
     • SEE INDEX CARD