SQL활용 #1 - 학사관리시스템

• 현실에 대한 상식이 기반

• 학사관리시스템

1. 학생 (학번(PK), 이름, 연락처)

2. 교수 (교수코드(PK), 이름, 연락처)

3. 교과목(교과목코드(PK), 교과목명, ... )

4. 수강 (수강코드(PK), ... )

1. VSCODE - ERD

2. ORACLE

1. 결과화면

2. ERD - DATA MODELER

전체코드

CREATE TABLE CLASSROOM1
(
  CLSNO   VARCHAR2(15)  NOT NULL,
  CLSADDR VARCHAR2(100),
  CONSTRAINT PK_CLASSROOM1 PRIMARY KEY (CLSNO)
);

COMMENT ON TABLE CLASSROOM1 IS '강의실테이블';

COMMENT ON COLUMN CLASSROOM1.CLSNO IS '강의실번호';

COMMENT ON COLUMN CLASSROOM1.CLSADDR IS '강의실위치';

CREATE TABLE COURSE1
(
  CRSCODE NUMBER NOT NULL,
  STDNO   NUMBER NOT NULL,
  SUBCODE NUMBER NOT NULL,
  CONSTRAINT PK_COURSE1 PRIMARY KEY (CRSCODE)
);

COMMENT ON TABLE COURSE1 IS '수강테이블';

COMMENT ON COLUMN COURSE1.CRSCODE IS '수강번호';

COMMENT ON COLUMN COURSE1.STDNO IS '학번';

COMMENT ON COLUMN COURSE1.SUBCODE IS '과목코드';

CREATE TABLE PROF1
(
  PROFCODE NUMBER       NOT NULL,
  PROFNAME VARCHAR2(20),
  PROFTEL  VARCHAR2(20),
  CONSTRAINT PK_PROF1 PRIMARY KEY (PROFCODE)
);

COMMENT ON TABLE PROF1 IS '교수테이블';

COMMENT ON COLUMN PROF1.PROFCODE IS '교수번호';

COMMENT ON COLUMN PROF1.PROFNAME IS '교수이름';

CREATE TABLE STUDENT1
(
  STDNO   NUMBER       NOT NULL,
  STDNAME VARCHAR2(20),
  STDTEL  VARCHAR2(20),
  CONSTRAINT PK_STUDENT1 PRIMARY KEY (STDNO)
);

COMMENT ON TABLE STUDENT1 IS '학생테이블';

COMMENT ON COLUMN STUDENT1.STDNO IS '학번';

COMMENT ON COLUMN STUDENT1.STDNAME IS '학생이름';

CREATE TABLE SUBJECT1
(
  SUBCODE  NUMBER       NOT NULL,
  SUBTITLE VARCHAR2(30),
  PROFCODE NUMBER       NOT NULL,
  CLSNO    VARCHAR2(15) NOT NULL,
  CONSTRAINT PK_SUBJECT1 PRIMARY KEY (SUBCODE)
);

COMMENT ON TABLE SUBJECT1 IS '과목테이블';

COMMENT ON COLUMN SUBJECT1.SUBCODE IS '과목코드';

COMMENT ON COLUMN SUBJECT1.SUBTITLE IS '과목명';

COMMENT ON COLUMN SUBJECT1.PROFCODE IS '교수번호';

COMMENT ON COLUMN SUBJECT1.CLSNO IS '강의실번호';

ALTER TABLE SUBJECT1
  ADD CONSTRAINT FK_PROF1_TO_SUBJECT1
    FOREIGN KEY (PROFCODE)
    REFERENCES PROF1 (PROFCODE);

ALTER TABLE COURSE1
  ADD CONSTRAINT FK_STUDENT1_TO_COURSE1
    FOREIGN KEY (STDNO)
    REFERENCES STUDENT1 (STDNO);

ALTER TABLE COURSE1
  ADD CONSTRAINT FK_SUBJECT1_TO_COURSE1
    FOREIGN KEY (SUBCODE)
    REFERENCES SUBJECT1 (SUBCODE);

ALTER TABLE SUBJECT1
  ADD CONSTRAINT FK_CLASSROOM1_TO_SUBJECT1
    FOREIGN KEY (CLSNO)
    REFERENCES CLASSROOM1 (CLSNO);