В этой статье мы расскажем о том, как спроектировать быструю схему реляционной базы данных и как написать необработанный SQL-запрос в реляционной базе данных. Итак, не теряя времени, давайте начнем.

Прежде чем углубляться в разработку схемы, давайте посмотрим на свойства, которые делают хорошую базу данных. ACID- свойства транзакции в БД.

Атомарность: каждая транзакция рассматривается как единое целое, которое либо полностью завершается успешно, либо полностью завершается неудачей.

Согласованность: БД согласована до и после транзакции.

Изолированность: транзакции происходят независимо друг от друга. означает чтение и запись в несколько таблиц одновременно.

Долговечность: Надежна при сбоях и отказоустойчива. Это означает, что после совершения транзакции она будет сохраняться даже в случае сбоя системы.

Разработка схемы БД

Примечание. Предполагается, что вы выбрали вариант реляционной БД, такой как (sql, postgres и т. д.), в качестве основного источника информации для вашего приложения. Вы также можете смешивать и сочетать (что типично для почти каждого приложения), но это выходит за рамки этого поста. Также предполагается, что вы достаточно хорошо знаете ключи и индексы в базах данных.

Схема базы данных - это каркасная структура, которая представляет логическое представление всей базы данных. Ее можно просто назвать метаданными, которые описывают отношения между объектами и информацией в базе данных.

Чтобы разработать хорошую схему реляционной БД, вам нужно знать только 2 вещи.

1 Тип отношений между объектами

2 Нормализация в БД (1НФ, 2НФ, 3НФ)

Типы отношений:

Тип отношений между объектами расскажет вам о количестве таблиц, которые вы должны составить в своей БД. БД может иметь несколько таблиц. Есть три типа отношений в целом. Давайте посмотрим на это внимательно 

Один к одному

А. В отношении «один к одному» одна запись / строка в таблице связана с одной и только одной записью / строкой в другой таблице.

Б. Обе таблицы будут иметь один и тот же первичный ключ, который будет использоваться для идентификации записи / строки. Мы получим полные данные из обеих таблиц, выполнив соединение с этим первичным ключом.

В. Пример: пользовательская таблица (разобьем ее на первичные данные и второстепенные данные пользователя)

Table 1: (Primary Info): User_ID (primary key), First name, Last name

Table 2: (Secondary Info): User_ID(primary key), address,email,contact и т.д..

Примечание. Вы можете поспорить о том, что все данные должны быть помещены в одну таблицу только с одним первичным ключом, но поверьте, такой дизайн не будет эффективным и оптимален для БД с миллионами значений.

Один ко многим

А. В отношении «один ко многим» каждая запись в таблице A может иметь много связанных записей / строк в таблице B, но каждая запись / строка в таблице B может иметь только одну соответствующую запись / строку в таблице A.

Б. Для получения данных просто поместите первичный ключ таблицы 1 в качестве внешнего ключа 2-й таблицы. Вам нужно минимум 2 таблицы, чтобы создать хорошие отношения 1 ко многим.

В. Пример: Хранение информации всех кандидатов от политических партий. При этом многие кандидаты принадлежат к одной партии. Они не могут представлять несколько сторон одновременно.

Table 1: (Political Party Info): Political_Party_ID (Primary key) , Party_Name , Political_Party_Head

Table 2: (Party Candidates Info): Candidate_ID (Primary key), Candidate_Name , Political_Party_ID(Внешний ключ в качестве ссылки из)

Много ко многим

А. Связь «многие ко многим» возникает, когда несколько записей в таблице связаны с несколькими записями в другой таблице.

Б. Системы реляционных баз данных обычно не позволяют реализовать прямую связь «многие ко многим» между двумя таблицами. Итак, вам необходимо создать третью таблицу для этих отношений. Это наиболее распространенные отношения, которые вы увидите почти в каждом приложении. Третья таблица называется таблицей ссылок или таблицей соединений. Вы можете запросить третью таблицу напрямую, чтобы получить данные для целей анализа.

В. Пример: студенты зачислены на разные курсы в старшей школе. Несколько студентов могут записаться на несколько курсов и несколько курсов могут быть выбраны несколькими студентами.

Table 1 : (Students Info ) : Student_ID (Primary Key) , Student_Name  и т.д.

Table 2 : (Courses Table): Course_ID (Primary key) , Course_Name, Cost  и т.д.

Table 3 : (Enrollments Table): [ Student_ID (Внешний ключ в качестве ссылки из Students Info ),Course_ID (Внешний ключ в качестве ссылки из Courses Table) ] —{ Комбинированный ключ } , Enrollment_ID и т.д..

Нормализация

Это процесс минимизации избыточности из отношения или набора отношений в БД.

1 НФ - должен быть ключ, однозначно идентифицирующий запись / строку. Обычно это первичный ключ или комбинированный ключ. (концепция первичного ключа)

2 НФ - должно быть в 1 НФ, и каждый неключевой столбец должен полностью зависеть от первичного ключа. (концепция внешнего ключа)

3 НФ - должно быть в 2-НФ, а столбцы без ключа не зависят друг от друга. если они зависят, то вам, вероятно, придется разделить таблицу.

Написание необработанного SQL-запроса

Давайте посмотрим на некоторые основные шаблоны для запроса базы данных. Помните - результатом запроса всегда является таблица.

Шаблон 1

SELECT * FROM TABLE_NAME WHERE CONDITION / RESTRICTION: Здесь * означает, что все столбцы и условия могут использоваться для сопоставления со значением в строке базы данных. * можно изменить значения столбца, необходимые для получения из таблицы.

Примеры:

SELECT FIRST_NAME , LAST_NAME FROM USERS WHERE USER_ID =1

SELECT COUNT(*) FROM USERS_TABLE WHERE PURCHASE_DATE > 12–12–2018 AND < 12–1–2019

SELECT NAME FROM USERS_TABLE WHERE NOT( AGE = 20 ) AND NOT(FIRST_NAME = ‘KARAN’)

INSERT INTO USERS_TABLE VALUES (‘KARAN ’,’JAGOTA’ )

Есть некоторые другие функции / предложения, которые вы можете использовать в этом шаблоне. Он включает в себя - значение смещения предельного значения или группу по col_name или порядок по column_name desc или count (*) как общее или имеющее col_name = value (происходит после «WHERE», тогда как group_by или order_by выполняются до «WHERE»).

Шаблон 2

SELECT OBJECT_OF_TABLE_1.COL , OBJECT_OF_TABLE_2.COL FROM TABLE_1 JOIN TABLE_2 ON OBJECT_OF_TABLE_1.COMMON_KEY = OBJECT_OF_TABLE_2.COMMON_KEY

Здесь вы создаете объект (используйте любое имя переменной. Например, мы используем U и P в примере для таблицы пользователей и заказов) для обеих таблиц, выбираете желаемое имя столбца и объединяете их (inner join / left join/right join) по имени таблицы по их общему ключу (первичный ключ одной таблицы с внешним ключом другой таблицы)

Пример: SELECT U.FIRST_NAME , P.PURCHASE FROM USERS_TABLE JOIN ORDERS_TABLE ON U.CUSTOMER_ID = P.CUSTOMER_ID

Шаблон 3

SELECT AVG(*) FROM (SUBQUERY) AS ALIAS

Здесь вы просто пишете запрос в запросе. Поскольку мы знаем, что результатом запроса является сама таблица, ее можно использовать для дальнейшего запроса и поиска необходимого результата или данных.

No Comments