The relational model, tables, and keys

One of the most common ways to store data is the relational model.

In this model, data is kept mainly as tables.

A university that wants to store student data can have a Students table:

The table holds three students. Each student is a row; their attributes sit in columns.

The relational model organizes data as tables that can relate to one another.

A table is a set of related data about one topic.

For example:

• Students for students
• Teachers for teachers
• Courses for courses
• Customers for customers
• Products for products
• Orders for orders

Each table should usually be about one main concept. Putting students, products, orders, and teachers in one table without a reason is a poor design.

Each table row is one record.

This row is one student.

Row = one instance or one record

A table of 1000 students usually has 1000 rows.

Columns describe attributes of the data.

In the students table:

• student_id — student number
• name — student name
• major — field of study

The name column holds every student’s name.

Column = one specific attribute of the records

This difference matters. Take this table:

In this table:

• Each row is one product.
• Each column is one product attribute.

The second row 2 | Mouse | 800000 is one record. The price column holds every product’s price.

An entity is something we want to store information about.

In a university:

• Student
• Teacher
• Course
• Department

In an online shop:

• Customer
• Product
• Order
• Payment

In a library:

• Book
• Member
• Loan

If we store student data, Student is an entity.

Each entity has properties. Those properties are attributes.

A Student entity may have:

• student_id
• first_name
• last_name
• birth_date
• major
• phone

Entity = the thing we store data about
Attribute = a property of that entity

In a shop, one entity is Product. Its attributes may be:

• product_id
• title
• price
• stock

Product is the entity. product_id, title, price, and stock are its attributes.

Suppose the students table looks like this:

Two students are named Ali Ahmadi. “Update Ali Ahmadi” is ambiguous.

Each record needs a unique identifier. That is where the primary key comes in.

A primary key is a column that uniquely identifies each record.

Even with the same name, student_id = 101 is not student_id = 103. We can point at exactly one student.

A primary key must distinguish records from each other.

Values must be unique

Two records must not share the same primary key. This is wrong:

A primary key must not be NULL

A record needs a definite identifier. This is not a good primary key:

Technically it can work in some cases, but it is usually a poor choice.

Names can repeat. Prefer an identifier such as student_id, customer_id, or product_id.

• Students: student_id
• Products: product_id
• Customers: customer_id
• Orders: order_id
• Employees: employee_id

Names like id or <entity>_id are very common.

Why not put everything in one big table?

A lot of data repeats: Ali’s name, the SQL course, and teacher Ahmadi appear more than once.

Better to split:

Students

Courses

Then we need to connect the tables. That is where relationships and foreign keys come in.

A foreign key is a column that links two tables.

Students

Orders

In Orders, student_id says which student owns each order. Order 1001 has student_id = 1; Students tells us student 1 is Ali. So order 1001 belongs to Ali.

This distinction is essential.

A primary key uniquely identifies rows in the same table. Example: Students.student_id.

A foreign key links to another table. Example: Orders.student_id references Students.student_id.

Customers

Orders

In Customers, customer_id is the primary key. In Orders, order_id is the primary key, and customer_id is a foreign key.

A relationship is a link between entities or tables.

• Customer → Order: a customer places orders.
• Student → Course: a student takes courses.
• Teacher → Course: a teacher teaches courses.

Three important types:

• One-to-one
• One-to-many
• Many-to-many

One-to-one means a row in A links to at most one row in B.

Example: Person ↔ Passport — each person has one passport, and each passport belongs to one person.

A simple view:

This type is very common: one row in A can link to many rows in B.

A customer can have many orders, but each order belongs to one customer.

Ali can have orders 101, 102, and 103. Customer and Order are one-to-many.

Each department can have many students — Computer: Ali, Reza, Sara, Maryam — but in this model each student belongs to only one department.

Sometimes many rows in the first table link to many rows in the second.

A student can take many courses, and a course can have many students.

So Student ↔ Course is many-to-many.

Ali takes SQL, Java, and Database. Sara takes SQL and Python. Reza takes Java and Database.

Storing that directly inside Students or Courses does not give a clean structure.

The usual solution is a junction table.

For many-to-many we usually add a third table.

