Database ER diagram 데이터베이스 ER 다이어그램

DB ER.pptx

DB Homework.sql

ER diagram.hwp

ER Diagram은 Entity Relation Diagram이다. Entity는 사람, 장소, 사물, 사건과 같이 독립적으로 존재하면서 고유하게 식별이 가능한 실세계의 객체이다. 이들간의 관계를 그린 것이 ERD이다.

깨알같은 과제가 나와서 개인 공부를 할 시간이 없어지니까 최대한 과제를 빨리 끝내고 싶었다. 평창 올림픽에 관한 ERD를 작성하는게 과제다.

Winter Olympic Games 2018 will be held in South Korea. Suppose that you are required to create a database for this event.

Overall score for HW2 is 10 Points.

You must do the followings:

1. Requirement Analysis

    - At least 5 requirements

2. E-R Diagram

    - Composite Attributes

    - Multi-Valued Attributes

    - Derived Attributes

    - Weak Entity

    - 3진  관계

    - Cyclic relationship

    - 1:1, 1:N, M:N

    - Indicate participations

3. Map E-R Diagram into relational schema

4. Use any DBMS to create your database.

5. Queries

    - At least 5

이런 조건들이 있었다. 우선 요구사항을 정하는 것보다 구글링으로 올림픽 DB에 무슨 엔티티들이 있고 무슨 관계가 있는지를 찾아봤다. 선수, 팀 등이 있었다. 여러 이미지들을 참고하여 아이디어를 얻고 바로 그냥 종이에 엔티티와 애트리뷰트를 적었다. 도출된 정보를 가지고 나름의 요구사항을 만들고 주어진 ERD 조건에 맞게 세부 사항을 추가하였다. ERD를 그리면서 조건에 맞는 엔티티와 관계를 찾느라 매우 힘들었다. 그래도 재밌었다. ERD를 다 작성한 뒤 스키마를 썼다. ERD를 스키마로 변환할 때는 7가지 순서를 따르게 된다.

1. 정규 엔티티 타입과 단일 값 애트리뷰트

이 단계에선 정규 엔티티 타입과 단순 애트리뷰트들이 릴레이션을 사상된다. 다치 애트리뷰트는 제외하고 복합 애트리뷰트를 구성하는 단순 애트리뷰트를 릴레이션에 포함한다. 그리고 엔티티 타입의 기본 키가 릴레이션의 기본 키가 된다.

2. 약한 엔티티 타입과 단일 값 애트리뷰트

약한 엔티티 타입과 단일 값 애트리뷰트들이 릴레이션으로 사상된다. 약한 엔티티 타입을 소유하는 엔티티의 기본키를 약한 엔티티의 외래 키로 삼는다. 그리고 약한 엔티티의 부분키와 외래 키를 합해 기본 키로 설정한다.

3. 2진 1:1 관계 타입

전체 참여하는 엔티티의 릴레이션에 부분 참여하는 엔티티의 기본 키를 외래 키로 포함시킨다.

만약 부분 참여하는 엔티티의 릴레이션에 전체 참여하는 엔티티의 기본 키를 외래키로 포함시킨다면 널값이 많이 생길 것이다.

4. 정규 2진 1:N 관계 타입

N측에 있는 엔티티 타입의 릴레이션에 1측의 엔티티 타입의 기본키를 외래키로 추가한다. 직원 여러명이 부서 하나에 속해있다면 부서의 번호를 직원 릴레이션의 외래키로 삼는 것이다.

5. 2진 M:N 관계 타입

이름이 관계 타입인 릴레이션을 새로 생성하여 M측과 N측의 엔티티의 기본 키를 외래 키로 포함시키고 이를 기본 키로 삼는다. 만약 관계 타입에 애트리뷰트가 있다면 이들도 추가해야한다.

6. 3진 이상의 관계 타입

이 경우 새로운 릴레이션을 생성하고 관계 타입에 연결되어 있는 3개의 엔티티의 기본 키를 외래키로 포함하고 기본키로 설정한다. 관계 타입에 붙어있던 애트리뷰트도 추가해준다.

7. 다치 애트리뷰트

다치 애트리뷰트를 갖고 있는 엔티티의 기본키와 다치 애트리뷰트 자체를 기본키로 삼는다.

이 7가지 단계는 직접 해보면 쉽게 감이 온다.

