데이터베이스 과제 (1). item 선정 및 데이터베이스 설계

✔️ item 선정 

: 전시회 관리 프로그램의 데이터베이스를 설계한다!

✔️ why ?

:  전시회 기획에 관심이 있어, 이번 기회에 관련 정보를 수집하면서 전시회에 대해 더 알아가보고 싶다는 취지에서 시작!

✔️ 설계

-  전시회 정보

• 전시회 id pk
• 전시회 이름
• 전시회 시작일
• 전시회 종료일
• 전시회 장소
• 전시회 설명
• 주최자

-  전시품 정보

• 전시품 id pk
• 전시품 이름
• 전시품 설명
• 전시품 카테고리
• 전시회 id fk

-  참가자 정보

• 참가자 id pk
• 참가자 연락처
• 참가자 이메일
• 참가자 소속/회사
• 참가자 등록일

-  이벤트/행사 정보

• 행사 id pk
• 행사 이름
• 행사 날짜
• 행사 장소
• 행사 설명
• 행사 주최자

-  부스 할당 정보 테이블

• 부스 id pk
• 부스 번호
• 부스 소유자/업체 이름
• 부스 크기
• 전시회 id fk

 -  판매 정보 테이블

• 판매 id pk
• 작품 id fk
• 구매자 이름
• 판매 날짜
• 판매 가격

 -  티켓 및 등록 정보 테이블

• 티켓 id pk
• 티켓 유형
• 티켓 가격
• 티켓수
• 등록된 참가자
• 전시회 id fk

 -  관리자/운영자 정보 테이블

• 운영자 id
• 운영자 이름
• 운영자 역할
• username
• password

✔️ 테이블 구성

-- 전시회 정보 테이블
CREATE TABLE exhibitions (
    exhibition_id INT AUTO_INCREMENT PRIMARY KEY,
    exhibition_name VARCHAR(255) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    location VARCHAR(255) NOT NULL,
    description TEXT,
    organizer VARCHAR(255)
);

-- 전시품 정보 테이블
CREATE TABLE artworks (
    artwork_id INT AUTO_INCREMENT PRIMARY KEY, 
    artwork_name VARCHAR(255) NOT NULL,
    description TEXT,
    category VARCHAR(100),
    artist VARCHAR(255),
    exhibition_id INT,
    FOREIGN KEY (exhibition_id) REFERENCES exhibitions(exhibition_id)
);

-- 참가자 정보 테이블
CREATE TABLE participants (
    participant_id INT AUTO_INCREMENT PRIMARY KEY,
    participant_name VARCHAR(255) NOT NULL,
    contact_info VARCHAR(255),
    email VARCHAR(255),
    affiliation VARCHAR(255),
    registration_date DATE
);

-- 이벤트/행사 정보 테이블
CREATE TABLE events (
    event_id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(255) NOT NULL,
    event_date DATETIME NOT NULL,
    location VARCHAR(255) NOT NULL,
    description TEXT,
    organizer VARCHAR(255)
);

-- 부스 할당 정보 테이블
CREATE TABLE booths (
    booth_id INT AUTO_INCREMENT PRIMARY KEY,
    booth_number INT NOT NULL,
    booth_owner VARCHAR(255),
    booth_size VARCHAR(100),
    exhibition_id INT,
    FOREIGN KEY (exhibition_id) REFERENCES exhibitions(exhibition_id)
);

-- 판매 정보 테이블
CREATE TABLE sales (
    sale_id INT AUTO_INCREMENT PRIMARY KEY,
    artwork_id INT,
    buyer_name VARCHAR(255),
    sale_date DATETIME,
    sale_price INT,
    FOREIGN KEY (artwork_id) REFERENCES artworks(artwork_id)
);

-- 티켓 및 등록 정보 테이블
CREATE TABLE tickets (
    ticket_id INT AUTO_INCREMENT PRIMARY KEY,
    ticket_type VARCHAR(100) NOT NULL,
    ticket_price INT NOT NULL,
    ticket_quantity INT NOT NULL,
    registered_participants INT,
    exhibition_id INT,
    FOREIGN KEY (exhibition_id) REFERENCES exhibitions(exhibition_id)
);

