CS340 Database Symbols Reference

ER Diagram Notation

Reference: Slide 02_cs340.pdf - Slide 23

Basic ER Components

Rectangle

Represents an Entity Type

Use for: Regular/Strong entities that exist independently

Examples from slides:

EMPLOYEE, DEPARTMENT, PROJECT, STUDENT, COURSE

Slide 02 - Conceptual Design

Oval/Ellipse

Represents an Attribute

Use for: Simple, atomic attributes

Examples:

Name, SSN, Salary, Address, Age

Slide 02 - ER Components

Diamond

Represents a Relationship Type

Use for: Associations between entities

Examples:

WORKS_FOR, MANAGES, WORKS_ON, ENROLLS

Slide 02 - ER Components

Double Rectangle

Represents a Weak Entity Type

Use for: Entities that depend on another entity for existence

Example from slides:

DEPENDENT (depends on EMPLOYEE)

Slide 02 - Weak Entity Types (Slide 45-46)

Double Diamond

Represents an Identifying Relationship

Use for: Relationships that help identify weak entities

Example:

DEPENDENTS_OF (identifying relationship for DEPENDENT)

Slide 02 - Weak Entity (Slide 46)

Double Oval

Represents a Multivalued Attribute

Use for: Attributes that can have multiple values

Examples from slides:

{Locations} for DEPARTMENT, {Degrees} for PERSON, {Colors} for CAR

Slide 02 - Attribute Types (Slide 17)

Dashed Oval

Represents a Derived Attribute

Use for: Attributes calculated from other attributes

Example from slides:

Age (derived from BirthDate), Number_of_employees (derived by counting)

Slide 02 - Attribute Types (Slide 18)

Underlined Attribute

Represents a Key Attribute

Use for: Attributes that uniquely identify entities

Examples:

SSN (EMPLOYEE), Dnumber (DEPARTMENT), StudentID

Slide 02 - Keys (Slide 20-21)

Dashed Underlined Oval

Represents a Partial Key (for weak entities)

Use for: Discriminator of weak entity (part of its identification)

Example:

Dependent_name in DEPENDENT entity

Slide 02 - Weak Entity (Slide 45)

Participation & Cardinality Constraints

Reference: Slide 02 - Slides 36-43, 55-57

―――

Single Line

Represents Partial Participation

Use when: Not all entities must participate (min = 0)

Example:

Some courses may have no enrollments

Slide 02 - Participation (Slide 39-40)

═══

Double Line

Represents Total Participation

Use when: Every entity must participate (min = 1)

Example from slides:

Every EMPLOYEE must WORK_FOR a DEPARTMENT

Slide 02 - Participation (Slide 39-40)

Cardinality: 1 (One)

Entity participates in at most one relationship instance

Use for: One-to-one or one-to-many relationships

Example:

EMPLOYEE―1―MANAGES―1―DEPARTMENT

Slide 02 - Cardinality (Slide 35-38)

N / M

Cardinality: N or M (Many)

Entity can participate in many relationship instances

Use for: One-to-many or many-to-many relationships

Example from slides:

EMPLOYEE―N―WORKS_ON―M―PROJECT

Slide 02 - Cardinality (Slide 35-38)

(min, max)

Min-Max Notation

Specifies minimum and maximum participation

(0,1) = optional one
(1,1) = mandatory one
(0,N) = optional many
(1,N) = mandatory many

Example from slides:

EMPLOYEE (1,1)―WORKS_FOR―(1,N) DEPARTMENT

Slide 02 - Min-Max Notation (Slide 55-57)

Relationship Types by Cardinality

Type	Notation	Description	Example from Slides
1:1	1 ― 1	One entity in A relates to at most one in B	EMPLOYEE MANAGES DEPARTMENT
1:N	1 ― N	One entity in A relates to many in B	DEPARTMENT has many EMPLOYEEs
N:1	N ― 1	Many entities in A relate to one in B	EMPLOYEE WORKS_FOR DEPARTMENT
M:N	M ― N	Many entities in A relate to many in B	EMPLOYEE WORKS_ON PROJECT

Slide 02 - Relationship Cardinality (Slides 35-38)

