These are the topics for week 2:
- What’s an identifier (keys)?
- Primary key
- Foreign key
- Composite key
- What are the 3 types of relationships?
- One-to-One (1-1)
- One-to-Many (1-M)
- Many-to-Many (M-M)
- What are indexes?
- What’s domain modeling?
- Entity Relationship Diagram (ERD)
- How to use SQL: joins, aggregate Functions, distinct, group by, having
- Joins
- Aggregate functions
- Distinct
- Group By
- Having
Your teachers Igor and Unmesh have made video lectures for this week's material. You can find them here: Videos 9 - 11
A key or identifier is a single or combination of multiple fields in a table which is used to fetch or retrieve records/data-rows from data table according to the condition/requirement.
Keys are also used to create a relationship among different database tables or views.
The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).
To define a Primary Key while creating the table, you should determine the attribute in Column definition part:
CREATE TABLE teachers (
teacher_number INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50),
date_of_birth DATE,
subject TEXT,
email VARCHAR(200),
PRIMARY KEY (teacher_number)
);If you already have the table, and you just want to change a column to Primary Key:
ALTER TABLE teachers ADD PRIMARY KEY (teacher_number);A FOREIGN KEY is a key used to link two tables together. This KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.
To define a Foreign Key while creating the table, you can use the below query:
CREATE TABLE students (
student_number INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50),
teacher_id INT,
email VARCHAR(200),
PRIMARY KEY (student_number),
FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_number)
);A foreign key does two useful things;
- It will verify if the related record exists, so you can't insert a row referencing a non-existing relation.
- It will create an index on this column, giving faster results when querying on the particular column.
or you can add a foreign key later:
ALTER TABLE students
ADD CONSTRAINT FK_TEACHER FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_number);The unique key is quite similar to a primary key, they both serve to check the uniqueness of a column value. The difference is that there can be only a single primary key, to define the record, and multiple unique keys, to define unique values. Columns with a unique constraint can be null unlike primary keys. Foreign keys can only reference primary keys and not unique keys.
ALTER TABLE teachers ADD UNIQUE KEY (email);A composite key is a key composed of two or more columns in a table that can be used to uniquely identify each row in the table when the columns are combined uniqueness is guaranteed, but when it taken individually it does not guarantee uniqueness.
For example in a database with students from several schools you'd expect the same student_number across schools.
CREATE TABLE students (
student_number INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50),
school_id INT,
PRIMARY KEY (student_number, school_id)
);Although composite keys show up in theoretical examples it isn't common to use them in practice.
Most frameworks will add an id column or a prefixed id column like student_id
For more information, check out the following:
There are three specific types of relationships that can exist between a pair of tables: one-to-one, one-to-many, and many-to-many. The tables participate in only one type of relationship at any given time. (You'll rarely need to change the type of relationship between a pair of tables. Only major changes in either of the table's structures could cause you to change the relationship.)
A pair of tables bears a one-to-one relationship when a single record in the first table is related to only one record in the second table, and a single record in the second table is related to only one record in the first table.
For example: each teacher has a personal account and a personal inbox (not more than one per account) and each inbox only belongs to a single account. So there is a One-to-One relationship between entity account and entity inbox.
A one-to-many relationship exists between two tables when a single record in the first table can be related to one or more records in the second table, but a single record in the second table can be related to only one record in the first table. To implement this relationship, we should put a foreign key in Many side that is referring to One side.
For example: each mentor can help some students, but in reverse direction each student at one time has just one mentor. So there is a Many-to-One relationship between entity student and entity mentor.
A pair of tables bears a many-to-many relationship when a single record in the first table can be related to one or more records in the second table and a single record in the second table can be related to one or more records in the first table. To implement this relationship, we should create an extra table. This concept is called a junction table. The table should (at least) contain the primary keys from both entities.
For example: each teacher will teach a course to multiple classes and these classes will be following several courses from different teachers. So there is a Many-to-Many relationship between the entities course and class
To learn more about relationships, check out the following:
Indexes are a type of a look-up table where the database server can quickly look up rows in the database tables. Imagine a (technical) textbook which has the index at the end. This index contains keywords in that book and it tells you on which pages those keyword appear. It helps to find pages that contains a word promise instead of looking for each page one by one. Note that a keyword may appear on more than one pages.
In this case, you will see all pages on which this keyword appears. In a JavaScript book, the word function may appear on many pages while the word
prototype chaining may appear only once. In the index, you can quickly find on which page these words appear.
A primary key is always an index, but you can add other indexes to the table yourself if you know that you are going to do many queries using that column. This will speed up your querying, but be aware that adding indexes will slow down writing to the table. As every time a change is made it needs to update all of the indexes.
Have a look at the video on indexes in the video playlist at the top of the page for a rundown of how this works and how you can add/remove indexes to a table.
To learn more about this topic, check out the following:
Domain Modeling is a way to describe and model real world entities and the relationships between them, which collectively describe the problem domain space.
Derived from an understanding of system-level requirements, identifying domain entities and their relationships provides an effective basis for understanding and helps practitioners design systems for maintainability testability, and incremental development.
Entity Relationship Diagrams (ERD) are used widely in domain modeling. In this diagram, entities are showing by boxes and are connected to each other with a line (relationships).
An example of ER diagrams are shown below:
To learn more about this topic, check out the following:
Let’s say we wanted to get a list of students and the details of their teacher, dismissing students without a teacher. This would be a perfect fit for an inner join, since an inner join returns records at the intersection of the two tables.
SELECT students.first_name, students.last_name, students.gender, students.grade, teachers.full_name
FROM students
INNER JOIN teachers
ON students.teacher_number = teachers.teacher_numberThe keyword INNER is optional and writing JOIN teachers will result in an inner join.
When you join two tables there can be columns in both tables with the same name.
To be explicit about the column you need you have to prefix the column with the table name like table.column
If you have long table names you can create an alias for a table using table AS alias.
The goal is to make queries easier to read, so be aware that using short or meaningless aliases (like a,b,c) can make it worse.
SELECT teachers.*, tq.title
FROM teachers
JOIN teacher_qualifications AS `tq` ON tq.teacher_id = teacher.idThe AS keyword is optional, so you could write it also like teacher_qualifications tq
If we wanted to simply append information about teachers to our students table regardless of whether a student has a teacher or not, we would use a left join. A left join returns all records from table A and any matching records from table B.
SELECT students.first_name, students.last_name, teachers.full_name
FROM students
LEFT JOIN teachers ON students.teacher_number = teachers.numberIf you tried to keep all the teachers and wanted to see teachers with or without students you need a right join. The right join will keep all records from the joined table and discard records, from the preceding tables, that can't be joined
Check out the following to get a more visual idea of what joins are:
Other types of joins are less common but can sometimes be needed. If you're interested you can read more about self joins and full outer joins.
In database management an Aggregate Function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning.
COUNT()SUM()AVG()MIN()MAX()
DISTINCT statement is used to return only distinct (different) values. It can be used with aggregation functions. In below example, we retrieve the numbers of teachers from students table.
SELECT COUNT(DISTINCT teacher_number) AS no_teachers
FROM studentsThe GROUP BY statement groups rows that have the same values into summary rows, like "find the number of students for each teacher".
The GROUP BY statement is often used with aggregate functions to group the result-set by one or more columns.
SELECT COUNT(teacher_number) AS no_teachers, teacher_number
FROM students
GROUP BY teacher_numberThe Having clause is like a WHERE statement but applied after the grouping has happened. It restricts the query results of GROUP BY clause.
For example in below example, we just retrieve the teachers who teach more that three students.
SELECT COUNT(teacher_number) AS no_teachers, teacher_number
FROM students
GROUP BY teacher_number
HAVING COUNT(teacher_number) > 3Are you finished with going through the materials? High five! If you feel ready to get practical, click here.