create a multi-table database using SQL queries and constructor
to know types of relationships between tables in database;
to know how to specify a primary key and foreign key in queries;
to know queries for creating/working with a multi-table database;
make a query for linking tables in a multi-table database;
make queries for selecting of data from a multi-table database
to design a multi-table database;
identify primary key in a multi-table database.
Specific vocabulary and terminology:
Multi-table database, foreign key,
queries for selecting, data joining, types of connections.
phrases for conversation / writing
link these tables, we must use ... type of connection
specify a foreign key, we use the command ...
... is used to ...
key in the table ... is the field ..., because ...
to work in a team to achieve results.
for other people's opinions.
respect and support.
English (key words in queries)
types in MySQL DBMS;
Course of the lesson
Class room management: greeting,
The teacher reads the email that
Dear students of the 10th grade!
We are going to expand our
business;therefore, we ask you to develop a forum for our corporate website!
Our forum will serve as a place
for sharing ideas and experiences for like-minded people.
Director of QZ-COMPANY!
The teacher asks questions:
-What is the forum?
-What data need to be stored?
-Is it possible to use one table
in the database to organize a forum?
-If there will be one table, is
it convenient to store and search for data?
Introduction lesson topic,
objectives and discussion of assessment criteria.
Actualization of knowledge:
The teacher asks questions:
What is a primary key?
What is a foreign key and what is
it used for?
There are 3 types of
relationships between tables:
•one to one;
• one to many;
• many to many.
Give examples of each type of
In 90% of cases using one-to-many
How do you think, how many tables
you need to create a forum?
What tables do we need?
What data will be in each of the
teacher demonstrates the structure of the future database consisting of three
tables: Users, topics and messages.
The teacher explains the
structure of the database.
Users must be registered, and
they must have a key field, for example, EMAIL, so that each user is unique. Message
and topic will have a unique ID field.
A user creates a topic and writes
messages. In this case, the user can write messages in topics that are not
created by him.
student pulls a random number out of the bag. Then pairs are formed on the
basis of 1 and 2, 3 and 4, and so on.
If number of studentsis odd, one
student works individually, with support of the teacher.
1. Write a query to create a database
2. Write a query to create the “users”
Students work in pairs, if it is necessary,
they can use the Internet to find out information. After that you can discuss
CREATE TABLE users (
PRIMARY KEY (email));
One of the students is called to the whiteboard.
The teacher asks by analogy to write a query to create the "themes"
table. Remind that we have to link the “email” field from the “users” table
to the “author_email” field from the “themes” table. You need to know how to
specify in the request which field will be a foreign key and which field to connect
1. Students individually learn the
process of creating a foreign key using the proposed resource.
2. The teacher asks to complete on the
board the request to create the “themes” table. In the process of writing the
request, students correct mistakes and give help for their classmate.