CSC271

DATABASE SYSTEMS
Complete Lecture Notes

Lectures 1 – 22 | All Topics Covered

Based on: Connolly & Begg, Database Systems, 4th Ed.

TABLE OF CONTENTS
Lecture 1–2 — Introduction to Databases & DBMS Environment
Lecture 3–4 — Three-Level Architecture, Data Independence, DB Languages
Lecture 5–6 — Relational Model, Keys & Integrity Constraints
Lecture 7 — Relational Algebra & Operations
Lecture 10 — SQL – Introduction & Data Manipulation Basics
Lecture 11 — SQL – WHERE Clause & Search Conditions
Lecture 12 — SQL – GROUP BY, HAVING & Subqueries
Lecture 13 — SQL – Multi-Table Queries & Joins
Lecture 14 — SQL – Data Definition Language (DDL)
Lecture 15 — SQL – Integrity Constraints & CREATE/ALTER/DROP
Lecture 18 — DB Planning, Design & SDLC
Lecture 19 — Requirements Analysis & DB Design Phases
Lecture 20 — Prototyping, Implementation & CASE Tools
Lecture 22 — Entity–Relationship (ER) Modeling

CSC271 Database Systems — Complete Notes Page 1

 LECTURE 1 – 2

Introduction to Databases
Chapter 1 & 2

What is a Database System?

A database is an organized collection of related data. A Database Management System (DBMS) is
software that enables users to define, create, maintain, and control access to a database.

Real-World Database Applications

• Supermarket purchase tracking
• Credit card transactions
• Online booking (flights, hotels)
• Library management
• University registration systems
• Insurance and banking

File-Based Systems (Old Approach)

Early computers used file-based systems where each application program defined and managed its own
data files. Problems included:
• Data redundancy — same data stored multiple times in different files
• Data inconsistency — updates in one file not reflected elsewhere
• Data isolation — data scattered across many files
• Integrity problems — hard to enforce constraints across files
• Security difficulties — hard to apply different access rights
• Program-data dependence — changing file structure broke all programs

Advantages of DBMS
Key Benefits of Using a DBMS

✔ Reduced data redundancy – data stored once, referenced everywhere

✔ Data consistency – single source of truth

✔ Improved data sharing – authorized users access same data

✔ Better security – authentication and access control

✔ Data integrity – constraints enforced automatically

CSC271 Database Systems — Complete Notes Page 2

 ✔ Concurrency control – multiple users can work simultaneously

✔ Backup & recovery – automatic data protection

✔ Program-data independence – change structure without rewriting code

Disadvantages of DBMS
• Complexity – requires skilled staff (DBA)
• Cost – expensive DBMS software and hardware
• Performance – overhead compared to simple file access
• Single point of failure – a DBMS crash affects all users

Roles in the Database Environment

Role Responsibilities

Data Administrator (DA) Database planning, standards, policies

Database Administrator (DBA) Physical design, security, performance, backup

Database Designer (Logical) Conceptual & logical schema design

Database Designer (Physical) Storage structures, indexing

Application Programmer Develop programs using DML

End User (Naive) Uses pre-built forms and menus

End User (Sophisticated) Writes own SQL queries

Brief History of DBMS

• 1960s: IBM developed IMS (hierarchical) for Apollo moon project
• Mid-1960s: General Electric built IDS (network model)
• 1970: E.F. Codd proposed the relational model at IBM
• 1974: Chamberlin defined SEQUEL (later renamed SQL)
• 1987: First ANSI/ISO SQL standard published
• 1992: SQL2/SQL92 – major revision
• 1999: SQL:1999 added object-oriented features
• Today: Generations: Hierarchical → Network → Relational → Object-Relational

CSC271 Database Systems — Complete Notes Page 3

 LECTURE 3 – 4

Three-Level Architecture & DB Languages

Chapter 2

ANSI-SPARC Three-Level Architecture

The ANSI-SPARC architecture separates how data is viewed by users from how it is physically stored. It
has three levels:

Level Also Called Description

External Level View Level / User View What individual users see. Each user gets a customized
view. Different users may see the same data in different
formats.