여기까지 했으면 스키마가 완성된다. 그리고 MySQL이나 MSSQL을 켜서 SQL문을 작성해야한다. 우선 테이블을 생성하고 각 테이블에 들어갈 값들을 삽입해줘야한다.

마지막 문제가 질의를 작성하는 것이어서 꾹 참고 insert문을 작성해봤다. 작성해보면서 영어 이름이 이렇게 다양하다는 걸 알았고 데이터 삽입문은 노가다라는 것도 알았다. 중요한게 날짜 형식과 시간 형식이 정해져 있다는 것이었다. 그래서 날짜는 'yyyy-mm-dd' 이렇게 썼고 시간은 'hh:mm:ss'로 작성했다. 이거 말고도 다양한 규칙이 있다.

아래는 직접 작성한 SQL 문이다. 완전하게 맞는 지는 잘 모르겠으나 질의를 몇개 했을 때 답은 잘 나오는 것 같다.

#drop table player; drop table olympic; drop table evnt; drop table team; drop table schedules;

#drop table fan; drop table has1; drop table support; drop table mascot; drop table type_;

create table player(player_no int,player_gender varchar(6),player_name varchar(40),player_weight int,player_height int,player_nation varchar(30),player_birthday date, PRIMARY KEY (player_no));

create table olympic(yr date,country varchar(30),city varchar(30),PRIMARY KEY (yr));

create table evnt(evnt_no int, evnt_name varchar(30),yr date,PRIMARY KEY (evnt_no));

create table team(team_no int,team_evnt varchar(30),PRIMARY KEY (team_no));

create table schedules(schedules_no int,evnt_no int,start_date date,end_date date,time_ time,yr date,PRIMARY KEY (schedules_no, yr));

create table fan(fan_no int,player_no int,fan_name varchar(30),PRIMARY KEY (fan_no, player_no));

create table has1(fan_no int,player_no int,PRIMARY KEY (player_no, fan_no));

create table support(sponsor_no int, player_no int,team_no int,sponsor_name varchar(30),PRIMARY KEY (player_no, sponsor_no, team_no));

create table mascot(yr date,mascot varchar(30),PRIMARY KEY (yr, mascot));

create table type_(evnt_no int,type_ varchar(10),PRIMARY KEY (evnt_no, type_));

insert into player values(1,'male','Ackerley',60,170,'USA','1988-1-7');insert into player values(2,'male','Rabbie',77,180,'China','1990-11-12');insert into player values(3,'male','Cadman',90,178,'Korea','1991-12-26');

insert into player values(4,'male','Fabio',100,191,'USA','1993-5-21');insert into player values(5,'female','Fabia',61,170,'China','1992-10-17');insert into player values(6,'female','Wendy',70,175,'Korea','1990-10-11');

insert into player values(7,'female','Odell',48,160,'England','1991-3-6');insert into player values(8,'female','Jacey',55,160,'USA','1992-7-3');insert into player values(9,'male','Prue',55,160,'China','1992-7-3');

insert into player values(10,'male','Bard',90,190,'Korea','1992-7-3');insert into player values(11,'female','Hamelin',73,171,'USA','1995-4-3');insert into player values(12,'male','Perla',70,171,'China','1997-4-22');

insert into player values(13,'male','Brad',65,165,'Korea','1994-7-29');insert into player values(14,'female','Gabby',85,182,'USA','1996-12-31');insert into player values(15,'male','Travis',75,188,'China','1995-5-12');

insert into player values(16,'male','Hahn',77,180,'Korea','1995-4-23');insert into player values(17,'female','Aggie',90,186,'USA','1998-9-11');insert into player values(18,'male','Tad',60,168,'China','1991-11-19');

insert into player values(19,'male','Hai',73,160,'Korea','1998-2-15');

insert into olympic values('2018-2-9','Korea','PyeongChang');

insert into evnt values(1,'Ski','2018-2-9');

insert into evnt values(2,'Ice hockey','2018-2-9');

insert into evnt values(3,'Bobsleigh','2018-2-9');

insert into evnt values(4,'Figure','2018-2-9');

insert into evnt values(5,'Alpine skiing','2018-2-9');

insert into evnt values(6,'Skeleton','2018-2-9');

insert into evnt values(7,'Luge','2018-2-9');

insert into team values(1,'Ski');insert into team values(2,'Ski');insert into team values(3,'Ski');

insert into team values(4,'Ice hockey');insert into team values(5,'Ice hockey');insert into team values(6,'Ice hockey');