-- 관리자/운영자 정보 테이블
CREATE TABLE administrators (
    admin_id INT AUTO_INCREMENT PRIMARY KEY,
    admin_name VARCHAR(255) NOT NULL,
    admin_role VARCHAR(100) NOT NULL,
    username VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL
);

 

✔️ 데이터

#전시회 정보
INSERT INTO exhibitions (exhibition_name, start_date, end_date, location, description, organizer)
VALUES ('아트 박람회 2024', '2024-03-15', '2024-07-21', '킨텍스', '현대 아트 거장들의 작품을 접할 수 있는 절호의 기회!', '국립 현호 아트'),
       ('웨딩 박람회 2024', '2024-05-25', '2024-10-13', '코엑스', '2024, 웨딩 박람회에서 사랑도 쟁취, 일생일대의 행운을 잡자 ', '여성가족부 산하 러브메이커즈'),
       ('IT 박람회 2024', '2024-02-27', '2024-06-08', '벡스코', '최근 신기술동향 정보를 제일 빠르게 알 수 있는 IT 총집합', 'IT 연합'),
       ('식품 박람회 2024', '2024-02-27', '2024-06-08', '벡스코', '전 세계 외식업과 문화를 알고! 맛보고! 즐기자!', '식문화연구소');

#전시품 정보
INSERT INTO artworks (artwork_name, description, category, artist, exhibition_id)
VALUES ('해바라기와 나', '뜨거운 여름, 무수한 해바라기 밭에서 발견하는 나', '아트', 'bm', 1),
       ('물고기', '현대사회의 물고기에 대한 쟁점을 조명한다!', '아트', '에밀리', 2),
       ('스마일', '웃고있는 현대인들의 초상', '아트', 'Michael Brown', 3),
       ('블랙스파클', '블랙감성의 빛나는 웨딩드레스와 턱시도!', '웨딩', 'swan', 2),
       ('첫만남과 같은 사랑', '첫만남과 같은 사랑을 이루어드리는 웨딩 앱', '웨딩', '룰루웨딩', 2),
       ('달콤한 허니문', '신혼여행 알잘딱깔센', '웨딩', '오로라여행사', 2),
       ('해커와의 전쟁, 이긴다!', 'Korea 보안기술의 총체, 이긴다에서', 'IT', '이긴다', 3),
       ('인공지능과 대화를!', '자잘자잘한 대화를 이 로봇과 함께해보세요', 'IT', '로보토태권B', 3),
       ('플라워 떡케잌', '꽃밭에 와있는 듯한 떡케잌을 와앙~ 한입에', '식품', '(주)꽃화', 4),
       ('중화 라멘', '어떻게 이런맛이?! 라멘과 중화의 조화', '식품', '띵호아오이시', 4),
       ('땅콩카라멜 타코', '땅콩카라멜을 녹여버린 타코! 냠냠냠', '식품', '울랄라', 4);

# 참가자 정보
INSERT INTO participants (participant_name, contact_info, email, affiliation, registration_date)
VALUES ('이지수', '123-456-7890', 'alice@naver.com', '열정주의자클럽', '2024-04-20'),
       ('김다윗', '987-654-3213', 'david@gmail.com', '허니문박사회사', '2024-04-21'),
       ('John', '222-312-4520', 'john@gmail.com', 'robotronic', '2024-02-17'),
       ('아리가또라이크', '775-125-8225', 'arigato@gmail.com', '라이크라멘', '2024-03-05'),
       ('조나희', '432-512-5442', 'nahee@kakao.com', '스드메웨딩', '2024-03-11'),
       ('짱쯔희', '777-451-4105', 'jangzhi@gmail.com', '띵호아오이시', '2024-04-09');

