Summary of Information Systems: Concepts and Digital Strategy
Information Systems: Concepts & Digital Strategy Summary
Introduction
Relational databases allow us to store and organize information in interrelated tables. In this material, you will learn how to model relationships between tables (1:1, 1:∞, ∞:∞), when to use join tables, and how to design keys and fields to maintain a normalized and efficient structure.
Definition: A relational database organizes data into tables that are related through primary keys and foreign keys.
Core Concepts Explained
Tables, Records, and Fields
- Table: a collection of records with the same structure.
- Record (row): a specific instance with values for each field.
- Field (column): an attribute that describes each record.
- Primary Key (PK): a unique identifier for each record in a table.
- Foreign Key (FK): a field in one table that references the PK of another table to establish a relationship.
Definition: A Primary Key is the field or set of fields that uniquely identifies a row in a table.
Types of Relationships Between Tables
We'll use simple examples (CUSTOMERS, ORDERS, PRODUCTS, ORDER_LINE) to illustrate the types of relationships.
- 1:∞ (One-to-Many) Relationship
- Description: One record in table A can relate to multiple records in table B, but each record in B relates to only one record in A.
- Implementation: Add the PK of A as an FK in B.
- Example: A customer (CUSTOMER) can have many orders (ORDERS). The ORDERS table includes the CustomerID field as an FK.
Definition: 1:∞ Relationship: Each record in the "one" table can have many corresponding records in the "many" table.
- 1:1 (One-to-One) Relationship
- Description: Each record in A relates to a single record in B, and vice versa.
- Implementation: The FK can be placed in either table; it's common practice to place it in the table whose record is generated later (based on a temporality criterion).
- Example: If each order generates a single shipment (SHIPMENT) and vice versa, we can store OrderID as an FK in SHIPMENTS.
- ∞:∞ (Many-to-Many) Relationship
- Description: One record in A can relate to multiple records in B, and one record in B can relate to multiple records in A.
- Problem: This cannot be resolved by adding the PK of A to B or vice versa without repeating fields and violating normalization rules.
- Solution: Create an intermediate table called a junction table or join table.
Definition: ∞:∞ Relationship: When multiple records from one table can be associated with multiple records from another table simultaneously.
Junction Tables (JOIN tables)
- Purpose: To materialize ∞:∞ relationships by creating a table that contains the keys of both related tables.
- Junction table key: This is usually the composite combination of the two FKs (composite PK).
- Additional fields: Can include attributes that depend on the combination of both entities (relationship attributes).
Practical Example: ORDERS, PRODUCTS, and ORDER_LINE
- ORDERS (OrderCode PK, other fields)
- PRODUCTS (ProductCode PK, other fields)
- ORDER_LINE (OrderCode FK, ProductCode FK, Quantity, other fields)
The ORDER_LINE table stores one row for each product included in each order. Its PK can be the combination $(\text{OrderCode},\ \text{ProductCode})$.
Definition: Junction Table: A table that contains the primary keys of the two tables involved in an ∞:∞ relationship and may include fields specific to that relationship.
Relationship-Dependent Attributes
Some fields depend on the combination of two records, not just one. For example, the quantity of a product ordered in a specific order belongs neither to ORDERS nor to PRODUCTS; it belongs to ORDER_LINE. These fields should be stored in the junction table.
Design and Normalization: Best Practices
- Avoid duplicate fields that represent the same concept (e.g., OrderCode1, OrderCode2) — this violates normalization.
- Each relationship must be represented by FKs: the number of FKs in the model should match the number of relationships.
- For 1:1 r
Sign up for the full summaryAlready have an account? Sign in
Relational Database Modeling
Klíčové pojmy: 1:Many Relationship: FK in the 'many' table, 1:1 Relationship: FK in the table created later based on timing, Many:Many Relationship: Resolve with a join table, Join table: Composite PK made of the two FKs, Relationship attributes should go in the join table, Avoid repeating fields (normalize your data), Validate design with referential integrity and attribute dependency, Use appropriate keys: PK for identity and FK for relationships, In a 1:Many relationship, each order belongs to only one customer, In a 1:1 relationship, choose the FK based on when the record is generated