4.3 학사관리 예제 만들기

MySQL 새로운 데이터베이스 생성
create database haksa_database;

생성된 데이터베이스 확인
show databases;

사용자 생성 및 권한 부여
grant all privileges on haksa_database.* to haksa_admin@localhost identified by '1234' with grant option;

use haksasystem
--학사시스템 데이터베이스 생성

CREATE TABLE department
(
dept_no varchar(10) not null primary key,
dept_name nvarchar(10),
chief varchar(10),
asssistant nvarchar(10),
dept_tel nchar(10),
location varchar(10))
Go
------ 학과테이블 생성

CREATE TABLE professor
(
p_no varchar(10) not null primary key,
p_name nvarchar(10),
grade nvarchar(10),
room_no varchar(10),
p_tel nchar(10),
p_address nchar(20),
p_birth varchar(10),
join_date nchar(10),
pay_step varchar(10))
Go
------- 교수테이블 생성

CREATE TABLE course
(
c_no int not null primary key,
c_name char(20),
times char(1) check(times>='1' AND times<='5'),
point char(1),
r_o char(1))
Go
------- 확실한 강좌테이블 생성

CREATE TABLE student
(
s_no int not null primary key,
s_name varchar(10),
dept varchar(12),
year char(1),
s_tel nchar(11),
s_address nchar(20),
advisor char(4),
s_birth char(8))
Go
----- 학생

CREATE TABLE lecture
(
c_no int identity not null,
p_no int not null,
day_week char(3),
time char(1) check(time>='1' AND time<='9'),
room_no char(4)
primary key(c_no,p_no))
Go

CREATE TABLE enrollment
(
c_no char(4) not null,
s_no char(4) not null,
grade char(1),
primary key(c_no,s_no))
GO

INSERT INTO department values
(001,'컴퓨터공학과',0001,'이지은',3333,'T101');
INSERT INTO department values
(002,'정보과학과',0015,'김영미',3334,'T201');
INSERT INTO department values
(003,'정보통신학과',0022,'박순애',3335,'T301');

INSERT INTO professor values
(0001,'김수철','교수','T102',4444,'서울',19520401,00880301,4444);
INSERT INTO professor values
(0002,'이기문','교수','T103',4445,'전화',19540605,00890901,4445);
INSERT INTO professor values
(0003,'박동진','부교수','T302',4555,'서울',19610105,00910301,4555);

INSERT INTO course values
(0001,'정보사회와 컴퓨터',3,3,'R');
INSERT INTO course values
(0002,'데이터베이스',3,3,'R');
INSERT INTO course values
(0003,'네트워크',3,3,'R');
INSERT INTO course values
(0004,'C언어',3,3,'O');

INSERT INTO student values
(9911001,'정찬우','컴퓨터공학과',2,023355519,'서울',0001,19800505);
INSERT INTO student values
(9912002,'김정길','정보과학과',2,03463355411,'경기도',0015,19801215);
INSERT INTO student values
(9911003,'강재우','컴퓨터공학과',2,0325491115,'인천',0002,19801119);
INSERT INTO student values
(9813009,'이수자','정보통신학과',3,0428601144,'대전',0022,19791205);
INSERT INTO student values
(9811015,'이길녀','컴퓨터공학과',3,023386634,'서울',0003,19791130);

INSERT INTO lecture values
(0001,0001,'mon',1,'T447');
INSERT INTO lecture values
(0001,0002,'tue',5,'T448');
INSERT INTO lecture values
(0002,0005,'wed',1,'T337')
INSERT INTO lecture values
(0002,0005,'thu',1,'T337)