Databases §

• Database Basics

  • Attributes

    • Individual pieces of data

      • Similar to a variable, can only store one piece of data and must be given a name

  • Entities

    • Store records

    • Each entity stores relational data about something

      • E.g. Students, Staff, Products, Sales

    • An entity has defined attributes that hold information about that ‘thing’

      • Attributes are simply characteristics

    • Different ‘things’ go into separate entities

      • For example, we would hold different information about students than staff. Therefore, two entities with different attributes

    • We express entities, and their relationships, in an entity relationship diagram (ERD).

  • Records

    • A collection of attributes

      • E.g. each person has their own record with the same defined attributes

  • Terminology

    • Entity

      • Used when designing or looking at theory

    • Attribute

      • Used when designing or looking at theory

    • Table

      • Used when the database is created

    • Field

      • Used when the database is created

    • I.E.

      • An Entity becomes a table

      • An Attribute becomes a field

      • We call a record a Tuple

        • A tuple is an ordered set of elements

• Atomic Data

  • Precise data is critical in a database.

  • If information cannot be broken down into smaller chunks of

  • data, we say it is atomic.

    • E.g. We create a field called Name and allow a user to enter three different names. They enter:

      • Bloggs, Fred George

      • Mohammed Prind Patel

      • Jones, Mr Albert Fred

    • The problem with this is the formatting is not same for all of the names, so making queries, ordering / sorting, non functional

      • They need to broken down into their atomic chunks

• Attributes hold the data

  • A database is a vast collection of data held in attributes.

  • Records and entities are just logical ways of separating them

  • Attributes have data types (as do variables). E.g:

    • Text

    • Number (integer/real)

    • Date/Time

    • Boolean (bit)

  • Attributes can also have complex rules added to them:

    • This validation was covered in 6.2 (data integrity). It includes presence, length, range, check-digits, etc.

• Relational Databases

  • A flat-file database is where all data is held in a single table structure

  • For example a doctor wants to keep a database to hold patients’ test results. He creates a flat-file DB as follows:

    • 

    • There are problems with this DB

      • 

    • Flat-file databases can lead to the following problems:

      • Data redundancy

        • For example, Kit Kline needs duplicate data for each test result. This will cause the database to grow unnecessarily large. Data should be stored only once.

      • Data inconsistency

        • When duplicating data, errors can occur. These errors lead to data inconsistency.

      • Data independence & Security

        • Let’s imagine we also store each person’s address and they move. We would have to search for every record and update that address

        • If data is split into different entities, we can limit which parts of the data can be accessed by people.

        • More stuff from the textbook needs to be added here.

• Relationships

  • There are three types of relationship (cardinality) that can be identified as existing between entities:

    • 

  • One-To-One relationship

    • If one and only one instance of entity X can be linked to one and only instance of entity Y, this is known as a one-to-one relationship. These are very uncommon and usually joined together into one entity (but not always).

      • When determining relationships, ignore historical data (unless this is a requirement of the database).

    • 

  • One-To-Many relationships

    • These are most common and are formed when one and only one instance of entity X can be linked to one or more instances of entity Y

    • 

  • Many-To-Many Relationships

    • Many instances of entity X are associated with many instances of entity Y

    • Many-to-many relationships break the rules of normalisation and therefore cannot exist in a normalised database

      • Needs notes on normalisation

    • If you find you have many-to-many relationships, resolve them using a bridging entity

      • 

        • A band can be booked to play at a venue and a venue can have many bans playing at it

      • The easiest way would be to associate both with a booking

        • A booking has one band and one venue

        • 

      • 

        • A child may have one or more library books and a library book (I.E. multiple copies) borrowed by more than one child

        • The easiest way would be to associate both with a BookLoan. This new entity would also hold the date of the loan.

          • The PK could consist of both FKs & the date or

          • A new PK called LoanID (for example)

        • 

• Normalisation

  • Normalisation is the process of efficiently organising data in a database. Often occurs when turning a flat file database into a relational database.

  • Normalised Forms

    • Normalisation works by deconstructing the raw data and applying a set of rules to it

    • These are broken down into ‘normal forms’. E.g.

      • First normal form

      • Second normal form

      • Etc.

  • Redundancy and Data Anomolies

    • Redundant data is where we have stored the same information more than once. i.e., the redundant data could be removed without the loss of information