insert into team values(7,'Bobsleigh');insert into team values(8,'Bobsleigh');insert into team values(9,'Bobsleigh');

insert into team values(10,'Figure');insert into team values(11,'Figure');insert into team values(12,'Figure');

insert into team values(13,'Figure');insert into team values(14,'Figure');insert into team values(15,'Figure');

insert into team values(16,'Alpine skiing');insert into team values(17,'Alpine skiing');insert into team values(18,'Alpine skiing');

insert into team values(19,'Luge');insert into team values(20,'Luge');insert into team values(21,'Luge');

insert into schedules values(1,1,'2018-2-9','2018-2-13','12:00:00','2018-2-9');insert into schedules values(2,2,'2018-2-10','2018-2-16','13:00:00','2018-2-9');insert into schedules values(3,3,'2018-2-13','2018-2-20','09:00:00','2018-2-9');

insert into schedules values(4,4,'2018-2-15','2018-2-20','13:00:00','2018-2-9');insert into schedules values(5,5,'2018-2-18','2018-2-21','13:00:00','2018-2-9');

insert into schedules values(6,6,'2018-2-16','2018-2-23','13:00:00','2018-2-9');insert into schedules values(7,7,'2018-2-25','2018-2-28','13:00:00','2018-2-9');

insert into fan values(1,1,'Json');insert into fan values(2,1,'Janney');insert into fan values(3,1,'Avril');insert into fan values(4,1,'Earleen');insert into fan values(5,1,'Ece');

insert into fan values(6,2,'Ulla');insert into fan values(7,5,'Ulrika');insert into fan values(8,5,'Dahab');insert into fan values(9,6,'Daewon');insert into fan values(10,6,'Yakira');

insert into fan values(11,10,'Magnus');insert into fan values(12,10,'Mahin');insert into fan values(13,10,'Babs');insert into fan values(14,10,'Bach');insert into fan values(15,10,'Barnat');

insert into has1 values(1,1);insert into has1 values(2,1);insert into has1 values(3,1);insert into has1 values(4,1);insert into has1 values(5,1);

insert into has1 values(2,6);insert into has1 values(7,5);insert into has1 values(8,5);insert into has1 values(9,6);insert into has1 values(10,6);

insert into has1 values(11,10);insert into has1 values(12,10);insert into has1 values(13,10);insert into has1 values(14,10);insert into has1 values(15,10);

insert into support values(1, 1, 1 ,'samsung');insert into support values(1, 4, 2 ,'samsung');insert into support values(1, 8, 3 ,'samsung');

insert into support values(1, 11, 4 ,'samsung');insert into support values(1, 14, 5 ,'samsung');insert into support values(1, 17, 6 ,'samsung');

insert into support values(1, 7, 7 ,'samsung');insert into support values(1, 3, 8 ,'kakao');insert into support values(1, 6, 9 ,'samsung');

insert into support values(1, 10, 10 ,'samsung');insert into support values(1, 13, 11 ,'kakao');insert into support values(1, 16, 12 ,'kakao');

insert into support values(2, 19, 13 ,'LG');insert into support values(2, 2, 14 ,'LG');insert into support values(2, 5, 15 ,'LG');

insert into support values(2, 12, 16 ,'LG');insert into support values(2, 15, 17 ,'LG');insert into support values(2, 18, 18 ,'LG');

insert into mascot values('2018-2-9','Soohorang');insert into mascot values('2018-2-9','Bandabi');

insert into type_ values(1,'single');insert into type_ values(2,'single');insert into type_ values(3,'single');insert into type_ values(4,'double');

insert into type_ values(5,'double');insert into type_ values(6,'double');insert into type_ values(7,'double');

select player_name, player_nation

from player;

select mascot

from mascot;

select sponsor_name, player_name

from support as S, player as P

where S.player_no = P.player_no;

select evnt_name, start_date, end_date

from schedules as S, evnt as E

where S.evnt_no = E.evnt_no;

select evnt_name, type_

from evnt as E, type_ as T

where E.evnt_no = T.evnt_no;

select player_name, fan_name

from player as P, fan as F

where P.player_no = F.player_no;

select yr, country, city

from olympic;

select player_name, player_height, player_weight

from player

order by player_height asc, player_weight asc;

select Sponsor_name, player_name, team_evnt, fan_name

from support as S, player as P, team as T, fan as F

where S.player_no = P.player_no and P.player_no = F.fan_no and S.team_no = T.team_no;