[MySQL] ERD 대학수강프로그램 1. 생성

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)