DB설계) 학생 테이블과 성적 테이블 설계하기

1. 데이터베이스 설계하기

db 이름: student_db
1) 학생 테이블

열 이름	student_info	데이터타입	최대길이	null 허용 여부
이름	student_name	char	5	x
학년	student_grade	tinyint	1	o
학번 (PK)	student_number	int	8	x
성별	student_gender	char	6(Male/Female)	x
생년월일	student_birth	date	YYYY-MM-DD	x
학과코드 (FK)	major_code	smallint	3	x

student_grade는 1~4까지만 표기되므로 tinyint 타입을 선택했고, 학과코드는 세 자리 정수로 설정해서 smallint 타입을 선택하였다.
2) 과목 테이블

열 이름	subject_info	데이터타입	최대길이	null 허용 여부
학과코드 (FK) (PK)	major_code	smallint	3 (111)	x
과목코드 (PK)	subject_code	int	5 (11111)	x
과목이름	subject_name	char	20	x
과목학점	subject_credit	tinyint	1	x

과목은 학과코드-과목코드로 표기되므로 학과코드와 과목코드를 같이 PK로  설정하였다.
3) 학과 테이블

열 이름	major_info	데이터타입	최대길이	null 허용여부
학과코드 (PK)	major_code	smallint	3	x
학과이름	major_name	char	20	x

4) 수강 테이블

열 이름	sign_info	데이터타입	최대길이	null 허용 여부
학과코드 (FK) (PK)	major_code	smallint	3	x
과목코드 (FK) (PK)	subject_code	int	5	x
학번 (FK) (PK)	student_number	int	8	x
수강학기	sign_semester	char	3 (4/1)	x
과목성적	subject_score	char	4 (PASS / FAIL 도 포함)	o

한 과목은 여러 학생이 들으므로 한 과목당 한 학생이 듣는 것을 PK로 설정해야할 것 같아서 학과코드, 과목코드, 학번을 PK로 지정하였다.
최종적으로 출력할 테이블은 한 학생의 전체  성적 테이블이다.

성적테이블
학과 이름	major_name
학번	student_number
이름	student_name
학년	student_grade
과목 이름	subject_name
과목 성적	subject_score
과목 학점	subject_credit

 

2. 테이블 만들 순서 정하기

FK가 없는 major_info 테이블을 먼저 만들고, sign_info 테이블은 모든 테이블의 PK를 FK로 받으므로 제일 마지막에 만든다.
 

3. major_info 테이블 만들고 데이터 넣기

create table major_info (
	major_code smallint not null primary key,
    major_name char(20) not null
);

show columns from major_info;

insert into major_info values ('111', '기계공학과');
insert into major_info values ('222', '컴퓨터공학과');
insert into major_info values ('333', '수학과');

select * from major_info;

 

4. student_info 테이블 만들고 데이터 넣기

FK를 설정해 주기 위해 마지막 줄에
foreign key (FK이름) references 마더테이블이름(PK이름)
를 추가해 주면 FK가 마더테이블의 PK를 참조하게 된다.

create database student_db;
use student_db;

create table student_info (
	student_name char(5) not null,
    student_grade tinyint,
    student_number int not null primary key,
    student_gender char(6) not null,
    student_birth date not null,
    major_code smallint not null
    foreign key (major_code) references major_info(major_code)
);

show columns from student_info;

insert into student_info values ('나는야', '4', 19111222, 'Female', '20000309', '111');
insert into student_info values ('졸업반', '4', 19111223, 'Female', '20001010', '222');
insert into student_info values ('새내기', '1', 22111333, 'Male', '20030712', '111');
insert into student_info values ('복학생', '2', 19111333, 'Male', '20000929', '333');

select * from student_info;

 

5. subject_info 테이블 만들고 데이터 넣기

마찬가지로 FK설정을 하고, 여러 개의 PK 값을 설정할 때는 밑에 
primary key (PK이름, PK이름, ...)
으로 하면 된다.

