Summary of Information Systems: Concepts and Management

Information Systems: Concepts & Management for Students

Summary Knowledge test FlashcardsPodcastMindmap

Introduction

Relational databases organise data into tables that relate to each other through keys and well-defined relationships. This material explains relationship types (1:1, 1:∞, ∞:∞), how to implement them with foreign keys and join tables, and how to model practical cases. You will learn how to design normalized schemas that avoid data redundancy and support querying and reporting requirements.

Definition: A relational table stores records (rows) with fields (columns); a key uniquely identifies a record.

Basic concepts

Tables, keys and foreign keys

Table: collection of records about a single entity (e.g., CUSTOMERS, ORDERS, PRODUCTS).
Primary key (PK): a field or set of fields that uniquely identify each record in a table.
Foreign key (FK): a field in one table that stores the primary key of another table to express a relationship.

Definition: A foreign key is a field in one table that references the primary key of another table to represent a relationship between records.

Relationship types overview

Use this table to compare the three main relationship types:

Relationship type	Meaning	How to implement	Typical use case
1:1	Each record in A matches at most one in B and vice versa	Put FK in the table created later (temporal criterion)	ORDER ⇄ SHIPMENT when one shipment per order
1:∞	Each record in A can match many in B; each B matches one A	Add A's PK as FK in B	CUSTOMER → ORDERS
∞:∞	Records in A can match many in B and vice versa	Create a join table that holds both PKs as a composite key	ORDERS ↔ PRODUCTS via ORDER_LINE

Implementing 1:1 relationships

Identify the two tables involved.
Decide which table will hold the foreign key (usually the one whose records are created later).
Example: If each ORDER has a single SHIPMENT, and shipments are created when orders are shipped, store OrderCode as FK in SHIPMENTS.

Definition: A 1:1 relationship associates each record in table A with at most one record in table B and vice versa.

Implementing 1:∞ relationships

Identify the parent (1) and the child (∞) table.
Add the parent's PK as a FK in the child table.
Example: CUSTOMER (PK = CustomerID), ORDERS (FK = CustomerID). Each customer may have many orders; each order belongs to one customer.

💡 Věděli jste?Did you know that placing the child table's foreign key in the parent table violates normalization when the parent can have many children because it would require repeating fields like OrderCode1, OrderCode2, OrderCode3?

Implementing ∞:∞ relationships with join tables

You cannot implement ∞:∞ by adding repeated FK fields in one table; that causes redundancy and denormalization.
Create a join table (also called relationship table or associative table) that stores the PKs from both related tables.
The join table’s primary key is typically the composite of the two foreign keys.
Example: ORDERS (OrderCode), PRODUCTS (ProductCode). Create ORDER_LINE with fields: ProductCode (FK), OrderCode (FK), Quantity, UnitPrice, etc. The composite key (ProductCode, OrderCode) uniquely identifies each line.

Definition: A join table is a table that implements a many-to-many (∞:∞) relationship by storing the primary keys from both related tables and any attributes specific to the relationship.

Attributes that belong in join tables

Some attributes depend on the combination of two entities rather than on one entity alone. Those attributes belong in the join table.
Example: Quantity of a product in a particular order depends on both ORDER and PRODUCT and therefore belongs in ORDER_LINE, not in ORDERS or PRODUCTS.

Example explained (ORDER, PRODUCT, ORDER_LINE)

ORDERS: OrderCode (PK), OrderDate, CustomerID (FK)
PRODUCTS: ProductCode (PK), Name, UnitPrice
ORDER_LINE: ProductCode (FK), OrderCode (FK), Quantity, LineTotal
ORDER_LINE.PK = (ProductCode, OrderCode)
Relationships:

Zaregistruj se pro celé shrnutí

FlashcardsKnowledge testSummaryPodcastMindmap

Start for free

Already have an account? Sign in

Relational Database Basics