Recursive (Unary) Relationships

Self-Referencing/Recursive Relationship

Same entity type participates in different roles

Use when: Entity relates to itself (e.g., supervision, prerequisite)

Example from slides:

EMPLOYEE supervises EMPLOYEE (supervisor role and supervisee role)

Slide 02 - Recursive Relationships (Slides 41-43)

EER (Enhanced ER) Symbols

Reference: Slide 03_cs340_eer.pdf

Specialization/Generalization Components

⊂

Subset Symbol (U-shaped arrow)

Indicates IS-A relationship from subclass to superclass

Points from subclass upward to circle connecting to superclass

Example:

SECRETARY ⊂ EMPLOYEE (SECRETARY IS-A EMPLOYEE)

Slide 03 - Notation (Slide 13)

○

Circle

Junction point connecting superclass to subclasses

Superclass connects to circle; circle connects to all subclasses

Slide 03 - Basic Notation (Slide 13)

d (Disjoint Constraint)

Entity can be member of at most ONE subclass

Place 'd' inside or near the circle

Example from slides:

EMPLOYEE → {SECRETARY, TECHNICIAN, ENGINEER} with d constraint

Slide 03 - Disjointness (Slide 23)

o (Overlapping Constraint)

Entity can be member of MULTIPLE subclasses simultaneously

Place 'o' inside or near the circle

Example from slides:

PART → {MANUFACTURED_PART, PURCHASED_PART} with o constraint

Slide 03 - Overlapping (Slide 23)

――

Single Line (from superclass to circle)

Partial Specialization - not all entities need to be in a subclass

Some superclass entities may not belong to any subclass

Slide 03 - Completeness (Slide 24)

══

Double Line (from superclass to circle)

Total Specialization - every entity must be in at least one subclass

Every superclass entity must belong to at least one subclass

Example from slides:

Every VEHICLE must be either CAR or TRUCK (total)

Slide 03 - Completeness (Slide 24)

Specialization Constraint Combinations

Disjointness	Completeness	Notation	Meaning
Disjoint	Total	d with double line	Every entity in exactly one subclass
Disjoint	Partial	d with single line	Entity in at most one subclass (or none)
Overlapping	Total	o with double line	Every entity in one or more subclasses
Overlapping	Partial	o with single line	Entity can be in multiple or no subclasses

Slide 03 - Constraints (Slide 25)

Category/Union Types

∪

Union Symbol (in circle)

Represents a Category (Union Type)

Subclass is subset of UNION of different superclasses with different keys

Example from slides:

OWNER ∪ {PERSON, BANK, COMPANY} - owner can be person OR bank OR company

Slide 03 - Categories (Slides 36-41)

――

Single Line to Union Circle

Partial Category - subset of union

Category holds subset of union of all superclass entities

Slide 03 - Category Completeness (Slide 40)

══

Double Line to Union Circle

Total Category - holds union of all

Category holds complete union of all superclass entities

Slide 03 - Category Completeness (Slide 40)

Key Difference:

Specialization/Generalization: Entity is member of ONE superclass, may be in subclasses (inheritance)
Category/Union: Entity is member of exactly ONE of multiple possible superclasses (union type)

Relational Model Notation

Reference: Slide 04_cs340_relational-model.pdf

Schema Notation

R(A₁, A₂, ..., Aₙ)

Relation Schema

R = relation name, A₁...Aₙ = attributes

Example from slides:

EMPLOYEE(Fname, Lname, SSN, Bdate, Address, Salary, Dno)

Slide 04 - Formal Definitions (Slide 16)

Attribute

Underlined Attribute

Indicates Primary Key

Example:

EMPLOYEE(SSN, Name, Salary)

Slide 04 - Keys (Slide 29-32)

Foreign Key

Attribute that references primary key of another relation

Often indicated with arrow or labeled as FK in diagrams

Example from slides:

EMPLOYEE(SSN, Dno) where Dno references DEPARTMENT(Dnumber)

Slide 04 - Referential Integrity (Slides 40-43)

→

Arrow (in diagrams)

Shows foreign key reference direction

Points from foreign key to referenced primary key

Slide 04 - Relational Schema Diagram (Slide 45)