#이벤트/행사 정보
INSERT INTO events (event_name, event_date, location, description, organizer)
VALUES ('아티스트와의 대화: bm', '2024-05-18 15:00:00', '메인홀', '21세기를 빛낸 아티스트, bm의 인사이트 속으로!', '국립 현호 아트'),
       ('IT와 법의 만남, 변곡점에서', '2024-03-30 13:00:00', '2층 A홀', 'it와 법률문제, 그 미묘한 차이의 해결을 다루는 컨퍼런스', 'IT 연합');

#부스 할당 정보
INSERT INTO booths (booth_number, booth_owner, booth_size, exhibition_id)
VALUES (101, 'bm', '10x10', 1),
       (102, '에밀리', '8x8', 2),
       (103, 'Michael Brown', '7x7', 3),
       (104, 'swan', '15x15', 2),
       (105, '룰루웨딩', '9x9', 2),
       (106, '오로라여행사', '11x11', 2),
       (107, '이긴다', '20x20', 3),
	   (108, '로보토태권B', '13x13', 3),
	   (109, '(주)꽃화', '4x4', 4),
       (110, '띵호아오이시', '7x7', 4),
       (111, '울랄라', '5x5', 4);

#판매 정보
INSERT INTO sales (artwork_id, buyer_name, sale_date, sale_price)
VALUES (1, '이지수', '2024-05-16 14:30:00', 5000000),
	   (5, '김수연', '2024-05-17 11:45:00', 300000),
       (11, '짱쯔희', '2024-05-18 19:30:00', 70000);
       
#티켓 및 등록 정보
INSERT INTO tickets (ticket_type, ticket_price, ticket_quantity, registered_participants, exhibition_id)
VALUES ('Regular', 5000, 500, 300, 1),
       ('VIP', 10000, 100, 80, 1);

#관리자/운영자 정보
INSERT INTO administrators (admin_name, admin_role, username, password)
VALUES ('Admin User', 'Super Admin', 'admin', 'admin123');

 

✔️ 다이어그램

✔️ 파이썬과 연동

import MySQLdb

class Expo:
    def __init__(self, exhibition_name, start_date, end_date, location, description, organizer):
        self.exhibition_name = exhibition_name
        self.start_date = start_date
        self.end_date = end_date
        self.location = location
        self.description = description
        self.organizer = organizer

    def setExhibition_name(self, exhibition_name):
        self.exhibition_name = exhibition_name

    def getExhibition_name(self):
        return self.exhibition_name

    def setStart_date(self, start_date):
        self.exhibition_name = start_date

    def getStart_date(self):
        return self.start_date

    def setEnd_date(self, end_date):
        self.end_date = end_date

    def getEnd_date(self):
        return self.end_date

    def setLocation(self, location):
        self.location = location

    def getLocation(self):
        return self.location

    def setDescription(self, description):
        self.description = description

    def getDescription(self):
        return self.description
    
    def setOrganizer(self, organizer):
        self.organizer = organizer

    def getOrganizer(self):
        return self.organizer

class ExpoDao:
    def __init__(self):
        self.db = None
    
    def connect(self):
        self.db = MySQLdb.connect(host='localhost', user='root', password='1234', db='expo')
    
    def disconnect(self):
        self.db.close()

    def insert(self, expo):
        self.connect()
        cur = self.db.cursor()
        sql = 'insert into exhibitions(exhibition_name, start_date, end_date, location, description, organizer) values (%s, %s, %s, %s, %s, %s)'
        data = (expo.getExhibition_name(), expo.getStart_date(), expo.getEnd_date(), expo.getLocation(), expo.getDescription(), expo.getOrganizer())
        cur.execute(sql, data)
        row = cur.fetchall()
        self.db.commit()
        cur.close()
        self.disconnect()
        return row

    def selectAll(self):
        self.connect()
        cur = self.db.cursor(MySQLdb.cursors.DictCursor)
        sql = 'select exhibition_name, start_date, end_date, location, description, organizer from exhibitions order by exhibition_name asc'
        cur.execute(sql)
        row = cur.fetchall()
        cur.close()
        self.disconnect()
        return row

    def search(self, exhibition_name):
        self.connect()
        cur = self.db.cursor(MySQLdb.cursors.DictCursor)
        sql = "select exhibition_name, start_date, end_date, location, description, organizer from exhibitions where exhibition_name like concat('%%', %s, '%%')"
        data = (exhibition_name,)
        cur.execute(sql, data)
        row = cur.fetchall()
        cur.close()
        self.disconnect()
        return row
    
    def update(self, expo):
        self.connect()
        cur = self.db.cursor()
        sql = 'update exhibitions set start_date=%s, end_date=%s, location=%s, description=%s, organizer=%s where exhibition_name=%s'
        data = (expo.getExhibition_name(), expo.getStart_date(), expo.getEnd_date(), expo.getLocation(), expo.getDescription(), expo.getOrganizer())
        result = cur.execute(sql, data)
        self.db.commit()
        if result > 0 :
            print('수정완료!!')
        else:
            print('에러')
        cur.close()
        self.disconnect()

    def delete(self, exhibition_name):
        self.connect()
        cur = self.db.cursor()
        sql = 'delete from exhibitions where exhibition_name=%s'
        data = (exhibition_name,)
        result = cur.execute(sql, data)
        self.db.commit()
        if result > 0:
            print('삭제되었습니다')
        else:
            print('오류')
        cur.close()
        self.disconnect()

