상세 컨텐츠

본문 제목

대학교 학부 데이터베이스 수준

데이터베이스

by 김일국 2017. 6. 21. 15:12

본문

여기서 제시하는 대학교 학부생의 과제물을 기준으로 데이터베이스 능력이 어느 정도일지 가늠해 보실 수 있습니다.


아래 과제물 예시(환경: Mysql/워크벤치 사용, 총 3단계의 작업=1.스키마구성, 2.더미데이터입력, 3.검증쿼리실행)

과제명: 영화예매사이트의 데이터베이스 구축
1. 엔티티(entity) 요구사항
1) 회원(membership) : 고객번호(cust_no), 고객이름(cust_name), 나이(age), 주소(cust_addr), 전화번호(cust_phone)로 인식한다.
2) 영화(movie) : 영화번호(m_no), 영화명(m_name), 장르(genre), 관람가능나이(see_age)로 인식한다.
3) 극장(theater) : 극장번호(t_no), 극장명(t_name), 극장주소(t_addr), 극장전화번호(t_tel)로 인식한다.
4) 요금(price) : 요금코드(p_code), 관람시간(see_time), 관람가능나이(see_age), 금액(fee)로 인식한다.
5) 회원 포인트(mileage) : 고객번호(cust_no), 적립마일(save_point)
※ 각 Attribute의 Data type은 사용자가 알아서 정의하며, instance 삽입시 금액은 원단위로 삽입한다. 단, 관람등급은 관람가능한 가장 적은 나이로 0, 12, 15, 18로 하고 15세 이상 관람가는 15이다.
※ 관람시간은 3가지로만 입력가능하고(조조, 일반, 심야), 관람가능한 나이에 따라 금액이 달라진다.
※ 모든 고객은 자산이 관람가능한 영화만 볼 수 있다.
※ 마일리지는 최종 마일리지만 가지고 있다.

2. 관계(relationship) 요구사항
1) 극장(theater)과 요금(price) 간의 일대다 관계
2) 회원(membership)과 영화(movie) 간의 다대다 관계
3) 극장(theater)과 영화(movie) 간의 일대다 관계
4) 회원(membership)과 회원 포인트(mileage) 간의 일대일 관계


작업1 : 스키마구성(과제용 DB생성)

결과화면:

/* 아래부터는 DDL 문으로 스키마 생성구문임 */
CREATE SCHEMA `knou` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
작업2: 영화예매사이트 ERD 생성 (워크벤치)
부모 개체부터 먼저 만든다 (일다다관계의 상위:회원, 극장)

==============================================================================

