Week 1 - Principles, Origins and Objectives

Introduction to databases

Database

An organised electronic collection of data that can be stored locally or on the cloud.

Data

The raw, unprocessed facts (e.g. John, 29, Birkbeck)

Information

Processed, organised data (e.g. John is 29 years old and studies at Birkbeck)

Persistent Data

Data that exists even when a computer is turned off.

Field

A single piece of information stored for an attribute in a database, such as a name or address

Record

In the physical layer it is collection of fields that together represent a data entry, such as a business's name, address, and phone number.

Database Management System

Software that allows users to define, construct, manipulate, and share data in a database.

Redundancy

The unnecessary duplication of data across different records or tables.

Physical

The lowest level of abstraction in a DBMS. It tells us how data is stored in memory

Logical

The middle level of abstraction in a DBMS. It tells us the type of data that is store and the relationship between the data

View

The top level of abstraction in a DBMS - it is the one that users see

Query language

A specialised language for making queries and retrieving information from databases such as SQL

Data independence

The ability to make changes at one level without affecting a higher level

Entity Relationship Model

A conceptual framework for designing databases by defining entities, attributes, and relationships.

Relation

Also known as a table, it is a collection of rows (tuples) and columns (attributes)

Many-to-One

A relationship where multiple records in one table relate to a single record in another table.

Schema

The blueprint or logical structure of a database

Bridge Table

A table used to link two other tables in a many-to-many relationship. It typically contains foreign keys from both linked tables

Key

Generic name for column that uniquely identifies a record in a table (e.g. STUDENT_ID, EMAIL). There are many types

Composite

aka concatenated, a key that consists of more than one attribute to create a unique value (e.g. an enrollment table could have STUDENT_ID and COURSE as one student could be on many courses and courses have many students)

Candidate

a type of key - one or a set of columns that is the minimum to uniquely identify a tuple. One is chosen as the primary key

Primary

A key that is a unique identifier for each row in a database. It cannot contain null values or duplicates

Foreign

A key that is an attribute in one table that refers to a candidate key of another table.

Entity Integrity Rule

A rule ensuring that each record in a table has a unique, non-null value (i.e. a primary key)

Referential Integrity Rule

A rule ensuring that every non-null foreign key value has a matching candidate key value in another relation.

Structured Query Language

A query language used to retrieve and manipulate data in relational databases.

Tuple

A row in a relational database table, representing individual entities.

Attribute

A column in a relational database table, showing characteristics of an entity

Cardinality

The number of relationships an entity has with another entity in a database.

Domain

A set of legal values that an attribute can assume in a database.

Null Value

A missing piece of information in a database, representing the absence of a value.

Time-varying Relation

A relation whose data is dynamic and can change over time, as opposed to a static schema.

Integrity Constraints

Rules that ensure the accuracy and consistency of data in a database.