Chapter 5- Relational Databases

Chapter 5- Relational Databases

1. A database is a base to store data in a structured manner

   • Table
     • Stores data about things that exist (people, places, objects & events)
     • Must have: a unique name, unique record & unique fields
   • Record
     • Stores data about an individual thing
     • =Row
   • Entity
     • = Record
     • Particular & discrete unit
   • Field
     • =Column
     • Category of info
     • Have field names
   • Data items
     • Individual items of data
   • Attribute
     • =fields
     • Characteristics of entities
   • Primary Key
     • unique identification for each record
     • ID or Code
   • Composite Key
     • A primary key that consists of 2 or more fields

2. Relationships exist between entities

   • One to One
     • ONE entity (record) from one table relates to only ONE entity (record) from another table
   • Foreign Key
     • A field in one table that links to a primary key in another table
     • Always goes on the MANY side
       • So it points to one entity
   • One to Many
     • ONE record from one table to MAN records in another table
   • Many to Many
     • Not allowed
     • There are two 'many' sides, which means there is too many 'many' sides
   • Referential Integrity
     • Ensures that data entered into a table on the many side of the relationship must exist in the table on the one side of the relationship
   • Duplicate (redundant) data
     • Occurs when relationship haven't been set up properly
     • When data occurs more than once

3. Entity Relationship Diagrams

4. Normalisation

   • The process of breaking down complex data structures into structured tables that can be used for a relational database
   • Purpose: (so that...)
     • Data is consistent
     • Data is not unnecessarily duplicated (redundancy)
     • Data structures are easier to maintain
     • The structure of each table is flexible enough to allow as many/as few items as required to be stored
     • Users can make any type of complex query
   • 0NF
     • Non-atomic data
     • Repeating groups of data
     • Not yet been normalised
   • 1NF
     • Must have a primary Key
     • Each field name must be unique
     • All data must be ATOMIC
     • No repeating groups of data
   • 2NF
     • Must be in 1NF
     • All non-primary key attributes MUST be dependent on the WHOLE primary key
   • 3NF
     • Must be in 2NF
     • All non-primary key attributes MUST be dependent on the primary key
   • Advantages
     • No redundant data
     • Integrity of data is increased
     • Database is easier to maintain
     • More flexibility for future expansion
     • Security can be placed on databases
   • Disadvantages
     • Reduced database performance
     • Coded data may not be understood
     • Detailed analysis & design
     • Complex queries require a high level of skill and experience

5. Data Dictionary

   • Names of tables
   • Field Names
   • Primary Key
   • Data Types
   • Length of fields
   • Indexed Fields
   • Default values
   • Validation
   • Relationships
   • Foreign Key
   • Permission/ Security

6. Alternative Data Types

   • Advantages
     • Data can be stored efficiently without wasting space
     • Only uses up the storage space necessary for it's type
     • Memory is allocated efficiently
     • Helps present data
     • Stored effectively
   • Disadvantages
     • Data is restricted for each field
     • Users need to have an understanding of what each data type allows

7. Queries

   • Asks a question of a database & produces results, usually in a tabular format.
     • It is necessary to specify: table(s) to be used, field(s) to be displayed & the criteria
   • Static Parameter Queries
     • Where the criterion doesn't change each time
   • Dynamic Parameter Queries
     • Where the parameter can change each time the query is processed
   • Simple Queries
     • One that has a single criteria
   • Complex Queries
     • One that has 2 or more criteria
       • AND
       • OR