1.3.2 Databases

• Relational Database


  A database that recognises the differences between entities by creating different tables for each entity
• Relational Database


  • Doctor, Patient
• Entity

  An item of interest about which information is stored
• Attributes


  Characteristics of the entity; categories about which data is collected
• Flat File

  A database that consists of a single file typically based around a single entity and its attributes
• Flat File layout

  Entity1(Attribute1, Attribute2, Attribute3 ...)
• Primary Key

  A unique identifier for each record in the table
• Foreign Key

  The attribute which links two tables together
• Secondary Key

  Allows a database to be searched quickly; set up on attributes that are easier to remember
• Entity Relationship Modelling

  Tables can have different kinds of relationships: One-to-one, One-to-many, Many-to-many
• One-to-one relationships


  Each entity can only be linked to one other entity
• Multiple child entities can be linked to the same mother entity
• One-to-one relationships are demonstrated using a single line used to connect two entities
• A one-to-many relationship will have a branch on one side, while a many-to-many relationship has branches on both sides
• Entity relationship modelling involves considering the link between customers, orders, and products
• Normalisation


  The process of coming up with the best possible layout for a relational database
• Goals of normalisation

  • No redundancy
  • Consistent data throughout linked tables
  • Records can be added and removed without issues
  • Complex queries can be carried out
• Types of normalisation

  • First Normal Form
  • Second Normal Form
  • Third Normal Form
• Indexing is a method used to store the position of each record ordered by a certain attribute to access data quickly
• The primary key is automatically indexed; secondary keys are used to make the table easier and faster to search through on those particular attributes
• Handling Data

  Data needs to be input into the database using various methods depending on the context
• Methods of capturing data

  Manually entering responses into the database, scanning cheques using MICR, using OMR for multiple choice questions, and OCR for other forms
• Selecting and Managing Data

  Selecting the correct data is an important part of data preprocessing, which may involve only selecting data that fits certain criteria to reduce input volume
• Exchanging Data

  The process of transferring collected data
• Exchanging data

  Transferring the collected data
• Exchanging data

  EDI (Electronic Data Interchange) doesn't require human interaction and enables data transfer from one computer to another
• SQL

  Structured Query Language used to manipulate databases, enabling the creating, removing, and updating of databases
• SQL

  • SELECT statement collects fields from a given table, FROM statement specifies which table(s) the information will come from, WHERE statement specifies the search criteria
• Data Types

  • CHAR(n), VARCHAR(n), BOOLEAN, INTEGER/INT, FLOAT, DATE, TIME, CURRENCY
• JOIN

  Combining rows from multiple tables based on a common field between them
• CREATE


  Allows making new databases
• ALTER

  Used to add, delete, or modify the columns in a table
• SQL Query

  • SELECT MovieTitle, DatePublished FROM Movie WHERE DatePublished BETWEEN #01/01/2000# AND #31/12/2005# ORDER BY DatePublished
• SQL Query Result

  • Howdy Partner! 04/21/2001, Okay Samantha, just leave. 04/21/2001, Bye Bye Bucky. 05/12/2004, My wife left me for my dog... 05/14/2004, Cars, Girls, and Money. 06/21/2012, Water Bottle Sadness 08/12/2015
• SQL Query

  • ORDER BY DatePublished Desc
• SQL Query

  • SELECT Movie.MovieTitle, Director.DirectorName, Movie.MovieCompany FROM Movie JOIN Director ON Movie.DirectorName = Director.DirectorName
• SQL Query

  • CREATE TABLE TableName (Attribute1 INTEGER NOT NULL PRIMARY KEY, Attribute2 VARCHAR(20) NOT NULL, ...)
• SQL Query

  • ALTER TABLE TableName ADD AttributeX and their dataTypes
• SQL Query

  • ALTER TABLE TableName DROP COLUMN AttributeX
• CURRENCY

  • sets the number as a monetary amount