1.3.2 - Databases

• Database:

  • Structured and persistent stores of data
  • Allows data to be:
  • Retrieved quickly
  • Updated easily
  • Filtered for different views
• There are 2 types of databases:

  • Flat-file Databases
  • Relational Databases
• Flat file :

  • Also called a simple database
  • Contains information about a single entity
  • Contains a single table
• Relational Databases:

  • Linked tables (relations)
  • Has entities (Rows & Columns)
  • Each row (tuple) in a table is equivalent to a record and is constructed in the same way.
  • Each column (attribute) is equivalent to a field and must have just one data type.
• Disadvantages of a flat file database:

  • Takes up unnecessary space due to redundant data
  • Slow to query
  • Become difficult to maintain
  • Data may be inconsistent
• Advantages of relational databases:

  • Improves data consistency & integrity
  • Easier to change data format & update records
  • Improves levels of security so easier to access data
  • Reduces data redundancy to avoid wasting storage
• Primary Key:

  • A unique indentifier in a table used to define each record
• Identifiers:

  • Needed to uniquely identify the entity
• Entity:

  • A category of object, person, event or thing of interest to an organisation
• CSV:

  • Comma seperated values
  • Each record is stored on a separate line in the file, and each field is separated by a comma
• Foreign Key:

  • The primary key in one table used as an attribute to provide links between tables
  • Allows relevant data to be extracted from different tables
  • Example of a 1 to many relationship
• Secondary Key (SK):

  • an attribute that allows a group of records in a table to be sorted and searched differently from the primary key
• Entitiy Relationships:

  • Used to help plan a relational database
  • Diagrams show relations
  • Helpful in reducing redundancy
• The three types of entitiy relationships:

  • One to one
  • One to many
  • Many to many
• Composite primary key:

  • A key which consists of more than one attribute
  • Sometimes 2 or more attributes are needed to uniquely define a record
  • OrderLine(OrderNumber, OrderLine, ProductID)
  • Eg. OrderNumber and OrderLine is a composite primary key
• Automated ways of capturing data:

  • Barcode scanner
  • QR codes
• 2 ways of exchanging data(Common formats):

  • XML
  • JSON
• Disadvantages and advantages of indexing data:

  • Advantage: Searches of Artist can be performed more quickly
  • Disadvantage: The index takes up extra space in the database
• One-to-one:

  • Each entity can only be linked to one other entity
  • Such as relationship between a husband and wife
  • Not suitable for a RDB
• Many-to-many

  • One entity an be linked with many other entities and the same applies the other way round.
  • Example - a student enrolls in multiple courses and each course has many students
  • Leads to data redundancy
  • Requires the creation of another table to solve
• One-to-many

  • One table can be associated with many other tables
  • Such as a teacher having multiple students
  • Multiple student entities can be linked to the same teacher entity
• DBMS(Database management systems):

  • Provides a layer of abstraction for the user and the programmer
  • Prevents the creation of duplicate primary keys
  • Enforces data integrity rules
  • Provides encryption
  • Uses SQL to communicate with other programs
  • Provides different view of the data for different users
  • Handles CRUD
• Different ways of capturing data:

  • Paper-based capture forms
  • Optical character recognition(OCR) - This technology automatically reads text by interpreting the shape of the letters
  • Optical mark recognition(OMR)- This technology is used for multiple-choice tests and lottery tickets - very fast and efficient way of collecting data
• Indexing:

  • The PK is normally indexed for quick access
  • The SK is an alternative index allowing for faster searches based on different attributes
  • Index takes up extra space in the database
  • When a data table is changed the indexes have to be rebuilt
• Serial Files:

  • Are relatively short and simple files.
  • Data records are stored chronologically i.e. in the order in which they are entered.
  • New data is always appended to the existing records at the end of the file.
  • To access a record, you search from the first item and read each preceding item.
  • Easy to implement.
  • Adding new records is easy.
  • Searching is easy but slow.
• Sequential files:

  • Are serial files where the data in the file is ordered logically according to a key field in the record.
• Queries:

  • Isolate and display a subset of data
  • QBE - Query by example
• Normalistation:

  • The process of organising data in a database, achieved by applying a set of guidelines
  • There are 3 stages of normalisation
• Benefits of Normalisation:

  • Reduces data duplication
  • Improves consistency
  • Makes the database more maintainable
• 1NF (1st Normal Form) :

  • Fields must be atomic
  • No repeating groups
  • Each record has a primary key
  • Unique field names
• 2NF (2nd Normal Form):

  • Must already be in 1NF
  • No partial-key dependencies
• 3NF (3rd Normal Form):

  • Must already be in 2NF
  • No non-key dependencies
• Repeating Groups:

  • One value per record
• Partial-key dependency:

  • Relies only on part of the primary key
  • Happens when the primary key is a composite key
• SQL:

  • Structured Query Language
  • Used to query a database
• SQL Commands:

  • CREATE TABLE
  • DROP
  • INSERT
  • DELETE
  • SELECT
  • WHERE
  • ALTER
  • AND
  • FROM
• CREATE example:

  
• DELETE:

  DELETE FROM table_name WHERE condition;
• DELETE everything from a table:

  DELETE FROM table_name 
• Referential Integrity:

  • Transactions should maintain refernential integrity (changes to data must be consistent across the entire database)
  • Enforced By DBMS