Conceptual Level Community View / Logical The complete logical structure of the whole database —
Level entities, attributes, relationships, and constraints.
Independent of storage.

Internal Level Physical Level / Storage How data is physically stored on disk — file
Level organizations, indexes, storage structures, access
methods.

Schemas
• External Schema (Subschema): Describes one user's view. Multiple per database.
• Conceptual Schema: Describes all entities, attributes, relationships, and integrity constraints. Only
ONE per database.
• Internal Schema: Describes physical storage — stored records, data fields, indexes. Only ONE per
database.

Mappings
• Conceptual/Internal Mapping: Translates conceptual records to physical storage.
• External/Conceptual Mapping: Maps each user's view to the conceptual schema.

Data Independence
Two Types of Data Independence

Logical Data Independence: Ability to change the conceptual schema without changing external
schemas or application programs. Example: adding a new attribute to a table should not break existing
user views.

CSC271 Database Systems — Complete Notes Page 4

 Physical Data Independence: Ability to change the internal schema (e.g., change file organization or
storage device) without changing the conceptual or external schemas.

Database Languages
Language Purpose Example Statements

DDL (Data Definition Define/describe database CREATE TABLE, ALTER TABLE, DROP
Language) structure TABLE

DML (Data Manipulation Insert, update, delete, retrieve SELECT, INSERT, UPDATE, DELETE
Language) data

DCL (Data Control Control access and permissions GRANT, REVOKE

Language)

Procedural vs. Non-Procedural DML

Type How it works Used in

Procedural DML You specify WHAT data to get AND HOW to Network/Hierarchical DBMSs
get it. Works on one record at a time.

Non-Procedural DML You specify WHAT data you need. DBMS Relational DBMSs (SQL, QBE)
figures out HOW. Works on sets of records.

Functions of a DBMS
• Data storage, retrieval, and update
• User-accessible catalog (data dictionary / metadata)
• Transaction support (ACID properties)
• Concurrency control – safe multi-user access
• Recovery services – after system failure
• Authorization services – access control
• Integrity enforcement
• Support for data communication

DBMS Architectures
Architecture Description Key Point

Teleprocessing Single mainframe, many terminals All processing on one machine

File-Server DBMS on each workstation, files shared Heavy network traffic, concurrency
problems

Two-Tier Client-Server Client handles UI; Server holds DB Better than file-server; fat client
issue

Three-Tier Client-Server Client (UI) + Middle tier (Logic) + Server Best scalability; web-friendly; thin
(DB) client

CSC271 Database Systems — Complete Notes Page 5

 LECTURE 5 – 6

The Relational Model

Chapter 3

Key Terminology
Formal Term Common Term Meaning

Relation Table A 2D table with rows and columns

Tuple Row / Record A single data entry in a table

Attribute Column / Field A named property of the relation

Domain (same) Set of all allowable values for an attribute

Degree (same) Number of attributes (columns)

Cardinality (same) Number of tuples (rows)

Primary Key (same) Attribute(s) that uniquely identify each row

Properties of a Relation
• Each relation has a unique name
• Each cell contains exactly one atomic value (no repeating groups)
• Each attribute has a distinct name
• All values in a column are from the same domain
• No duplicate tuples – every row is unique
• Order of attributes has no significance
• Order of tuples has no significance (theoretically)

Types of Keys
Key Type Definition Example (Branch table)

Superkey Any attribute or set of attributes that {branchNo, city}, {branchNo,

uniquely identifies a tuple street}

Candidate Key Minimal superkey – no proper subset is also branchNo, postcode

a superkey

Primary Key The chosen candidate key to identify tuples branchNo

Alternate Key Candidate keys NOT chosen as primary key postcode

CSC271 Database Systems — Complete Notes Page 6

 Key Type Definition Example (Branch table)

Foreign Key Attribute(s) in one table referencing branchNo in Staff table

candidate key in another table

Integrity Constraints
NULL: Represents missing, unknown, or not-applicable data. It is NOT zero or blank.

Two Core Integrity Rules

