SQL for AI 102

Acknowledgement: This post is based mainly on Dr. Vinh Dinh Nguyen’s presentation and slides and partly on on TA, STA, and peers’ responses from the course AIO2025 offered by AI VIETNAM.

Relevance: The audience of this lecture were those who have read SQL for AI 101, had some coding experience, and known a bit of AI knowledge. Absolute beginners are encouraged to read the material and repeatedly practice coding without AI assistance to strengthen their coding and code-reading skills. Note that only necessary Python knowledge is introduced here.

In today’s post, we’ll explore entity relationship diagram and database normalization

Entity Relationship Diagram (ERD)

When developing a software application, we’ll need a tool to design and visualize it. An Entity Relationship Diagram (ERD) exists for this purpose before we can start the implementation into DBMS.

Formally, an ERD is a visual representation of different entities within a system and how they relate to each other. There are two popular standard notations to represent an ERD: Chen notation and Crow’s Foot notation.

Chen Notation

In the Chen notation, we use different shapes to illustrate components of a database and lines to connect them as above. We’ll first go over definitions and then discuss SQL syntax.

Entity

An entity is the fundamental building block of a database design.

• It represents a real-world concept and physically displayed by a table in a DBMS.
• A singular noun is conventionally used to name an entity.
• Its name is placed inside a rectangle.

An entity—like a class in Python (check out Python for AI 105)—has its own attributes which are equivalent to columns in a table. We’ll discuss attributes more later.

Weak Entity
A weak identity is itself an entity.

• It cannot be uniquely identitified by its attributes alone, depending on the existence of another identity.
• Its name is placed inside a double rectangle.

In an online shopping store database, if the “Order table is an entity, the “Order Item” is a weak entity.

Associative Entity
An associate entity is created to relate two independent entities.

• Its name is placed inside a rhombus/diamond maybe nested in a rectangle.
• It represents a many-to-many relationship.

For example, there are two entities: student and course. Each of them has more than one instance—row in a table. A student can enroll in multiple courses while a course can have many students. An associative entity like enrollment can be the middleman making such connections.

Attribute

As we previously mentioned, an attribute of an entity is a column of a table.

• It is named using a noun.
• Its name is placed inside an ellipse/oval.

Key Attribute

• It is the primary key of a table.
• Its name is underlined.

Composite Attributes

• Composite attributes are child attributes of an existing attribute.
• They can be independent attributes but are preferred to belong to another.

For example, when ordering products from an online store, you must provide your address which is subdivided into different boxes like street address, city, state, and zip code. These boxes refers to composite attributes of the “address” attribute.

Multivalued Attribute

• A multivalued attribute can have more than one value.
• Its name is placed inside a double ellipse/oval.

If a person is an entity, their hobby will likely be its multivalued attribute. They may enjoy doing two or more activities like reading books, playing golf, and watching TV.

Derived Attribute

• A derived attribute can be calculated from an existing attribute.
• It is not stored directly in a database
• Its name is placed inside a dashed ellipsed/oval.
• It rarely appears in an ERD.

We can spring the “age” attribute from a “date-of-birth” attribute.

Relationships

As we know from the last post, tables or entities must be connected with each other to make a well-defined database. This is where relationships come into play. There are three kinds of relationship in Chen notation.

1. One-to-One (1:1): each instance of one entity is related to only one instance of the other.
2. One-to-Many (1:N) or Many-to-One (N:1): each instance of one entity is related to many instances of the other.
3. Many-to-Many (M:N): many instances of one entity are related to many instances of another.

They are all illustrated using:

• a rhombus/diamond with a verb inside to describe how one entity is related to another
• lines to join entities with their corresponding relation diamond
• notations to indicate what kind of relationship it is.

Pay attention to verbs representing relationships; changing one between entities can change their relationship.

Participation Constraints

To make a thorough database, participation constraints should be made clear. There are two types of participations as follows.

1. Total participation:
   • All instances of an entity must be engaged in a relationship.
   • A double line is drawn between an entity and a diamond relation.
2. Partial participation:
   • Not all instances of an entity must involved in a relationship.
   • A single line is drawn between an entity and a diamond relation.

