MySql. MSSQL을 활용하여 기말 프로젝트를 진행 해 보았다.
주제는 내가 평소에 자주먹고 맛있어하는 하남돼지로 선정하였고, 이에 따른 데이터베이스를 구축해보자고 계획을 잡아 보았다.
1. 요구사항 분석
1) 데이터베이스를 구축하고자 하는 목적
어렸을 때부터 돼지고기를 정말 좋아해서 중학교 때까지 비만이 될 정도로 많이 먹었습니다. 성인이 된 지금도 단백질 보충 및 체중 관리를 위해 오히려 돼지고기가 필수라고 생각합니다. 필자 뿐 만 아니라 많은 사람들이 돼지고기에 대한 수요가 많아져서 돼지고기를 파는 식당도 기하급수적으로 늘어나고 있고, 가게를 체계적으로 관리할 수 있는 데이터베이스가 구축되어야 한다고 생각합니다.
새로운 돼지고기 메뉴가 추가되거나 삭제될 때 데이터베이스를 통해 쉽게 추가하거나 삭제할 수 있을 것이며, 어떤 고기가 가장 많이 팔리는지 파악하여 정육점에 미리 재고를 주문할 수 있을 것입니다. 또한, 다른 식당 데이터베이스와는 다르게 진행하고 싶은 점이 있습니다. 첫번째로는, 매니저를 배치하여 체계적으로 매니저가 전반적인 총괄을 맡을 것입니다. 이렇게 한다면, 가맹점들을 관리하기 편안할 것이라고 생각합니다. 두번째로는, 요즘은 칼로리에 대한 예민함이 더해진 시대인 것 같습니다. 각 고기마다 몇 그램에 몇 칼로리인지, 단백질과 지방 등 상세한 정보를 기입하여 데이터베이스에 저장하여 한 끼 식사에 얼마나 많은 칼로리를 섭취하는지 손님들에게도 제공될 수 있게 데이터베이스에 저장합니다.
이렇게 만들어진 데이터베이스로는 보다 현대 사회에 최적화된 데이터베이스가 구축될 것으로 기대할 수 있습니다. 최종 목표는 지역별로 "하남 돼지" 가게를 운영하는 사장님이 편안하게 관리하기 위해 만들었습니다.
2) 어떤 업무를 위해 데이터베이스를 구축하는지
“하남 돼지"라는 가게에서는 다양한 종류의 고기 메뉴를 판매하고 있습니다. 이를 효율적으로 관리하기 위해 데이터베이스를 구축하고자 합니다. 데이터베이스를 통해 메뉴의 삽입, 삭제, 가격 관리, 재고 관리 등을 수행하여 가맹점 식당들을 운영하는 데 도움이 되도록 지원할 계획입니다. 또한, 매니저들은 각 지점을 관리하여 매장 운영이 원활하게 이루어질 수 있도록 도와줄 것입니다. 데이터베이스를 통해 손님 별 총 식사 가격을 확인할 수 있고, 매니저들과 알바생들의 월급 관리도 가능합니다. 또한, 각 매장별로 재고 관리를 할 수 있으며, 날짜별로도 식사 기록을 확인할 수 있습니다. 매니저는 알바생 한 명씩을 담당하여 매장 운영을 원활하게 조율할 수 있습니다.
데이터베이스에는 식사한 기록이 자세히 나타나는 테이블을 구성하여 고객들이 어떤 고기를 가장 선호하는지 확인할 수 있으며, 이를 통해 재고 조절 및 신메뉴 개발에 도움을 받을 수 있습니다. 또한, 1인분 200g에 대한 칼로리 정보도 데이터베이스에 저장하여 소비자에게 제공함으로써, 최근 MZ세대의 다이어트 열풍을 고려한 고객들의 만족도를 높일 수 있을 것으로 기대합니다.
이렇게 구축된 데이터베이스는 "하남 돼지" 가게를 운영하는 사장님들이 편안하게 매장을 관리할 수 있도록 도와줄 것입니다.
3) 업무 흐름
가상으로 설정한 사장은, 천안, 서울, 대전, 부산, 논산에 각각 1개씩 총 5개의 매장을 보유하고 있습니다. 각 매장은 다음과 같은 업무 흐름으로 운영됩니다.
업무 흐름도
4) 요구사항 명세서
|
<요구 사항 명세서>
매니저
매니저의 번호, 이름, 전화번호, 월급, 매장을 관리한다.
매니저는 고객을 회원으로 등록한다.
매니저는 매장들을 담당한다.
매니저는 담당하는 알바를 교육한다.
매니저는 재고를 확인 후 고기를 주문한다.
알바 관리
알바의 번호, 이름, 전화번호, 월급, 담당매니저를 관리한다.
알바는 담당매니저로부터 교육을 받는다
알바는 손님의 예약을 관리한다.
손님 관리
손님의 번호, 이름, 전화번호, 주소 관리한다.
손님은 포인트적립을 하기 위해 회원등록을한다.
손님은 담당매니저에게 식사 안내를 받고 고기를 먹는다.
식사한 정보는 기록으로 남는다.
식사기록
식사기록의 식사번호, 주문고기, 주문가격, 식사날짜, 결제정보를 관리한다.
본 식사기록을 토대로 손님들의 선호고기를 알 수 있다.
고기관리
고기의 번호, 이름, 칼로리, 단백질, 지방을 관리한다.
매니저가 재고를 주문 할 수 있다
주문기록
주문기록의 주문번호, 주문날짜, 주문수량, 주문가격을 관리한다.
매니저가 주문한 고기의 자세한 정보를 알 수 있고, 매장별로 얼마나
주문했는지도 파악 할 수 있다
매장
매장의 매장번호, 매장이름을 관리한다.
예약기록
예약기록의 번호, 예약일, 예약매장을 관리한다.
알바생이 손님의 예약기록을 관리한다.
|
4) 개발범위 어디까지
- 매니저가 지점별로 전반적인 총괄을 맡는다. 재고관리를 하며 직접 고기를 주문한다. 고기에 있는 200g당 영양성분(칼로리, 단백질, 지방)과 이름, 가격을 제공하여 단백질 보충에 예민한 손님들이 식사하기에 편안하도록 제공한다.
- 매니저의 주문기록(주문날짜, 주문수량, 주문금액)을 자세하게 남겨두어 재고 파악을 쉽게 할 수 있고 주문오차도 확인할 수 있다. 어떤 매장에 얼만큼 고기를 주문했는지 알 수 있다.
- 매니저들과 알바생들의 월급을 관리할 수 있다.
- 매장별로(천안점, 대전점, 서울점, 부산점, 논산점) 매니저를 배치하여 체계적으로 관리할 수 있다.
- 매니저가 직접 알바를 교육하여 알바생이 손님의 고기를 구워 드리는 방법, 고객 만족 서비스를 배울 수 있다.
- 알바생은 담당매니저에게 교육을 받고, 손님의 예약을 지정 받아 관리할 수 있다.
- 손님은 매니저에게 회원등록을 받고, 식사를 한다. 손님이 식사한 정보가 저장이 되는데, 주문한 고기, 주문한 가격, 식사 날짜, 결제정보를 저장하여 후에 매니저가 기록을 토대로 분석 한 후 신메뉴를 개발함에 용이하게 한다.
- 손님의 식사기록을 토대로 가장 많이 팔린 고기, 가장 적게 팔린 고기를 파악 한 후 재고주문을 용이하게 할 수 있고, 많이 팔리지 않는 고기는 추후에 메뉴에서 제거를 논의해 볼 수 있다.
- 식사를 한 손님 중에 가장 많은 금액을 식사한 고객을 파악할 수 있다. 많이 먹은 고객은 단골로 파악할 수 있다.
- 매니저와 알바생들의 이름, 핸드폰번호를 필요할 때 조회할 수 있다.
- 예약테이블을 관리하여 예약일을 관리하며 각 매장마다 예약을 받을 수 있다.
ER 다이어그램
다음은 SQL 쿼리문이다.
-- 하남 데이터베이스 생성
--하남돼지 홈페이지https://hanampig.co.kr/를 참고하였습니다.
--수정시 DB 초기화 진행
IF EXISTS(SELECT name FROM sys.databases WHERE (name = 'Hanam') OR (name = 'hanam'))
DROP DATABASE [Hanam];
CREATE DATABASE Hanam
GO
-- GO 명령어는 T-SQL 문은 아니지만 현재까지 명령어를 SQL Server에 보내서 실행시키는 역할을 한다.
-- 즉 GO 명령어는 명령어를 묶어서 배치로 서버에 보내는 역할을 한다.
-- 매장, 매니저, 알바, 손님, 고기, 주문기록, 식사기록, 예약기록, 예약, 구매, 식사, 담당, 관리, 등록 테이블 생성
USE [Hanam]
-- 1. 데이터 정의어 입니다.
-- 매장 테이블 입니다.
CREATE TABLE Store (
StoreID INT PRIMARY KEY,
StoreName VARCHAR(20)
);
-- 매니저 테이블 입니다.
CREATE TABLE Manager (
ManagerID INT PRIMARY KEY,
ManagerName VARCHAR(20),
PhoneNumber VARCHAR(20),
Salary INT
);
-- 알바생 테이블 입니다.
CREATE TABLE PartTimeEmployee (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(20),
PhoneNumber VARCHAR(20),
Salary INT
);
-- 매니저가 어떤 매장을 관리하는지 알 수 있는 담당 테이블입니다.
CREATE TABLE Responsible (
ResponsibleID INT PRIMARY KEY,
ManagerID INT REFERENCES Manager(ManagerID),
StoreID INT REFERENCES Store(StoreID)
);
-- 매니저가 어떤 알바를 관리하는지 알 수 있는 관리 테이블입니다.
CREATE TABLE Management (
ManagementID INT PRIMARY KEY,
ManagerID INT REFERENCES Manager(ManagerID),
EmployeeID INT REFERENCES PartTimeEmployee(EmployeeID)
);
--고객의 정보가 있는 고객테이블입니다.
CREATE TABLE Customer (
CustomerID INT PRIMARY KEY,
ManagerID INT REFERENCES Manager(ManagerID),
Name VARCHAR(20),
Address VARCHAR(20),
PhoneNumber VARCHAR(20)
);
--매니저가 고객을 언제 회원으로 등록한지 알 수 있는 테이블입니다.
CREATE TABLE Registration (
RegistrationID INT PRIMARY KEY,
ManagerID INT REFERENCES Manager(ManagerID),
CustomerID INT REFERENCES Customer(CustomerID),
RegistrationDate DATE
);
--고기의 칼로리, 단백질, 지방이 등록되어있는 테이블입니다.
CREATE TABLE Meat (
MeatID INT PRIMARY KEY,
MeatName VARCHAR(20),
MeatPrice INT,
MeatCalories INT,
MeatProtein INT,
MeatFat INT
);
--매니저가 고기의 재고를 주문한 기록이 있는 테이블입니다.
CREATE TABLE OrderHistory (
OrderHistoryID INT PRIMARY KEY,
StoreID INT REFERENCES Store(StoreID),
OrderQuantity INT,
OrderDate DATE,
PurchasePrice INT
);
--매니저가 무슨 고기를 얼마나 시켰는지 알 수 있는 테이블입니다.
CREATE TABLE Purchase (
PurchaseID INT PRIMARY KEY,
ManagerID INT REFERENCES Manager(ManagerID),
OrderHistoryID INT REFERENCES OrderHistory(OrderHistoryID),
MeatID INT REFERENCES Meat(MeatID)
);
--손님들의 자세한 식사기록이 있는 테이블입니다.
CREATE TABLE MealRecord (
MealRecordID INT PRIMARY KEY,
MeatID INT REFERENCES Meat(MeatID),
OrderedMeat VARCHAR(40),
OrderPrice INT,
MealDate DATE,
PaymentInfo VARCHAR(40)
);
--예약된 기록이 있는 테이블입니다.
CREATE TABLE ReservationRecord (
ReservationRecordID INT PRIMARY KEY,
ReservationDate DATE
);
--어떤 손님이 예약했는지 알 수 있는 테이블입니다.
CREATE TABLE Reservation (
ReservationID INT PRIMARY KEY,
EmployeeID INT REFERENCES PartTimeEmployee(EmployeeID),
ReservationRecordID INT REFERENCES ReservationRecord(ReservationRecordID),
CustomerID INT REFERENCES Customer(CustomerID),
StoreID INT REFERENCES Store(StoreID)
);
--식사에 관한 테이블입니다.
CREATE TABLE Meal (
MealID INT PRIMARY KEY,
CustomerID INT REFERENCES Customer(CustomerID),
MealRecordID INT REFERENCES MealRecord(MealRecordID)
);
-- 2. 데이터 조작어 INSERT 입니다.
-- 매장, 매니저, 알바 데이터 생성
INSERT INTO Store VALUES(1, '하남돼지 천안점');
INSERT INTO Store VALUES(2, '하남돼지 대전점');
INSERT INTO Store VALUES(3, '하남돼지 서울점');
INSERT INTO Store VALUES(4, '하남돼지 부산점');
INSERT INTO Store VALUES(5, '하남돼지 논산점');
INSERT INTO Manager VALUES(1, '장정우', '01012345678', 5500000);
INSERT INTO Manager VALUES(2, '권지용', '01034567890', 5500000);
INSERT INTO Manager VALUES(3, '태양', '01078901234', 3000000);
INSERT INTO Manager VALUES(4, '유재석', '010111111111', 3000000);
INSERT INTO Manager VALUES(5, '하동훈', '01022222222', 3000000);
INSERT INTO PartTimeEmployee VALUES (1, '정준하', '01033333333', 2800000);
INSERT INTO PartTimeEmployee VALUES (2, '박명수', '01044444444', 2800000);
INSERT INTO PartTimeEmployee VALUES (3, '양세형', '01055555555', 2800000);
INSERT INTO PartTimeEmployee VALUES (4, '조세호', '01066666666', 2800000);
INSERT INTO PartTimeEmployee VALUES (5, '황광희', '01077777777', 2800000);
--- 담당, 관리, 고객 데이터 생성
INSERT INTO Responsible VALUES (1, 1, 1);
INSERT INTO Responsible VALUES (2, 2, 2);
INSERT INTO Responsible VALUES (3, 3, 3);
INSERT INTO Responsible VALUES (4, 4, 4);
INSERT INTO Responsible VALUES (5, 5, 5);
INSERT INTO Management VALUES (1, 1, 1);
INSERT INTO Management VALUES (2, 2, 2);
INSERT INTO Management VALUES (3, 3, 3);
INSERT INTO Management VALUES (4, 4, 4);
INSERT INTO Management VALUES (5, 5, 5);
INSERT INTO Customer VALUES (1, 1, '김태호', '대전 서구 관저동', '01012222222');
INSERT INTO Customer VALUES (2, 2, '길성준', '천안 동남구 가전리', '01032222222');
INSERT INTO Customer VALUES (3, 3, '전진', '서울 노원구 노원동', '01033334444');
INSERT INTO Customer VALUES (4, 4, '정형돈', '부산 수영구 광안리', '01033334444');
INSERT INTO Customer VALUES (5, 5, '미소', '논산 와야리', '01033334444');
--등록, 고기, 주문기록 데이터 생성
INSERT INTO Registration VALUES (1, 1, 1, '2023-07-01');
INSERT INTO Registration VALUES (2, 2, 2, '2023-07-02');
INSERT INTO Registration VALUES (3, 3, 3, '2023-07-02');
INSERT INTO Registration VALUES (4, 4, 4, '2023-07-03');
INSERT INTO Registration VALUES (5, 5, 5, '2023-07-03');
INSERT INTO Meat VALUES (1, '생삼겹살', 14000, 661, 34, 56);
INSERT INTO Meat VALUES (2, '특목살', 15000, 400, 23, 19);
INSERT INTO Meat VALUES (3, '생갈비', 15000, 144, 21, 5);
INSERT INTO Meat VALUES (4, '항정살', 16000, 209, 28, 9);
INSERT INTO Meat VALUES (5, '갈매기살', 16000, 188, 14, 14);
INSERT INTO Meat VALUES (6, '가브리살', 16000, 131, 21, 5);
INSERT INTO Meat VALUES (7, '숙성오겹살', 17000, 698, 34, 60);
INSERT INTO OrderHistory VALUES (1, 1, 30, '2023-07-01', 300000);
INSERT INTO OrderHistory VALUES (2, 2, 40, '2023-07-01', 400000);
INSERT INTO OrderHistory VALUES (3, 3, 10, '2023-07-02', 100000);
INSERT INTO OrderHistory VALUES (4, 4, 5, '2023-07-02', 50000);
INSERT INTO OrderHistory VALUES (5, 5, 50, '2023-07-02', 500000);
INSERT INTO OrderHistory VALUES (6, 1, 20, '2023-07-03', 200000);
INSERT INTO OrderHistory VALUES (7, 2, 20, '2023-07-04', 200000);
-- 구매, 식사기록, 예약기록 데이터 생성
INSERT INTO Purchase VALUES (1, 1, 1, 1);
INSERT INTO Purchase VALUES (2, 2, 2, 2);
INSERT INTO Purchase VALUES (3, 3, 3, 3);
INSERT INTO Purchase VALUES (4, 4, 4, 6);
INSERT INTO Purchase VALUES (5, 5, 5, 4);
INSERT INTO Purchase VALUES (6, 1, 6, 5);
INSERT INTO Purchase VALUES (7, 2, 7, 5);
INSERT INTO MealRecord VALUES (1, 3, '생갈비 3인분', 45000, '2023-07-05', '45000원 카드');
INSERT INTO MealRecord VALUES (2, 7, '숙성오겹살 3인분', 51000, '2023-07-05', '51000원 카드');
INSERT INTO MealRecord VALUES (3, 5, '갈매기살 2인분', 32000, '2023-07-05', '32000원 현금');
INSERT INTO MealRecord VALUES (4, 1, '생삼겹살 5인분', 70000, '2023-07-06', '70000원 카드');
INSERT INTO MealRecord VALUES (5, 4, '항정살 6인분', 96000, '2023-07-06', '96000원 현금');
INSERT INTO MealRecord VALUES (6, 5, '갈매기살 4인분', 64000, '2023-07-06', '64000원 카드');
INSERT INTO ReservationRecord VALUES (1, '2023-07-07');
INSERT INTO ReservationRecord VALUES (2, '2023-07-08');
INSERT INTO ReservationRecord VALUES (3, '2023-07-09');
INSERT INTO ReservationRecord VALUES (4, '2023-07-10');
INSERT INTO ReservationRecord VALUES (5, '2023-07-11');
--예약, 식사 데이터 생성
INSERT INTO Reservation VALUES (1, 1, 1, 1, 1);
INSERT INTO Reservation VALUES (2, 2, 2, 2, 2);
INSERT INTO Reservation VALUES (3, 3, 3, 3, 3);
INSERT INTO Reservation VALUES (4, 4, 4, 4, 4);
INSERT INTO Reservation VALUES (5, 5, 5, 5, 5);
INSERT INTO Meal VALUES (1, 1, 1);
INSERT INTO Meal VALUES (2, 2, 2);
INSERT INTO Meal VALUES (3, 3, 3);
INSERT INTO Meal VALUES (4, 4, 4);
INSERT INTO Meal VALUES (5, 5, 5);
INSERT INTO Meal VALUES (6, 1, 6);
--3. 데이터 조작어 SELECT 입니다.
-------
--1. 김태호의 총 식사 가격
SELECT Customer.Name, SUM(MealRecord.OrderPrice) AS 총식사가격
FROM Customer
JOIN Meal ON Customer.CustomerID = Meal.CustomerID
JOIN MealRecord ON Meal.MealRecordID = MealRecord.MealRecordID
WHERE Customer.Name = '김태호'
GROUP BY Customer.Name;
--2. 매니저 중에서 장정우보다 월급이 낮은사람
SELECT Manager.ManagerName, Manager.Salary
FROM Manager
WHERE Manager.Salary
< (SELECT Salary FROM Manager WHERE ManagerName = '장정우');
--3.매장별 총 구매액이 400000원보다 크거나 같은 매장
SELECT Store.StoreName, SUM(OrderHistory.PurchasePrice) AS TotalPurchase
FROM Store
JOIN OrderHistory ON Store.StoreID = OrderHistory.StoreID
GROUP BY Store.StoreName
HAVING SUM(OrderHistory.PurchasePrice) >= 400000;
--4. 어떤 매니저가 어떤 매장에서 얼만큼 무슨 고기를 시켰는지와 핸드폰 번호
SELECT M.ManagerID, M.ManagerName, M.PhoneNumber, MT.MeatName, O.OrderQuantity, S.StoreName
FROM Purchase P
JOIN Manager M ON P.ManagerID = M.ManagerID
JOIN OrderHistory O ON O.OrderHistoryID = P.OrderHistoryID
JOIN Store S ON O.StoreID = S.StoreID
JOIN Meat MT ON P.MeatID = MT.MeatID;
--5. 2023년 07월 01일부터 2023년 07월 07일 동안 방문한 고객의 이름과 먹은 음식, 식사 날짜, 주문한 고기, 주문 가격
SELECT C.Name AS CustomerName, MR.MealDate, MR.OrderedMeat, MR.OrderPrice
FROM Customer C
JOIN Registration R ON C.CustomerID = R.CustomerID
JOIN Meal Ml ON C.CustomerID = Ml.CustomerID
JOIN MealRecord MR ON Ml.MealRecordID = MR.MealRecordID
JOIN OrderHistory O ON Ml.MealRecordID = O.OrderHistoryID
WHERE O.OrderDate >= '2023-07-01' AND O.OrderDate <= '2023-07-07';
-- 다음은 5개 이상으로 작성해본SELECT, INSERT문입니다. 본 하남돼지 프로젝트에서 장점을 보였던, 고기 하나하나의 칼로리 계산을 볼 수 있고,
-- 제일 선호하는 고기를 알게 된 후 재고 주문 및 신매뉴개발에 도움을 줄 수있는 SELECT, INSERT문 입니다.
--6. 고기별 주문 금액(얼마나 팔렸는지)
SELECT m.MeatName, SUM(mr.OrderPrice) AS TotalSales
FROM Meat m
JOIN MealRecord mr ON m.MeatID = mr.MeatID
GROUP BY m.MeatName
ORDER BY TotalSales DESC
--7. 팔리지 않은 고기
SELECT m.MeatName
FROM Meat m
LEFT JOIN MealRecord mr ON m.MeatID = mr.MeatID
WHERE mr.MealRecordID IS NULL;
--8. 고기별 단백질 함량
SELECT MeatName, MeatProtein
FROM Meat
ORDER BY MeatProtein DESC
--9. 칼로리가 낮은 순서대로 고기를 나열
SELECT MeatName, MeatCalories
FROM Meat
ORDER BY MeatCalories ASC;
--10. 사이드 메뉴가 부족하다고 판단. 신메뉴 껍데기 추가
INSERT INTO Meat (MeatID, MeatName, MeatPrice, MeatCalories, MeatProtein, MeatFat)
VALUES (8, '껍데기', 12000, 250, 10, 15);
SELECT *
FROM Meat
--기말 프로젝트를 마치며.
데이터베이스에 대해서 전혀 알지 못했던 저는 길지 않은 시간동안 3학점 DB수업을 들어야한다는 부담감이 작지 않았습니다.
프로젝트에서도 외래키 제약조건, 무결성 제약조건 등에서 오류가 발생하여 수정하는데 많이 노력했던 것 같습니다.
하지만, 교수님의 눈높이에 맞는 이해하기 쉽게 설명해주시는 수업과, 조교님의 꼼꼼한, 친절한 설명 덕에
어렵지않게 따라 올 수 있었습니다. 정말 감사합니다.
수업을 들으며 DB에 흥미가 많이 생겼다고 생각합니다. 앞으로 최선을 다 해서 좋은 개발자가 되도록 하겠습니다.
'Project' 카테고리의 다른 글
| 도로노면 훼손탐지 애플리케이션 - 도타미 (0) | 2024.02.21 |
|---|---|
| ajax통신으로 AI모델 서빙 - 관상가 양반, 내 관상좀 봐주게. (0) | 2024.02.21 |
| 모두의 레시피 - MVC패턴, AI모델을 활용한 나만의 레시피 게시판 (0) | 2024.02.19 |
| Inceptionv3 모델을 활용한 체중관리서비스 Hungry Bear 프로젝트 (3) | 2023.12.20 |