1. Entity Integrity: No attribute that is part of the PRIMARY KEY can be NULL. Every row must be
uniquely and completely identifiable.

2. Referential Integrity: If a FOREIGN KEY exists in a relation, its value must either match a candidate
key value in the referenced (parent) table, OR it must be completely NULL. You cannot reference a
non-existent record.

General Constraints: Business rules set by users or DBA. Example: "No staff member may manage
more than 100 properties."

Views
A View is a virtual table derived from one or more base relations. It does not physically store data — it is
computed on demand.

• Base Relation: A real table physically stored in the database

• View: A named query result — a virtual relation
• Views provide security by hiding sensitive columns
• Views simplify complex queries for end users
• Views support logical data independence

Restrictions on updating views:

• Updates ALLOWED if view is based on a single table containing a candidate key
• Updates NOT ALLOWED on views involving multiple tables
• Updates NOT ALLOWED on views with aggregation or grouping operations

CSC271 Database Systems — Complete Notes Page 7

 LECTURE 7

Relational Algebra & Calculus

Chapter 4

Introduction
Relational Algebra is a formal procedural query language (you specify HOW to get data). Relational
Calculus is declarative (you specify WHAT you want). Both are equivalent in expressive power.

A language is relationally complete if it can express any query that can be expressed in relational
calculus.

Five Basic Operations

Operation Symbol Description

Selection (Restriction) σ (sigma) Returns rows that satisfy a condition. Works on one
relation.

Projection Π (pi) Returns specified columns; eliminates duplicates.

Cartesian Product × (times) Combines every row of R with every row of S.

Union ∪ All tuples in R OR S (or both). Relations must be

union-compatible.

Set Difference − Tuples in R but NOT in S. Relations must be

union-compatible.

Derived Operations
Operation Symbol Definition

Intersection ∩ Tuples in BOTH R and S. Can be expressed as: R − (R −

S)

Join (Natural/Theta) ■ Combines related rows from two tables based on a

condition

Division ÷ Finds tuples in R associated with ALL tuples in S

Examples of Operations
Selection — σ

List all staff with salary > 10,000:

CSC271 Database Systems — Complete Notes Page 8

 σ (Staff)
salary > 10000

Projection — Π

Show only staffNo, fName, lName, salary:

Π (Staff)
staffNo, fName, lName, salary

Union

All cities with a branch office OR property for rent:

Π (Branch) ∪ Π (PropertyForRent)
city city

Set Difference

Cities with a branch office but NO property for rent:

Π (Branch) − Π (PropertyForRent)
city city

Intersection

Cities with BOTH a branch office AND a property for rent:

Π (Branch) ∩ Π (PropertyForRent)
city city

CSC271 Database Systems — Complete Notes Page 9

 LECTURE 10 – 11

SQL – Introduction & Data Manipulation

Chapter 5

What is SQL?
SQL (Structured Query Language) is the standard language for relational databases. It is
non-procedural — you tell the DBMS what you want, not how to get it.

Component Purpose

DDL (Data Definition Language) CREATE, ALTER, DROP tables and schemas

DML (Data Manipulation SELECT, INSERT, UPDATE, DELETE data

Language)

DCL (Data Control Language) GRANT and REVOKE permissions

DML Statements
Statement Purpose Example

SELECT Retrieve data SELECT * FROM Staff;

INSERT Add new rows INSERT INTO Staff VALUES ('S01','Ali');

UPDATE Modify existing rows UPDATE Staff SET salary=12000 WHERE

staffNo='S01';

DELETE Remove rows DELETE FROM Staff WHERE staffNo='S01';

The SELECT Statement

SELECT [DISTINCT] column1, column2, ... -- or * for all
FROM table_name
WHERE condition
GROUP BY column
HAVING group_condition
ORDER BY column [ASC | DESC];

SELECT * returns all columns. DISTINCT removes duplicate rows.

WHERE Clause – Search Conditions

Condition Type Syntax Example

Comparison =, <>, <, <=, >, >= WHERE salary > 10000

