Week 5 - Database design

Relational Databases

Primary Keys and Foreign Keys

Constraints

Database normalization

PostgreSQL setup

Joins

Java and databases

Practice

Assignment

• Teachers

Back end Track

For reference, the data from the previous example:

Student

Course

Enrollment

Primary keys

Each table has a set of columns whose values uniquely identify a single row in it. That sounds complicated, but simply put: If you know the studentId, you can always know the studentName. The column studentId uniquely identifies a single student. Note that the other way around is not true! If you know the name, you cannot figure out the studentId uniquely; for instance, the name "Jan" could refer to either studentId = 101 or studentId = 104. The studentId column is known as the Primary Key (PK) for the Student table. In this case, it is a single column, but a PK can consist of multiple columns!

<aside> 🙋‍♀️

• What could be the Primary Key of the Course table? </aside>

For the Enrollment table it is more difficult! A row in Enrollment is not uniquely identified by studentId; a student can follow multiple courses at the same time. Similarly, a course can have multiple students, so a single course can occur multiple times in the Enrollment table. However, the combination (studentId, courseName) is unique! So the PK of the Enrollment table is the tuple (studentId, courseName).

Relations and foreign keys

<aside> 🙋‍♀️

• Can you figure out which students follow Dr. Bakker's class? </aside>

The way to figure this out is by following the relations between the tables. Instead of storing all data in a single table, we define multiple tables that are related in some way. There are three types of relationships. Let’s say we have table A and table B:

• One-to-one (1:1) relations (1:1); each row in table A relates to one row in table B.
• One-to-many (1:N) relations; each row in table A relates to multiple rows in table B. Each row in table B related to exactly 1 row in table A.
• Many-to-many (M:N) relations; each row in table A related to multiple rows in table B, and each row in table B relates to multiple rows in table A.

Some examples:

<aside> 🙋‍♀️

• What kind of relationship exists between Student and Course ? </aside>

Courses and enrollments are related via the Course's name. Enrollments and Students are related via the studentID. This can be displayed in a diagram:

erDiagram

    **Student** {
        int studentID PK
        string studentName
    }

    **Course** {
        string courseName PK
        string instructor
    }

    **Enrollment** {
        int studentID FK
        string courseName FK
    }

    **Student** ||--o{ **Enrollment** : enrolls
		**Course**  ||--o{ **Enrollment** : contains

In this diagram, you can see the relations being represented as lines between the tables. In the table structure, we noted earlier that the relations are represented by a column in the tables:

• In the Course <> Enrollment relation, the relationship is from Course.courseName to Enrollment.courseName
• In the Student <> Enrollment relation, the relationship is from Student.studentId to Enrollment.studentId.

In the table structure, such a relationship is defined by a Foreign Key (FK). FK's show the relationship, but are also used to enforce some more structure: foreign keys from one table to another ensure that a reference exists.

For example in the Enrollment table, the studentId's 101, 102, and 103 all exist in the Student table, and your relational database technology will ensure this is always the case. If you were to try and insert a row into the Enrollment table with studentId = 99, your insert would fail, as there is no row in the Student table with studentId = 99.

Foreign keys always consists of one (or more!) columns. In the case of the student, the foreign key relation uses the studentId column, but could also have been made differently.

In a properly normalized relational database (we'll get too what to means, I promise), foreign keys always refer to a primary key of another table.

The HackYourFuture curriculum is licensed under CC BY-NC-SA 4.0 *https://hackyourfuture.net/*

Built with ❤️ by the HackYourFuture community · Thank you, contributors

Found a mistake or have a suggestion? Let us know in the feedback form.

JASPER test