Key Concepts in Database Systems (COMP5320/COMP5321)

Three main categories of data models

High level (Conceptual data model), Representational level (Logical data model), and Low level (Physical data model).

Database

A shared collection of logically related data and its description, designed to meet the information needs of an organization. It is a collection of data and its description, organised to provide information.

Database Management System (DBMS)

Software that interacts with end users, applications, and the database itself to capture and analyze data.

Three important, basic concepts of Entity-Relationship (ER) modelling

Entity, Attribute, and Relationship.

Entity in ER modelling

A group of objects of interest with the same characteristics. It is usually a noun and may be concrete (e.g., Film, Car) or abstract (e.g., Registration, Booking). In a Relational Model, an entity is called a Table.

Attributes in ER modelling

Qualities or characteristics an entity has. These are also often nouns.

Relationships in ER modelling

As verbs (what the entity can do or experience). They describe how different entities are connected.

Diagrammatic notation used for ER diagrams

UML (Unified Modelling Language) notation.

Multiplicity in relationships

It defines the number, or range (i.e., min..max), of possible occurrences of an entity that may participate in a relationship with another entity type. Multiplicity constraints are determined by the business rules of the organisation.

Where multiplicity constraints are specified in an ER diagram

At the "far end" (opposite side) of the entity being considered.

Difference between a Strong Entity and a Weak Entity

A strong entity does not depend on the existence of another entity and is uniquely identifiable by its primary key attributes. A weak entity depends partially or wholly on the existence of another entity and cannot be uniquely identified by only its own attributes; it needs PK attributes from the related entity.

Candidate Keys

A set of attributes that uniquely identify each row in a table. The sources mention identifying candidate keys as a step in database design.

Primary Key (PK)

One or more attributes chosen to uniquely identify each row in a table.

Primary Key

If there is more than one candidate key, one is chosen as the primary key.

Requirements of a Primary Key

Unique values (must uniquely identify each entity instance/row) and No part of the primary key may have a null value (Entity Integrity rule).

Foreign Key (FK)

Columns (or a set of columns) that match the candidate key of the associated table. They are used to represent relationships between tables.

Entity Integrity Rule

No part of the primary key may have a null value.

Referential Integrity Rule

A foreign key value must either match a primary key value in the referenced table or be null.

Core Functions of SQL

Data Definition Language (DDL) and Data Manipulation Language (DML).

Purpose of Data Definition Language (DDL)

For defining and creating the database objects.

Purpose of Data Manipulation Language (DML)

For manipulating and querying the data contained in the database, i.e., populating, reading, and updating the data in the tables.

Basic DML SQL Commands

INSERT, SELECT, UPDATE, DELETE.

GROUP BY Clause

Specifies the attribute(s) to group the records by, summarising data by groups and producing a single summary row for each group.

HAVING Clause vs WHERE Clause

`WHERE` filters individual rows, whereas `HAVING` filters groups.

Nested Queries (Subqueries)

Queries can be nested within a query. The inner query is used to determine the results of the outer query.

Purpose of EXISTS Operator

It produces a simple true or false result. It returns `TRUE` if and only if there exists at least one row in the result returned by the subquery; otherwise, it returns `FALSE`.

Set Operators in SQL

`UNION`, `INTERSECT`, and `EXCEPT`. To use them, the tables must be union compatible.

Union Compatible Tables

The tables must have the same number of columns, and corresponding columns must have compatible data types.

View in SQL

The syntax `CREATE VIEW View_Name AS sql_query` is provided. Views are used to restrict data access and simplify complex queries.

Views

Used to restrict data access and simplify complex queries; can be based on one or more tables or even other views.

INNER JOIN

Includes elements where there is a match in both tables.

LEFT JOIN

Includes all elements of the left table.

RIGHT JOIN

Includes all elements of the right table.

FULL JOIN

Includes elements from both tables even if there is no match.

ACID properties

Atomicity, Consistency, Isolation, Durability.

Lost Update

A situation that may occur when two database update operations execute concurrently.

Serializable Schedule

A schedule where the order of reads/writes is important if one transaction writes a data item and another reads/writes the same data item; gives the same output as serializability.

Normal recovery

Recovery after normal shutdown.

Warm recovery

Recovery after system failure.

Cold recovery

Recovery after media failure.

Checkpoint

All modified pages in memory are written to disk to ensure an accurate record of modified data exists on stable storage.

Limitations of Relational Databases

They can be too rigid, as not all applications match well the relational structure; they provide a lot of correctness guarantees, which might be too much for some applications.

Key-Value Stores

One type of NoSQL database system.

Document Stores

One type of NoSQL database system.

Search Engine Databases

One type of NoSQL database system.

Graph Databases

One type of NoSQL database system.

Complex types in PostgreSQL

Data types like Arrays and BLOBs (Binary Large Objects) can be used; composite types can also be created.

Inheritance in PostgreSQL

Implemented using the INHERITS keyword when creating a table; queries on the parent table return rows from the parent and all inheriting tables.

User Time

The general use of temporal columns/attributes (i.e., of date/time type), e.g., date_of_birth.

Valid Time

The period in which the facts will be true with respect to the world.

Transaction Time

The period during which the data is stored in the database.

Database Security

The subset of cybersecurity that deals specifically with databases, protecting the DBMS and database against intentional or accidental threats.