erdcloud에서 기본틀생성
기본 이름 설정
mysql> CREATE TABLE student (
-> st_num int NOT NULL,
-> st_name varchar(20) NOT NULL,
-> st_term int NOT NULL DEFAULT 0,
-> st_point int NOT NULL DEFAULT 0
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE professor (
-> pr_num int NULL,
-> pr_name varchar(20) NOT NULL,
-> pr_age int NOT NULL,
-> pr_room varchar(45) NOT NULL,
-> pr_state varchar(45) NOT NULL DEFAULT '재직',
-> pr_position varchar(45) NOT NULL DEFAULT '조교수'
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE subject (
-> su_num int NOT NULL auto_increment,
-> su_code varchar(20) NOT NULL,
-> su_title varchar(45) NOT NULL,
-> su_point int NOT NULL DEFAULT 0,
-> su_time int NOT NULL DEFAULT 0,
-> primary key(su_num)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> CREATE TABLE course (
-> co_num int NOT NULL auto_increment,
-> co_term varchar(10) NOT NULL DEFAULT 1,
-> co_year int NOT NULL,
-> co_timetable varchar(100) NOT NULL,
-> co_pr_num int NOT NULL,
-> co_su_num int NOT NULL,
-> primary key(co_num)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE guide (
-> gu_num int NOT NULL auto_increment,
-> gu_year varchar(45) DEFAULT null,
-> gu_pr_num int NOT NULL,
-> gu_st_num int NOT NULL,
-> primary key(gu_num)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE attend (
-> at_num int NOT NULL auto_increment,
-> at_mid int NOT NULL DEFAULT 0,
-> at_final int NOT NULL DEFAULT 0,
-> at_attend int NOT NULL DEFAULT 0,
-> at_homework int NOT NULL DEFAULT 0,
-> at_score varchar(45) DEFAULT null,
-> at_pass varchar(1) DEFAULT 'n',
-> at_repetition varchar(1) DEFAULT 'n',
-> at_st_num int NOT NULL,
-> at_co_num int NOT NULL,
-> primary key(at_num)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER TABLE student ADD PRIMARY KEY (st_num);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE professor ADD PRIMARY KEY (pr_num);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE course ADD FOREIGN KEY (co_pr_num) REFERENCES professor (pr_num);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> ALTER TABLE course ADD FOREIGN KEY (co_su_num) REFERENCES subject (su_num);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> ALTER TABLE guide ADD FOREIGN KEY (gu_pr_num) REFERENCES professor (pr_num);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> ALTER TABLE guide ADD FOREIGN KEY (gu_st_num) REFERENCES student (st_num);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> ALTER TABLE attend ADD FOREIGN KEY (at_st_num) REFERENCES student (st_num);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> ALTER TABLE attend ADD FOREIGN KEY (at_co_num) REFERENCES course (co_num);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show tables;
+-------------------+
| Tables_in_college |
+-------------------+
| attend |
| course |
| guide |
| professor |
| student |
| subject |
+-------------------+
6 rows in set (0.00 sec)