Tuple Notation

t[A]

Tuple Component

Value of attribute A in tuple t

Example:

t[SSN] refers to the SSN value in tuple t

Slide 04 - Notation (Slide 22)

t[A₁, A₂, ..., Aₙ]

Sub-tuple

Values of multiple attributes in tuple t

Slide 04 - Notation (Slide 22)

DDL (Data Definition Language) Notation

Reference: Slide 05_cs340_mapping-ER-to-relational-and-DDL.pdf

DDL Keywords

PRIMARY KEY

Defines primary key constraint

CREATE TABLE Student (
StudentID INT,
Name VARCHAR(50),
PRIMARY KEY (StudentID)
);

Slide 05 - DDL Basics (Slide 17)

FOREIGN KEY

Defines foreign key constraint

FOREIGN KEY (Dno)
REFERENCES DEPARTMENT(Dnumber)

Slide 05 - DDL Foreign Keys (Slide 19)

NOT NULL

Attribute cannot be null

Name VARCHAR(50) NOT NULL

Slide 05 - DDL Constraints (Slide 24)

ON DELETE CASCADE

Delete referenced rows when parent is deleted

FOREIGN KEY (Dno)
REFERENCES DEPARTMENT(Dnumber)
ON DELETE CASCADE

Slide 05 - FK Conditions (Slide 24)

ON DELETE SET NULL

Set to NULL when parent is deleted

ON DELETE SET NULL

Slide 05 - FK Conditions (Slide 24)

ON UPDATE CASCADE

Update foreign key when primary key changes

ON UPDATE CASCADE

Slide 05 - FK Conditions (Slide 24)

Functional Dependency Notation

Reference: Slide 09_cs340_normalization.pdf

FD Symbols

X → Y

Functional Dependency

X functionally determines Y

If two tuples have same X value, they must have same Y value

Examples from slides:

SSN → ENAME
PNUMBER → {PNAME, PLOCATION}
{SSN, PNUMBER} → HOURS

Slide 09 - FD Definition (Slides 23-27)

X⁺

Closure of X

Set of all attributes determined by X

Used to find all attributes functionally dependent on X

Slide 09 - Closure (Slides 83-84)

F⁺

Closure of F

Set of all FDs implied by F

Slide 09 - Closure Algorithm (Slide 80)

Types of Dependencies

Full Functional Dependency

Y depends on entire X (removing any attribute from X breaks dependency)

Example from slides:

{SSN, PNUMBER} → HOURS is full FD
(neither SSN → HOURS nor PNUMBER → HOURS hold)

Slide 09 - 2NF Definition (Slide 52)

Partial Dependency

Y depends on part of composite key X

Example from slides:

{SSN, PNUMBER} → ENAME is partial
(since SSN → ENAME also holds)

Violates 2NF

Slide 09 - 2NF (Slide 52)

Transitive Dependency

X → Y and Y → Z (where Y is not a candidate key)

Example from slides:

SSN → DNUMBER and DNUMBER → DMGRSSN
Therefore SSN → DMGRSSN is transitive

Violates 3NF

Slide 09 - 3NF (Slide 55)

Armstrong's Axioms

Slide 09 - Inference Rules (Slide 81)

Rule	Notation	Meaning
Reflexivity	If Y ⊆ X, then X → Y	Trivial dependency
Augmentation	If X → Y, then XZ → YZ	Add same attributes to both sides
Transitivity	If X → Y and Y → Z, then X → Z	Chaining dependencies
Union	If X → Y and X → Z, then X → YZ	Derived from axioms
Decomposition	If X → YZ, then X → Y and X → Z	Derived from axioms

Normal Forms

Reference: Slide 09_cs340_normalization.pdf

