I will now talk about normalization. So, what is normalization? It has very important characteristics when designing. Let's read through this together. First, normalization is a method to ensure stable processing by eliminating anomalies in input, modification, and deletion during data processing. The second basic principle is to ensure that there is no duplicate data in the table. The third aspect is related to the theory of procedures that further structure and improve the data model through various types of checks. 

Looking at the first point, anomalies in input, modification, and deletion can be referred to as anomalies. It is about eliminating anomalies. This means that data can be processed stably. To elaborate further, it helps to ensure consistency, which means that the data can be processed as expected.

Then what should we do? When there is data duplication, many anomalies occur. We need to eliminate redundancy. So, we have the term redundancy that we mentioned earlier. By removing redundancy, we can ensure stable processing. This process is called normalization. 

To perform normalization, there is a mathematical theory that states fx is y. Here, fx refers to a function where x is the input and y is the output. When we input a value for x, the corresponding y value follows. This relationship is referred to as a determinant for x and a dependent for y. 

Thus, we focus on the relationship between the determinant and the dependent to minimize data redundancy, allowing us to address anomalies such as input anomalies, modification anomalies, and deletion anomalies. By eliminating these anomalies, we ensure the stability of data processing, which we can refer to as normalization. Furthermore, when we input x, y follows. This is known as functional dependency.

It is called functional dependency. You don't need to be thoroughly familiar with all these theories; since this is a process related to data analysis. However, at least you should be able to interpret what normalization is and its characteristics. If you are a professional, that level of understanding is sufficient. 

So, the concept of functional dependency comes from mathematical theory, and the reason we mention the determinant is because of that. The determinant defines the dependent attributes; saying that it functionally determines them is a somewhat complex and seemingly difficult term. But what it really means is quite simple. For example, the primary key determines the remaining general attributes. What I mean is, if you know the resident registration number (the primary key), you can figure out the name, birthplace, and address. If you know the resident registration number, you can deduce all the rest.

This is about how a resident registration number determines a person's name, birth date, and address. This can be expressed with arrows, and we can refer to it as functional dependency (FD). We say that the name, birth place, and address are functionally dependent on the resident registration number. Using this FD relationship, let's talk about first, second, and third normal forms. Currently, there are a total of six normal forms. The first normal form, second normal form, and third normal form were first presented by E.F. Codd. Later, a person named Boyce, who learned from E.F. Codd, developed the third normal form, but some anomalies still occurred. So, he should have called it Boyce Normal Form, but when he brought it to Codd, Codd agreed that it was indeed strange, and they decided to create what is now known as Boyce-Codd Normal Form.

This is created again. Up to this point, it has been made, and then it was created by a person named Code. After that, the fourth and fifth normal forms come out, but at this time, a person named Pagini confirmed that normalization occurs beyond functional dependency. Thus, in the fourth and fifth normal forms, the expansion referred to as multi-valued and join dependency appears. However, in the process, I will exclude the fourth and fifth normal forms from the discussion and focus on the basic characteristics of the first, second, and third normal forms that were initially presented. First, the first normal form separates attributes that have multiple values and secures atomic values for the primary key. Please make sure to note this down. The second normal form separates non-dependently related attributes from the main identifier, which means it separates partial dependency.

I will show you. The third normalization separates the non-dependent attributes of the primary key. This is referred to as transitive dependency active 2. So, if you look closely at these three, I will show you a sample. First normal form generally means that for the primary key, most of the time, there is only one value for each attribute. However, if you look here, there is a part about the mouse monitor, where two values appear. This indicates that there are more than two values. This part, where there are more than two values, is called a repeating value. So, a repeating value means that it has appeared multiple times. The process of breaking this part into a separate table is first normal form. Therefore, there should be only one record for each primary key.

It should come out, but there are more than two. If we look at the situation where more than two appear, we have the product number, product name, stock quantity, and order quantity. If the product number and stock are removed, and we say that there are more than two orders here, then the primary key is not atomic; it means that multiple values have appeared. You can think of it as being subject to the first normal form. The essence of the first normal form is that there are multiple values for the primary key, and it is not atomic. So, if we do not achieve the first normal form, then for a single order, multiple columns will need to be added continuously. When modifying, more than two need to be modified, and when deleting, unwanted information may also be deleted, leading to such phenomena. How do we refine this? The part where more than two appeared earlier can be separated into a different table. By separating it like this and creating a table, we can achieve the first normal form. Then, regarding the order number, we have the product number.