create table subject_info (
	major_code smallint not null,
    subject_code int not null,
    subject_name char(20) not null,
    subject_credit tinyint not null,
    primary key (major_code, subject_code),
    foreign key (major_code) references major_info(major_code)
);

show columns from subject_info;

insert into subject_info values (111, 11111, '공기역학', 3);
insert into subject_info values (111, 11122, '진동학', 3);
insert into subject_info values (111, 11133, '제어공학', 3);
insert into subject_info values (111, 11144, '전산열유체', 3);
insert into subject_info values (222, 22111, '컴퓨터 프로그래밍', 3);
insert into subject_info values (333, 33155, '암호의 수학적 이해', 3);
insert into subject_info values (333, 33100, '미분적분학1', 2);
delete from subject_info where student_number='22300242';

select * from subject_info;

 

6. sign_info 테이블 만들기

create table sign_info (
	major_code smallint not null,
    subject_code int not null,
    student_number int not null,
    sign_semester char(4) not null,
    subject_score char(5),
    primary key (major_code, subject_code, student_number),
    foreign key (student_number) references student_info(student_number),
    foreign key (major_code) references major_info(major_code)
);

insert into sign_info values (111, 11111, 19111222, '3/2', 'B+');
insert into sign_info values (111, 11122, 19111222, '4/1', 'A0');
insert into sign_info values (111, 11133, 19111222, '4/1', 'A+');
insert into sign_info values (111, 11144, 19111222, '4/2', 'PASS');
insert into sign_info values (333, 33155, 19111222, '4/2', 'A+');
insert into sign_info values (111, 11122, 19111223, '4/2', 'A+');

select * from sign_info;

 

7. 조인해서 성적 테이블 데이터 보기

select major_info.major_name, student_info.student_number, student_info.student_name,
		student_info.student_grade, subject_info.subject_name, sign_info.subject_score, subject_info.subject_credit
	from sign_info
    left join student_info
		on student_info.student_number = sign_info.student_number
    left join subject_info
		on subject_info.subject_code = sign_info.subject_code
	left join major_info
		on major_info.major_code = sign_info.major_code
	where student_info.student_number=19111222;

만약 Error Code: 1052. Column 'student_number' in field list is ambiguous 가 뜬다면,  value 이름이 겹쳐서 어느 테이블의 value를 가져오는 건지 확실하게 표시해 주면 된다.

 

7-1. view로 만들기

성적 테이블을 더 쉽게 보기 위해서 이 테이블을 view로 지정해도 된다.

create view student_grade
as
select major_info.major_name, student_info.student_number, student_info.student_name,
		student_info.student_grade, subject_info.subject_name, sign_info.subject_score, subject_info.subject_credit
	from sign_info
    left join student_info
		on student_info.student_number = sign_info.student_number
    left join subject_info
		on subject_info.subject_code = sign_info.subject_code
	left join major_info
		on major_info.major_code = sign_info.major_code
    
select * from student_grade
	where student_number=19111222;

select * from student_grade
	where student_number=19111223;

 

8. 개선사항

아쉬웠던 점은 재수강과 복수전공을 고려하지 않았다는 점이다. 
학생 하나하나마다 테이블을 만드는 건 오바이기 때문에 과목을 수강한 학생으로 성적을 냈는데, 이때 수강 테이블의 PK값을 무엇으로 잡을지에 대한 고민이 있었다. 하지만 지금 테이블은 같은 학생이 같은 전공, 과목코드의 수업을 들을 수가 없다(PK값이 겹치기 때문에). ==> 수강테이블에 일련번호를 추가해서 그걸 PK값으로 잡으면 한 학생이 같은 과목을 수강해도 계속 데이터가 쌓이기 때문에 나중에 출력할 때 같은 데이터가 있으면 신규 데이터를 출력하도록 하면 될 것 같다.
찾아보니 우리 학교는 약 11,000명의 재학생과 졸업생들의 성적까지 있을 텐데, 이렇게 많은 데이터를 어떻게 설계해서 보관하는 걸까..
그래도 복수전공은 생각보다 쉽게 될 것 같다.
복수전공은 소속에서 1:m으로 설정해서 복수전공은 불가능하지만, student_info에 복수전공 행을 만들고 복수전공이 없는 학생들을 위해 null 값을 허용하면 되지 않을까 싶다.
이에 대한 의견이나 조언이 있으면 언제나 댓글 남겨주시면 감사하겠습니다..
 
