장점
단점
정규화는 총 4가지 수준에 따라 데이터 구조를 변화시킬 수 있으며, 각 1 정규화, 2 정규화, 3 정규화, BCNF 정규화로 칭함
Order
테이블
OrderID CustomerName ProductName Quantity
----------------------------------------------------------
1 John Smith Apple iPhone 12 2
2 Mary Johnson Samsung Galaxy S21 1
3 John Smith Apple AirPods Pro 3
PK를 소유하고, 각 속성을 분해하는 1 정규화 수행
Order
OrderID CustomerName
-----------------------
1 John Smith
2 Mary Johnson
3 John Smith
Product
OrderID ProductName Quantity
---------------------------------------
1 Apple iPhone 12 2
2 Samsung Galaxy S21 1
3 Apple AirPods Pro 3
Order
OrderID CustomerName ProductName Quantity ProductCategory
----------------------------------------------------------------------------
1 John Smith Apple iPhone 12 2 Smartphones
2 Mary Johnson Samsung Galaxy S21 1 Smartphones
3 John Smith Apple AirPods Pro 3
OrderId
를 소유 Order
OrderID CustomerName
-----------------------
1 John Smith
2 Mary Johnson
3 John Smith
Product
ProductName ProductCategory
-------------------------------------
Apple iPhone 12 Smartphones
Samsung Galaxy S21 Smartphones
Apple AirPods Pro Headphones
OrderId
에 대한 완전 함수 족송성은 유지 Order
+---------+-------------+------------+-------------+-------+-----+
| OrderID | CustomerName | OrderDate | ProductName | Price | ... |
+---------+-------------+------------+-------------+-------+-----+
| 1 | John | 2022-05-01 | Shirt | 20 | ... |
| 2 | Mary | 2022-05-02 | Shoes | 50 | ... |
+---------+-------------+------------+-------------+-------+-----+
Order 테이블
+---------+-------------+------------+
| OrderID | CustomerName | OrderDate |
+---------+-------------+------------+
| 1 | John | 2022-05-01 |
| 2 | Mary | 2022-05-02 |
+---------+-------------+------------+
Product 테이블
+-------------+-------------+-------+-----+
| ProductName | Manufacturer | Price | ... |
+-------------+-------------+-------+-----+
| Shirt | Brand A | 20 | ... |
| Shoes | Brand B | 50 | ... |
+-------------+-------------+-------+-----+
Category
테이블로 생성Order 테이블
+---------+-------------+------------+-------------+
| OrderID | CustomerName | OrderDate | ProductName |
+---------+-------------+------------+-------------+
| 1 | John | 2022-05-01 | Shirt |
| 2 | Mary | 2022-05-02 | Shoes |
+---------+-------------+------------+-------------+
Product 테이블
+-------------+-------------+-------+-----+
| ProductName | Manufacturer | Price | ... |
+-------------+-------------+-------+-----+
| Shirt | Brand A | 20 | ... |
| Shoes | Brand B | 50 | ... |
+-------------+-------------+-------+-----+
Category 테이블
+-------------+
| Category |
+-------------+
| Clothing |
| Footwear |
+-------------+
Product 테이블
+-------------+-------------+-------+-----+
| ProductName | Manufacturer | Price | ... |
+-------------+-------------+-------+-----+
| Shirt | Brand A | 20 | ... |
| Shoes | Brand B | 50 | ... |
+-------------+-------------+-------+-----+
Students
Students (StudentID, StudentName, CourseCode, CourseName)
BCNF 적용
Students (StudentID, StudentName)
Courses (CourseCode, CourseName)
JOIN
연산 등을 고려해야 함 재밌게도 나는 실무에서 비정규화된 데이터와 정규화된 데이터 시스템 두 가지를 전부 다루고 있는데 정규화 데이터가 아닌 비정규화 데이터에 대한 소감은 다음과 같음
이상
현상을 마주함