Klíčové pojmy: A foreign key stores the primary key of another table to implement relationships, 1:1 relationships: place FK in the table created later based on temporality, 1:∞ relationships: parent PK becomes FK in the child table, ∞:∞ relationships require a join table with both PKs as FKs, Join table primary key is typically the composite of the two FKs, Attributes depending on both entities belong in the join table (e.g., Quantity), Normalize: avoid repeated groups like OrderCode1, OrderCode2 in one table, Document each relationship with its type and a short description, Use surrogate keys only when composite keys complicate operations, Follow stepwise modelling: entities → keys → relationships → FKs → join tables

## Introduction Relational databases organise data into tables that relate to each other through keys and well-defined relationships. This material explains relationship types (1:1, 1:∞, ∞:∞), how to implement them with foreign keys and join tables, and how to model practical cases. You will learn how to design normalized schemas that avoid data redundancy and support querying and reporting requirements. > Definition: A relational table stores records (rows) with fields (columns); a key uniquely identifies a record. ## Basic concepts ### Tables, keys and foreign keys - **Table**: collection of records about a single entity (e.g., CUSTOMERS, ORDERS, PRODUCTS). - **Primary key (PK)**: a field or set of fields that uniquely identify each record in a table. - **Foreign key (FK)**: a field in one table that stores the primary key of another table to express a relationship. > Definition: A foreign key is a field in one table that references the primary key of another table to represent a relationship between records. ### Relationship types overview Use this table to compare the three main relationship types: | Relationship type | Meaning | How to implement | Typical use case | |---|---:|---|---| | 1:1 | Each record in A matches at most one in B and vice versa | Put FK in the table created later (temporal criterion) | ORDER ⇄ SHIPMENT when one shipment per order | | 1:∞ | Each record in A can match many in B; each B matches one A | Add A's PK as FK in B | CUSTOMER → ORDERS | | ∞:∞ | Records in A can match many in B and vice versa | Create a join table that holds both PKs as a composite key | ORDERS ↔ PRODUCTS via ORDER_LINE | ### Implementing 1:1 relationships - Identify the two tables involved. - Decide which table will hold the foreign key (usually the one whose records are created later). - Example: If each ORDER has a single SHIPMENT, and shipments are created when orders are shipped, store OrderCode as FK in SHIPMENTS. > Definition: A 1:1 relationship associates each record in table A with at most one record in table B and vice versa. ### Implementing 1:∞ relationships - Identify the parent (1) and the child (∞) table. - Add the parent's PK as a FK in the child table. - Example: CUSTOMER (PK = CustomerID), ORDERS (FK = CustomerID). Each customer may have many orders; each order belongs to one customer. > Did you know that placing the child table's foreign key in the parent table violates normalization when the parent can have many children because it would require repeating fields like OrderCode1, OrderCode2, OrderCode3? ### Implementing ∞:∞ relationships with join tables - You cannot implement ∞:∞ by adding repeated FK fields in one table; that causes redundancy and denormalization. - Create a **join table** (also called relationship table or associative table) that stores the PKs from both related tables. - The join table’s primary key is typically the composite of the two foreign keys. - Example: ORDERS (OrderCode), PRODUCTS (ProductCode). Create ORDER_LINE with fields: ProductCode (FK), OrderCode (FK), Quantity, UnitPrice, etc. The composite key (ProductCode, OrderCode) uniquely identifies each line. > Definition: A join table is a table that implements a many-to-many (∞:∞) relationship by storing the primary keys from both related tables and any attributes specific to the relationship. ## Attributes that belong in join tables - Some attributes depend on the combination of two entities rather than on one entity alone. Those attributes belong in the join table. - Example: Quantity of a product in a particular order depends on both ORDER and PRODUCT and therefore belongs in ORDER_LINE, not in ORDERS or PRODUCTS. ### Example explained (ORDER, PRODUCT, ORDER_LINE) - ORDERS: OrderCode (PK), OrderDate, CustomerID (FK) - PRODUCTS: ProductCode (PK), Name, UnitPrice - ORDER_LINE: ProductCode (FK), OrderCode (FK), Quantity, LineTotal - ORDER_LINE.PK = (ProductCode, OrderCode) - Relationships: