Databases (CS2855)

Databases

An organised collection of structured information/data that is stored electronically. This allows for easy access, management, and updating of data.

Database Management System (DBMS)

An application that efficiently stores, manages, and retrieves data while acting as an interface between users.

Example of Database Management Systems (DBMS)

Oracle, MS SQL Server, PostgreSQL, MySQL

Instance

The data the database contains

Schema

Description of a database’s structure

Data Management System (DBMS)

Software application that creates, modifies, and manages databases - acting as an interface between the database and users.

Abstraction

The process of simplifying complex ideas by focusing on essential features and ignoring unnecessary details

Physical Level of Data Storage

Presents how the data is stored and organised in memory

Logical Level of Data Storage

Presents how the data is organised with each other

View Level of Data Storage

A restricted/filtered version of the contents for reasons of data protection

Compiler Level in a Database

Where data structures in a program are implemented

Programmer Level in a Database

Where data structures are built to store information

User Level in a Database

Hides the storage/database details from the user

Compiler Level Comparison

The physical level of a database

Programmer Level Comparison

The logical level of a database

User Level Comparison

The view level of a database

Database Instance

A snapshot of the actual data contained within the schema at a specific point in time.

Database Schema

The blueprint/structure of the database (defining its tables, columns, and relationships).

Logical Schema

The overall logical structure of the database

Instances

The actual contents of the database at som point in time

Physical Schema

The overall physical structure of the database

Data Models

Summarises the description of the database via diagrams, describing:

• The data itself

• Relationships among the data

• Semantics of the data

• Constraints the data must satisfy

Entity-Relationship (ER) Model

Data model which describes interrelated entities and specifies relationships that can exist between them.

Components of an Entity-Relationship (ER) Model

Entity, Attributes, Relationships

Entity in an ER Model

Represents a real-world object, place, person, or concept about which data is stored.

Attributes in an ER Model

The properties or characteristics that describe an entity

Relationships in an ER Model

The association between two or more entities.

The Relational Model

Organises the data into tables (or “relations”) with rows and columns.

Relational Model Representation

Represents a real-world entity

Data Definition Language (DDL)

Used for defining the database schema, a subset of SQL

Data Manipulation Language (DML)

Used for querying and updating the contents, provides mechanisms for accessing (querying) and manipulating (updating) the data that is organised by the appropriate model.

Strong Entities

Entities uniquely identifiable by one or more of their attributes (primary keys)

Weak Entities

Entities unidentifiable by their own attributes alone, depending on a strong, parent entity for their existence

Simple/Atomic Attributes

Attributes that cannot be broken down further

Composite Attributes

Attributes that can be divided into smaller sub-parts

Multi-valued Attributes

Attributes that can have more than one value for an entity instance

Derived Attributes

Attributes that derives from other attributes

Primary Key

An attribute that uniquely identifies each record in an entity

Foreign Key

An attribute that references the primary key in another table, establishing a relationship between them

One to One Relationships

Where a record in one entity is associated with exactly one record in another entity.

One to Many Relationships

Where a record in one entity is associated with many records in another entity

Many to Many Relationships

Where many records in one entity are associated with many records in another entity

Many to One Relationships

Where many records in one entity are associated with exactly one record in another entity.

Constraints in ER Diagrams

Specifications that the contents of the database must always conform to

Three types of possible constraints in an ER diagram

Cardinality constraints, Key constraints, Participation constraints

Cardinality Constraints in an ER diagram

Defines the maximum and minimum number of relationship instances an entity can take part in

Participation Constraints in an ER diagram

Specifies whether an entity’s participation in a relationship is required/optional

Key Constraints in an ER diagram

Rules that maintain data accuracy in the ER diagram

Weak Entity Sets

Entity set that does not have a primary key and requires an attribute from another entity (via a foreign key)

Relation Schema

Blueprint of a relational database, defines the table, columns, data types, and relationships.

Table Representation in Relational Models

Relations

Column Representation in Relational Models

Attributes

Row Representation in Relational Models

Unique records/instances

Relation Instance

The actual set of data (snapshot) stored in a database table

Relation Instance Key

Uniquely identify records (tuples) in a relational database table and link different tables together

Super Keys

A set/group of single or multiple keys that uniquely identify rows in a table

Candidate Key

A minimal super key, uniquely identifies a record but contain no extra attributes (no repeated data or values)

Scheme Diagrams

A visual diagram representation of a database’s structure

Strong Entity Set

Entity set that has a primary key and is independent on its own

Relationship Set

A collection of similar relationships linking two or more entity sets