class ExpoService:
    def __init__(self):
        self.dao = ExpoDao()

    def insertExpo(self):
        exhibition_name = input('박람회 이름을 입력하세요')
        start_date = input('시작일은?')
        end_date = input('종료일은?')
        location = input('위치는?')
        description = input('상세설명을 써주세요')
        organizer = input('주관을 입력하세요')
        expo = Expo(exhibition_name, start_date, end_date, location, description, organizer)
        self.dao.insert(expo)

    def printAll(self):
        datas = self.dao.selectAll()
        for data in datas:
            print(f"{data['exhibition_name']}, 시작일: {data['start_date']}, 종료일: {data['end_date']}, 위치: {data['location']}, 상세설명 : {data['description']}, 주최: {data['organizer']}")

    def searchExpo(self):
        exhibition_name = input('검색할 박람회를 입력하세요')
        datas = self.dao.search(exhibition_name)
        if datas:
            for data in datas:
                print(f"{data['exhibition_name']}, 시작일: {data['start_date']}, 종료일: {data['end_date']}, 위치: {data['location']}, 상세설명 : {data['description']}, 주최: {data['organizer']}")
        else:
            print('찾는 박람회가 없습니다')

    def editExpo(self):
        exhibition_name = input('수정할 박람회를 입력하세요')
        expo = self.dao.search(exhibition_name)
        if not expo:
            print('수정할 정보가 없습니다')
        else:
            start_date = input('시작일을 수정하세요')
            end_date = input('종료일을 수정하세요')
            location = input('위치를 수정하세요')
            description = input('상세설명을 수정하세요')
            organizer = input('주관을 수정하세요')
            expo = Expo(exhibition_name, start_date, end_date, location, description, organizer)
            self.dao.update(expo)

    def delExpo(self):
        exhibition_name = input('삭제할 박람회를 입력하세요')
        expo = self.dao.search(exhibition_name)
        if not expo:
            print('삭제할 단어가 없습니다.')
        else:
            self.dao.delete(expo)

class Menu:
    def __init__(self):
        self.service = ExpoService()

    def run(self):
        while True:
            try:
                menu = int(input('1.전시회등록 2. 출력하기 3.검색하기 4.수정하기 5.삭제하기 6. 종료하기'))
                if menu == 1:
                    self.service.insertExpo()
                elif menu == 2:
                    self.service.printAll()
                elif menu == 3:
                    self.service.searchExpo()
                elif menu == 4:
                    self.service.editExpo()
                elif menu == 5:
                    self.service.delExpo()
                elif menu == 6:
                    print('프로그램을 종료합니다')
                    break
            except Exception as e:
                print(e)
                print('다시 입력하세요')

start = Menu()
start.run()

 

단어장 코드를 토대로 다시 만들어보았는데 

수정하기와 삭제 부분에서 오류가 나고 있는 상황!!

빨리 고쳐봐야겠다