CSC271 Database Systems — Complete Notes Page 10

 Condition Type Syntax Example

Range BETWEEN … AND … WHERE salary BETWEEN 20000 AND

30000

Set Membership IN (val1, val2, …) WHERE position IN ('Manager','Supervisor')

Pattern Match LIKE (% = any chars, _ = one WHERE address LIKE '%Glasgow%'
char)

Null Test IS NULL / IS NOT NULL WHERE comment IS NULL

Pattern Matching – LIKE

% matches any sequence of zero or more characters

_ (underscore) matches exactly ONE character

Example: WHERE name LIKE 'A%' → finds all names starting with A

Example: WHERE name LIKE '_ohn' → finds John, Mohn, etc.

SQL Literals & Writing Rules

• Non-numeric literals in single quotes: 'London'
• Numeric literals without quotes: 10000
• SQL keywords are case-insensitive: SELECT = select = Select
• String data IS case-sensitive: 'Smith' ≠ 'SMITH'
• Use semicolon (;) to end statements in most dialects

ORDER BY Clause
SELECT staffNo, lName, salary
FROM Staff
ORDER BY salary DESC; -- highest first

ORDER BY lName ASC; -- alphabetical (default)

Aggregate Functions
Function Returns Example

COUNT(*) Number of rows SELECT COUNT(*) FROM Staff;

COUNT(col) Number of non-null values SELECT COUNT(salary) FROM Staff;

SUM(col) Total of numeric column SELECT SUM(salary) FROM Staff;

AVG(col) Average of numeric column SELECT AVG(salary) FROM Staff;

MAX(col) Largest value SELECT MAX(salary) FROM Staff;

MIN(col) Smallest value SELECT MIN(salary) FROM Staff;

CSC271 Database Systems — Complete Notes Page 11

 LECTURE 12

SQL – GROUP BY, HAVING & Subqueries

Chapter 5

GROUP BY Clause
Groups rows with the same value in the specified columns, and produces one summary row per group.
Used with aggregate functions.

-- Count staff and total salary per branch:

SELECT branchNo, COUNT(staffNo) AS myCount, SUM(salary) AS mySum
FROM Staff
GROUP BY branchNo
ORDER BY branchNo;

Important rule: Every column in SELECT must either appear in GROUP BY OR be inside an aggregate
function.

HAVING Clause
HAVING filters groups (like WHERE filters rows). Use HAVING only with GROUP BY, and only with
conditions involving aggregate functions.

-- Only branches with MORE than 1 staff member:

SELECT branchNo, COUNT(staffNo) AS myCount, SUM(salary) AS mySum
FROM Staff
GROUP BY branchNo
HAVING COUNT(staffNo) > 1
ORDER BY branchNo;

WHERE vs HAVING

WHERE — filters individual ROWS before grouping

HAVING — filters GROUPS after grouping

You CANNOT use aggregate functions in a WHERE clause.

Subqueries (Nested Queries)

A subquery is a SELECT statement embedded inside another SELECT. The inner query runs first, and its
result is used by the outer query.

-- List staff who work at '163 Main St':

SELECT staffNo, fName, lName, position
FROM Staff

CSC271 Database Systems — Complete Notes Page 12

 WHERE branchNo = (SELECT branchNo
FROM Branch
WHERE street = '163 Main St');

Subquery Type Returns Used with

Scalar subquery Single value (one column, one row) =, <, >, etc.

Row subquery Multiple columns, one row Row comparison

Table subquery Multiple rows and columns IN, EXISTS, ANY, ALL

CSC271 Database Systems — Complete Notes Page 13

 LECTURE 13

SQL – Multi-Table Queries & Joins

Chapter 5

Multi-Table Queries
Joins combine rows from two or more tables based on a related column. A join is a filtered Cartesian
product.

-- List branches and properties in same city:

SELECT [Link], [Link], [Link], propertyNo
FROM Branch b, Staff s, PropertyForRent p
WHERE [Link] = [Link] AND [Link] = [Link]
ORDER BY [Link], [Link];

Types of Joins
Join Type SQL Syntax What it returns

