A-level OCR CS 1.3.2 Databases

• Normalization is the process of organizing data in a database to minimize redundancies and anomalies to ensure data integrity.
• The main purpose of databases are to store large amounts of information that needs to be accessed quickly by many users at the same time
• Data can be stored as text, numbers or images
• Relational databases consist of multiple tables which relate to one another through primary keys and foreign keys
• Foreign key - links two tables together using a common field
• Entity Relationship Model:

  • An entity is an item of interest about which information is stored
  • A relational database recognises differences between entities by creating different tables for each entity
  • The diagram shows two entities: Doctor and Patient
  • DoctorID is the attribute linking the two tables together
• Flat File:

  • A flat file is a database that consists of a single file
  • Flat files are based around a single entity and its attributes
  • Flat files are typically written out as Entity1(Attribute1, Attribute2, Attribute3 …)
  • Example: Car(CarID, Age, Price)
• Primary Key:

  • A primary key is a unique identifier for each record in the table
  • In the Doctor table, the primary key would be DoctorID
  • In the Patient table, the primary key would be PatientID
• Foreign Key:

  • A foreign key links two tables together
  • In the Patient table, DoctorID is the foreign key
• Secondary Key:

  • A secondary key allows for quick searching in a database
  • Example: setting up a secondary index on the surname attribute
• Entity Relationship Modelling:

  • Tables can have different relationships: one-to-one, one-to-many, many-to-many
  • One-to-one relationships are demonstrated using a single line
  • One-to-many relationships have a branch on one side
  • Many-to-many relationships have branches on both sides
• Normalisation:

  • The process of coming up with the best layout for a relational database
  • First Normal Form: No attribute contains more than a single value
  • Second Normal Form: No partial dependencies, attributes cannot depend on part of a composite key
  • Third Normal Form: No non-key dependencies, attributes only depend on the primary key
• Indexing:

  • Indexing stores the position of each record ordered by a certain attribute
  • Primary keys are automatically indexed
  • Secondary keys are indexed for faster searching
• Handling Data:

  • Capturing Data: Data input methods depend on the context
  • Selecting and Managing Data: Data preprocessing involves selecting and managing data
  • Exchanging Data: Data transfer methods like EDI (Electronic Data Interchange)
• SQL:

  • SQL stands for Structured Query Language
  • SQL is used to manipulate databases
  • SQL commands include SELECT, FROM, WHERE, ORDER BY
• CREATE:

  • CREATE function allows making new databases
  • Attributes must specify if they are the primary key, data type, and if they must be filled in
• ALTER:

  • ALTER is used to add, delete, or modify columns in a table
• INSERT INTO:

  • Used to insert a new record into a database table
• UPDATE:

  • Used to update a record in a database table
• DELETE:

  • Used to delete a record from a database table
• Referential Integrity:

  • Ensures consistency in linked databases
  • Prevents removal of information required elsewhere
• Transaction Processing:

  • A transaction is a single operation on data
  • ACID principles: Atomicity, Consistency, Isolation, Durability
• Record Locking:

  • Prevents simultaneous access to records in a database
  • Used to prevent inconsistencies or loss of updates
  • Deadlock can occur in record locking
• Redundancy:

  • Redundancy is having one or more copies of data in physically different locations
  • Helps in data recovery in case of damage
• SQL - Structured Query Language used to interact with DBMS
• Database Management System (DBMS) provides the software interface between users and databases
• Column


  A named data storage area in a table that holds a specific type of data
• Field


  A specific value within a column
• Record


  A single row in a database table that contains data for each column in that table
• Table


  A collection of related data that is organized into rows and columns
• Column


  A named data storage area in a table that holds a specific type of data. It is a vertical group of cells that extends from the top to the bottom of the table.
• Row


  A horizontal group of cells that extends from left to right across the table. Each row is identified by a unique number or numbers, and all the data in a row relates to a specific record or observation.