|
Term
|
Diagram
|
Definition
|
Examples
|
| Database |
|
A database is a collection of tables with related data. |
my_test_db |
| Table |
|
A table is a matrix with data. A table in a database looks like a simple spreadsheet. |
Users, Updates |
| Row |
|
A row (also known as a record, tuple, or entry) is a group of related data. |
user_id, username, password, ip_address |
| Column |
|
A column contains data of one and the same kind. |
last_name |
| Key |
|
A key consists of one or more columns. A key is unique; it cannot occur twice in one table. |
user_id |
| Compound Key |
|
A compound key (composite key) is a key that consists of multiple columns. |
update_id, user_id |
| Foreign Key |
|
A foreign key is the link between two tables. |
Updates contains User_user_id column (fk_Updates_Users) |
| Referential Integrity |
|
Referential Integrity makes sure that a foreign key value always points to an existing row. A "dead" link as shown in the picture can not happen anymore. |
user_id in TableA MUST reference a valid user_id in TableB |
| Identifying Relationship |
|
Identifying relationships appear in the Primary Key (PK). |
Users_user_id is PK in Updates |
| Non-Identifying Relationship |
|
Non-identifying relationships do not appear in the Primary Key (PK). |
Updates_update_id is NOT PK in Users; may be optional or null |
| Index |
|
Indexes are used to find rows with specific column values quickly. |
|
| Join |
|
With a join you combine columns from different tables to one big temporary result table. |
Street number and city in TableA joined with state or province and country in TableB results in an address. |
| One to Many Relationships |
|
One-to-many (1:*) relationships occur when each record in TableA may have many linked records in TableB but each record in TableB may have only one corresponding record in TableA. This is the most common pattern. |
One book contains many chapters. |
| Many to One Relationships |
|
Many-to-one (*:1) relationships are the same as one-to-many, but from a different viewpoint. |
Many chapters comprise one book. |
| Many to Many Relationships |
|
Many-to-many (*:*) relationships represent usage or reference between instances. |
Papers have many readers; readers have many papers. |
| One to One Relationships |
|
One-to-one (1:?) relationships occur when there is exactly one record in the first table that corresponds to exactly one record in the related table. Subordinate or secondary extents are often optional or null. |
People (the primary extent) might have a separate partition called PersonalInformation (the secondary extent). |
| Self-Referencing Relationships |
|
A self-referencing relationship describes a relationship between two instances in the same extent. |
In a mentoring relationship between two people, one role is the mentor, the other the mentee. |
| High Frequency Function |
|
A high frequency function is a function that is used very often, hundreds or even thousands times per day. |
get_row_count() |
| Redundancy |
|
Storing data twice, redundantly to make the system faster. |
|