Inner Join (default) FROM A, B WHERE [Link] = [Link] OR: A Only rows with matching values in
INNER JOIN B ON [Link] = [Link] BOTH tables

Left Outer Join A LEFT JOIN B ON [Link] = [Link] All rows from A + matched rows from
B. Unmatched B rows → NULL

Right Outer Join A RIGHT JOIN B ON [Link] = [Link] All rows from B + matched rows from
A. Unmatched A rows → NULL

Full Outer Join A FULL JOIN B ON [Link] = [Link] All rows from BOTH tables.
Unmatched rows → NULL

Cross Join (Cartesian) A CROSS JOIN B Every row of A combined with every
row of B

-- Left Outer Join example:

SELECT b.*, p.*
FROM Branch1 b LEFT JOIN PropertyForRent1 p
ON [Link] = [Link];

-- Full Outer Join example:

SELECT b.*, p.*
FROM Branch1 b FULL JOIN PropertyForRent1 p
ON [Link] = [Link];

EXISTS and NOT EXISTS

CSC271 Database Systems — Complete Notes Page 14

 EXISTS returns TRUE if the subquery returns at least one row. NOT EXISTS returns TRUE if the
subquery returns NO rows.

-- Find staff who work at some branch in Glasgow:

SELECT staffNo, fName, lName
FROM Staff s
WHERE EXISTS (SELECT * FROM Branch b
WHERE [Link] = [Link]
AND [Link] = 'Glasgow');

Set Operations in SQL

Operation SQL Keyword Description

Union UNION Combines results of two queries, removes duplicates

(UNION ALL keeps them)

Intersection INTERSECT Rows common to BOTH queries

Difference EXCEPT (or MINUS) Rows in first query but NOT in second

CSC271 Database Systems — Complete Notes Page 15

 LECTURE 14 – 15

SQL – Data Definition Language (DDL)

Chapter 6

SQL Identifiers & Data Types

SQL identifiers name database objects (tables, columns, views). Rules: max 128 characters, must start
with a letter, no spaces.

Data Type Description Example

CHAR(n) Fixed-length string of exactly n CHAR(5) for postcode

characters

VARCHAR(n) Variable-length string up to n VARCHAR(30) for name

characters

INTEGER / INT Whole number number of rooms

SMALLINT Small whole number short codes

DECIMAL(p,s) Exact decimal: p digits total, s after DECIMAL(7,2) for salary

decimal

FLOAT / REAL Approximate floating-point number scientific values

DATE Calendar date (YYYY-MM-DD) date of birth

TIME Time of day appointment time

BOOLEAN TRUE or FALSE active flag

Creating Tables – CREATE TABLE

CREATE TABLE Staff (
staffNo VARCHAR(5) NOT NULL,
fName VARCHAR(15) NOT NULL,
lName VARCHAR(15) NOT NULL,
position VARCHAR(10) NOT NULL,
sex CHAR(1) CHECK (sex IN ('M', 'F')),
DOB DATE,
salary DECIMAL(9,2) NOT NULL,
branchNo CHAR(4) NOT NULL,
PRIMARY KEY (staffNo),
FOREIGN KEY (branchNo) REFERENCES Branch (branchNo)
ON UPDATE CASCADE
ON DELETE SET NULL
);

CSC271 Database Systems — Complete Notes Page 16

 Integrity Constraints in DDL
Constraint SQL Keyword Effect

Not Null NOT NULL Column must always have a value

Domain check CHECK (condition) Value must satisfy condition

Unique UNIQUE All values in column must be

distinct (allows NULLs)

Entity Integrity PRIMARY KEY Unique + NOT NULL; one per

table

Referential Integrity FOREIGN KEY … REFERENCES Value must exist in parent table or
be NULL

Foreign Key Referential Actions

Action Effect when parent row is deleted/updated

CASCADE Automatically delete/update all matching child rows

SET NULL Set child foreign key columns to NULL

SET DEFAULT Set child foreign key columns to their DEFAULT value

NO ACTION / Reject the delete/update if matching child rows exist

RESTRICT