이렇게 나오고, 여기에는 속성을 요렇게 들어가면 1차 전개가 완료가 됩니다. 이렇게 주문 테이블이 있고, 아까 중복되어 있는 거 있죠? 거기를 뚝 떼서 이렇게 옮긴 거예요. 옮기고, 좋은 번호와 제품 번호가 PK가 되고, 나머지가 여기를 따라가는 1차 정교화가 완성된 테이블입니다. 그러니까 여러분이 보시는, 여러분이 각 기관에 들어가서 보는 그 데이터베이스 테이블들이 이런 식으로 정규화가 완성이 돼서 만들어진 데이터베이스 테이블이다 이렇게 이해하시면 돼요. 여러분이 보시는 테이블이 이런 식으로 정규화가 완성이 돼서 만들어진 그런 테이블이다 이렇게 보시면 되겠습니다. 그런데 그때 1차 정규화는 PK에 대해서 리프팅되어 있는 반복적으로 여러 개 나오는 그 속성들을 구분했구나라고 기억하시면 되겠습니다. 2차 정규화는 PK 속성이 두 개 이상일 때, 몇 개 이상일 때, 두 개 이상입니다.

It occurs when there is a situation where the primary key (PK) consists of more than one element, and among those elements, some have a functional dependency (FD) relationship only with a subset of the attributes. This may seem a bit difficult, but just remember that you don't need to understand this perfectly. It's enough to grasp the concept that some attributes among the primary keys are functionally dependent on certain elements. You won't be designing everything perfectly, so you can think of it this way: if you don't separate these elements, data will be stored incorrectly.

For example, the product name and inventory quantity can be stored with just the product number, even without the order number, as long as the product number is the determinant. However, because this is all in one table, duplication occurs, leading to issues. Therefore, this should be separated into a distinct entity for each product.

When we extract and input data, this leads to second normalization. If we do not maintain cleanliness, anomalies occur when we input, modify, or delete information. This serves as an example of those anomalies. So, when we extract a product as a separate entity, it undergoes second normalization. The key point of second normalization is that there must be more than one primary key (PK) column, specifically two or more. Among those, only a portion is addressed, which is why we separate partial dependencies into a different table to eliminate anomalies. You can think of this as a refined second normalization process. 

Then, regarding the product entity, we can create a table to stably process information about lists and orders. Lastly, we will discuss third normalization.

The core of normalization is what is called remove transitive dependency. What this means is that there is a primary key (PK), and it has a dependent relationship. Within these general attributes, there can also be elements that can serve as a PK. This means that among the general attributes, there are things that can qualify as a PK. For example, if customer number can be a key, then the business number or priority could just depend on the customer number alone. So, we can understand that the PK has been transferred to the general attributes and is now transitive. If we do not separate this, issues with input, modification, and deletion can arise. Therefore, we extract elements that can serve as a PK from these general attributes into a separate table, assigning customer numbers to them and sending the corresponding attributes along with it. This process results in third normal form.

The core of normalization is to break down attributes that could serve as primary keys into separate tables. If this separation is not done, issues with input, modification, and deletion arise. Therefore, if third normal form is not applied, anomalies will occur. As we discussed earlier, customer information is separated, and in the order information, the customer number acts as the primary key, while attributes like export status and business number are categorized as general attributes and divided into separate tables. This is the essence of achieving third normal form by distinguishing transitive dependencies, allowing data to be processed reliably. The ERD we worked on earlier has been structured to maintain stable information throughout the first, second, and third normal forms. This way, when we input, modify, or delete data in the system, redundancy is eliminated, preventing data from being stored incorrectly.

It has characteristics that allow for accurate and stable storage. So, when designing normalized data, normalization can be understood as a process of eliminating redundancy based on the theory of functional dependency.

13. 정규화 | 논리 데이터 모델링 | ★SQL 뽀개기 | SQLD | 정보처리기사 | 정보처리기능사 | 데이터베이스 | SQL 무료강의

빅데이터분석기사, ADsP, SQLD부터 정보관리기술사까지! 빅데이터&인공지능 분야 자격증 정보는 아이리포와 함께!

13. 정규화 | 논리 데이터 모델링 | ★SQL 뽀개기 | SQLD | 정보처리기사 | 정보처리기능사 | 데이터베이스 | SQL 무료강의

13. 정규화 | 논리 데이터 모델링 | ★SQL 뽀개기 | SQLD | 정보처리기사 | 정보처리기능사 | 데이터베이스 | SQL 무료강의

스크립트