Unit 5 - Relational Database Concepts

Unit 5 - Relational Database Concepts

1. Database Terms

   • Table: Contains data about 'things' and is made up of records. Made up from rows and columns. Must have unique name
   • Record: A record is a single row within a table. It is a collection of data about a single item/event. Made up of different fields.
   • Field: A field is an individual data item within a record. Each field within a record should hav a unique name and only contain a single data item.
   • Primary Key: The most important. This is a field in the table that allows each record to be uniquely identified.
   • Foreign Key: Is used to link tables together. A foreign key is a field in one table that is linked to a primary key in another table.
   • Relationships: One to One, One to Many, Many to Many
   • Entity: Is the name given to anything you can store data about.
   • Attribute: Is a characteristic of an entity.
   • Duplicate data: Is data that exists in two tables. Duplicate data is removed through normalisation and linking of tables through relationships.
   • Referential integrity: Makes sure that it is impossible to enter a reference in a database to a link which does not exist.

2. Relationships

   • One to One: One record in one table can only be linked to one record in another table.
   • One to Many: One record in one table can be linked to many records in another table.
   • Many to Many: Many records in one table can be linked to many records in another table.

3. Different Normal Forms

   • Unnormalised form (ONF): When data is first collected and put into a table, it is in unnormalised form.
   • First Normal Form (1NF)
     • First stage of normalising a data model and involves ensuring that all data items are atomic and there are no repeating fields.
     • Each table must have a primary key
     • Each field name must be unique
     • There are no repeating fields in a single record
   • Second Normal Form (2NF)
     • Second stage of normalising a data model, A table is in 2NF if all of its non-key attributes are dependant on the primary key
     • Table must already be in 1NF
     • All the non-key fields can be found by using the entire primary key
   • Third Normal Form (3NF)
     • Third stage of normalising data. 3NF ensures that every attribute of any entity that is not part of the primary key is dependant on that key.
     • Table must already be in 2NF
     • All non-key items must be fully dependant on the primary key

4. Normalisation

   • Advanatges
     • Removes redundant and duplicate data, therefore saving on storage apce
     • Easier maintenance
     • Increases data consistancy
     • Increases integrity
     • Provides flexibility if expansion is required
   • Disadvantages
     • Reduced database performance. A normalised database requires greater use of the CPU and memory
     • Normalisation isn't appropriate for some databases

5. Data Dictionary

   • Data dictionary contains metadata

6. Data Types

   • Text/String: Used to store just text, text and number and just numbers
   • Integer: Numbers without a decimal place
   • Real: Numbers with decimal places including currency
   • Boolean: Can only store 2 value. e.g. Yes or No
   • Date/Time: This data type is used for defining dates and time

7. Queries

   • Parameter Queries
     • Static parameter queries: Cannot be changed. This means everytime the query is run the same parameters will be searched for
     • Dynamic parameter queries: These can be created to ask the user for the value to search for
     • Simple parameter query: These have only one parameter value
     • Complex parameter query: These search using more than one parameter value, that is using two or more criteria. Joined using AND, OR and NOT

Images and files in the map