Skip to main content

22.02.07 - Introduction to relational databases

Everything in the module will be SQL databases, other databases which are not relational and will not be looking at them in this module

Why databases​

Give a set of tools for storing, searching and managing this information

Basic concepts and skills with database systems are part of the skill set you will be assumed to have as a CS graduate

Early days​

Used to be file-based systems, each file had its own format and the program had to know what format. Any other program using that file would also have to know that format

Database Management System​

  • A program in the middle can coordinate access
  • Applications link with DBMS rather than data files

Definitions​

Database: Shared collection of logically related data and a description of the data designed to meet the needs of an organisation Database Management System: Software system which enables the user to control the database Application Program: Program which interacts with a database through the DBMS

The relational Model​

  • The foundation for most modern database systems
  • Information stored as records in relations (tables)

Structure​

  • Data is stored in relations (tables)
  • Relations are made up of attributes (columns)
  • Data takes the form of tuples (rows)
    • Must not be duplicate tuples

5dba29f948d9ef890360d0fff798218f.png

Each column has a domain, a set from which all possible values for that column can come

  • Degree of a relation - How long each tuple is or how many columns the table has
  • Cardinality of a relation - How many different tuples there are, or how many rows a table has

Schemas and Attributes​

Often helpful to reference columns using names Attributes are named columns in a relation Schema defines the attributes for a relation . Each relation has a schema. Scheme is like a header for the column. (All headers wrapped in one big {})

Named and Unnamed Tuples​

Tuples specify values for each attribute in a relation, can be named as sets of pairs, or unnamed for convenience. Need to be careful with the ordering of unnamed tuples Tuple e.g. {(ID, A368), (Name. Jane Brown) ...}

Summary: Schema is a set of attributes Tuple assigns a value to each attribute in the schema Relation is a set of tuples with the same schema

Candidate Keys​

A set of attributes in a relation is a candidate key if and only if

  • Every tuple has a unique value for that set of attributes: uniqueness
  • No proper subset of the set has the uniqueness property: minimality

Choosing Candidate Keys - Cant necessarily infer the candidate keys based solely on the data in your table Must use knowledge of the real-world to help

NULLs and Primary Keys​

Primary Key - Unique identifier Missing information can be represented using NULLs Not the same as 0 or blank character Entity integrity: Primary Keys cannot contain NULL values

Foreign Keys​

Used to link data in two relations Set of attributes in the first relation is a foreign key if its value matches a primary/candidate key value in a second relation Called Referential Integrity

Referential Integrity​

When relations are updated, referential integrity might be violated Usually occurs when a referenced tuple is updated or deleted

Number of options when this occurs:

  • RESTRICT – stop the user from doing something
  • CASCADE – let the changes flow on
  • SET NULL – make referencing values null
  • SET DEFAULT – make referencing values the default for their column