CREATE TABLE `membership` (
  `cust_no` VARCHAR(10) NOT NULL COMMENT '고객번호',
  `cust_name` VARCHAR(50) NULL DEFAULT NULL COMMENT '고객이름',
  `age` DECIMAL(10,0) NULL DEFAULT NULL COMMENT '나이',
  `cust_addr` VARCHAR(255) NULL DEFAULT NULL COMMENT '주소',
  `cust_phone` VARCHAR(50) NULL DEFAULT NULL COMMENT '전화번호',
  PRIMARY KEY (`cust_no`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '회원';

CREATE TABLE `theater` (
  `t_no` VARCHAR(10) NOT NULL COMMENT '극장번호',
  `t_name` VARCHAR(50) NULL DEFAULT NULL COMMENT '극장명',
  `t_addr` VARCHAR(255) NULL DEFAULT NULL COMMENT '극장주소',
  `t_tel` VARCHAR(50) NULL DEFAULT NULL COMMENT '극장전화번호',
  PRIMARY KEY (`t_no`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '극장';

CREATE TABLE `movie` (
  `m_no` VARCHAR(10) NOT NULL COMMENT '영화번호',
  `m_name` VARCHAR(50) NULL DEFAULT NULL COMMENT '영화명',
  `genre` VARCHAR(50) NULL DEFAULT NULL COMMENT '장르',
  `see_age` DECIMAL(10,0) NULL DEFAULT NULL COMMENT '관람가능나이',#CHECK(see_age IN (0 , 12, 15, 18))
  `t_no` VARCHAR(10) NOT NULL COMMENT '극장번호',
  PRIMARY KEY (`m_no`),
  INDEX `fk_movie_theater_idx` (`t_no` ASC),
  CONSTRAINT `fk_movie_theater`
    FOREIGN KEY (`t_no`)
    REFERENCES `theater` (`t_no`)
    on DELETE NO ACTION
    on UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '영화';

CREATE TABLE `mileage` (
  `cust_no` VARCHAR(10) NOT NULL COMMENT '고객번호',
  `save_point` DECIMAL(10,0) NULL DEFAULT NULL COMMENT '적립마일',
  PRIMARY KEY (`cust_no`),
  CONSTRAINT `fk_mileage_membership1`
    FOREIGN KEY (`cust_no`)
    REFERENCES `membership` (`cust_no`)
    on DELETE NO ACTION
    on UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '회원 포인트';

CREATE TABLE `price` (
  `p_code` VARCHAR(10) NOT NULL COMMENT '요금코드',
  `see_time` DECIMAL(10,0) NULL DEFAULT NULL COMMENT '관람시간',
  `see_age` DECIMAL(10,0) NULL DEFAULT NULL COMMENT '관람가능나이',
  `fee` DECIMAL(10,0) NULL DEFAULT NULL COMMENT '금액',
  `t_no` VARCHAR(10) NOT NULL COMMENT '극장번호',
  PRIMARY KEY (`p_code`),
  INDEX `fk_price_theater1_idx` (`t_no` ASC),
  CONSTRAINT `fk_price_theater1`
    FOREIGN KEY (`t_no`)
    REFERENCES `theater` (`t_no`)
    on DELETE NO ACTION
    on UPDATE NO ACTION
  #CONSTRAINT ck_see_time CHECK(see_time IN('조조','일반','심야')),
  #CONSTRAINT ck_see_age CHECK(see_age IN(0,12,15,18))
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '요금';

CREATE TABLE `mem_mov_relation` (
  `cust_no` VARCHAR(10) NOT NULL,
  `m_no` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`cust_no`, `m_no`),
  INDEX `fk_mem_mov_relation_movie1_idx` (`m_no` ASC),
  CONSTRAINT `fk_mem_mov_relation_membership1`
    FOREIGN KEY (`cust_no`)
    REFERENCES `membership` (`cust_no`)
    on DELETE NO ACTION
    on UPDATE NO ACTION,
  CONSTRAINT `fk_mem_mov_relation_movie1`
    FOREIGN KEY (`m_no`)
    REFERENCES `movie` (`m_no`)
    on DELETE NO ACTION
    on UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '영화관람';

===================================================================================

/* CHECK제약조건을 테이블 생성시 사용하지 않았을 경우 아래 DDL문으로 추가가능 */
ALTER TABLE price ADD CONSTRAINT ck_see_time CHECK ( see_time IN('조조','일반','심야') );
ALTER TABLE price ADD CONSTRAINT ck_see_age CHECK ( see_age IN(0,12,15,18) );
ALTER TABLE movie ADD CONSTRAINT ck_see_age CHECK ( see_age IN(0,12,15,18) );


/* MYSQL은 CHECK제약조건을 무시한다. 그래서 트리거로 대체한다 */
CREATE TRIGGER `price_BINS` BEFORE INSERT on `price` FOR EACH ROW
BEGIN
IF NEW.see_time NOT IN ('조조','일반','심야') THEN
SIGNAL SQLSTATE '10000'
 SET MESSAGE_TEXT = 'CHECK INSERT NOT VALUE';
END IF;
IF NEW.see_age NOT IN(0,12,15,18) THEN
SIGNAL SQLSTATE '10000'
 SET MESSAGE_TEXT = 'CHECK INSERT NOT VALUE';
END IF;
END;


작업2 : 더미데이터 입력
INSERT INTO `membership` VALUES ('08-001','김일국',11,'서울','1111'),('08-002','김이국',12,'천안','2222'),('08-003','김삼국',13,'서울','3333'),('08-004','김사국',14,'천안','4444'),('08-005','김오국',15,'대전','5555'),('08-006','김육국',16,'천안','6666'),('08-007','김칠국',17,'서울','7777'),('08-008','김팔국',18,'천안','8888'),('08-009','김구국',33,'대전','9999');
INSERT INTO `theater` VALUES ('T01','한국대극장','서울','02-0000-0000'),('T02','천안극장','천안','041-0000-0000');
INSERT INTO `mileage` VALUES ('08-001',9000),('08-002',15000),('08-003',10000),('08-004',8000),('08-005',9000),('08-006',20000),('08-007',8000),('08-008',30000);
INSERT INTO `price` VALUES ('P0001','일반',0,8000,'T01'),('P0002','일반',12,9000,'T01'),('P0003','심야',15,10000,'T01'),('P0004','조조',18,15000,'T01'),('P0005','일반',18,18000,'T01');
INSERT INTO `movie` VALUES ('10-08-001','원더우먼','SF',0,'T01'),('10-08-002','미이라','액션',18,'T01'),('10-08-003','캐리비안해적4','액션',12,'T01'),('10-09-001','스파이더맨','만화',0,'T01'),('10-09-002','파리넬리','멜로',15,'T01'),('10-09-003','아저씨','액션',18,'T01'),('10-10-001','아가씨','스릴러',18,'T01');
INSERT INTO `mem_mov_relation` VALUES ('08-001','10-08-001'),('08-008','10-08-002'),('08-009','10-10-001');


작업3 : 검증쿼리실행
#검증문제 (결과 Instance를 1번~3번까지 7건이상, 4번,5번문제는 2건 이상으로 출력가능하게 더미 데이터를 입력해 놓는다)

#1) 회원 테이블상의 모든 고객번호, 고객이름, 나이, 주소, 전화번호를 출력한다.
SELECT
cust_no AS 고객번호
, cust_name AS 고객이름
, age AS 나이
, cust_addr AS 주소
, cust_phone AS 전화번호
FROM membership;

/*검증문제1 결과화면*/


#2) 마일리지 8000점당 1장의 무료 영화표를 받을 수 있다. 무료 영화표를 받을 수 있는 회원의
고객이름, 적립마일리지, 받을 수 있는 영화표 수를 고객이름으로 내림차순 정렬로 출력한다.
테이블 참조를 단축하기 위해 테이블 별명(알리아스,Alias)를 사용한다.
SELECT
 a.cust_name AS 고객이름
 ,b.save_point AS 적립마일리지
 , floor(b.save_point/8000) AS 무료관람가능한영화표수
FROM membership AS a
 JOIN (SELECT mil.cust_no, mil.save_point
   FROM mileage AS mil
   WHERE mil.save_point >= 8000) AS b
 ON b.cust_no = a.cust_no
ORDER BY 고객이름 DESC;

/* 검증문제2 결과화면 */


#3)'한국대극장'에서 상영하는 영화번호, 영화명, 장르, 관람가능나이를 출력한다. 테이블명 참조를 단축하기위해 테이블 별명(Alias)를 사용
SELECT
 a.m_no AS 영화번호
 , a.m_name AS 영화제목
 , a.genre AS 장르
 , a.see_age AS 관람가능나이
FROM movie AS a
 JOIN (SELECT the.t_no
   FROM theater AS the
   WHERE the.t_name = '한국대극장') AS b
 ON b.t_no = a.t_no;

/* 검증문제3 결과화면 */ 


#4)영화 '미이라'의 관람시간, 관람가능나이, 금액을 출력한다. 테이블명 참조를 단축하기 위해 테이블별명(Alias)를 사용한다.
단, 관람가능나이는 영화테이블의 관람가능나이 이상 모두 출력한다.
SELECT
 a.see_time AS 관람시간
 , a.see_age AS 관람가능나이
 , a.fee AS 금액
FROM price AS a
 JOIN (SELECT mov.see_age
   FROM movie AS mov
   WHERE mov.m_name = '미이라') AS b
 ON a.see_age >= b.see_age;
/* 검증문제4 결과화면 */ 


#5)18세 이상 관람가 영화를 '조조'에 관람한 고객의 고객번호, 고객이름, 나이, 극장명, 영화명, 금액을 출력한다.
모든 영화를 관람한 고객이 '조조'에 관람하였다고 가정하고 하고 질의한다.
SELECT
 mem.cust_no AS 고객번호
 , mem.cust_name AS 고객이름
 , mem.age AS 나이
 , the.t_name AS 극장명
 , mov.m_name AS 영화명
 , p.fee AS 금액
FROM mem_mov_relation AS mmr
 JOIN membership AS mem
 ON mmr.cust_no = mem.cust_no
 JOIN (SELECT m.see_age, m.m_no, m.t_no, m.m_name
   FROM movie AS m
   WHERE m.see_age >= 18) AS mov
 ON mmr.m_no = mov.m_no
 JOIN theater AS the
 ON the.t_no = mov.t_no
 JOIN price AS p
 ON p.see_age = mov.see_age
WHERE p.see_time = '조조';

/* 검증문제5 결과화면 */ 


관련글 더보기

댓글 영역