전체코드

create database student_db;
use student_db;

-- major_info table
	create table major_info (
	major_code smallint not null primary key,
    major_name char(20) not null
);

insert into major_info values ('111', '기계공학과');
insert into major_info values ('222', '컴퓨터공학과');
insert into major_info values ('333', '수학과');


-- student_info table
create table student_info (
	student_name char(5) not null,
    student_grade tinyint,
    student_number int not null primary key,
    student_gender char(6) not null,
    student_birth date not null,
    major_code smallint not null,
    foreign key (major_code) references major_info(major_code)
);

insert into student_info values ('나는야', '4', 19111222, 'Female', '20000309', '111');
insert into student_info values ('졸업반', '4', 19111223, 'Female', '20001010', '222');
insert into student_info values ('새내기', '1', 22111333, 'Male', '20030712', '111');
insert into student_info values ('복학생', '2', 19111333, 'Male', '20000929', '333');


-- subject_info table
create table subject_info (
	major_code smallint not null,
    subject_code int not null,
    subject_name char(20) not null,
    subject_credit tinyint not null,
    primary key (major_code, subject_code),
    foreign key (major_code) references major_info(major_code)
);

insert into subject_info values (111, 11111, '공기역학', 3);
insert into subject_info values (111, 11122, '진동학', 3);
insert into subject_info values (111, 11133, '제어공학', 3);
insert into subject_info values (111, 11144, '전산열유체', 3);
insert into subject_info values (222, 22111, '컴퓨터 프로그래밍', 3);
insert into subject_info values (333, 33155, '암호의 수학적 이해', 3);
insert into subject_info values (333, 33100, '미분적분학1', 2);


-- sign_info table
create table sign_info (
	major_code smallint not null,
    subject_code int not null,
    student_number int not null,
    sign_semester char(4) not null,
    subject_score char(5),
    primary key (major_code, subject_code, student_number),
    foreign key (student_number) references student_info(student_number),
    foreign key (major_code) references major_info(major_code)
);

insert into sign_info values (111, 11111, 19111222, '3/2', 'B+');
insert into sign_info values (111, 11122, 19111222, '4/1', 'A0');
insert into sign_info values (111, 11133, 19111222, '4/1', 'A+');
insert into sign_info values (111, 11144, 19111222, '4/2', 'PASS');
insert into sign_info values (333, 33155, 19111222, '4/2', 'A+');
insert into sign_info values (111, 11122, 19111223, '4/2', 'A+');


-- 테이블 조인해서 성적 데이터 조회하기
select major_info.major_name, student_info.student_number, student_info.student_name,
		student_info.student_grade, subject_info.subject_name, sign_info.subject_score, subject_info.subject_credit
	from sign_info
    left join student_info
		on student_info.student_number = sign_info.student_number
    left join subject_info
		on subject_info.subject_code = sign_info.subject_code
	left join major_info
		on major_info.major_code = sign_info.major_code
	where student_info.student_number=19111222;
    
-- view로 만들기
create view student_grade
as
select major_info.major_name, student_info.student_number, student_info.student_name,
		student_info.student_grade, subject_info.subject_name, sign_info.subject_score, subject_info.subject_credit
	from sign_info
    left join student_info
		on student_info.student_number = sign_info.student_number
    left join subject_info
		on subject_info.subject_code = sign_info.subject_code
	left join major_info
		on major_info.major_code = sign_info.major_code;
    
select * from student_grade
	where student_number=19111222;
select * from student_grade
	where student_number=19111223;