be11-1st-1team-PetLeader

PET LEADER~!!🐶🩷

훈련사와 반려견 패밀리를 이어주는 플랫폼
개발기간: 2024.12.02 ~ 2024.12.03

👍Team All for joonhyuk 팀원소개

🐻고준혁(팀장)	🐹최영재	🐰한성민	🐻‍❄️홍성혁
팀장	팀원	팀원	팀원

@luail	@impitang	@HanSungMin99	@dksqjq4851

🔨 기술 스택

‼️프로젝트 개요

추진 배경 및 목적

강아지와 함께 생활하는 사람들이 늘어나면서, 반려견의 올바른 행동 교육과 훈련에 대한 수요도 급증하고 있습니다.
하지만 전문적인 훈련 서비스를 찾는 것이 쉽지 않으며, 서비스의 질과 가격이 천차만별이라는 문제점이 존재합니다.
이러한 문제를 해결하기 위해, 신뢰할 수 있는 강아지 훈련 서비스를 제공하고 다양한 훈련 정보를 한곳에서 확인할 수 있는 데이터베이스를 구축하게 되었습니다.
이를 통해 반려인과 훈련 서비스 제공자를 연결하여 반려견의 행동 문제를 효과적으로 해결하고, 반려견과의 조화로운 삶을 지원하는 것이 목적입니다.

고객층 및 기대효과

주요 고객층
• 초보 반려인: 강아지 교육 방법을 잘 모르는 사람들.
• 행동 문제를 겪는 반려견의 보호자.
• 전문 훈련사가 필요한 반려인.
• 전문 훈련사: 자신의 훈련 프로그램을 홍보하고, 고객과 연결될 수 있는 플랫폼을 찾는 훈련사들.

기대효과
• 반려인은 필요한 정보를 손쉽게 찾아볼 수 있고, 적합한 훈련사를 선택하여 시간과 비용을 절약할 수 있습니다.
• 훈련사는 플랫폼을 통해 더 많은 고객을 확보하고, 신뢰도를 높일 수 있습니다.
• 데이터베이스를 통해 훈련 사례 및 효과적인 프로그램을 공유함으로써 반려견 훈련 분야 전반의 발전에 기여할 수 있습니다..
• 강아지의 행동 문제 해결로 반려인의 스트레스 감소 및 반려견의 삶의 질 향상시킬 수 있습니다.
• 고객 확보: 플랫폼을 통해 더 많은 고객에게 노출되어 신규 고객을 확보할 수 있습니다.
• 브랜드 인지도 향상: 자신의 전문성을 홍보하고, 훈련 프로그램을 소개함으로써 브랜드 인지도를 높일 수 있습니다.
• 피드백 및 평가: 고객으로부터 직접적인 피드백을 받아 서비스 품질을 개선하고, 긍정적인 평가를 통해 신뢰도를 높일 수 있습니다.
• 수익성 향상: 플랫폼을 통해 더 많은 수업을 진행하고, 다양한 프로그램을 제공하여 수익을 증가시킬 수 있습니다.

‼️프로젝트 소개

이 프로젝트는 전국의 강아지 훈련 서비스 제공자를 데이터베이스로 정리하여 플랫폼화하는 것을 목표로 합니다.

주요 기능
• 강아지 훈련 서비스 제공하는 개인 훈련사 검색.
• 사용자 후기 및 평점 시스템 제공.
• 훈련 서비스 유형(기본 교육, 문제 행동 교정, 사회화 훈련 등) 및 가격 비교.
• 훈련사 프로필 관리: 자신의 이력, 전문 분야 등을 등록하고 관리하여 고객에게 신뢰를 줄 수 있는 프로필 제공.
• 훈련 서비스 등록 및 설명: 제공하는 훈련 프로그램(기본 교육, 문제 행동 교정, 사회화 훈련 등)에 대한 상세한 설명과 가격을 등록할 수 있는 기능.
• 후기 및 평가 관리: 고객의 후기 및 평점을 관리하고, 이를 통해 서비스 품질을 개선할 수 있는 피드백 시스템.