Students

Courses

Enrollments

This says student 1 takes course 10, student 1 takes course 20, and student 2 takes course 10.

Besides linking Student and Course, we may also store the grade.

Enrollments now stores the relationship and data about that relationship.

First entities can be:

Customer: customer_id, name, phone

Product: product_id, title, price

Order: order_id, customer_id, order_date

Customer and Order are one-to-many, because one customer can place many orders.

An order can contain many products, and a product can appear in many orders. Order ↔ Product is many-to-many, so we need a junction table.

OrderItems

This table says which products are in each order, and in what quantity.

If we store data without a decent design we may get:

• lots of repetition
• inconsistent data
• hard updates
• deletes that wipe related facts
• painful queries
• a system that is hard to maintain

Database structure is a core part of the work.

We can briefly see how these ideas show up in SQL. Creating Students:

You do not need to memorize every detail yet. Notice PRIMARY KEY: student_id is the table’s primary key.

Now create Orders:

FOREIGN KEY (student_id) marks the column as a foreign key. REFERENCES Students(student_id) says it points at Students.

Suppose Orders has:

But Customers only has:

Customer 50 does not exist. A correctly defined foreign key lets the DBMS reject that invalid link. That protects data integrity.

Last session covered integrity and the DBMS. Here is a concrete case.

A foreign key stops us from storing a row that points at a missing record — for example an order for a customer who does not exist.

The sessions start to connect.

In ER modeling, sometimes an entity relates to itself. That is a recursive relationship.

Example: the Employee entity.

Each employee may have a manager, and the manager is also an employee.

So we have:

Employee → manages → Employee

For example:

• Ali manages Reza.
• Reza manages Maryam.

There is only one entity, but the link runs between instances of the same entity.

In an Employees table we usually add a column such as manager_id that points to employee_id in the same table.

To remember: recursive relationship = one entity relates to itself.

Cardinality says how many instances on one side of a relationship can link to instances on the other side — minimum and maximum.

Three common forms:

• 1:1 — one-to-one
• 1:N — one-to-many
• M:N — many-to-many

Example:

Department 1 ---- N Employee

One department can have many employees, but in this model each employee belongs to one department.

Cardinality can be more precise than the relationship name alone, for example:

• 0..1 — zero or one
• 1..N — at least one, many allowed
• 0..N — zero or many

To remember: cardinality = how many instances can participate on each side?

In ER modeling, existence usually means existence dependency.

One entity can exist only if another entity exists.

Example:

Order → OrderItem

An order item without an order makes no sense. If there is no order, the item cannot stand on its own.

So OrderItem has an existence dependency on Order.

Another example:

Employee → Dependent

A dependent person in the system only makes sense when a specific employee exists.

This idea is closely tied to a weak entity, because a weak entity usually cannot exist without its owner entity.

To remember:

Existence dependency = can this entity exist without the other one?

• A student can exist without taking a course → no existence dependency.
• An order item cannot exist without an order → existence dependency.

Strong entity

An entity that can be identified on its own and usually has its own primary key.

Example: Student

student_id alone identifies each student. Student is a strong entity.

Weak entity

An entity that cannot be fully identified on its own and depends on another entity.

Example: OrderItem

item_no = 1 is not enough, because it can repeat in different orders. Full identity needs order_id + item_no, for example 1001 + 1.

OrderItem depends on Order and can be modeled as a weak entity.

Strong = it introduces itself.
Weak = it needs another entity to introduce itself.

Weak does not mean unimportant — it only means the model needs an owner for identity or existence.

• Table — related records
• Row — one record
• Column — one attribute of the records
• Entity — something we store data about
• Attribute — a property of an entity
• Primary key — uniquely identifies a row
• Foreign key — links two tables

Three important relationship types: one-to-one, one-to-many, and many-to-many. Many-to-many usually needs a junction table.

In ER we also covered:

• Recursive relationship — one entity relates to itself (Employee → manages → Employee).
• Cardinality — how many instances can link on each side.
• Existence dependency — can one entity exist without another?
• Strong entity — independent, identified by its own primary key.
• Weak entity — depends on an owner entity for identity or existence (OrderItem).