Anastasia Sulukhia

DB1
week 3 notes

Here are some notes on the topics that generated the most questions and confusion among
students during seminars.

Please note that this is not official material—just something that might be helpful.

Although I’ve fact-checked these notes thoroughly, there may still be some errors. If you
notice any, please let me know.

--------------------------------------------------------------------------------------------------------------------------

Implementing database schema into a real database

Implementing a database schema can be done in two ways:

1. Using a GUI – For example, pgAdmin provides a user-friendly interface.

2. Using SQL Code – This method is often preferred because it’s faster, widely used,
and more straightforward. For this reason, I'll use SQL commands in the labs and in
these notes.

Where to Start When Creating Tables – Does It Matter?

Yes, the order matters in terms of efficiency. Start by creating tables that don’t have foreign
key dependencies on others. For example, create tables that don't reference the primary
keys of other tables first. If you create tables that reference other tables' primary keys first,
you’ll encounter errors because those referenced tables don’t yet exist. Then, you’ll end up
needing to skip foreign keys initially and then go back to add foreign key constraints after
creating the necessary tables, which is unnecessary extra work.

Therefore, begin with tables that don’t reference other tables, then proceed to tables that
depend on them.

Naming tables/attribiutes with capitals not allowed

Always name your tables, attributes with lowercase. if you name them with capitals through
SQL code postgre will still store it as lowercase name, but this does not hold when working
with GUI, so to avoid confusion remember to only use lowercase when naming.

Why?
- PostgreSQL treats unquoted identifiers (table names, column names, etc.)
as lowercase by default. If you define a column like ColumnName, it will be stored
as columnname. However, if you enclose the name in double quotes
(e.g., "ColumnName"), PostgreSQL will preserve the case but then require you to
 always use double quotes and match the case exactly when querying that column,
which can be cumbersome.

When do we put unique constraint on attribute

We put unique constraint on an attribute when we want to have unique values in that
column.
For example

having relationship trainer heads area with cardinality 1:1, merged into area.

In table course, we should not have duplicate entries of a trainer since one trainer can only
be associated with only one course. So in the trainer column all values should be unique.
Hence, we should put unique constraint on trainer foreign key in course table.

Actions on deletion/update on foreign keys

This concept led to some confusion in labs. If this explanation isn’t enough, I recommend
additional research and examples to master it.

When a foreign key in table A references a primary key in table B, consider what should
happen to the foreign key in table A if the primary key in table B is updated or deleted.

PostgreSQL offers five options for handling this:

By default FK actions are set as:

• On Update: No action
• On Delete: No action

CASCADE:
• ON DELETE CASCADE: When the referenced row in the parent table is deleted, the
corresponding rows in the child table are automatically deleted as well.
• ON UPDATE CASCADE: When the primary key in the parent table is updated, the
foreign key in the child table is automatically updated to match.
SET NULL:
• ON DELETE SET NULL: When the referenced row in the parent table is deleted, the
foreign key value in the child table is set to NULL.
• ON UPDATE SET NULL: When the primary key in the parent table is updated, the
foreign key in the child table is set to NULL.
SET DEFAULT:
• ON DELETE SET DEFAULT: When the referenced row in the parent table is deleted,
the foreign key in the child table is set to its default value (if one is defined).
• ON UPDATE SET DEFAULT: When the primary key in the parent table is updated, the
foreign key in the child table is set to its default value (if one is defined).
RESTRICT/NO ACTION:
• ON DELETE RESTRICT/ NO ACTION: Prevents the deletion of the referenced row in
the parent table if there are any matching rows in the child table. This action blocks
the delete operation and must be handled manually.

• ON UPDATE RESTRICT/ NO ACTION: Prevents updates to the referenced primary key

if there are any matching rows in the child table. This is also a blocking action.

RESTRICT vs NO ACTION

The actions "RESTRICT" and "NO ACTION" are practically the same, with a slight difference in
when they are executed within a transaction, which is not the topic of our course.
Therefore, in this course, we will treat them as equivalent.

"Students do not need to understand the minor difference between the two foreign key
constraints because they likely do not know what a transaction is. We will address that in
DB2."

If you encounter a requirement that specifies restricting deletion or updating in the parent
table, you will have two options: leave it as the default (‘NO ACTION’) or use RESTRICT.
Both will be considered correct solutions on the midterm.

Personally, I would prefer to use RESTRICT to emphasize that I understood the requirement
and didn’t skip it out of confusion.

CHECK Constraints

A CHECK constraint is a rule that limits the values that can be placed in a column. It ensures
that the data entered into a table meets specific criteria. If a value violates the CHECK
constraint, the database will reject the insertion or update operation.
How CHECK Constraints Work:
• CHECK constraints can be defined when creating or altering a table.
• They can be applied to one or more columns.

Example on our SportsClub database

1. first check constraint - value in parent field must not be same as in Memname field.

CHECK (parent <> Memname)

This constraint ensures that when a row is inserted, the value in the parent column is
not the same as the value in the Memname column. This means that a child cannot
insert themselves as their parent.
Second check constraint - a child may not be in the club without a parent.

CHECK (
(age(date_of_birth) < interval '18 years' AND parent IS NOT NULL)
OR
(age(date_of_birth) >= interval '18 years')
)

For the row to be inserted successfully, whole check constraint should return TRUE. So,
either the first half (before the OR) must hold true, or the second half must hold true.

(Age function returns ‘xx years yy mons zz days’ of type interval)

If we wrote the constraint as:

CHECK ((age(date_of_birth) < interval '18 years' AND parent IS NOT NULL))

This would not work for people over 18. In this case, the first part (age < 18) would be false,
and the second part would be also false, resulting in a FALSE AND FALSE evaluation, which is
FALSE overall. Therefore, the constraint would fail, causing an error. This is why we need the
OR statement.

Alternatively, we could handle this CHECK constraint with:

CHECK (NOT (age(date_of_birth) < interval '18 years' AND parent IS NULL))

This would check for every record inserted that such thing does not hold when age is > 18
and parent is null.