This handy dictionary is your companion as you navigate the exciting world of databases. Feeling lost in a maze of tables, queries, and schemas? Don’t worry, we’ve got you covered. This cheat sheet deciphers all the essential database terms you’ll encounter on your journey. (And if you are still stuck, and need that little extra help, please do not hesitate to reach out to me!)
Attribute (Column): A specific characteristic or property of an entity, represented by a column in a table. It holds a particular data type (e.g., text, number, date).
Example: Within the Customers table, CustomerID, Name, Address, and Email are all attributes of the Customer entity.
Constraints. Constraints are rules that enforce data integrity within a database. They restrict the values that can be stored in attributes or tables, preventing invalid or inconsistent data from entering the system.
Example:
- Primary Key Constraint: Ensures each record in a table has a unique identifier (e.g., the
CustomerIDin theCustomerstable cannot have duplicates). - Foreign Key Constraint: Enforces a relationship between tables by requiring a foreign key value (e.g.,
CustomerIDin theOrderstable) to match a valid primary key value in the referenced table (Customers). - NOT NULL Constraint: Prevents null values from being inserted into a specific attribute (e.g., ensuring
Emailaddresses are always provided in theCustomerstable).
Data Type, A data type defines the format and category of data that can be stored in an attribute (column) of a table. It ensures data consistency and integrity by enforcing specific value ranges or formats.
Example: Common data types include:
Integer: Stores whole numbers (e.g.,CustomerID).Varchar: Stores variable-length character strings (e.g.,CustomerName).Date: Stores date values (e.g.,OrderDate).Decimal: Stores numbers with decimal places (e.g.,UnitPrice).
Entity: A real-world object or concept that you represent in a database. An entity is typically mapped to a relation (table).
Example: The Customer entity in the real world corresponds to the Customers table in the database.
Foreign Key: A column (or set of columns) in a table that references the primary key of another table. It establishes relationships between tables, allowing data linking and retrieval across tables.
Example: An Orders table might have a CustomerID foreign key that references the primary key (CustomerID) in the Customers table. This links customer information to their respective orders.
Index: An index is a special data structure that acts like an organized filing system for a table’s data. It allows for faster retrieval of specific records by enabling the database engine to quickly locate relevant rows based on indexed column values.
Example: Consider a large Customers table with thousands of entries. An index on the Name column would significantly speed up searches for customers by name compared to scanning the entire table.
Primary Key: A unique identifier for each record in a table. It enforces data integrity by ensuring no duplicate records exist. A table can only have one primary key.
Example: In the Customers table, CustomerID can be set as the primary key, guaranteeing a distinct identifier for each customer.
Normalization. Normalization is a process of organizing data in a database to minimize redundancy and improve data integrity. By following normalization rules, you can prevent data inconsistencies, simplify data manipulation, and enhance database efficiency. Different levels of normalization exist, with increasing complexity.
Example: Consider a table named Customers that stores customer information along with their order details. This can lead to redundancy if a customer places multiple orders. Normalization would involve separating the order details into a separate table named Orders, with a foreign key in the Orders table referencing the CustomerID in the Customers table. This avoids duplicating customer information for each order.
Record. A record (also known as a row) represents a single instance of data within a table. It contains specific values for all the attributes (columns) defined for that table.
Example: In the Customers table, each row would represent a single customer. It might contain values for attributes like CustomerID (unique identifier), Name, Address, Email, etc. There could be multiple records (rows) in the table, each representing a different customer with their corresponding information.
Relation (Table): A structured collection of data records (rows) that share a specific purpose. Each record consists of fields containing information (columns).
Example: A Customers table might store details about your clients, with each row representing a customer and columns for attributes like CustomerID, Name, Address, and Email.
Query. A query is a statement used to interact with a database. It allows you to retrieve, insert, update, or delete data. Queries are typically written in a standardized language like SQL (Structured Query Language).
Example: Here’s a simple SQL query that retrieves all customer names from the Customers table:
SQL
SELECT Name FROM Customers;
This query uses the SELECT clause to specify the data to be retrieved (Name) and the FROM clause to indicate the table (Customers) from which data will be fetched.
Schema. The schema defines the overall structure of a database. It outlines the tables within the database, their attributes (columns), data types for each attribute, and the relationships between tables (often established through primary and foreign keys).
Example: The schema for a library database might include tables like Books (with attributes like BookID, Title, Author), Authors (with attributes like AuthorID, Name), and Borrowers (with attributes like BorrowerID, Name). Relationships might exist between Books and Authors (through an AuthorID foreign key in the Books table referencing the primary key in the Authors table) and between Books and Borrowers (for tracking book borrowing).
Supertype and Subtype: A hierarchical relationship between entities, where a supertype represents a more general category and subtypes inherit its attributes while adding their own specific ones.
Example: A Product table could be a supertype, with subtypes like Electronics and Clothing. The Product table might contain attributes like ProductID, Name, and Price. The Electronics subtype could inherit these attributes and add specific ones like Type and Manufacturer.
View. A view is a virtual table that provides a customized way to access data from one or more underlying tables. It doesn’t store data itself, but rather presents a specific subset or transformation of data based on a pre-defined query.
Example: You might create a view named ActiveCustomers that shows only customers with a recent order date from the Customers and Orders tables, simplifying queries for user groups who only need information about active customers.
TIM COATES 
Leave a comment