The project was carried out using the concepts learned in the major class "DB Design and Implementation", and the final goal of the project is to interview real companies that need database systems, design databases based on their requirements, and derive several SQLs using the database. The number of participants in the project is 4, and I managed the overall progress as a team leader and was in charge of interviews with corporate officials & ERD design & SQL.
์ ๊ณต ์์
'DB ์ค๊ณ ๋ฐ ๊ตฌํ'์์ ๋ฐฐ์ ๋ ๊ฐ๋
๋ค์ ํ์ฉํ์ฌ ์งํํ ํ๋ก์ ํธ๋ก, ๋ฐ์ดํฐ๋ฒ ์ด์ค ์์คํ
์ ํ์๋ก ํ๋ ์ค์ ๊ธฐ์
์ ์ธํฐ๋ทฐํ๊ณ ๊ทธ๋ค์ ์๊ตฌ์ฌํญ์ ๊ธฐ๋ฐ์ผ๋ก ๋ฐ์ดํฐ๋ฒ ์ด์ค๋ฅผ
์ค๊ณํ๊ณ , ํด๋น ๋ฐ์ดํฐ๋ฒ ์ด์ค๋ฅผ ํ์ฉํ ๋ช๊ฐ์ง์ SQL์ ๋์ถํ๋ ๊ฒ์ด ํ๋ก์ ํธ ์ต์ข
๋ชฉํ์ด๋ค. ํ๋ก์ ํธ ์ฐธ์ฌ ์ธ์์ 4๋ช
์ด๋ฉฐ, ๋ณธ์ธ์ ํ์ฅ์ผ๋ก ์ ์ฒด์ ์ธ ์งํ ์ํฉ์ ๊ด๋ฆฌํ๊ณ , ๊ธฐ์
๊ด๊ณ์ ์ธํฐ๋ทฐ & ERD ์ค๊ณ & SQL ์ ๋ด๋นํ๋ค.
'A์ฌ(๊ธฐ์
๊ด๊ณ์๋ถ์ ์์ฒญ์ผ๋ก ๊ธฐ์
์ด๋ฆ์ ์ต๋ช
์ฒ๋ฆฌ)'๋ ๋ธ๋ ๋ฉ ํฐ๋ฅผ ํ๋งคํ๋ ์์ ๊ท๋ชจ์ ์คํํธ์
๊ธฐ์
์ด๋ค. ๋ธ๋ ๋ฉ ํฐ๋ ๊ธฐ๋ณธ์ ์ผ๋ก ์กด์ฌํ๋ ์ฐจ (e.g., ๋
น์ฐจ,์บ๋ชจ๋ง์ผ)์ ๋ค๋ฅธ ์ฐจ๋ ์ฌ๋ฃ๋ค์ ์์ด ํ์ํ ์๋ก์ด ํ๋ฏธ์ ์ฐจ์ธ๋ฐ, ์ด ๊ธฐ์
์ ํน์ด์ ์ ๋ธ๋ ๋ฉ ํฐ๋ฅผ ํ๊ตญ์ ๊ฑฐ์ฃผํ๊ณ ์๋ ์๋์ธ๋ค์ด ๋ง๋ ๋ค๋ ์ ์ด๋ค. A์ฌ๋ ๋ธ๋ ๋ฉ ํฐ์ ์๋ฃ๊ฐ ๋๋ ์ฌ๋ฃ๋ค์ ๊ณต๊ธ์
์ฒด๋ก๋ถํฐ ๊ตฌ๋นํ๊ณ , ์ด ์ฌ๋ฃ๋ฅผ๋ฅผ ๊ธฐ๋ฐ์ผ๋ก ์๋์ธ ๋ธ๋ ๋๋ค์ด ์ ์ํ ๋ธ๋ ๋ฉ ํฐ๋ฅผ ํ๋งคํ๋ฉฐ, ๋ธ๋ ๋์๊ฒ ์ธ์ผํฐ๋ธ๋ ๋ก์ดํฐ๋ฅผ ์ฃผ๋ ์์ผ๋ก ๋น์ฆ๋์ค๋ฅผ ํ๊ณ ์๋ค. ์๋๋ A์ฌ๊ฐ ์ฐ๋ฆฌ ๋ฐ์ดํฐ๋ฒ ์ด์ค ์ค๊ณ ํ์๊ฒ ์๊ตฌํ๋ Business rules & Requirements์ด๋ค.
-
๊ณ ๊ฐ(Customer)์ ์ ํ์ ๋นํ์, ์ ํ์(Regular), ๊ตฌ๋ ํ์(Subscriber)๋ก ๋๋๋ค. ๋นํ์์ ์ ํ์์ด๋ ๊ตฌ๋ ํ์ ๋ชจ๋์ ์ํ์ง ์๋ ๊ณ ๊ฐ์ด๋ฉฐ, ๊ตฌ๋ ํ์๊ณผ ์ ํ์์ ๋น์ฐํ ๊ฒธํ ์ ์๋ค. ๋ชจ๋ ๊ณ ๊ฐ์ ๊ณตํต์ ์ผ๋ก ID(unique identifier), Name, Address(์/๋/ํธ), PhoneNumber(์ฌ๋ฌ๊ฐ ์ฐ๋ฝ์ฒ ๊ธฐ์ฌ ๊ฐ๋ฅ), Gender, Birth Date์ ๋ก๊ทธ์ธ์ ํ์ํ Login ID, Login Password ์ ๋ณด๋ฅผ ์ ์ฅํ๋ค. ๋ฉ๋ชจ) ๋นํ์์ผ ๊ฒฝ์ฐ์ ์ด Login ID, Login Password๊ฐ Null์ด๋ ํด๋์ ํ ๋ฒํธ ๋ฑ์ผ๋ก ๋์ฒด๋ ์ ์๋ค. 1-1. ์ ํ์(Regular)์ ๊ฒฝ์ฐ ๊ตฌ๋งค ๋ฑ์ ํ๋์ ํ ๋๋ง๋ค ์์ด๊ฒ ๋ ๋ง์ผ๋ฆฌ์ง(ํฌ์ธํธ)๋ฅผ Point์ ์ ์ฅํ๊ฒ ๋๋ค. ๋นํ์์ ๊ฒฝ์ฐ ์ ๊ทํ์์ด ์๋๋ ์ด๋ฌํ ํํ์ ๋ฐ์ ์ ์๊ฒ ํด์ผ ํ๋ฉฐ, ๊ตฌ๋ ์์ ๊ฒฝ์ฐ ๋ถ๊ท์น์ ์ผ๋ก ๊ตฌ๋งค๊ธ์ก์ ๋น๋กํ์ฌ ํฌ์ธํธ๋ฅผ ๋ฐ๋ ์ ํ์๊ณผ ๋ฌ๋ฆฌ ๊พธ์คํ ์ ์ก์ ๋ก ๊ฒฐ์ ํ๋ฏ๋ก ๊ตฌ๋ ํ๋์ ์์ด ์ด๋ฌํ ํฌ์ธํธ๋ ์ ๊ณต๋์ง ์๊ฒ ํด๋ฌ๋ผ๋ ๊ฒ์ด A์ฌ์ ์๊ตฌ์ด๋ค.(๋ค๋ง ๊ตฌ๋ ํ์์ผ ์ง๋ผ๋ ์ผ๋ฐํ์์ผ๋ก์ ๋ณ๋ ๊ตฌ๋งค๋ฅผ ํ ๋์๋ ํฌ์ธํธ๊ฐ ์ ๋ฆฝ๋๋ค.) 1-2. ๊ตฌ๋ ํ์(SubScriber)์ ์ ๊ธฐ์ ์ธ ๊ฒฐ์ ๊ฐ ํ์ํ๊ธฐ ๋๋ฌธ์ ์ ์ฉ์นด๋ ๋ฒํธ(CreditCard Number)๋ฅผ ์ ์ฅํด์ผ ํ๋ค. ๊ฐ๊ฐ์ ๊ตฌ๋ ํ์์ ํ๋์ ๊ตฌ๋ ์ (Rate_Plan)๋ฅผ ์ ํํ์ฌ ๊ตฌ๋ ํ ์ ์์ผ๋ฉฐ, ๊ฐ๊ฐ์ ๊ตฌ๋ ์ ๋ ์ฌ๋ฌ ๊ตฌ๋ ํ์๋ค์๊ฒ ๊ตฌ๋ ๋์ด์ง ์ ์๋ค. Rate_Plan์ Plan_ID(Unique Identifier), ๊ตฌ๋ ์ ์ ์ด๋ฆ์ธ Plan Name, ๊ตฌ๋ ๊ฐ๊ฒฉ์ธ Price, ๋งค๋ฌ ์ ๊ณต๋๋ ์ฐจ์ ์๋์ธ Provided Count ์ ๋ณด๋ฅผ ์ ์ฅํด์ผ ํ๋ค. ๊ตฌ๋ ์ ํ ํ์๋ง์ด ๊ตฌ๋ ํ์ ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ์ ์ฅ์ด ๋๋ ๋ฐ๋ฉด, ๊ณ ๊ฐ์ ์ํด ๊ตฌ๋ ๋์ด ์ง์ง ์์ ๊ตฌ๋ ์ ๋๋ผ๋ ๋์ค์ ๋๊ตฐ๊ฐ์ ์ํด ๊ตฌ๋ ์ด ๋ ์ ์๊ธฐ์ ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ์ ์ฅ๋๋๋ก ํด์ผํ๋ค.
-
A์ฌ๋ ๊ฐ๊ฐ์ ๊ณ ๊ฐ์ ์ํด ๋ฐ์ํ ์ฃผ๋ฌธ(Order) ์ ๋ณด๋ฅผ ์ ์ฅํ๊ธธ ์ํ๋ค. ์ฃผ๋ฌธ์๋ Order ID(Unique Identifier)์ ์ธ์ ์ฃผ๋ฌธ์ด ์ผ์ด๋ฌ๋์ง์ ๋ํ Order Date๊ฐ ์ ์ฅ๋๋ค. ๊ฐ๊ฐ์ ๊ณ ๊ฐ์ ์ฌ๋ฌ๊ฐ์ ์ฃผ๋ฌธ์ ํ ์ ์์ผ๋ฉฐ, ๊ฐ๊ฐ์ ์ฃผ๋ฌธ์ ํ๋ช ์ ๊ณ ๊ฐ์ ์ํด ์ผ์ด๋๋ค. ๊ณ ๊ฐ์ ์ํด ๋ฐ์ ๋ ์ฃผ๋ฌธ๋ง์ด ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ์ ์ฅ๋์ด์ง์ ์๋ ๋ฐ๋ฉด, ์ฃผ๋ฌธ์ ํ์ง ์์ ๊ณ ๊ฐ์ด๋ผ๋ ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ์ ์ฅ๋ ์ ์๋ค.
-
A์ฌ๋ ๋ธ๋ ๋ฉ ํฐ(Blending Tea)์ ๋ํด Tea ID, Name, Stock(์ฌ๊ณ ์๋), Price(๊ฐ๊ฒฉ)๋ฅผ ์ ์ฅํ๊ธธ ์ํ๋ฉฐ, ๊ฐ๊ฐ์ ๋ธ๋ ๋ฉ ํฐ๋ ์ฌ๋ฌ ์ฃผ๋ฌธ(Order)์ ์ฃผ๋ฌธ(orderred)๋์ด์ง์ ์๊ณ (๊ฐ๊ฐ์ ์ฃผ๋ฌธ์ ์ฌ๋ฌ ๋ธ๋ ๋ฉ ํฐ๋ฅผ ์ฃผ๋ฌธ(orderred)ํ ์ ์๋ค๋ ๋ง์ ์๋ฏธ๋ ๊ฐ๊ฐ์ ์ฃผ๋ฌธ ๋ฐ์ดํฐ์๋ ์ฌ๋ฌ ๋ธ๋ ๋ฉ ํฐ๋ฅผ ๋ด์ ์ ์๋ค๋ ์๋ฏธ๋ก ๋ณด๋๊ฒ์ด ์ดํด๊ฐ ์ฝ๋ค.) , ๊ฐ๊ฐ์ ์ฃผ๋ฌธ์ ์ฌ๋ฌ ๋ธ๋ ๋ฉ ํฐ๋ฅผ ์ฃผ๋ฌธํ ์ ์๋ค. Blending Tea๋ฅผ ์ฃผ๋ฌธํ Order๋ง์ด ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ์ ์ฅ๋๋ ๋ฐ๋ฉด, ์ฃผ๋ฌธ์ด ๋์ด์ง์ง ์์ Blending Tea์ผ์ง๋ผ๋ ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ์ ์ฅ๋์ด์ง ์ ์๋ค. ๋ํ ๋ธ๋ ๋ฉ ํฐ๊ฐ ์ฃผ๋ฌธ ๋์ด์ง๋, ์ฃผ๋ฌธ๋ ์๋(Quantity)๊ฐ ์ ์ฅ๋๋ค.
-
๋ธ๋ ๋ฉ ํฐ๋ ๊ธฐ๋ณธ์ด ๋์ด์ง๋ ์ฐจ์ ๋ค๋ฅธ์ฐจ๋ ์ฌ๋ฃ๋ค์ ์์ด ์ ์์ด ๋์ด์ง๋๋ฐ, A์ฌ๋ ์ด๋ฌํ ๊ธฐ๋ณธ์ด ๋์ด์ง๋ ์ฐจ๋ ์ฌ๋ฃ๋ค์ Material์ ์ ์ฅํ๊ณ ์ถ์ดํ๋ค. Material์๋ ๋ธ๋ ๋ฉ ํฐ์ ๋น์ทํ๊ฒ Material ID(Unique Identifier), Name, Stock, Price ์ ๋ณด๊ฐ ์ ์ฅ๋๋ค. ๊ฐ๊ฐ์ ๋ธ๋ ๋ฉ ํฐ๋ ์ฌ๋ฌ Material์ ์ด์ฉํ์ฌ ์ ์๋์ด์ง๊ณ , ๊ฐ๊ฐ์ Material ๋ํ ์ฌ๋ฌ Blending Tea์ ์ ์์ ์ฐ์ผ ์ ์๋ค. ์์ ๋งํ๋ฏ Blending Tea๋ ๊ธฐ์กด์ ์กด์ฌํ๋ ์ฐจ์ ๋ค๋ฅธ ์ฐจ๋ ์ฌ๋ฃ๋ค์ ์ถ๊ฐํ์ฌ ๋ง๋ค์ด์ง ๊ฒ์ด๋ฏ๋ก ๋ฐ๋์ Material ์ผ๋ก ์ ์๋์ด์ผ ํ๋ ๋ฐ๋ฉด, ์์ง Blending Tea์ ์ ์์ ์ฐ์ด์ง ์์ Material์ด๋ผ๋ ๋์ค์ ์๋ก์ด ์ข ๋ฅ์ Blending Tea์ ์ ์ ๋ฑ์ ์ฐ์ผ ์ ์๊ธฐ ๋๋ฌธ์ ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ์กด์ฌํ ์ ์์ด์ผ ํ๋ค. ์ถ๊ฐ์ ์ผ๋ก, Material์ด Blending Tea์ ์ ์์ ์ด์ฉ๋ ๋, ์ผ๋งํผ์ ์(Amount)์ด ๋ค์ด๊ฐ๋์ง ํ์ํ๋ค. ์ฐธ๊ณ ) A์ฌ๋ Material์ ๋ํด ๊ณต๊ธ์๊ฐ ์ ์ํ๋ ๊ฐ๊ฒฉ์ด ์๋ A์ฌ๊ฐ ํฌ๋งํ๋ ๊ฐ๊ฒฉ์ ๋ง์ถฐ์ฃผ๋ ๊ณต๊ธ์๋ค๋ก๋ถํฐ ๊ณ์ฝ์ ์ฒด๊ฒฐํ์ฌ Material์ ๊ณต๊ธ๋ฐ๊ณ ์๋ค. ์ด๋ ์ฐ๋ฆฌ ํ์ด ERD ์ค๊ณ์ ์ฌ๋ฃ์ ๊ฐ๊ฒฉ์ ๋ํ ๋ฐ์ดํฐ๋ฅผ ๋ด๊ณ ์๋ Price์ ํธ๋ฆฌ๋ทฐํธ๋ฅผ Material ๋ฆด๋ ์ด์ ํ์ ์ชฝ์ ์ค์ ํ ์ด์ ์ด๋ค.
-
๊ฐ๊ฐ์ Material์ ๊ณต๊ธ์ ์ฒด(Supplier)์ ์ํด ๊ณต๊ธ๋๋๋ฐ, ๊ณต๊ธ์ ์ฒด๋ Supply ID(Unique Identifier), Name, Address(์/๋/ํธ), PhoneNumber ์ ์ ๋ณด๋ฅผ ๊ฐ์ง๊ณ ์์ผ๋ฉฐ, ๊ฐ๊ฐ์ Supplier๋ ์ฌ๋ฌ Material์ ๊ณต๊ธํ ์ ์๊ณ , ๊ฐ๊ฐ์ Material ๋ํ ์ฌ๋ฌ Supplier์ ์ํด ๊ณต๊ธ๋ ์ ์๋ค. ์์ง Supplier์ ์ํด ๊ณต๊ธ๋ ์ ์ด ์๋ Material๋ ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ์กด์ฌํ ์ ์์ง๋ง, Material์ ํ๋ฒ์ด๋ผ๋ ๊ณต๊ธํ ์ ์ด ์๋ Supplier๋ง์ด ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ์ ์ฅ๋๋ค. ๊ณต๊ธ์ด ์ด๋ค์ง๋ ๋ ์ง์ ๊ณต๊ธ์๋์ ๋ํ ์ ๋ณด๊ฐ ๊ธฐ๋ก๋๋ค. ๋ฉ๋ชจ) Supplier์ Material์ Realtionship Type์ ๊ธฐ๋ก๋ Date๋ ํ์ Relational Model๋ก ๋ณํํ ๋ ์๋ก ์์ฑ๋ ๋ฆด๋ ์ด์ ์ ์์์ ์ผ๋ก Primary key์ ์ผ๋ถ๋ก ์ค์ ํ์ฌ Material ID + Supply ID + Date์ ํ์์ผ๋ก ๊ธฐ๋ณธํค๋ฅผ ์ด๋ฃจ๊ฒ ํ์ฌ ๊ฐ๊ฐ์ ๊ณต๊ธ์ ๋ํ ์ ๋ณด๋ฅผ ๊ธฐ๋กํ ์ ์๋๋ก ํ์ฉํ ๊ณํ์ด๋ค.
-
Blending Tea๋ Blender์ ์ํด ๋ง๋ค์ด์ง๋ค. Blender์๋ Blender ID(Unique Identifier), Name, Address(์/๋/ํธ), PhoneNumber ๋ฅผ ์ ์ฅํ๋ค. Blender ์ค์๋ Blending Tea๋ฅผ ๋ง๋ค๊ธฐ ์ ์ A์ฌ์ ๊ณ์ฝ์ ํ๋ ๊ฒฝ์ฐ๋ ์๊ธฐ ๋๋ฌธ์ ์์ง ๋ง๋ Blending Tea๊ฐ ์๋ Blender๋ ์ ์ฅ๋ ์ ์์ง๋ง Blending Tea์ ๊ฒฝ์ฐ Blender๊ฐ ๋ง๋๋ ๊ฒ์ด๊ธฐ์ ๋ฐ๋์ ํด๋น ํฐ๋ฅผ ๋ง๋ Blender๊ฐ ์กด์ฌํด์ผ๋ง ํ๋ค. ์ด ๋, A์ฌ๋ Blender์๊ฒ Blending Tea๋ฅผ ๋ง๋ค์ด์ ํ ๋ ๋ง๋ค Blender์๊ฒ ์ผ์ ๋์ Royalty๋ฅผ ์ง๊ธํ๋ค.
-
๊ฐ๊ฐ์ Blender๋ ์์ ์ ๊ด๋ฆฌํ๋ Employee 1๋ช ์ ๋ฐฐ์ ๋ฐ์ ๊ด๋ฆฌ๋๋ค. Employee์ ๋ํ ์ ๋ณด๋ Employee ID(Unique Identifier), Name, Address(์/๋/ํธ), PhoneNumber ๊ทธ๋ฆฌ๊ณ Salary๊ฐ ํ์ํ๋ค. Employee๋ ์ฌ๋ฌ๋ช ์ Blender๋ฅผ ๊ด๋ฆฌํ ์ ์๋ค. ๋ํ A์ฌ์์๋ ์ฌ์ - ๋ถ์ฌ์๊ฐ์ 1๋1 ์ฌ๋ด๊ต์ก์ ์ค์ํ๊ณ ์๋ค. ๋ณดํต ์ฌ์์ ๋ถ์ฌ์์ ๊ฒฝ์ฐ ์ง์๊ฐ ์ฌ์๊ณผ ์ฃผ์ ์ ๋์ผ ํ๋ฅ ์ด ๋๊ณ , ์ฌ์๋ ๋ถ์ฌ์๊ฐ ์๋ ๊ณ ์ฐธ ์ง์๋ค์ด๋, ๊ต์ก์ด ๋ฑํ ํ์ ์๋ ๋ถ์์ ์์๋ ์ง์ ๋ฑ์ ์ด ์ฌ๋ด๊ต์ก์ ์ฐธ์ฌํ์ง ์์ ์ ์๋ค๋ ๋น์ฆ๋์ค ๋ฃฐ์ ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ๋ฐ์ํด์ผ ํ๋ค. ํ์ฌ์์๋ ์ฌ๋ด๊ต์ก์ด ์ด๋ฃจ์ด์ง ๋ ์ด์ ๋ํ ๊ฒฉ๋ ค์ ์๋ฏธ๋ก ๊ต์ก๊ธฐ๊ฐ์ ๋ฐ๋ผ Incentive๋ฅผ ์ฐจ๋ฑ์ง๊ธํ๋๋ฐ ์ด๋ฅผ ์ํด ๊ฐ๋ฅด์น๋ ๋ ์ง์ ๋๋ ๋ ์ง๋ฅผ ๊ธฐ๋กํ๋ค(Start Date, End Date).
-
A์ฌ์๋ ์ฌ๋ฌ๊ฐ์ง์ Department๊ฐ ์๊ณ ์ด๋ฅผ ์ ์ฅํ๋๋ฐ ๊ฐ๊ฐ์ DepartmentID(Unique Identifier), Name, PhoneNumber, FAXNumber, Location์ ์ ์ฅํ๋ค. ๊ฐ๊ฐ์ Department์๋ ์ต์ ํ๋ช ์์ ์ฌ๋ฌ Employee๊ฐ ์ํ ์ ์์ผ๋ฉฐ, ๋ชจ๋ Employee๋ ๋ฐ๋์ 1๊ฐ์ ๋ถ์์(๋ง) ์ํด์ผ ํ๋ค.
-
A์ฌ๋ ์๋ ๋ฌธํ๊ถ์ ๋ํ ์ดํด๋ฅผ ๋์ด๊ณ ์ ์ฌ์๋ค์๊ฒ ๋ฐ๋์ 1๊ฐ์ด์์ Lecture๋ฅผ ๋ค์ ๊ฒ์ ๊ท์น์ผ๋ก ๋๊ณ ์๋ค. Lecture๋ Lecture ID(Unique Identifier), Name, TutorName, Time, Place์ ๋ฐ์ดํฐ๋ฅผ ์ ์ฅํด์ผ ํ๋ฉฐ, ๊ฐ๊ฐ์ Employee๋ ์ฌ๋ฌ ๊ฐ์ ๊ฐ์๋ฅผ ๋ค์ ์ ์๊ณ ๊ฐ๊ฐ์ ๊ฐ์๋ ์ฌ๋ฌ๋ช ์ด ๋ค์ ์ ์๋ค. ๋ํ ๊ทธ ๋๊ตฌ์ Employee๋ ์๊ฐํ์ง ์์ Lecture๋ผ๋ ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ์กด์ฌํ ์ ์๋ค. ๊ฐ์๋ฅผ ๋ค์ผ๋ฉด Employee๊ฐ Lecture๋ฅผ ๋ค์ ๋ ์ง์ Lecture๊ฐ ๋๋ ์ดํ์ ํด์ฆ๋ฅผ ์ค์ํ์ฌ ๊ทธ ์ฑ์ ์ ๊ธฐ๋กํ๋ค.
Department(Department_ID, DepartmentName, DepartmentPhoneNum, FAXNumber, Location)
Employee(Employee_ID, EmployeeName, Si, Dong, Ho, PhoneNumber, Salary, Department_ID, Mentor_ID, StartDate, EndDate, Incentive)
FOREIGN KEY (Department_ID) REFERENCES Department(Department_ID)
FOREIGN KEY (Mentor_ID) REFERENCES Employee(Employee_ID)
Lecture(Lecture_ID, LectureName, TutorName, Time, Place)
Lecture_Taken(Employee_ID, Lecture_ID, Date, Grade)
FOREIGN KEY (Employee_ID) REFERENCES Employee(Employee_ID)
FOREIGN KEY (Lecture_ID) REFERENCES Lecture(Lecture_ID)
Blender(Blender_ID, Name, Age, Si, Dong, Ho, PhoneNumber, Gender, Employee_ID)
FOREIGN KEY (Employee_ID) REFERENCES Employee(Employee_ID)
BlendingTea(Tea_ID, Name, Stock, Price, Blender_ID, Royalty)
FOREIGN KEY (Blender_ID) REFERENCES Blender(Blender_ID)
Material(Material_ID, Name, Stock, Price)
BlendingTea_Manufactured(Tea_ID, Material_ID, Amount)
FOREIGN KEY (Tea_ID) REFERENCES BlendingTea (Tea_ID)
FOREIGN KEY (Material_ID) REFERENCES Material(Material_ID)
Supplier(Supplier_ID, Name, Si, Dong, Ho, PhoneNumber)
Material_Supplied(Material_ID, Supplier_ID, Quantity, Date)
FOREIGN KEY (Material_ID) REFERENCES Material(Material_ID)
FOREIGN KEY (Supplier_ID) REFERENCES Supplier(Supplier_ID)
Customer(Customer_ID, Name, Si, Dong, Ho, LoginID, LoginPassword, Gender, BirthDate)
Customer_PhoneNumber(Customer_ID, PhoneNumber)
FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID)
Regular(Customer_ID, Point)
FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID)
Rate_Plan(Plan_ID, PlanName, Price, ProvidedCount)
Subscriber(Customer_ID, CreditCardNumber, Plan_ID, SubscribeDate)
FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID)
FOREIGN KEY (Plan_ID) REFERENCES Rate_Plan(Plan_ID)
Order(Order_ID, OrderDate, Customer_ID)
FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID)
BlendingTea_Ordered(Tea_ID, Order_ID, Quantity)
FOREIGN KEY (Tea_ID) REFERENCES BlendingTea(Tea_ID)
FOREIGN KEY (Order_ID) REFERENCES Order(Order_ID)
- ์ ๊ทํ Check
Relation์ ๋ชจ๋ Attribute๊ฐ Atomic ํ๋ฏ๋ก : 1NF ์ถฉ์กฑ
Every non-primary-key attribute is FULLY functionally dependent on the primary key ์ฆ, Partial Functional Dependecy ๊ฐ ์์ผ๋ฏ๋ก : 2NF ์ถฉ์กฑ
Transitive Functional Dependency ๊ฐ ์์ผ๋ฏ๋ก : 3NF ์ถฉ์กฑ
์ด ๋จ๊ณ์์ Denormalization์ ํ์ง ์๊ธฐ๋ก ๊ฒฐ์ . Data dictionary๋ Data Dictionary.pdf ํ์ผ ์ฐธ์กฐ
Create&Insert.sql ํ์ผ ์ฐธ์กฐ
1.์ด๋ค ๊ณ ๊ฐ์ด O0012 ์ฃผ๋ฌธ์ ํ๋ค๊ณ ๊ฐ์ ํ๋ฉด, ์๋์ ๊ฐ์ SQL์ด ๋์ํ์ฌ ๋ธ๋ ๋ฉ ํฐ์ ์ฌ๊ณ ์ํ๊ฐ ๊ฐฑ์ ๋๋ค.
UPDATE
(
SELECT stock,quantity
FROM blendingtea, blendingtea_ordered, ordertable
WHERE blendingtea.tea_id = blendingtea_ordered.tea_id
AND blendingtea_ordered.order_id = ordertable.order_id
AND ordertable.order_id = 'O0012'
)
SET stock = stock - quantity;
์ค๋ช
: O0012 ์ฃผ๋ฌธ์ ๋ํ ์ฃผ๋ฌธ์๋(quantity) ๋งํผ blending tea์ ์ฌ๊ณ (stock)๊ฐ ๋น ์ ธ์ผ ํ๋ฏ๋ก,
์กฐ์ธ์ ํตํด ์ด๋ค ๋ธ๋ ๋ฉ ํฐ(tea_id)๊ฐ ์ด๋ค ์ฃผ๋ฌธ(order_id)์ ์ฌ์ฉ๋์๋์ง ์ ๋ณด๋ฅผ ํ์
ํด์ผ ํ๋ค. ๋ฐ๋ผ์ ํ
์ด๋ธ๋ค์ ์์ ๊ฐ์ด ์กฐ์ธํด์ฃผ๊ณ , blendingtea_orderred์ ์ฃผ๋ฌธ๋ quantity๋งํผ blendingtea์ ์ฌ๊ณ ์ธ stock์ ๋นผ์ฃผ๊ณ ์ด๋ฅผ ๊ฐฑ์ ํด์ฃผ๋ฉด ์ฌ๊ณ ๊ฐฑ์ ์ด ๋๋๊ฒ์ด๋ค. ํด๋น SQL๋ฌธ์ ์คํ์ํค๋ฉด 200๊ฐ์ด๋ T0014์ ์ฌ๊ณ ๊ฐ O0012์ ์ฃผ๋ฌธ์๋์ธ 88๊ฐ๋งํผ ๋น ์ ธ์ 112๊ฐ๊ฐ ๋ ๊ฒ์ ํ์ธํ ์ ์๋ค.
2.๊ณต๊ธ์
์ฒด๊ฐ ์์ฌ๋ฃ๋ฅผ ๊ณต๊ธํ ๋ ์๋์ ๊ฐ์ SQL์ ํตํด ์ฌ๊ณ ์ ๋ณด๋ฅผ ์
๋ฐ์ดํธํ๋ค.
๊ณต๊ธ์ S0012๊ฐ ์์ฌ๋ฃ M0005๋ฅผ ๊ณต๊ธํ๋ค๊ณ ๊ฐ์ ํ๋ฉด, ์๋์ ๊ฐ์ SQL์ด ๋์ํ๋ค.
UPDATE
(
SELECT stock, quantity
FROM material, material_supplied, supplier
WHERE material.material_id = material_supplied.material_id
AND material_supplied.supplier_id = supplier.supplier_id
AND supplier.supplier_id = 'S0012'
AND material.material_id = 'M0005'
)
SET stock = stock+quantity;
์ค๋ช
: ๊ณต๊ธ์ ๋ฐ์ผ๋ฉด ๋ฐ์ ๋งํผ ์ฌ๊ณ ๊ฐ ๋์ด๋์ผ ํ๋ค. ์ SQL์ ์ํฉ์์ S0012 id๋ฅผ ๊ฐ์ง ๊ณต๊ธ์(Supplier)๋ก๋ถํฐ M0005 id๋ฅผ ๊ฐ์ง ์์ฌ๋ฃ(Material)๋ฅผ ๊ณต๊ธ๋ฐ๋ ์ํฉ์ ๊ฐ์ ํ๋ค. ์ฌ๊ณ ๋ฅผ ๊ฐฑ์ ํ๋ ค๋ฉด ์ฐ์ ์กฐ์ธ์ ํตํด ์ด๋ค ๊ณต๊ธ์(supplier_id)๊ฐ ์ด๋ค ์์ฌ๋ฃ(material_id)๋ฅผ ๊ณต๊ธํ๋์ง ์ ๋ณด๋ฅผ ํ์
ํด์ผ ํ๋ค. ๋ฐ๋ผ์ ์กฐ์ธ์ ํตํด ์ด๋ฅผ ์ถ์ ํ์ฌ material_supplied๋ก ์ฐ๊ฒฐํด์ฃผ๊ณ , material_supplied์ quantity๋งํผ material์ stock์ ๋ํด์ฃผ๋ฉด ์์ฌ๋ฃ์ ์ฌ๊ณ ๊ฐฑ์ ์ด ์๋ฃ๋๋ค.
3. ๊ฐ์ฅ ๊ฐ์ฅ ๋ง์ด ํ๋งค๋ ๋ธ๋ ๋ฉ ํฐ๋ฅผ ํ์ธํ๊ธฐ ์ํ์ฌ ์๋์ ๊ฐ์ ์ฟผ๋ฆฌ๋ฅผ ์ฌ์ฉํ๋ค.
SELECT tea_id, tea_name, sell
FROM (SELECT tea_id, SUM(quantity) as sell from blendingtea_ordered GROUP BY tea_id) NATURAL JOIN blendingtea
WHERE sell = (SELECT MAX(sell) FROM (SELECT tea_id,SUM(quantity) as sell from blendingtea_ordered GROUP BY tea_id));
์ค๋ช
: ๊ฐ์ฅ ์ ํ๋ฆฌ๋ ์ฐจ์ ๊ทธ ์ฐจ์ ํ๋งค๋์ ์์๋ณด๊ธฐ ์ํด ํ์ฌ ordertable์ ์ฌ๋ผ์จ ์ฃผ๋ฌธ์์ ํ๋งค๋ ์ฐจ๋ณ๋ก ํ๋งค๋ ์์ ํฉ์ ๊ตฌํ๊ณ ์ด ์ค ํ๋งค๋์ด ๊ฐ์ฅ ํฐ ์ฐจ์ ID์ ์ด๋ฆ, ํ๋งค๋์ ๋ถ๋ฌ์จ๋ค.
4. ๊ฐ๊ฐ์ ๋ธ๋๋๊ฐ ๋ง๋ ๋ธ๋๋ฉ ํฐ์ ๊ฐ์/ํ๋งค๋์ ํฉ์ ๋ณด์ฌ์ฃผ๋ ๋ทฐ
CREATE OR REPLACE VIEW made
AS SELECT blender_id,
COUNT(*) as maded,
SUM(quantity) as sales_rate
FROM blendingtea, blendingtea_ordered
WHERE blendingtea.tea_id = blendingtea_ordered.tea_id GROUP BY blender_id;
์ค๋ช
: ํ์ฌ์ ์ํด์๋ ๋ธ๋๋๊ฐ ๋ช๊ฐ์ ๋ธ๋๋ฉ ํฐ๋ฅผ ๋ง๋ค์๋์ง๋ฅผ ๋ณด์ฌ์ฃผ๊ณ ๊ฐ๊ฐ์ ๋ง๋ค์ด์ง ํฐ๋ค์ ํ๋งค๋์ ํฉ์ ๋ํ๋ด๋ ๋ทฐ์ด๋ค, OR REPLACE ๋ฅผ ๋ช
์ํ์๊ธฐ ๋๋ฌธ์ ํ๋งค๋์ด๋ ์๋ก์ด ๋ธ๋๋ฉ ํฐ๊ฐ ๊ฐ๋ฐ์ด ๋๋ฉด ํด๋น ์ฟผ๋ฆฌ๋ฅผ ์คํ์ํค๋ฉด ์
๋ฐ์ดํธ๋ ๊ฐ๋ฅํ๋ค.
5. ๊ณ ๊ฐ์ด ์์ ์ ๊ตฌ๋งค ๋ด์ญ์ ํ์ธํ๊ณ ์ถ์ ๊ฒฝ์ฐ ๊ณ ๊ฐ์๊ฒ ์๋์ VIEW๋ฅผ ์ ๊ณตํ๋ค. View์๋ ์์ ์ด ์ธ์ ์ด๋ค ์ฃผ๋ฌธ์ ํ๊ณ , ๊ทธ ์ฃผ๋ฌธ์์ ์ด๋ค ์ฐจ๋ฅผ, ๋ช๊ฐ๋ฅผ ์์ผฐ๊ณ ์ด ์ผ๋ง๋ฅผ ๊ฒฐ์ ํ๋์ง(๋ธ๋ ๋ฉ ํฐ์ ๊ฐ๋น ๊ฐ๊ฒฉ * ์ฃผ๋ฌธ ์๋) ํ์ธํ ์ ์๋ค. - ์๋์ ์ํฉ์ C0001 ๊ณ ๊ฐ์ด ์์ ์ ๊ตฌ๋งค ๋ด์ญ์ ์กฐํํ๋ ์ํฉ์ด๋ค.
CREATE OR REPLACE VIEW purchase_history
AS SELECT ordertable.order_id, order_date, blendingtea.tea_name, blendingtea_ordered.quantity, blendingtea_ordered.quantity * blendingtea.price as purchase_price
FROM ordertable,blendingtea_ordered,blendingtea
WHERE ordertable.order_id = blendingtea_ordered.order_id
AND blendingtea_ordered.tea_id = blendingtea.tea_id
AND ordertable.customer_id = 'C0001';
์ค๋ช
: OR REPLACE VIEW๋ก ๋ง๋ค์ด์ ๊ธฐ์กด์ ๊ฐ์ ์ด๋ฆ์ VIEW๊ฐ ์๋๋ผ๋ ์๋กญ๊ฒ ๊ทธ๋ ๊ทธ๋ ๋ทฐ๋ฅผ ์์ฑํ ์ ์๊ฒ ํ์๋ค(๊ทธ๋ฅ CREATE VIEW๋ฅผ ์ฌ์ฉํ๋ฉด ๊ธฐ์กด์ purchase_history ๋ทฐ๋ฅผ ์์ฑํ์ ๊ฒฝ์ฐ ์๋กญ๊ฒ ๋ทฐ๋ฅผ ๊ฐฑ์ ํ์ง ๋ชปํ๋ค.) ํด๋น ์ฃผ๋ฌธ์ ๋ํด(ordertable.order_id) ์ด๋ค ๋ธ๋ ๋ฉ ํฐ๊ฐ ์ฃผ๋ฌธ๋์ด์ก๋์ง ํ์ธํ๊ธฐ ์ํด blendingtea_ordered ํ
์ด๋ธ๊ณผ ์กฐ์ธํด ์ฃผ์๊ณ , ํด๋น ๋ธ๋ ๋ฉ ํฐ์ ๊ฐ๋น ๊ฐ๊ฒฉ์ ์์์ผ ํ๊ธฐ์ ๊ฐ๊ฒฉ ์ ๋ณด๊ฐ ์ ์ฅ๋์ด์ง blendingtea ํ
์ด๋ธ๊ณผ ์กฐ์ธํด ์ฃผ์๋ค.
- ๊ตฌ๋
์(Subscriber)์ ๊ฒฝ์ฐ ์๋์ SQL๊ณผ ๊ฐ์ด ์ ๊ธฐ์ ์ผ๋ก ordertable๊ณผ blendingtea_ordered ์ ์๋ก์ด ์ฃผ๋ฌธ์ด rate_plan์ ๋ช
์๋ ๊ฐ์์ ๊ณ ๊ฐ์ด ์ ํํ ์ฐจ๋ก insert ์์ผ์ค๋ค.
INSERT INTO ordertable (order_id,order_date,customer_id) select 'O0019', subscribe_date,customer_id from subscriber NATURAL JOIN rate_plan where customer_id='C0012' ;
INSERT INTO blendingtea_ordered (tea_id,order_id,quantity) select 'T0009', order_id,provided_amount from subscriber NATURAL JOIN rate_plan natural join ordertable where order_id='O0019';
์ค๋ช
: rate_plan ํ
์ด๋ธ์ provied amount๋ ํด๋น ๊ตฌ๋
์ ๋ฅผ ๊ฒฐ์ ํ์ ๋ ๋ฌ๋ง๋ค ๋ช๊ฐ์ ์ฐจ๊ฐ ๊ณ ๊ฐ์๊ฒ ๋ฐฐ์ก๋๋์ง์ ์์ด๋ค.
๋ง์ผ ๊ตฌ๋
์ ๋ก ํด๋น ๊ธฐ์
์ ์๋น์ค๋ฅผ ์ด์ฉํ๋ C0012 ๊ณ ๊ฐ์ด ์ฐจ ID๊ฐ T0009์ธ ์ํ์ ์ ๊ธฐ์ ์ผ๋ก ๋ฐ๊ธฐ๋ก ์ ํํ๋ค๊ณ ๊ฐ์ ํ๋ฉด ๊ณ ๊ฐ์ด ๊ตฌ๋
ํ ๊ตฌ๋
์ ์์ ์ ๊ณต๋๋ provided amount ๋งํผ ํด๋น ์ฐจ๊ฐ ํ๋ฌ์ ํ๋ฒ์ฉ ๊ณ ๊ฐ์๊ฒ ๋ฐฐ์ก๋ ๊ฒ์ด๋ค. ์ด๋ provided amount ๋งํผ์ ์ฐจ ์ฃผ๋ฌธ์ด ordertable์ ๋ค์ด๊ฐ๋ ๊ฒ๊ณผ ๊ฐ๊ธฐ์ ์์ ๊ฐ์ SQL์ด ํ์ํ ๊ฒ์ด๋ค.
subscriber๊ฐ ์ ๊ธฐ ๊ตฌ๋
์ด ๋งค๋ฌ ์
๋ฐ์ดํธ๊ฐ ๋ ๋ ๋ง๋ค order table๊ณผ blendingtea_ordered ํ
์ด๋ธ์ ์๋ก์ด ์ฃผ๋ฌธ์ ๋ฃ์ด์ ๊ธฐ๋ก์ ๋จ๊ธด๋ค. ์ ์ฟผ๋ฆฌ๋ C0012 ๊ณ ๊ฐ์ด ์
๋ฐ์ดํธ๊ฐ ๋์์ ๋ ๋ง์ง๋ง ์ฃผ๋ฌธ์ธ O0018๋ค์ O0019์ ์ถ๊ฐ ํ๋ฉด์ blendingtea_ordered ์๋ ๊ณ ๊ฐ์ด ์ง์ ํ T0009๊ฐ ํด๋น rate_plan์ ์ ํ์๋ provided amount๋งํผ ์ฃผ๋ฌธ๊ธฐ๋ก์ด ์๋์ผ๋ก ์ถ๊ฐ๋๋ค. ์ฆ, ์ด ๊ณ ๊ฐ์ ๊ฒฝ์ฐ ๋งค๋ฌ 30๊ฐ์ ์ฐจ๊ฐ ์ ๊ณต๋๋ Basic Plan์ ์ฑํํ ๊ณ ๊ฐ์ด๊ธฐ์ 30๊ฐ์ ์๋๋งํผ order๊ฐ ๋ฐ์ฃผ๋๋ ๊ฒ์ด๋ค.
- ๋ธ๋ ๋์ ์ฐ๋ น๋(ํ๊ท )๋ณ ๋ฐ๋ ๋ก์ดํฐ๋ฅผ ์ค๋ฆ์ฐจ์์ผ๋ก ๋ํ๋ธ๋ค.
SELECT royalty, AVG(blender.age) as Average_age
FROM blender, blendingtea
WHERE blender.blender_id = blendingtea.blender_id
GROUP BY royalty
ORDER BY AVG(blender.age);
- ๋ธ๋ ๋์ ๋์ด๊ฐ 30์ดํ์ ์ฑ๋ณ์ ๋ฐ๋ผ ๊ฐ๋ฐ๋๋ ์ฐจ์ ์ด๋ฆ์ ์ค๋ฆ์ฐจ์์ผ๋ก ๋ํ๋ธ๋ค. ์ฐจ๋ฅผ ๊ฐ๋ฐํ๋ ์ฌ๋์ ๋์ด์ ์ฑ๋ณ์ ๋ฐ๋ผ ์ด๋ค ์ข
๋ฅ์ ํฐ๋ฅผ ๋ง๋๋์ง ํ์
ํ ์ ์๋ค.
SELECT age,gender, tea_name
FROM blender, blendingtea
WHERE blender.blender_id = blendingtea.blender_id and age<30
GROUP BY age, gender, tea_name
ORDER BY tea_name;
- ๊ณ ๊ฐ์ด ๊ตฌ๋
ํ ์๊ธ์ ์ ์ด๋ฆ, ๊ฐ๊ฒฉ, ์ ๊ณต๋๋ ์ฐจ์ ๊ฐ์๋ฅผ ๋น์ผ ๊ฐ๊ฒฉ์์ผ๋ก ๋ณด์ฌ์ค๋ค.
SELECT plan_name, price, provided_amount
FROM rate_plan
ORDER BY price DESC;
- A๋ฅผ ๋ง์ด ๋ฐ์ ์ง์ ์์ผ๋ก ์ ๋ ฌํ์ฌ ์ฐ์ ์ง์์ ์์ํ๊ธฐ ์ํด ์๋์ ๊ฐ์ด
๊ฐ์๋ฅผ ์๊ฐํ ์ง์ ์ค ์ฑ์ A๋ฅผ ๋ฐ์ ์ง์์ ์ด๋ฆ, ์ ํ๋ฒํธ, A๋ฅผ ๋ฐ์ ํ์๋ฅผ ๋ณด์ฌ์ค๋ค.
SELECT emp_name,phone_number, count(emp_id) as grade_A
FROM lecture_taken NATURAL JOIN employee
WHERE grade = 'A'
group by emp_id,emp_name,phone_number
order by grade_A DESC;
- ํด๋น ๊ธฐ์
์ ์ต๊ทผ ๊ตฌ๋
ํ ๊ณ ๊ฐ๋ค์๊ฒ ์ด๋ฒคํธ๋ฅผ ์ค๋น์ค์ ์๊ธฐ์ 2022๋
12์ ์ดํ๋ก ๊ตฌ๋
ํ ๊ตฌ๋
์์ ์ด๋ฆ, ์ฃผ์(์,๋,ํธ), ๋ก๊ทธ์ธ ID, ๊ตฌ๋
์ผ์ ์๋์ SQL๋ฌธ์ ํตํด ํ์
ํ๋ค.. ์ต๊ทผ์ ๊ตฌ๋
ํ ์ฌ๋ ์์ผ๋ก ์ ๋ ฌํด์ ๊ตฌ๋
๊ฐฑ์ ํ ์ฌ๋์ ๊ตฌ๋ถํ๋ค.
SELECT customer_name,si,dong,ho,login_id,subscribe_date
FROM customer NATURAL JOIN subscriber
WHERE subscribe_date > '2022-11-30'
ORDER BY subscribe_date DESC;
- 90๋
๋ ์ถ์ํ ๊ณ ๊ฐ์ค ์ฑ๋ณ ๊ธฐ์ค์ผ๋ก ๊ณ ๊ฐ์ ๋๋์ด ์๋ฅผ ์ผ๋ค. ์๋น์ค๋ฅผ ์ด์ฉํ๋ ๊ณ ๊ฐ์ด
์ฒญ๋
์ธต์ด ์ผ๋ง๋ ์ด์ฉํ๋์ง, ์ด๋ค ์ฑ๋ณ์ด ์ฃผ๋ก ์ด์ฉํ๋์ง ํ์
ํ๋๋ฐ ์ฌ์ฉํ๋ค.
SELECT gender,count(customer_id) as gender_count
FROM customer
WHERE birthdate > '1989-12-31' AND birthdate < '2000-01-01'
GROUP BY gender;
- ๊ณ ๊ฐ์ด๋ ์ง์, ๋ธ๋๋๊ฐ ์ด์ฌํ ๋ ์๋์ ๊ฐ์ UPDATE ๋ฌธ์ผ๋ก ์ฃผ์๋ฅผ ์
๋ฐ์ดํธ ํ ์ ์๋ค.
UPDATE customer
SET si = '๋ถ์ฒ', dong = '์ก๋ด๋', ho ='13'
WHERE customer_id = 'C0003';