728x90
1. 정규화
⦁ 관계형 데이터베이스의 설계에서 중복을 최소화하게 데이터를 구조화하는 프로세스
⦁ 기본 목표는 관련이 없는 함수 종속성은 별개의 릴레이션으로 표현
⦁ 릴레이션은 주로 테이블과 같은 의미로 사용되며, 데이터의 집합을 의미
2. 제 1 정규형
⦁ 릴레이션에 속한 모든 속성의 도메인이 더 이상 분해되지 않는 원자값으로만 구성된 정규형
3. 제 2 정규형
⦁ 릴레이션이 제 1 정규형에 속하고, 기본키가 아닌 모든 속성이 기본키에 완전 함수 종속되면 제 2 정규형
⦁ 부분 함수 종속을 제거하고 모든 속성이 기본키에 완전 함수 종속되도록 릴레이션을 분해
a. 완전 함수 종속
⦁ 어떤 속성이 기본키에 대해 완전히 종속일 때
b. 부분 함수 종속
⦁ 어떤 속성이 기본키가 아닌 다른 속성에 종속되거나, 기본키가 여러 속성으로 구성되어 있을 경우 기본키를 구성하는 속성 중 일부만 종속될 때
4. 제 3 정규형
⦁ 릴레이션이 제 2 정규형에 속하고, 기본키가 아닌 모든 속성이 기본키에 이행적 함수 종속이 되지 않으면 제 3 정규형
a. 이행적 함수 종속
⦁ a ➔ b, b ➔ c인 경우 a ➔ c가 성립될 때
5. Example
a. db_study2_explain
CREATE DATABASE IF NOT EXISTS `db_study2_explain` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `db_study2_explain`;
-- MySQL dump 10.13 Distrib 8.0.32, for Win64 (x86_64)
--
-- Host: 127.0.0.1 Database: db_study2_explain
-- ------------------------------------------------------
-- Server version 8.0.32
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `course_mst`
--
DROP TABLE IF EXISTS `course_mst`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `course_mst` (
`course_id` int NOT NULL,
`student_id` int NOT NULL,
`lecture_id` int NOT NULL,
`instructor_id` int NOT NULL,
PRIMARY KEY (`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `course_mst`
--
LOCK TABLES `course_mst` WRITE;
/*!40000 ALTER TABLE `course_mst` DISABLE KEYS */;
INSERT INTO `course_mst` VALUES (1,1,1,1),(2,2,1,1),(3,3,2,2),(4,2,3,3);
/*!40000 ALTER TABLE `course_mst` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `instructor_mst`
--
DROP TABLE IF EXISTS `instructor_mst`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `instructor_mst` (
`instructor_id` int NOT NULL,
`instructor_name` varchar(45) NOT NULL,
`university_id` int NOT NULL,
PRIMARY KEY (`instructor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `instructor_mst`
--
LOCK TABLES `instructor_mst` WRITE;
/*!40000 ALTER TABLE `instructor_mst` DISABLE KEYS */;
INSERT INTO `instructor_mst` VALUES (1,'김준일',1),(2,'김준이',2),(3,'김준삼',3);
/*!40000 ALTER TABLE `instructor_mst` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `lecture_mst`
--
DROP TABLE IF EXISTS `lecture_mst`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `lecture_mst` (
`lecture_id` int NOT NULL,
`lecture_name` varchar(45) NOT NULL,
`lecture_price` int NOT NULL,
PRIMARY KEY (`lecture_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `lecture_mst`
--
LOCK TABLES `lecture_mst` WRITE;
/*!40000 ALTER TABLE `lecture_mst` DISABLE KEYS */;
INSERT INTO `lecture_mst` VALUES (1,'java',100000),(2,'python',80000),(3,'c',90000);
/*!40000 ALTER TABLE `lecture_mst` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `mentor_mst`
--
DROP TABLE IF EXISTS `mentor_mst`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `mentor_mst` (
`mentor_id` int NOT NULL,
`mentor_name` varchar(45) NOT NULL,
PRIMARY KEY (`mentor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `mentor_mst`
--
LOCK TABLES `mentor_mst` WRITE;
/*!40000 ALTER TABLE `mentor_mst` DISABLE KEYS */;
INSERT INTO `mentor_mst` VALUES (1,'문자영'),(2,'문성현');
/*!40000 ALTER TABLE `mentor_mst` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `student_mst`
--
DROP TABLE IF EXISTS `student_mst`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `student_mst` (
`student_id` int NOT NULL,
`student_name` varchar(45) NOT NULL,
`mentor_id` int NOT NULL,
PRIMARY KEY (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `student_mst`
--
LOCK TABLES `student_mst` WRITE;
/*!40000 ALTER TABLE `student_mst` DISABLE KEYS */;
INSERT INTO `student_mst` VALUES (1,'김동민',1),(2,'김두영',1),(3,'장진원',2);
/*!40000 ALTER TABLE `student_mst` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `university_mst`
--
DROP TABLE IF EXISTS `university_mst`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `university_mst` (
`university_id` int NOT NULL,
`university_name` varchar(45) NOT NULL,
PRIMARY KEY (`university_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `university_mst`
--
LOCK TABLES `university_mst` WRITE;
/*!40000 ALTER TABLE `university_mst` DISABLE KEYS */;
INSERT INTO `university_mst` VALUES (1,'서울대'),(2,'부산대'),(3,'경상대');
/*!40000 ALTER TABLE `university_mst` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Dumping events for database 'db_study2_explain'
--
--
-- Dumping routines for database 'db_study2_explain'
--
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2023-01-19 17:36:05
b. nomalization
insert into student_mst values (1, '김동민');
insert into student_mst values (2, '김두영');
insert into student_mst values (3, '장진원');
select * from student_mst;
insert into lecture_mst values (1, 'java', 100000);
insert into lecture_mst values (2, 'python', 80000);
insert into lecture_mst values (3, 'c', 90000);
select * from lecture_mst;
insert into instructor_mst values (1, '김준일', '서울대');
insert into instructor_mst values (2, '김준이', '부산대');
insert into instructor_mst values (3, '김준삼', '경상대');
select * from instructor_mst;
insert into course_mst values (1, 1, 1, 1);
insert into course_mst values (2, 2, 1, 1);
insert into course_mst values (3, 3, 2, 2);
insert into course_mst values (4, 2, 3, 3);
select * from course_mst;
insert into university_mst values (1, '서울대');
insert into university_mst values (2, '부산대');
insert into university_mst values (3, '경상대');
select * from university_mst;
insert into mentor_mst values (1, '문자영');
insert into mentor_mst values (2, '문성현');
select * from mentor_mst;
update mentor_mst
set mentor_name = '문자영'
where mentor_id = 1;
select * from mentor_mst;
select
course_id,
student_name,
mentor_name,
lecture_name,
lecture_price,
instructor_name,
university_name
from
course_mst cm
left outer join student_mst sm on (sm.student_id = cm.student_id)
left outer join mentor_mst mm on (mm.mentor_id = sm.mentor_id)
left outer join lecture_mst lm on (lm.lecture_id = cm.lecture_id)
left outer join instructor_mst im on (im.instructor_id = cm.instructor_id)
left outer join university_mst um on (um.university_id = im.university_id);