FOREIGN KEY (staffNo) REFERENCES Staff ON DELETE SET NULL

FOREIGN KEY (ownerNo) REFERENCES PrivateOwner ON UPDATE CASCADE

Modifying Tables – ALTER TABLE

-- Add a new column:
ALTER TABLE Client ADD prefNoRooms INTEGER;

-- Drop a column:
ALTER TABLE Staff DROP COLUMN sex;

-- Change a default:
ALTER TABLE Staff ALTER position DROP DEFAULT;
ALTER TABLE Staff ALTER sex SET DEFAULT 'F';

-- Drop a constraint:
ALTER TABLE PropertyForRent DROP CONSTRAINT StaffNotHandlingTooMuch;

Dropping Tables & Schemas

-- Drop a table (RESTRICT fails if dependent objects exist):
DROP TABLE PropertyForRent RESTRICT;

-- Drop with CASCADE (removes all dependent objects too):

DROP TABLE PropertyForRent CASCADE;

CSC271 Database Systems — Complete Notes Page 17

 -- Create and drop a schema:
CREATE SCHEMA SqlTests AUTHORIZATION Smith;
DROP SCHEMA SqlTests CASCADE;

CREATE DOMAIN
Domains define reusable data types with constraints:
CREATE DOMAIN SexType AS CHAR(1) CHECK (VALUE IN ('M', 'F'));
CREATE DOMAIN PropertyRooms AS SMALLINT CHECK (VALUE BETWEEN 1 AND 15);
CREATE DOMAIN PropertyRent AS DECIMAL(6,2) CHECK (VALUE BETWEEN 0 AND 9999.99);

-- Use domain in table:

sex SexType NOT NULL -- instead of: sex CHAR(1) NOT NULL

CREATE ASSERTION (General Constraint)

For constraints spanning multiple tables:
-- Prevent staff from managing more than 100 properties:
CREATE ASSERTION StaffNotHandlingTooMuch
CHECK (NOT EXISTS (
SELECT staffNo FROM PropertyForRent
GROUP BY staffNo
HAVING COUNT(*) > 100
));

CSC271 Database Systems — Complete Notes Page 18

 LECTURE 18 – 20

Database Planning, Design & SDLC

Chapter 9 & 10

The Software/Database Development Lifecycle

The Database System Development Lifecycle (DB SDLC) is a structured approach to building database
systems:

# Stage Description

1 Database Planning Define mission statement & mission objectives. Plan standards, format,
documentation.

2 System Definition Define scope, boundaries, and major user views of the database.

3 Requirements Collection Gather and analyze user needs. Produce requirements specification.
& Analysis

4 Database Design Conceptual → Logical → Physical design.

5 DBMS Selection Choose the right DBMS product based on requirements.

(Optional)

6 Application Design Design user interface and application programs (transaction & UI design).

7 Prototyping (Optional) Build working model to verify requirements and design.

8 Implementation Create the physical database using DDL; write application programs.

9 Data Conversion & Transfer existing data into new database.

Loading

1 Testing Test with real data; verify correctness, performance, and usability.
0

1 Operational Maintenance Monitor performance, fix issues, incorporate new requirements.

1

Requirements Collection Approaches

Approach Description When to Use

Centralized All user requirements merged into one set. Simple systems with few user
One data model created. groups

View Integration Each user view kept separate → local data Complex systems with many
models created → merged later into global different user groups
model

CSC271 Database Systems — Complete Notes Page 19

 Approach Description When to Use

Hybrid (Combined) Mix of both approaches Most real-world large systems

Three Phases of Database Design

Phase 1: Conceptual Design

Build a high-level model of the data, completely independent of any DBMS or physical
considerations. Uses Entity-Relationship (ER) diagrams. Captures entities, relationships, and attributes
from users' requirements specification.

Phase 2: Logical Design

Convert the conceptual model into a logical model based on a specific data model (e.g., relational).
Still independent of a particular DBMS. Produces normalized table structures.

Phase 3: Physical Design

Translate the logical model into actual database structures for a specific DBMS. Defines storage
structures, file organizations, indexes for optimal performance. Creates the real database.

