1. Table
A table is a collection of related data organized into rows and columns. It’s like a spreadsheet where each row represents a data entry (record), and each column represents a field (attribute) of that record.
Example: A table of Employees might have columns like EmployeeID, Name, Age, Department, etc.
*2.Row *(also called a Record or Tuple)
A row represents a single, unique record in a table. Each row contains values for each of the table's columns.
Example: A row in an Employees table might contain the values 1, John Doe, 28, Marketing in the respective columns for EmployeeID, Name, Age, and Department.
3. Column (also called a Field or Attribute)
A column is a vertical set of data values in a table. Each column is dedicated to a specific type of data, such as EmployeeID, Name, Age, Salary, etc.
Example: The Name column in a Students table might contain values like Alice, Bob, Charlie.
4. Primary Key
A primary key is a unique identifier for each row in a table. It ensures that no two rows have the same value in this column. The primary key cannot contain NULL values.
Example: In a Users table, the UserID might be the primary key, ensuring that each user has a unique ID.
Key Characteristics:
Uniqueness: No two rows can have the same primary key value.
Not NULL: Every row must have a value in the primary key column.
5. Foreign Key
A foreign key is a column (or a set of columns) in one table that links to the primary key of another table. It establishes a relationship between the two tables.
Example: In an Orders table, the CustomerID column might be a foreign key that refers to the CustomerID primary key in a Customers table. This shows which customer placed each order.
Key Characteristics:
It creates a relationship between two tables.
A foreign key in the child table corresponds to a primary key in the parent table.
A foreign key can have NULL values, unlike a primary key.
6. Index
An index is a database object that improves the speed of data retrieval operations on a table. It’s like a table of contents for a book—it allows quick access to rows based on the values in one or more columns.
Example: You might create an index on the Email column of a Users table so that queries that search by email are faster.
7. NULL
NULL is a special marker used in databases to represent missing or undefined data. It’s different from an empty string or zero.
Example: If an employee doesn’t have a phone number, the PhoneNumber column for that employee’s row might contain NULL.
8. Relationship
A relationship is a connection between two tables. Relationships are established using foreign keys and can be:
One-to-One: One record in the first table is related to one record in the second table.
One-to-Many: One record in the first table can be related to many records in the second table.
Many-to-Many: Many records in the first table can be related to many records in the second table. This is typically implemented using a junction table.
Example: In an Employee table and a Departments table, an employee can belong to one department, but a department can have many employees—this is a one-to-many relationship.
9. Schema
A schema is the structure that defines the organization of data in a database. It includes tables, relationships, views, indexes, and other database objects. The schema defines how data is organized and how relationships between data are handled.
Example: A schema for a school database might include tables like Students, Courses, and Enrollments with relationships between them.
10. View
A view is a virtual table that provides a way to look at data from one or more tables. It’s a stored query that can be treated like a table. Views do not store data themselves but display data from the underlying tables.
Example: A view could display a list of employees with their department names by joining the Employees table and the Departments table.
11. Constraint
A constraint is a rule applied to a column or table to ensure data integrity. Common constraints include:
NOT NULL: Ensures a column cannot have NULL values.
UNIQUE: Ensures all values in a column are unique.
CHECK: Ensures that values in a column meet a specified condition.
DEFAULT: Sets a default value for a column when no value is provided.
FOREIGN KEY: Ensures that a column’s value matches a primary key in another table.
Example: A constraint could ensure that the Age column only allows values greater than 18.
12. Join
A join is an SQL operation used to combine rows from two or more tables based on a related column. Common types of joins include:
INNER JOIN: Returns records that have matching values in both tables.
LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table and the matched records from the right table.
RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table and the matched records from the left table.
FULL JOIN (or FULL OUTER JOIN): Returns records when there is a match in either table.
Example: If you have a Customers table and an Orders table, you can use a join to get all orders for a specific customer.
13. Transaction
A transaction is a unit of work that is performed in a database. Transactions ensure that database operations are completed in a reliable and consistent way, following the ACID properties (Atomicity, Consistency, Isolation, Durability).
Example: In a banking system, transferring money from one account to another involves multiple steps, and a transaction ensures that all steps either complete successfully or none at all (i.e., rollback if there's an error).
Top comments (0)