6WuaNs8y

1drop database if exists 박범준;
2create database 박범준;
3use 박범준;
4
5create table 박범준_department (
6	Dept_ID int NOT NULL,
7    Dept_Name char(20) NOT NULL,
8    primary key(Dept_ID)
9);
10insert into 박범준_department
11values (1001, '소프트웨어학과'),
12	   (1002, '전자전기공학과'),
13       (1003, '화학공학과'),
14       (1004, '수학교육과');
15
16create table 박범준_instructor (
17	Ins_id int,
18	Ins_name char(10) NOT NULL,
19    Ins_dept int NOT NULL,
20    primary key(Ins_id),
21    foreign key(Ins_dept) references 박범준_department(Dept_ID) 
22);
23insert into 박범준_instructor
24values (101, '이석균', 1001),
25	   (102, '이한구', 1004),
26       (103, '윤혜순', 1004),
27       (104, '이한구', 1004),
28       (105, '우진운', 1001),
29       (106, '박소현', 1001),
30       (107, '박규식', 1001);
31
32create table 박범준_student (
33	Stu_id int NOT NULL,
34    Stu_name char(10) NOT NULL,
35    Stu_major int NOT NULL,
36    Stu_address char(10) NOT NULL,
37    Stu_gpa double,
38    Stu_bdate date NOT NULL,
39    primary key(Stu_id),
40    foreign key(Stu_major) references 박범준_department(Dept_ID)
41);
42insert into 박범준_student
43values (32151675, '박범준', 1001, '인천광역시', 3.5, 19961106),
44	   (32151453, '김종혁', 1001, '부산광역시', 2.7, 19960813),
45       (32181104, '최인규', 1004, '서울특별시', 3.3, 19960204),
46       (32141283, '장재성', 1002, '경기도 화성시', 4.2, 19950423);
47
48create table 박범준_course (
49	Cou_id char(12) NOT NULL,
50    Cou_name char(15) NOT NULL,
51    Cou_instructor int,
52    Cou_prerequisite char(15),
53    primary key(Cou_id),
54    foreign key(Cou_instructor) references 박범준_instructor(Ins_id),
55    foreign key(Cou_prerequisite) references 박범준_course(Cou_id)
56);
57insert into 박범준_course
58values ('452660-2', '데이터베이스 기초', 101, NULL),
59	   ('391020-2008', '일반수학1', 104, NULL),
60       ('447940-2002', '프로그래밍1', 106, NULL),
61       ('391040-8', '일반수학2', 103, '391020-2008'),
62       ('339170-5', '멀티미디어 시스템', 107, NULL),
63       ('447950-3', '프로그래밍2', 105, '447940-2002');
64
65create table 박범준_course_taken (
66	Ct_id int NOT NULL auto_increment,
67    Ct_sid int NOT NULL,
68    Ct_cid char(12) NOT NULL,
69    Ct_grade char(3),
70    Ct_year char(6),
71    primary key (Ct_id),
72    foreign key(Ct_sid) references 박범준_student(Stu_id),
73    foreign key(Ct_cid) references 박범준_course(Cou_id)
74);
75insert into 박범준_course_taken (Ct_sid, Ct_cid, Ct_grade, Ct_year)
76values (32151675, '452660-2', 'B', '2018'),
77	   (32151675, '447940-2002', 'C', '2015'),
78       (32151675, '447950-3', 'B', '2015'),
79       (32141283, '391020-2008', 'B', '2014'),
80       (32141283, '391040-8', 'A', '2014'),
81       (32181104, '339170-5', 'D', '2018'),
82       (32181104, '452660-2', 'F', '2018'),
83       (32141283, '447940-2002', 'B', '2015'),
84       (32151675, '339170-5', 'A', '2018'),
85       (32151453, '452660-2', 'C', '2018'),
86       (32151453, '447940-2002', 'B', '2016'),
87	   (32151675, '447950-3', 'F', '2016');