3 Lesson №59 Database development Presentation

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.
Sincerely, Director of QZ-COMPANY!

Topic: Database Development

Lesson objectives:
create a multi-table database using SQL queries and constructor

Knowledge and understanding:
- 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;

Applying:
- make a query for linking tables in a multi-table database;
- make queries for selecting of data from a multi-table database

Analysis:
- to design a multi-table database;
- identify primary key in a multi-table database.

-One to one (1:1)
-One to many (1:М)
-Many to many (М:М)

One book could be written by many authors
One author could write many books

One client could have many phone numbers

But the number has only one client

Primary key (PK) very important for each table. Let me explain why.
Primary key does not allow to create the same records (rows) in the table;
PK provide a logical connection between tables of the same database (for relational databases).

Foreign key (FK)  Provides a unique logical connection between the tables of one database.

CREATE TABLE users (
email varchar(30),
name varchar(30),
surname varchar(30),
age int,
PRIMARY KEY (email));

CREATE TABLE themes (
id int,
topic varchar(20),
author_email varchar(30),
PRIMARY KEY (id),
FOREIGN KEY (author_email) REFERENCES users (email));

CREATE TABLE messages (
id int,
message text,
author_email varchar(30),
theme_id varchar(30),
PRIMARY KEY (id),
FOREIGN KEY (author_email) REFERENCES users (email),
FOREIGN KEY (theme_id) REFERENCES themes (id));

insert into users values ('damir@mail.kz', 'Дамир', 'Султанов', 18);

Add all records in the "themes" table

insert into messages VALUES
(1, 'Как правильно выключать компьютер?', 'luiza@mail.kz', 1),
(2, 'Как спарить борщ?', 'luiza@mail.kz', 4),
(3, 'Какое масло лучше заливать в автомобиль?', 'damir@mail.kz', 3),
(4, 'Как подшить брюки?', 'luiza@mail.kz', 2);

The syntax for the simplest joining of data from several database tables:

SELECT имена_столбцов_таблицы_1, имена_столбцов_таблицы_2
FROM имя_таблицы_1, имя_таблицы_2;

SELECT users.name, themes.topic FROM users, themes WHERE users.email = themes.author_email;

A query for a selecting of names and surnames of users and their messages:
SELECT users.name, users.surname, messages.message FROM users, messages WHERE users.email = messages.author_email;

A query for a selecting of names and surnames of users and forum topics that they created:
SELECT users.name, users.surname, themes.topic FROM users, themes WHERE themes.author_email = 'luiza@mail.kz' AND users.email='luiza@mail.kz';

1. In your opinion, did you get useful knowledge in class today?
2. Where can you use this knowledge?
3. What do you need to implement your ideas?
4. What do you think we will do in the next lesson and will it be related to the topic of the current lesson?