서비스 방식
• 웹사이트 및 모바일 앱을 통해 누구나 접근 가능.
• 훈련사와 반려인을 연결하는 수강신청 서비스 제공

‼️프로젝트 필요성

반려견 수 증가
반려동물을 키우는 인구가 증가함에 따라, 강아지 훈련 서비스에 대한 수요가 꾸준히 늘어나고 있습니다.

훈련 서비스의 정보 부족
기존에는 훈련사나 업체에 대한 정보를 구하기 어려웠으며, 신뢰할 만한 후기와 평가를 찾기 어려운 경우가 많았습니다.

반려견 행동 문제 심화
적절한 훈련이 이루어지지 않을 경우 반려견의 문제 행동이 심화될 가능성이 높으며, 이는 보호자와 반려견 모두에게 스트레스가 될 수 있습니다.

반려동물 산업의 성장 가능성
반려동물 관련 서비스는 지속적으로 성장하는 시장으로, 플랫폼화된 훈련 서비스 데이터베이스는 반려동물 산업 내에서 큰 경쟁력을 가질 수 있습니다.
이 데이터베이스 프로젝트는 반려인의 편의를 높일 수 있을 것 입니다.

‼️자료

1️⃣WBS

>저희 팀은 프로젝트의 모든 작업과 구성 요소를 체계적으로 나누어 관리하기 위하여 WBS를 작성하였습니다.

2️⃣JIRA

>저희 팀은 이슈의 우선순위를 설정하여 중요한 작업에 집중하며 효율적인 이슈 관리하고, 팀원들이 진행 상황을 쉽게 추적하기 위해서 JIRA 플랫폼을 이용하였습니다.

3️⃣notion

>저희 팀은 각 단계에서 수행한 작업을 정리하고 기록하고자 notion을 이용하였습니다.
>혹시나 데이터를 잃어버릴 수 있을 것을 대비하여 테이블 생성 과정, 데이터 입력 값(최소20개 이상), 프로시저 등을 모아 정리하는데 notion을 이용하였습니다.

4️⃣요구사항 정의 및 분석서

5️⃣논리적 ERD

>url: https://www.erdcloud.com/d/fqt6byGLZJNG9DnEP

‼️주요 쿼리 요약

1. DDL