Fact-Finding Techniques
Technique Description Best For

Examining Documentation Review existing forms, reports, manuals, org Understanding current
charts system

Interviewing Face-to-face discussions with users and Gathering detailed

managers requirements

Observation Watch users work in their natural Discovering unstated

environment workflows

Questionnaires Written surveys sent to many users Gathering data from large
groups

Research Study industry standards and similar Benchmarking and best

systems practices

CASE Tools
Computer-Aided Software Engineering (CASE) tools assist throughout the SDLC:
• Data dictionary to store metadata about the database
• Design tools for data analysis and modeling
• Diagram tools for conceptual and logical data models
• Prototyping support for applications
• Benefits: Standards, Integration, Consistency, Automation

Data Administrator (DA) vs Database Administrator (DBA)

CSC271 Database Systems — Complete Notes Page 20

 Role Focus Key Activities

DA (Data Administrator) Early stages – strategic Database planning, standards, policies,

conceptual/logical design

DBA (Database Administrator) Later stages – technical Physical design, security, performance tuning,
backup, recovery, implementation

CSC271 Database Systems — Complete Notes Page 21

 LECTURE 22

Entity–Relationship (ER) Modeling

Chapter 11

Why ER Modeling?
Database designers, programmers, and end-users often think about data differently. The
Entity-Relationship (ER) model provides a common, non-technical, unambiguous communication tool
that everyone can understand.

We use UML notation (as it is expected to become the de facto standard) for drawing ER diagrams, while
using traditional database terminology to describe concepts.

Three Core Concepts of ER Model

1. Entity Types

Entity Type: A group of objects with the same properties that the enterprise recognizes as having an
independent existence. Shown as a rectangle labeled with a singular noun.

Entity Occurrence: A uniquely identifiable object of an entity type (one specific instance).

Examples: Staff, Branch, Client, PropertyForRent, Lease, PrivateOwner

2. Relationship Types

Relationship Type: A meaningful association among entity types. Shown as a line connecting entities,
labeled with a verb or verb phrase.

Relationship Occurrence: A unique association of specific entity occurrences.

Example: Staff — Manages → PropertyForRent | Client — Leases → PropertyForRent

3. Attributes

Attribute: A property of an entity or relationship type. Holds values that describe each entity occurrence.

Attribute Domain: The set of allowable values for one or more attributes.

CSC271 Database Systems — Complete Notes Page 22

 Degree of a Relationship
Degree Name Description Example

2 Binary Connects 2 entity types Staff HasContract Lease

3 Ternary Connects 3 entity types Doctor Prescribes Medicine

for Patient

4 Quaternary Connects 4 entity types Complex business

arrangements

1 Unary / Recursive Entity type relates to itself Staff Supervises Staff

Types of Attributes
Attribute Type Description Example

Simple (Atomic) Cannot be further divided staffNo, salary, position

Composite Made of multiple components address = street + city + postcode

Single-valued Holds only ONE value per occurrence staffNo, DOB

Multi-valued Can hold MULTIPLE values per telNo (staff can have several
occurrence phones)

Derived Calculated from other attributes age (derived from DOB), duration
(from rentStart & rentFinish)

Candidate Key in ER Model

The Candidate Key is the minimal set of attributes that uniquely identifies each occurrence of an entity
type. Every entity type must have at least one candidate key. The chosen one becomes the primary key.

Quick Reference: ER Notation (UML style)

• Rectangle → Entity Type

• Line with label → Relationship Type

• Attribute listed inside/beside entity → Attribute

• Underlined attribute → Primary Key / Candidate Key

• Double line → Recursive (unary) relationship

• Relationship names start with uppercase, use verbs: Manages, Leases, Supervises

CSC271 Database Systems — Complete Notes Page 23

 LECTURE QUICK REFERENCE

Key SQL Commands Summary

All Chapters

Complete SQL Command Reference

SELECT basics
SELECT * FROM Staff;
SELECT staffNo, fName, lName FROM Staff;
SELECT DISTINCT city FROM Branch;

