Relational Databases - glossary of terms

advantages disadvantages, examples

client server def

way of organising computers or devices where:

• Client: A device (like your phone or computer) that asks for information or services.

• Server: A central computer that stores data or runs services and responds to client requests.

How it works:

• The client sends a request (e.g., to load a website).

• The server processes the request and sends back the needed information.

Example: When you use a web browser (client) to visit a website, it sends a request to the server where the site is hosted. The server then sends the webpage back to your browser.

Benefits: Everything is managed in one place (the server), so it’s easier to update and secure.

Drawbacks:

• If the server goes down, clients can’t access services. Also, too many requests can slow down the server.

• This model is used in many everyday applications, like websites, emails, and file-sharing systems

Open Source def

source code available for the general public to use and modify if they want to for free. Meant to be collaborative

Proprietary def

means that the software is owned by a commercial organisation and sells a licence to use it. e.g Microsoft office

Data Type

data type used in programming and database development. means type of data in a field e.g text and number

Relation

connections between tables, fields etc

Attribute

field or column

tuple

row or record

entity relationship

entities (table) are real world things like customers, books, authors, courses, students. entities often have relationships with each other aka entity relationships. e.g customers order goods, authors write books, students study courses.

generic

relationships can be modified in a non specific way

semantic

logic model of the relationships between specific items

In relational databases, semantic meaning refers to how the data in the database reflects real-world information in a clear and meaningful way.

Example:

1. 1. Tables and Columns:

   A table named Customers stores information about real people, like their Name and Email. The table’s name and its columns should clearly represent what the data is about

2. Relationships:

   If a CustomerID in a Orders table links to the Customers table, it shows the connection between a real customer and their actual orders

3. Rules:

   Data integrity rules (like making sure an order always belongs to an existing customer) keep the data accurate and meaningful.

In short, semantic meaning makes sure the database reflects the real world in a way that's easy to understand.

Primary key

• A primary key is a unique identifier for each record in a table. It ensures that no two rows have the same value in that column, providing a way to uniquely identify each record.

• Example: In a Students table, StudentID can be a primary key because each student has a unique ID.

Primary Key: Unique identifier for each record in a table.

foreign key

A foreign key is a column (or set of columns) in one table that links to the primary key of another table. It creates a relationship between the two tables.

Example: In an Orders table, a CustomerID foreign key can link to the StudentID primary key in the Students table to show which student placed the order.

Foreign Key: Links one table to another by referencing a primary key.

super key

• A super key is any combination of columns in a table that can uniquely identify a row. It may include extra columns that are not necessary for uniqueness.

• Example: In a Students table, a combination of StudentID and Name can be a super key because StudentID alone is enough to identify a student, but the combination also works.

Super Key: Any set of columns that can uniquely identify a record (may have extra columns).

candidate key

• A candidate key is a minimal super key. It is a column or a set of columns that can uniquely identify a record without any extra columns. A table can have multiple candidate keys, but one is chosen as the primary key.

• Example: In a Students table, both StudentID and Email could be candidate keys if each is unique. Only one of them would be chosen as the primary key.

A minimal super key; can uniquely identify a record with no extra columns.

one to one entity relationship

A one-to-one entity relationship in a database means that each record in one table is linked to exactly one record in another table.

Key Points:

• One record only: For each entry in the first table, there is only one matching entry in the second table.

• Unique pairs: This relationship ensures that no two records in either table are related to the same record in the other table.

Example:

• Employees and EmployeeIDs:

  • If you have an Employees table where each employee has a unique EmployeeID, and there is a separate EmployeeDetails table that stores additional information (like address or phone number) for each employee, then each EmployeeID in the Employees table matches exactly one record in the EmployeeDetails table.

Visualization:

• Employees Table:

  • | EmployeeID | Name |

  • |------------|---------|

  • | 1 | Alice |

  • | 2 | Bob |

• EmployeeDetails Table:

  • | EmployeeID | Address |

  • |------------|------------------|

  • | 1 | 123 Main St. |

  • | 2 | 456 Elm St. |

In this example, each employee has exactly one set of details, and each set of details corresponds to one employee.

one to many relationships

A one-to-many entity relationship in a database means that one record in one table can be associated with multiple records in another table.

Key Points:

• One record: For each entry in the first table, there can be many corresponding entries in the second table.

• Multiple associations: This relationship allows one item to be linked to several items.

Example:

• Customers and Orders:

  • If you have a Customers table where each customer can place multiple orders, then each customer (one) can be linked to many orders (many).

Visualization:

• Customers Table:

  • | CustomerID | Name |

  • |------------|--------|

  • | 1 | Alice |

  • | 2 | Bob |

• Orders Table:

  • | OrderID | CustomerID | Product |

  • |---------|------------|-----------|

  • | 101 | 1 | Laptop |

  • | 102 | 1 | Phone |

  • | 103 | 2 | Tablet |

In this example, Alice has placed two orders, while Bob has placed one order. Each customer can have many orders, but each order belongs to only one customer.

many to many relationship

A many-to-many relationship in a database means that multiple records in one table can be associated with multiple records in another table.

Key Points:

• Multiple records: Each record in the first table can relate to many records in the second table.

• Cross-relationships: Each record in the second table can also relate to many records in the first table.

Example:

• Students and Courses:

  • A student can enroll in multiple courses, and each course can have multiple students.

Visualization:

• Students Table:

  • | StudentID | Name |

  • |-----------|--------|

  • | 1 | Alice |

  • | 2 | Bob |

• Courses Table:

  • | CourseID | CourseName |

  • |----------|--------------|

  • | 101 | Math |

  • | 102 | Science |

• Enrollment Table (to link students and courses):

  • | StudentID | CourseID |

  • |-----------|----------|

  • | 1 | 101 |

  • | 1 | 102 |

  • | 2 | 101 |

In this example, Alice is enrolled in both Math and Science, and Bob is enrolled in Math as well. This illustrates the many-to-many relationship where students can take many courses, and courses can have many students.