For example, in a PhD program, a candidate must have an advisor to be able to complete their dessertation whereas some professors in the department may not guide any student. These restrictions can be seen below.

Crow’s Foot Notation

Recall database schema generated through “reverse engineering”, that is a detailed version of a Crow’s Foot notation.

An entity and its attributes are placed inside a rectangle with a separation line between them. A key attribute is specified with a $*$ in front of it.

Relationships

Instead of using a verb to represent a relationship, we connect two entities directly by a straight line and use symbols on each side to indicate such a relation. Relationships have two parts:

• multiplicity: next to an entity, refering to the maximum number of instances of an entity can be associated with isntances from the other
  • $|$ means one instance
  • $>$ means many instances
• possibility: the minimum number of times one instance of an entity can be related to another
  • $\circ$ means optional or at least zero
  • $|$ means mandatory or at least one.

There are four types of relationships in Crow’s Foot notation as follows.

1. zero or many
2. one or many
3. one and only one
4. zero or one

Practice Exercises

Describe relationships between entities in pictures below.
Exercise 1: Chen notation

Exercise 2: Crow's Foot notation

How to Draw ERD

It’s recommended we follow the below process when drawing an ERD. Entities, relationships, and attributes should all have clear and meaningful names.

1. Identify all the entities: Keep in mind that an entity can only appear once in a diagram.
2. Identify their relationships
3. Add attributes for entities

In practice, a wide range of tools are available for creating a pretty ERD. Some examples are Visual Paradigm, Lucidchart, Microsoft Visio, dbdiagramio, and drawio.

Pro tip: Always optimize ERDs by discussing with working partners before implementing it into DBMS.

It’s also best to approach the implementation as follows.

1. Create independent tables which only have primary keys
2. Create middleman tables and define their relationships and contraints with independent ones.

There are two ways to define a primary key in a table.

CREATE TABLE table_name (
    -- columns' information
    PRIMARY KEY(primary_column_name)
)

CREATE TABLE table_name (
    primary_column_name DATATYPE PRIMARY KEY,
    -- columns' information
)

Demonstrating dependent tables with constraints and foreign keys is more complicated.

CREATE TABLE table_name (
    -- columns' information
    foreign_column_name_1,
    ...
    foreign_column_name_n,
    PRIMARY KEY(primary_column_name),
    CONSTRAINT constraint_name_1 FOREIGN KEY (foreign_column_name_1)
        REFERENCES related_table_name_1(related_column_name_1) ON UPDATE CASCADE,
    ...
    CONSTRAINT constraint_name_n FOREIGN KEY (foreign_column_name_n)
        REFERENCES related_table_name_n(related_column_name_n) ON UPDATE CASCADE,   
)

If we want to have a composite key—a primary key made from a few foreign keys, change the above primary key assignment to

    PRIMARY KEY (foreign_column_name_1, ..., foreign_column_name_k)

Let’s break down some terms in the template. This syntax helps preserve data solidarity and data completeness.

• CONSTRAINT marks the start of restriction or relation.
• FOREIGN KEY is to identify columns as foreign keys.
• REFERENCE associates foreign keys to their root tables and columns.
• ON UPDATE CASCADE enables changes in root columns to be automatically contrived to foreign keys.

Database Normalization

We’ve been talking about creating middle tables to build well-defined relationships within a database.

You may wonder, **How do I know if the diagram/schema I created is acceptable?

That’s an amazing question. In this section, we’ll discuss normalization forms, a set of rules to inspect how well our database design adheres to standard practice.

Why Database Normalization?

Database normalization is a process of organizing data to reduce data redundancy and improve data integrity, consistency, and accuracy. As we’ve seen, large tables can be broken into smaller ones and connect them using relationships. There are a few situations which can emphasize the importance of normalization.

1. Insert anomolies:
   • Redundant data: some added details are the same as existing ones.
   • Partial data insertion: the data needs adding but only some information is available. For example, a new course needs to be included but no students are enrolled yet.
   • Null value issues: not all data is put in.

Machine Learning is powerless without proper data, so missing information not only costs resources but can also create critical problems.

1. Update anomolies:
   • Redundant data update: the same data items are repeated with the same values but not linked to each other.
   • Inconsistent data: some data may not be updated incorrectly.