Normal Form	Requirements	What it Prevents	Slide Reference
1NF	All attributes are atomic (no multivalued or composite attributes)	Repeating groups, nested relations	Slide 09 - Slides 46-51
2NF	1NF + No partial dependencies (non-prime attributes fully depend on entire key)	Partial dependencies on composite keys	Slide 09 - Slides 52-54
3NF	2NF + No transitive dependencies (non-prime attributes don't depend on other non-prime attributes)	Transitive dependencies	Slide 09 - Slides 55-57
BCNF	3NF + Every determinant is a candidate key	Any dependency where determinant is not a superkey	Slide 09 - Slides 68-78

Remember: Every relation in BCNF is in 3NF, every 3NF is in 2NF, and every 2NF is in 1NF.

SQL Operators & Keywords

References: Slides 06 & 07 (basic-sql and advanced-sql)

Comparison Operators

Slide 06 - Selection (Slide 18)

Operator	Meaning	Example from Slides
=	Equal to	WHERE major = "CS"
<>	Not equal to	WHERE status <> 'active'
<	Less than	WHERE age < 25
>	Greater than	WHERE salary > 30000
<=	Less than or equal	WHERE quantity <= 10
>=	Greater than or equal	WHERE rating >= 4

Logical Operators

Slide 06 - Selection (Slide 18)

AND

Both conditions must be TRUE

WHERE Fname='John' AND Lname='Smith'

Slide 06 - Example Query (Slide 9)

At least one condition must be TRUE

WHERE cus_areacode='615' OR cus_areacode='713'

Slide 07 - Example (Major 2 Exam)

NOT

Negates a condition

WHERE NOT status = 'inactive'

Pattern Matching

Slide 06 - Pattern Matching (Slide 32)

Pattern matching with wildcards

WHERE Address LIKE '%Houston,TX%'

Slide 06 - Slide 32

Matches any number of characters (0 or more)

Examples:

'A%' - starts with A
'%son' - ends with son
'%data%' - contains data

Slide 06 - Slide 32

Underscore (single character wildcard)

Matches exactly one character

Example:

'CS___' - CS followed by exactly 3 characters

Slide 06 - Slide 32

Range & Set Operators

BETWEEN

Value within range (inclusive)

WHERE Salary BETWEEN 30000 AND 40000

Slide 06 - Between Operator (Slide 35)

Value matches any in a list

WHERE Pno IN (1, 2, 3)

Slide 06 - Example (Slide 17); Slide 07 - Nested Queries (Slide 12)

NOT IN

Value does not match any in list

Slide 07 - Nested Query Exercises (Slide 14)

NULL Handling

Slide 07 - NULL Values (Slides 4-7)

IS NULL

Tests if value is NULL

WHERE Super_ssn IS NULL

Slide 07 - NULL in SQL (Slide 7)

IS NOT NULL

Tests if value is not NULL

WHERE email IS NOT NULL

Slide 07 - NULL Handling

Important: Never use = NULL or <> NULL. Always use IS NULL or IS NOT NULL.

Subquery Operators

Slide 07 - Nested Queries (Slides 8-25)

EXISTS

TRUE if subquery returns at least one row

WHERE EXISTS (SELECT *
FROM DEPENDENT
WHERE Ssn = Essn)

Slide 07 - EXISTS (Slide 23)

NOT EXISTS

TRUE if subquery returns no rows

WHERE NOT EXISTS (SELECT *
FROM DEPENDENT
WHERE Ssn = Essn)

Slide 07 - NOT EXISTS (Slide 24)

= ANY / IN

Equals at least one value in set

= ANY is equivalent to IN

WHERE rating > ANY
(SELECT rating FROM Sailors
WHERE name='dustin')

Slide 07 - Comparison Operators (Slide 13)

> ALL

Greater than all values in set

WHERE Salary > ALL
(SELECT Salary FROM EMPLOYEE
WHERE Dno=5)

Slide 07 - ALL Operator (Slide 13)

UNIQUE

TRUE if no duplicate tuples in subquery result

Slide 07 - EXISTS and UNIQUE (Slide 23)

Aggregate Functions

Slide 07 - Aggregation (Slides 40-48)

COUNT(*)

Counts number of rows

SELECT COUNT(*) FROM EMPLOYEE

Slide 07 - Aggregation (Slide 44)

COUNT(DISTINCT A)

Counts unique values in column A

SELECT COUNT(DISTINCT department)
FROM Student

Slide 07 - Aggregation (Slide 43)

SUM(A)

Sum of values in column A

SELECT SUM(Salary) FROM EMPLOYEE

Slide 07 - Aggregation (Slide 45)

AVG(A)

Average of values in column A

SELECT AVG(gpa) FROM Student

Slide 07 - Aggregation (Slide 41)

MAX(A)

Maximum value in column A

SELECT MAX(gpa) FROM Student

Slide 07 - Aggregation (Slide 42)

MIN(A)

Minimum value in column A

SELECT MIN(gpa) FROM Student

Slide 07 - Aggregation (Slide 42)

Grouping & Filtering

GROUP BY

Groups rows by column values

Used with aggregate functions

SELECT Dno, COUNT(*)
FROM EMPLOYEE
GROUP BY Dno

Slide 07 - GROUP BY (Slide 49-51)

HAVING

Filters groups (used after GROUP BY)

WHERE filters rows, HAVING filters groups

GROUP BY Pnumber, Pname
HAVING COUNT(*) > 2

Slide 07 - HAVING (Slide 53)

ORDER BY

Sorts result by column(s)

ORDER BY D.Dname, E.Lname

Slide 06 - ORDER BY (Slide 36-37)

ASC / DESC

Ascending or descending order

ORDER BY age ASC
ORDER BY rating DESC

Slide 06 - ORDER BY (Slide 37)

Set Operations

Slide 06 - Set Operations (Slides 38-40)

UNION

Combines results, removes duplicates

SELECT name FROM students
UNION
SELECT name FROM professors

Slide 06 - UNION (Slide 38)

UNION ALL

Combines results, keeps duplicates

Slide 06 - UNION ALL (Slide 39)

INTERSECT

Returns only common rows

Slide 06 - INTERSECT (Slide 40)

EXCEPT / MINUS

Returns rows from first query not in second

Slide 06 - EXCEPT/MINUS (Slide 40)

Join Types

Slide 07 - Joins (Slides 26-38)

INNER JOIN

Returns only matching rows from both tables

FROM A INNER JOIN B
ON A.id = B.id

Slide 07 - INNER JOIN (Slide 33)

LEFT OUTER JOIN

All rows from left table + matching from right

FROM Course
LEFT OUTER JOIN Faculty
ON Course.Lecturer_id = Faculty.id

Slide 07 - LEFT JOIN (Slide 29)

RIGHT OUTER JOIN

All rows from right table + matching from left

Slide 07 - RIGHT JOIN (Slide 30)

FULL OUTER JOIN

All rows from both tables

Slide 07 - FULL JOIN (Slide 31)

NATURAL JOIN

Joins on all common columns automatically

FROM EMPLOYEE
NATURAL JOIN DEPARTMENT

Slide 07 - NATURAL JOIN (Slide 32)

CROSS JOIN

Cartesian product (all combinations)

Slide 07 - CROSS JOIN (Slide 36)

Other SQL Keywords

DISTINCT

Removes duplicate rows from result

SELECT DISTINCT sname
FROM sailors

Slide 06 - DISTINCT (Slide 27-28)

Renames column or table (alias)

SELECT AVG(gpa) AS avg_gpa

Slide 06 - Renaming (Slide 31)

Selects all columns

SELECT * FROM STUDENT

Slide 06 - Basic Queries (Slide 8)

Quick Reference Guide

Cardinality Recognition

How to identify relationship types:

1:1 - Both sides marked with 1 or (0,1) or (1,1)
1:N - One side has 1 or (0,1)/(1,1), other has N or (0,N)/(1,N)
M:N - Both sides have N/M or (0,N)/(1,N)

Slide 02 - Cardinality Constraints

Common Pitfalls

Avoid these mistakes:

Using = or <> with NULL (use IS NULL / IS NOT NULL)
Forgetting DISTINCT when removing duplicates
Using WHERE for aggregate conditions (use HAVING)
Not specifying JOIN conditions (creates Cartesian product)
Mixing aggregate and non-aggregate columns without GROUP BY

SQL Query Order of Execution

Conceptual execution order:

FROM - Identify tables
WHERE - Filter rows
GROUP BY - Group rows
HAVING - Filter groups
SELECT - Choose columns
ORDER BY - Sort result

Slide 07 - Summary (Slide 54)