[SQLD] 2. Data model and performance

Data modeling with performance in mind

1. Perform normalization accurately when modeling data.

2. Perform database capacity calculation.

3. Identify the type of transaction that occurs in the database.

4. Perform semi-normalization according to capacity and transaction type.

5. Perform historical model adjustment, PK/FK adjustment, super borrowing/subtype adjustment, etc.

6. Verify the data model from a performance perspective.

Database normalization

Normalization is a method to remove unnecessary data and organize tables logically and intuitively. Normalization is divided into 1st normalization, 2nd normalization, 3rd normalization, BCNF, 4th normalization, and 5th normalization, and each will be explained below.

First, primary normalization refers to having one column for each row with atomic values. For example, if an employee has two affiliations, such as sales and human resources, the rows should be separated into two.

In case of duplication at the column level, it is also subject to first normalization.

Quadratic normalization satisfies complete functional dependence. Conversely, it removes partial functional dependencies. The attributes that an employee can have include name, age, job, and position. If the name is taken as PK, the complete functional dependency relationship cannot be satisfied because there are other attributes. Therefore, the table is separated into tables with each attribute.

Third-order normalization refers to eliminating transitive function dependence. In other words, if duplicate data may occur in a case where one attribute among several attributes determines the value of another attribute, it should be separated into another table. For example, let's assume that all employees have the same annual salary depending on their position. Here, the annual salary is a value defined according to the attributes of the position, and only the position is assigned to the employee table, and the position and annual salary are separated in another table.

BCNF, Boyce and Codd Normal Form, satisfies the third normal form and is a normal form in which all determinants belong to the candidate key set.

Here, the determiner refers to the position that determines the annual salary by determining one attribute, as mentioned in the example of the third normal form above. A candidate key is a subset of attributes used to uniquely identify a tuple and is an attribute that can be used as a primary key. Candidate keys that are not selected as primary keys are called replacement keys.

In other words, the case where all determiners do not belong to the candidate key set is the case where a general column determines the candidate key, which is the same as the case where the course taken by the student in the student table and the professor attribute that determines the course exist in the same tuple. Therefore, it is necessary to create the table separately.

It seems that many questions are asked mainly about the 1st to 3rd normal forms. Other normalizations are as follows.

Quaternary normalization refers to separating the table to remove if a relation has properties that show multi-value dependency in a situation that satisfies the third normal form.

If all join dependencies existing in the relation are established only through the relation candidate key, then the relation R can be considered to belong to the fifth normal form.

Denormalization of tables

Merge tables, split tables, add tables

Denormalization of columns

Add duplicate column, add derived column, add history table column, add column by PK, add column for application system malfunction

Low chaining

A phenomenon that occurs when the data size of one row is larger than the data storage space of a block. When the data size is larger than the data storage space of the block, one row is separated into several pieces and each piece is stored in a different block.

Row chaining may cause deterioration in query performance. So, by analyzing the column type accessed by the transaction and separating the tables 1:1, disk I/O can be reduced and query performance can be improved.

Partitioning

Partitioning of the database.

When a large amount of data is stored in one table, performance may deteriorate even if you add an index and split the table into several parts. At this time, a technique is applied to the table by logically dividing it into one table but physically dividing it into multiple tables to improve data access and data management methods.

An example of horizontal segmentation is dividing customer data into male and female based on gender.

An example of vertical partitioning is to increase search efficiency by dividing the most recent year's worth of data into separate tables.

UNION / UNION ALL

UNION combines relationships with duplicate rows removed. On the other hand, UNION ALL outputs all rows regardless of duplication. When using the UNION operator, the number, name, and data type of columns must match each query.

PK order decision criteria

It can be said that the index's use efficiency is highest when the column corresponding to the condition entered with '=' in the WHERE value of the query statement is located at the very front of the index.

---

3 elements of data modeling: Understanding attributes

Normalization concept and normalization process (4NF, 5NF)

DataBase-start (normalization): Naver Blog

[DATABASE] Learn about database normalization 1NF, 2NF, 3NF, BCNF, 4NF, 5NF

Database normalization - 1NF, 2NF, 3NF

Oracle's Table/Row Chaining/Row Migration

Database partitioning - Wikipedia, the free encyclopedia

[Oracle] How to use Oracle UNION and UNION ALL (combining query results)

Aladdin: SQL Qualification Test Practice Questions

https://www.aladin.co.kr/shop/wproduct.aspx?ItemId=97612848&start=pgooglemc