Key Types
Primary Key
Definition: A column or combination of columns that uniquely identifies each row in a
table. It cannot contain NULL values.
Example: In a Students table, StudentID (e.g., 101, 102) is the primary key. No two
students can share the same ID, and every student must have one.
Unique Key
Definition: A column or set of columns that ensures all values in that column are
distinct. Unlike a primary key, it can accept one NULL value.
Example: In an Employees table, the EmailAddress column is a unique key. Every
employee must have a different email, but a new employee might not have an email
assigned yet (NULL).
Foreign Key
Definition: A column in one table that links to the primary key of another table to create
a relationship between them.
Example: In an Orders table, a CustomerID column is a foreign key because it links
back to the CustomerID primary key in the Customers table.
Candidate Key
Definition: Any column or minimal set of columns that qualifies to be a primary key
because it uniquely identifies a record.
Example: In a Citizens table, both SocialSecurityNumber and PassportNumber are
candidate keys. Either one could uniquely identify a person.
Super Key
Definition: A set of one or more columns that uniquely identifies a record, which may
include extra, unnecessary columns.
Example: In a Students table, combining StudentID + StudentName + PhoneNumber is
a super key. The ID alone is enough, making the name and phone number extra.
Composite Key
Definition: A primary key that consists of two or more columns combined together to
uniquely identify a row.
Example: In a CourseEnrollments table, neither StudentID nor CourseID is unique on
its own. Combined together (StudentID + CourseID), they form a composite key to
show which student is in which class.
Alternate Key
Definition: Any candidate key that was not chosen by the database designer to be the
primary key.
Example: If SocialSecurityNumber is chosen as the Primary Key for an employee
table, then PassportNumber becomes the alternate key.
Surrogate Key
Definition: A system-generated, artificial unique identifier that has no real-world
meaning.
Example: An auto-incrementing integer like ID = 1, ID = 2, ID = 3 automatically
added to a Products table by the database software.
Operations, Threats, & Maintenance
Joins
Definition: A keyword used to combine and query data from two or more tables based
on a related column between them.
Example: Joining an Employees table and a Departments table on DepartmentID to list
every employee next to their department name.
SQL Injection
Definition: A cyberattack where malicious code is inserted into input fields to trick the
database into running unauthorized commands.
Example: A hacker typing ' OR '1'='1 into a website login password box to bypass
security and log in without a valid password.
Database Backups
Definition: Creating a copy of operational data to store separately so it can be restored
if the original data is lost or corrupted.
Example: Saving a full copy of a bank's transaction database to a secure cloud server
every night at 2:00 AM.
Database Design & Normalization
Normalization
Definition: The process of organizing data into multiple tables to eliminate duplicate
data and ensure data dependencies make sense.
Example: Instead of repeating the manager's phone number next to every employee's
row, moving manager details to a separate Managers table.
De-normalization
Definition: The intentional process of adding redundant data back into a database to
speed up complex data retrieval and reading times.
Example: Storing the TotalOrderAmount directly inside the Customers table so the
system does not have to calculate thousands of individual sales every time a report
loads.
1st Normal Form (1NF)
Definition: A table where each column contains only atomic (indivisible) single values,
and there are no repeating groups of columns.
Example: Instead of storing Hobbies as "Soccer, Chess, Reading" in one cell,
creating three separate rows for that user so each cell holds only one hobby.
2nd Normal Form (2NF)
Definition: Must be in 1NF, and all non-key columns must fully depend on the entire
primary key (no partial dependencies on composite keys).
Example: In a StoreInventory table with a composite key (StoreID + ItemID), moving
the StoreAddress to a separate Stores table because the address depends only on the
StoreID, not the ItemID.
3rd Normal Form (3NF)
Definition: Must be in 2NF, and no non-key column can depend on another non-key
column (no transitive dependencies).
Example: In an Employees table, removing the columns DepartmentName and
DepartmentLocation because they depend on DepartmentID, not directly on the
employee's ID.
4th Normal Form (4NF)
Definition: Must be in 3NF, and a table cannot have multi-valued dependencies where
independent multi-valued facts about an entity are mixed.
Example: If a Teacher speaks multiple languages and teaches multiple subjects,
splitting this into two tables (TeacherLanguages and TeacherSubjects) so languages
and subjects do not randomly pair up.
Performance & Interaction
Index
Definition: A performance optimization feature that acts like a pointer map to look up
specific rows instantly without scanning the whole table.
Example: The index at the back of a textbook. Instead of reading all 500 pages to find
"Normalization", you look up the word in the index and jump straight to page 320.
Query
Definition: A precise request or command written in code (like SQL) to retrieve, insert,
or manipulate data in a database.
Example: Running the command SELECT * FROM Products WHERE Price < 10; to get
a list of all items that cost less than ten dollars.