2. Delete anomolies:
   • Unintended data loss: deleting one piece of information may lead to loss of relavant details.

Due to these situations, we need normalization forms to ensure our data are properly enforced. They are nested sets with 1NF being the outter and BCNF being the atom.

Necessary Terminology

Let’s go over some important terms before learning about normalization forms in a moment.

• A super key is a group of multiple keys that serves as a primary key. It may contain non-essential attributes.
  • Candidate keys are attributes/columns that can play the role of a primary key.
    • Primary key is the chosen attribute/column.
    • Alternate keys are the rest.
  • A composite key mainly contain essential attributes.
• A partial dependency means non-prime attributes can depend on a part of the primary key which is a combination of attributes.
• A transitive dependency means attribute A indirectly determines non-prime attribute C via non-prime attribute B.
• A functional dependency $X \to Y$ is called trivial if $Y \subseteq X$.
• A functional dependency $X \to Y$ is called non-trivial if $Y \not\subset X$
• A multivalued dependency satisfies the following conditions:
  • For a dependency $A \to B$, $B$ has multiple values for a single value of $A$.
  • A table should have at least three columns. Or else, it’s against 1NF.
  • For a relation $R(A, B, C)$, if $A$ and $B$ has a multivalued dependency, then $B$ and $C$ must be independent of each other.

1NF

A relation R is said to be 1NF if and only if

• All the attributes are atomic.
• It does not contain any multivalued or multityped attributes.
• Each row is unique, meaning it can be identified by a primary key.

If a table violates 1NF, items with more than one value should be separated.

2NF

A relation R is said to be 2NF if and only if

• It satisfies 1NF.
• No partial dependencies are allowed, meaning all non-prime attributes are fully dependent on the primary key.
• Special case: When a candidate key determines a non-prime attributes, the table is ruled out from 2NF.

In this example, the primary key contains “Student Code” and “Project ID”. We can retrieve students’ names just from knowing their student codes rather than with the project id. This indicates partial dependencies or a violation of 2NF. To fix such an issue, we can make three tables: “Student”, “Project”, and “Student Project”.

3NF

A relation R is said to be 3NF if and only if

• It satisfies 2NF.
• No transitive dependencies are allowed, meaning no bridging implications.
• Violations of 3NF can come from having a composite key.

This is 2NF with only one primary key. But it isn’t 3NF because we can get a student’s city from their student code via their zip code. We can modify it in a usual way.

Practice Exercises

Exercise 1: Identify the primary key of these tables. What is the highest normalization form do they satisfy?

Hints: One course name can be taught at different departments with different credit hours.
Exercise 2: Separate each table into smaller tables so that it satisfies the highest possible normalization forms here (3NF).

BCNF (3.5NF)

BCNF is short for Boyce-Codd Normal Form. A relation R is said to be BCNF (3.5NF) if and only if

• It satisfies 3NF.
• $X$ must be a super key for every functional dependency $X \to Y$, that is, if $Y$ is a prime attribute, so must $X$.

This example is of 3NF only if it has additional conditions:

• Each instructor only teaches one course.
• Each instructor ID is associated with only one name.

However, it isn’t 3.5F since the “Instructor ID” can reveal the “Instructor Name” but the id isn’t part of the primary key.

4NF

A relation R is said to be 4NF if and only if

• It satisfies 3.5NF.
• No multivalued dependency is allowed.

All three columns must be serve as the primary key, so the table is of 3.5NF. But it’s not 4NF because a single value M1 of “Model ID” has two color values (red and blue).

5NF

A relation R is said to be 5NF if and only if

• It satisfies 4NF.
• Joining deconstructed tables should not result in the loss of records or the creation of new records.

Practice Exercises

Exercise 1: Identify the primary key of this table. What is the highest normalization form does it satisfy?

Exercise 2: Give a solution to fix this table so that it can satisfy at least 3NF.

FAQ

Here are some good questions asked during class. The answers to these questions came from either some admin, TA, STA, or members of AIO2025.

https://stackoverflow.com/questions/10778561/what-do-the-mysql-workbench-column-icons-mean