CREATE DATABASE pet;
USE pet;<br>
CREATE TABLE address(id BIGINT PRIMARY KEY AUTO_INCREMENT, state VARCHAR(255) NOT NULL, city VARCHAR(255) NOT NULL, street VARCHAR(255) NOT NULL);
```sql
CREATE TABLE owner (id BIGINT PRIMARY KEY AUTO_INCREMENT, password VARCHAR(255) NOT NULL, address_id BIGINT NOT NULL, name VARCHAR(30) NOT NULL, email VARCHAR(50), phone VARCHAR(255) NOT NULL,  em_phone VARCHAR(255), age INT, valid ENUM("Y","N") NOT NULL, created_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP(), nickname VARCHAR(30) NOT NULL UNIQUE, FOREIGN KEY (address_id) REFERENCES address(id));
```sql
CREATE TABLE dog ( id BIGINT PRIMARY KEY AUTO_INCREMENT, owner_id BIGINT NOT NULL, name VARCHAR(255) NOT NULL, age INT NOT NULL, gender ENUM("male","female") NOT NULL, size ENUM("소","중","대"), sign_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP(), FOREIGN KEY (owner_id) REFERENCES owner(id));
```sql
CREATE TABLE trainer(id BIGINT PRIMARY KEY AUTO_INCREMENT, nickname VARCHAR(255) NOT NULL UNIQUE, name VARCHAR(255) NOT NULL, age INT, gender ENUM('male', 'female') NOT NULL, career INT NOT NULL DEFAULT 0);
```sql
CREATE TABLE speciality( id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL, trainer_id BIGINT NOT NULL, FOREIGN KEY (trainer_id) REFERENCES trainer(id));
```sql
CREATE TABLE chat( id BIGINT PRIMARY KEY AUTO_INCREMENT, sender ENUM('보호자', '훈련사') NOT NULL, lasttime DATETIME DEFAULT CURRENT_TIMESTAMP(), contents VARCHAR(3000) NOT NULL, owner_id BIGINT, trainer_id BIGINT, FOREIGN KEY (owner_id) REFERENCES owner(id), FOREIGN KEY (trainer_id) REFERENCES trainer(id));
```sql
CREATE TABLE trainer_class (id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT, trainer_id BIGINT NOT NULL, name VARCHAR(255) NOT NULL, fee INT NOT NULL, location VARCHAR(255) NOT NULL, class_day ENUM('월', '화', '수', '목', '금', '토', '일') NOT NULL, training_time ENUM('1교시', '2교시', '3교시', '4교시', '5교시', '6교시', '7교시', '8교시', '9교시') NOT NULL, description VARCHAR(3000) NOT NULL, size ENUM('대', '중', '소') NOT NULL, limits INT NOT NULL, current INT NOT NULL DEFAULT 0, start_date DATE NOT NULL, FOREIGN KEY (trainer_id) REFERENCES trainer(id));
```sql
CREATE TABLE owner_class(id BIGINT PRIMARY KEY AUTO_INCREMENT, owner_id BIGINT NOT NULL, trainer_class_id BIGINT NOT NULL, dog_id BIGINT NOT NULL, classjointime DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (owner_id) REFERENCES owner(id), FOREIGN KEY (trainer_class_id) REFERENCES trainer_class(id), FOREIGN KEY (dog_id) REFERENCES dog(id));
```sql
CREATE TABLE post( post_id BIGINT PRIMARY KEY AUTO_INCREMENT, owner_class_id BIGINT NOT NULL, owner_id BIGINT NOT NULL, title VARCHAR(255) NOT NULL, contents VARCHAR(3000) NOT NULL, rating ENUM('1','2','3','4','5'), FOREIGN KEY (owner_class_id) REFERENCES owner_class(id), FOREIGN KEY (owner_id) REFERENCES owner(id));
```sql
CREATE TABLE alarm(id BIGINT PRIMARY KEY AUTO_INCREMENT, trainer_class_id BIGINT NOT NULL, owner_id BIGINT NOT NULL, created_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP(), FOREIGN KEY (trainer_class_id) REFERENCES trainer_class(id), FOREIGN KEY (owner_id) REFERENCES owner(id));<br>
```sql
CREATE TABLE coupon ( id BIGINT PRIMARY KEY AUTO_INCREMENT, discount_amount INT NOT NULL, code VARCHAR(255) NOT NULL UNIQUE, name varchar(255) not null);
```sql
CREATE TABLE owner_coupon(id BIGINT PRIMARY KEY AUTO_INCREMENT, owner_id bigint not null, coupon_id bigint not null, issue_date datetime not null default current_timestamp(), expiration_date datetime, foreign key(owner_id) references owner(id), foreign key(coupon_id) references coupon(id));
```sql
CREATE TABLE payment(id BIGINT PRIMARY KEY AUTO_INCREMENT, owner_id BIGINT NOT NULL, class_id BIGINT NOT NULL, coupon_id bigint, amount INT NOT NULL, payment_date DATETIME DEFAULT CURRENT_TIMESTAMP(), FOREIGN KEY (owner_id) REFERENCES owner(id), FOREIGN KEY (class_id) REFERENCES owner_class(id), FOREIGN KEY (coupon_id) REFERENCES owner_coupon(id));
```sql
SHOW TABLES;

2. DML

INSERT INTO address (state, city, street) VALUES ('서울특별시', '강남구', '압구정로'),('경기도', '수원시', '팔달구');
INSERT INTO owner (password, address_id, name, email, phone, em_phone, age, valid, nickname) VALUES('password1', 1, '홍길동', 'hong1@naver.com', '010-1234-5678', '010-1111-2222', 30, 'Y', 'hong1'),('password2', 2, '이영희', 'younghee2@google.com', '010-2345-6789', '010-2222-3333', 25, 'Y', 'leeyoung');
INSERT INTO dog (owner_id, name, age, gender, size) VALUES(1, '바둑이', 2, 'male', '중'),(1, '흰둥이', 1, 'female', '소');
INSERT INTO trainer (nickname, name, age, gender, career) VALUES('trainer1', '김훈련', 40, 'male', 10),('trainer2', '이훈련', 35, 'female', 8);
INSERT INTO speciality (name, trainer_id) VALUES('기본 훈련', 1),('복종 훈련', 2);
INSERT INTO chat (sender, lasttime, contents, owner_id, trainer_id) VALUES('보호자', CURRENT_TIMESTAMP(), '안녕하세요, 강아지 훈련 관련 상담 문의드립니다.', 1, 1),('훈련사', CURRENT_TIMESTAMP(), '안녕하세요! 상담 도와드리겠습니다.', 1, 1);
INSERT INTO trainer_class (trainer_id, name, fee, location, class_day, training_time, description, size, limits, current, start_date) VALUES(1, '기본 복종 훈련', 100000, '서울 강남구', '월', '1교시', '기본적인 복종 훈련 프로그램입니다.', '중', 10, 5, '2024-12-10'),(2, '사회성 향상 훈련', 120000, '서울 마포구', '화', '2교시', '강아지의 사회성을 키우는 훈련입니다.', '대', 8, 4, '2024-12-11');
INSERT INTO owner_class (owner_id, trainer_class_id, dog_id, classjointime) VALUES(1, 1, 1, CURRENT_TIMESTAMP()),(1, 2, 2, CURRENT_TIMESTAMP());
INSERT INTO post (owner_class_id, owner_id, title, contents, rating) VALUES(1, 1, '기본 복종 훈련 후기', '강아지가 말을 잘 듣게 되었어요!', '5'),(2, 2, '사회성 향상 후기', '강아지가 다른 개들과 잘 어울리게 되었어요.', '4');
INSERT INTO alarm (trainer_class_id, owner_id, created_time) VALUES(1, 1, CURRENT_TIMESTAMP()),(2, 2, CURRENT_TIMESTAMP());
INSERT INTO coupon (discount_amount, code, name) VALUES(10000, 'COUPON10', '10% 할인 쿠폰'),(15000, 'COUPON15', '15% 할인 쿠폰');
INSERT INTO owner_coupon (owner_id, coupon_id, issue_date, expiration_date) VALUES(1, 1, CURRENT_TIMESTAMP(), '2024-12-31'),(2, 2, CURRENT_TIMESTAMP(), '2024-12-31');
INSERT INTO payment (owner_id, class_id, coupon_id, amount, payment_date) VALUES(1, 1, 1, 90000, CURRENT_TIMESTAMP()),(2, 2, 2, 105000, CURRENT_TIMESTAMP());

‼️프로시저 실행결과

1. 회원가입 🔑

DELIMITER //
CREATE DEFINER=root@localhost PROCEDURE 회원가입(in 시도 varchar(255), in 시군구 varchar(255), in 상세주소 varchar(255),
    in 이름 varchar(255), in 전화번호 varchar(255), in 닉네임 varchar(255), in 이메일 varchar(255), in 패스워드 varchar(255))
begin
    declare newAddressid bigint;
    insert into address (state, city, street) values(시도, 시군구, 상세주소);
    set newAddressId = Last_Insert_ID();
    insert into owner (name, phone, address_id, nickname, email, password) values(이름, 전화번호, newAddressId, 닉네임, 이메일, 패스워드);

    select * from owner;
    end
//
DELIMITER ;

2. 로그인 🔑

delimiter //
    create procedure 로그인(in 이메일 varchar(255), in 패스워드 varchar(255))
    begin
    declare 사용자존재 int;
    select count(*) into 사용자존재
    from owner
    where email = 이메일 and password = 패스워드;
    if 사용자존재 > 0 then
        select '로그인 성공' as 메시지;
    else
        select '로그인 실패' as 메시지;
    end if;
    end
    // delimiter ;

3. 비밀번호수정 🔑

delimiter // 
CREATE PROCEDURE 비밀번호변경하기(in 내이메일 varchar(255), in 내휴대폰번호 varchar(255), in 새로운비밀번호 varchar(255))
begin
	declare ownerId bigint;
	select id into ownerId from owner where email = 내이메일 and phone = 내휴대폰번호;
	IF ownerId IS NOT NULL THEN
        UPDATE owner SET password = 새로운비밀번호 WHERE id = ownerId ;
    END IF ;
    select password from owner where email = 내이메일  and phone = 내휴대폰번호 ;
end
// delimiter ;

4. 아이디 찾기🔑

delimiter //
CREATE PROCEDURE 아이디찾기(in 내닉네임 varchar(255), in 내휴대폰번호 varchar(255))
begin
	declare ownerId bigint;
	select id into ownerId from owner where nickname = 내닉네임 and phone = 내휴대폰번호;
	IF ownerId IS NOT NULL THEN
        select email from owner where id = ownerId;
    END IF;
end
// delimiter //

5.회원 긴급연락처 수정🔑

DELIMITER //
CREATE PROCEDURE 회원긴급연락처수정(in 긴급연락처 varchar(255), in 닉네임 varchar(255))
BEGIN
	declare ownerId bigint;
    select id into ownerId from owner where nickname = 닉네임;
    update owner set phone=긴급연락처 where id = ownerId; 
    select * from owner where id = ownerId;
END
// DELIMITER ;

6.내 정보 조회🔑

delimiter //
create procedure 내정보확인(in 내이메일 varchar(50))
begin
    select * from owner where email=내이메일;
end
// delimiter ;

7.내 강아지 등록하기 🔑

delimiter //
    create procedure 강아지등록(in 내이메일 varchar(255), in 강아지이름 varchar(255), in 나이 int, in 성별 enum('male','female'), in 사이즈 enum('대', '중', '소'))
    begin
    declare ownerId bigint;
    select id into ownerId from owner where email = 내이메일;
    insert into dog(owner_id,name, age, gender, size) values(ownerId,강아지이름, 나이, 성별, 사이즈);
    end
    // delimiter ;

8.내 강아지 정보 조회🔑

delimiter //
CREATE PROCEDURE 내강아지정보확인하기(in 내닉네임 varchar(30))
begin
declare ownerId bigint;
select id into ownerId from owner where nickname = 내닉네임;
select * from dog where owner_id = ownerId;
end

// delimiter ;

9.수강 신청하기🔑

delimiter //
CREATE PROCEDURE 수강신청하기(in 내닉네임 varchar(255), in 훈련명 varchar(255), in 내강아지이름 varchar(255))
begin
declare ownerId bigint;
declare dogId bigint;
declare trainerClassId bigint;
    select id into ownerId from owner where nickname = 내닉네임;
    select id into trainerClassId from trainer_class where name = 훈련명;
    select id into dogId from dog where name = 내강아지이름;
    insert into owner_class (owner_id, trainer_class_id, dog_id) values(ownerId, trainerClassId ,dogId); 
    select * from owner_class where owner_id = ownerId;
end
// delimiter ;

10.수강신청내역조회하기🔑

delimiter //
CREATE PROCEDURE 수강신청내역조회하기(in 내닉네임 varchar(255))
begin
declare ownerId bigint;
declare dogId bigint;
declare trainerClassId bigint;
    select id into ownerId from owner where nickname = 내닉네임;
    select * from owner_class where owner_id = ownerId;
end
// delimiter ;

11.수강하고 싶은 수업을 알림예약 조회🔑

DELIMITER //
CREATE PROCEDURE 보호자본인이예약한수업조회(IN 내email varchar(255))
BEGIN
    declare ownerId bigint;
    select id into ownerId from owner where email = 내email;
    SELECT  tc.id AS class_id, tc.name AS class_name, tc.fee AS class_fee, tc.location AS class_location, tc.class_day AS class_day, tc.training_time AS training_time, tc.description AS class_description, tc.size AS class_size, tc.start_date AS class_start_date, d.name AS dog_name,  d.size AS dog_size
    FROM owner_class oc
    JOIN  trainer_class tc ON oc.trainer_class_id = tc.id
    JOIN 
        dog d ON oc.dog_id = d.id
    WHERE 
       oc.owner_id = ownerId ;
END
// DELIMITER ;

12.강아지 크기별 수업조회🔑

DELIMITER //
create procedure 강아지크기별수업조회(IN 내이메일 varchar(255), IN dogSize ENUM('대', '중', '소'))
begin
    declare ownerId bigint;
    select id into ownerId from owner where email = 내이메일 ;
    select tc.id AS class_id, tc.name AS class_name, tc.fee AS class_fee, tc.location AS class_location, tc.class_day AS class_day, tc.training_time AS training_time, tc.description AS class_description, tc.size AS class_size, tc.limits AS participant_limit, tc.current AS current_participants, tc.start_date AS start_date
    from
        trainer_class tc
    join
        owner o ON o.id = ownerId
    where
        tc.size = dogSize;
end
// DELIMITER ;

13.수업료 낮은 순으로 수업 정렬🔑

DELIMITER //
CREATE PROCEDURE 수업료가낮은순으로수업정렬()
BEGIN
    select * from trainer_class order by fee;
END
// DELIMITER ;

14.원하는 요일에 개설되어있는 수업 조회🔑

DELIMITER //
CREATE PROCEDURE 원하는요일에개설되어있는수업조회(IN desiredDay ENUM('월', '화', '수', '목', '금', '토', '일'))
BEGIN
    select *  from trainer_class tc
    join
        trainer t ON tc.trainer_id = t.id
   where
        tc.class_day = desiredDay ;
END 
// DELIMITER ;

15.본인이 선택한 수업의 가격 조회🔑

DELIMITER //
CREATE PROCEDURE 본인이선택한수업의가격조회(IN 내email varchar(255))
BEGIN
    declare ownerId bigint;
    select id into ownerId from owner where email = 내email ;
    select
        tc.name AS class_name,
        tc.fee AS class_fee,
        tc.location AS class_location,
        tc.class_day AS class_day,
        tc.training_time AS training_time,
        tc.start_date AS start_date
    from
        owner_class oc
    inner join
        trainer_class tc ON oc.trainer_class_id = tc.id
    where
        oc.owner_id = ownerId;
END //
DELIMITER ;

16.훈련등록🔑

delimiter //
    create procedure 훈련등록(in 내닉네임 varchar(255), in 훈련이름 varchar(255), in 훈련료 int,
    in 훈련요일 enum('월','화','수','목','금','토','일'),
    in 훈련시간 enum('1교시', '2교시', '3교시','4교시','5교시','6교시','7교시','8교시','9교시'),
    in 훈련소개 varchar(3000),
    in 훈련가능한견종크기 enum('소','중','대'),
    in 수강정원 int,
    in 수업시작일자 date,
    in 수업장소 varchar(255))
    begin
    declare trainerId bigint;
    select id into trainerId from trainer where nickname = 내닉네임;
    insert into trainer_class(trainer_id, name, fee, class_day, training_time, description, size, limits, start_date, location)
    values(trainerId, 훈련이름, 훈련료, 훈련요일, 훈련시간, 훈련소개, 훈련가능한견종크기, 수강정원, 수업시작일자, 수업장소);
    end
    // delimiter ;

17.훈련사정보와전문기술등록하기🔑

DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `훈련사정보와전문기술등록하기`(in 이름 varchar(255), in 나이 int, in 성별 enum('male', 'female'), in 경력 int, in 닉네임 varchar(255), in 전문분야 varchar(255))
begin
    declare trainerId bigint;
    insert into trainer (name, age, gender, career, nickname)
    values (이름, 나이, 성별, 경력, 닉네임);
    set trainerId = last_insert_id();
    insert into speciality (name, trainer_id)
    values (전문분야, trainerId);
end
 // DELIMITER ;

18.특정훈련전문강사찾기🔑

delimiter //
    create procedure 특정훈련전문강사찾기(in 훈련이름 varchar(255))
    begin
        select t.name from trainer t inner join speciality s on t.id = s.trainer_id where s.name = 훈련이름;
    end
    // delimiter ;

19.원하는 성별의 훈련사 조회하기🔑

delimiter //
CREATE PROCEDURE 원하는성별의훈련사조회하기(IN 성별 enum('male','female'))
BEGIN
select name from trainer where gender=성별; 
END;
// delimiter ;

20.특정훈련사수업목록조회하기🔑

DELIMITER //
CREATE PROCEDURE 특정훈련사수업목록조회하기(in 훈련사닉네임 varchar(255))
BEGIN
    declare trainerId bigint;
    select id into trainerId from trainer where name = 훈련사닉네임;
    select * from trainer_class where trainer_id=trainerId;
END
// DELIMITER ;

21.훈련사와대화하기🔑

delimiter //
create procedure 훈련사와대화하기(in 내닉네임 varchar(255),in 대화할훈련사닉네임 varchar(255), in 보내는사람 enum('보호자','훈련사'), in 전송할메시지 varchar(255))
begin
declare ownerId bigint;
declare trainerId bigint;

    select id into ownerId from owner where nickname = 내닉네임;
    select id into trainerId from trainer where nickname = 대화할훈련사닉네임;
    INSERT INTO chat (sender, contents, owner_id, trainer_id) VALUES(보내는사람, 전송할메시지, ownerId, trainerId);
end
// delimiter ;

22.훈련사닉네임으로별점확인🔑

delimiter //
CREATE PROCEDURE 훈련사닉네임으로별점확인(in 훈련사닉네임 varchar(30))
begin
select p.rating from post p inner join owner_class oc on p.owner_class_id = oc.id inner join trainer_class tc on oc.trainer_class_id = tc.id
inner join trainer t on tc.trainer_id = t.id where t.nickname = 훈련사닉네임;
end

// delimiter ;

23.내결제정보조회🔑

delimiter //
create procedure 내결제정보조회(in 내닉네임 varchar(255))
begin
declare ownerId bigint;

    select id into ownerId from owner where nickname = 내닉네임;
    select * from payment where owner_id = ownerId;
end
// delimiter ;

24.남은기간이빨리만기되는쿠폰조회🔑

DELIMITER //

CREATE PROCEDURE 남은기간이빨리만기되는쿠폰조회(in 내닉네임 varchar(255))
BEGIN
declare ownerId bigint;
    select id into ownerId from owner where nickname = 내닉네임;
    SELECT * FROM owner_coupon where owner_id = ownerId ORDER BY expiration_date;
END //

DELIMITER ;

25.내가받은쿠폰중할인금액이큰순으로나열🔑

DELIMITER //
CREATE PROCEDURE 내가받은쿠폰중할인금액이큰순으로나열(in 내닉네임 varchar(255))
BEGIN
declare ownerId bigint;
    select id into ownerId from owner where nickname = 내닉네임;
    SELECT oc.*, c.discount_amount
    FROM owner_coupon oc
    JOIN coupon c ON oc.coupon_id = c.id
    where owner_id = ownerId
    ORDER BY c.discount_amount DESC;
END //
DELIMITER ;

26.수업들은고객에게쿠폰발급🔑

DELIMITER //
CREATE PROCEDURE 수업들은고객에게쿠폰발급(IN 발급받을닉네임 varchar(255), IN 발급할쿠폰id bigint)
BEGIN
    DECLARE attended_count INT;
declare ownerId bigint;
    select id into ownerId from owner where nickname = 발급받을닉네임;
SELECT COUNT(DISTINCT c.id) into attended_count
    FROM owner_class c
    JOIN owner o on o.id = c.owner_id 
    JOIN owner_coupon oc ON oc.owner_id = o.id
    where o.id = ownerId;
    IF attended_count > 0 THEN
        INSERT INTO owner_coupon (owner_id, coupon_id)
        VALUES (ownerId, 발급할쿠폰id);
else
SELECT '수업을 안들은 보호자 입니다!' AS message;
    END IF;
END //
DELIMITER ;

27.쿠폰사용해서결제🔑

DELIMITER //
CREATE PROCEDURE 쿠폰사용해서결제(IN 결제할고객닉네임 varchar(255), IN 결제할훈련이름 varchar(255), IN 사용할쿠폰id BIGINT, IN 결제할금액 INT)
BEGIN
declare ownerId bigint;
    declare classId bigint;
    DECLARE v_discount INT DEFAULT 0;
    DECLARE v_coupon_exists INT;
    DECLARE v_coupon_expiration DATETIME;
    DECLARE final_amount int;
select id into ownerId from owner where nickname = 결제할고객닉네임;
    select oc.id into classId from owner_class oc join trainer_class tc on oc.trainer_class_id = tc.id where tc.name = 결제할훈련이름;
    SELECT COUNT(), expiration_date INTO v_coupon_exists, v_coupon_expiration
    FROM owner_coupon
    WHERE owner_id = ownerId AND coupon_id = 사용할쿠폰id;
    IF v_coupon_exists > 0 THEN
        IF v_coupon_expiration > NOW() THEN
            SELECT discount_amount INTO v_discount
            FROM coupon
            WHERE id = 사용할쿠폰id;
            SET final_amount = 결제할금액 - v_discount;
            IF final_amount < 0 THEN
                SET final_amount = 0;
            END IF;
        ELSEIF v_coupon_expiration is null then
        SELECT discount_amount INTO v_discount
            FROM coupon
            WHERE id = 사용할쿠폰id;
            SET final_amount = 결제할금액 - v_discount;
            ELSE
            SET final_amount = 결제할금액;
        END IF;
    ELSE
        SET final_amount = 결제할금액;
    END IF;
    INSERT INTO payment (owner_id, class_id, coupon_id, amount, payment_date)
    VALUES (ownerId, classId, 사용할쿠폰id, final_amount, NOW());
    select from payment where owner_id = ownerId and class_id = classId;
END //
DELIMITER ;

‼️ 회고

|🐻고준혁|정말 회의도 많이하고 주제도 많이 엎어지고 수많은 일이 있었지만 그래도 잘 안됐던 과정이 있었기에 다같이 프로젝트를 끝까지 완수할 수 있었던 것 같습니다. 최선을 다했기에 아쉬움은 있지만 후회는 없습니다. 다들 고생하셨습니다.

|🐹최영재|인간의 한계를 느꼈고 부싯돌이 부딪힐 때 왜 불꽃이 튀는지 알 것 같았습니다. 과제하는 기간이 드라마였고 레저였고, 익스트림스포츠였습니다. 각자의 자리에서 최선을 다하고자 고집있게 진행했기에 뜻깊은 시간이었다 생각합니다.

|🐰한성민|이번 프로젝트는 처음이라 막막하고 힘든 부분도 많았지만, 팀원들과 함께 노력하며 마무리할 수 있어 감사한 시간이었습니다. 특히, 이론으로 배운 내용을 실전에 적용하며 많은 것을 배우고 성장할 수 있었던 뜻깊은 경험이었습니다. 다소 아쉬운 점도 있지만, 최선을 다한 결과라 만족스럽습니다.

|🐻‍❄️홍성혁|데이터베이스를 작성하면서 데이터 구조와 관계를 명확히 이해하는 것이 중요하다는 것을 깨달았습니다. 또한, 효율적인 쿼리 작성과 성능 최적화가 데이터베이스의 성능에 큰 영향을 미친다는 점을 다시한번 배웠습니다.

Name		Name	Last commit message	Last commit date
Latest commit History 141 Commits
dir/images		dir/images
README.md		README.md
data		data
database		database
table		table

beyond-sw-camp/be11-1st-1team-PetLeader

Folders and files

Latest commit

History

Repository files navigation