WHERE conditions
WHERE salary > 10000
WHERE salary BETWEEN 20000 AND 30000
WHERE position IN ('Manager', 'Supervisor')
WHERE address LIKE '%Glasgow%'
WHERE comment IS NULL

ORDER BY
ORDER BY salary DESC;
ORDER BY lName ASC, fName ASC;

Aggregate Functions
SELECT COUNT(*), SUM(salary), AVG(salary), MAX(salary), MIN(salary) FROM Staff;

GROUP BY + HAVING
SELECT branchNo, COUNT(staffNo) AS cnt, SUM(salary) AS total
FROM Staff
GROUP BY branchNo
HAVING COUNT(staffNo) > 1;

JOIN
SELECT [Link], [Link], [Link]
FROM Staff s, Branch b
WHERE [Link] = [Link];

-- Left outer join:

SELECT b.*, p.* FROM Branch b LEFT JOIN PropertyForRent p ON [Link] = [Link];

Subquery
SELECT staffNo, fName FROM Staff
WHERE branchNo = (SELECT branchNo FROM Branch WHERE street = '163 Main St');

CSC271 Database Systems — Complete Notes Page 24

 CREATE TABLE
CREATE TABLE Staff (
staffNo VARCHAR(5) NOT NULL,
salary DECIMAL(9,2),
branchNo CHAR(4),
PRIMARY KEY (staffNo),
FOREIGN KEY (branchNo) REFERENCES Branch ON DELETE SET NULL
);

ALTER TABLE
ALTER TABLE Client ADD prefNoRooms INTEGER;
ALTER TABLE Staff ALTER sex SET DEFAULT 'F';
ALTER TABLE Staff DROP COLUMN telNo;

DROP TABLE
DROP TABLE PropertyForRent RESTRICT;
DROP TABLE PropertyForRent CASCADE;

INSERT / UPDATE / DELETE

INSERT INTO Staff VALUES ('SG16', 'Ahmed', 'Ali', 'Manager', 'M', NULL, 18000, 'B003');
UPDATE Staff SET salary = salary * 1.1 WHERE position = 'Manager';
DELETE FROM Staff WHERE staffNo = 'SG16';

CSC271 Database Systems — Complete Notes Page 25

 LECTURE GLOSSARY

Key Terms & Definitions

All Chapters

Essential Database Terms

Attribute A named column of a relation; a property of an entity type

Base Relation A real table physically stored in the database

Candidate Key A minimal superkey — uniquely identifies tuples, no subset can

Cardinality Number of rows (tuples) in a relation

Conceptual Schema Describes all entities, attributes, relationships in the database

Data Independence Ability to change one level of the DB architecture without affecting others

DBMS Database Management System — software managing the database

DDL Data Definition Language — for defining database structure (CREATE, ALTER,
DROP)

Degree Number of attributes (columns) in a relation

DML Data Manipulation Language — for accessing/modifying data (SELECT, INSERT,

UPDATE, DELETE)

Domain Set of all allowable values for one or more attributes

Entity Integrity No part of a primary key can be NULL

Entity Type A group of objects with same properties, recognized as independent by the
enterprise

Foreign Key Attribute(s) in one table referencing a candidate key in another table

Instance (DB) The actual data in the database at a specific point in time

Internal Schema Describes how data is physically stored

Logical Data Changing the conceptual schema without affecting external schemas
Independence

NULL Represents unknown, missing, or inapplicable data — NOT zero or blank

Physical Data Changing the internal schema without affecting the conceptual schema
Independence

Primary Key The chosen candidate key used to uniquely identify each row

CSC271 Database Systems — Complete Notes Page 26

 Referential Integrity Foreign key must match an existing primary key or be NULL

Relation A 2D table with rows and columns (the foundation of the relational model)

Schema The description/structure of the database (stays relatively constant)

Superkey Any set of attributes that uniquely identifies a tuple (may have extra attributes)

Tuple A single row in a relation

View A virtual table defined by a query on base relations; not physically stored

CSC271 Database Systems — Complete Notes Page 27