M 5.1 – ASSIGNMENT
Name: Syeda Yumna
AIT 524 (DL2)
Table Creaton and Management, and Constraints
Entty Relatonship Diagram
Table creatons queries and screenshots for all tables in the above ERD are listed below,
INSTRUCTOR
CREATE TABLE INSTRUCTOR
(INSTRUCTOR_ID CHAR(9) CONSTRAINT INSTRUCTOR_ID_PK PRIMARY KEY,
L_NAME VARCHAR2(16),
F_NAME VARCHAR2(16),
ADDRESS VARCHAR2(30),
GENDER VARCHAR2(1),
AGE NUMBER(3),
SSN NUMBER(9),
PHONE NUMBER(12),
EMAIL VARCHAR2(255),
LOGIN_PASSWORD VARCHAR2(16)
);
select sysdate, ‘SYEDA YUMNA ZAIDI’ from dual;
SCHOOL
CREATE TABLE SCHOOL
(SCHOOL_ID NUMBER(9) CONSTRAINT SCHOOL_ID_PK PRIMARY KEY,
SCH_NAME VARCHAR2(16),
ADDRESS VARCHAR2(255)
);
select sysdate, ‘SYEDA YUMNA ZAIDI’ from dual;
STUDENT
CREATE TABLE STUDENT
(STUDENT_ID CHAR(9) CONSTRAINT STUDENT_ID_PK PRIMARY KEY,
L_NAME VARCHAR2(16),
F_NAME VARCHAR2(16),
ADDRESS VARCHAR2(30),
GENDER VARCHAR2(1),
AGE NUMBER(3),
SSN NUMBER(9),
PHONE NUMBER(12),
EMAIL VARCHAR2(255),
LOGIN_PASSWORD VARCHAR2(16),
SCHOOL_ID NUMBER(9),
CONSTRAINT FK_SCHOOL_ID FOREIGN KEY (SCHOOL_ID) REFERENCES SCHOOL(SCHOOL_ID)
);
select sysdate, ‘SYEDA YUMNA ZAIDI’ from dual;
COURSE
CREATE TABLE COURSE
(COURSE_ID NUMBER(9) CONSTRAINT COURSE_ID_PK PRIMARY KEY,
COURSE_NAME VARCHAR2(16),
COURSE_GROUP VARCHAR2(16),
EDUCATION_YEAR NUMBER(4),
TIME TIMESTAMP,
TERM VARCHAR2(16),
INSTRUCTOR_ID CHAR(9),
CLASSROOM_ID CHAR(9),
CONSTRAINT FK_INSTRUCTOR_ID FOREIGN KEY (INSTRUCTOR_ID) REFERENCES
INSTRUCTOR(INSTRUCTOR_ID),
CONSTRAINT FK_CLASSROOM_ID FOREIGN KEY (CLASSROOM_ID) REFERENCES
CLASSROOM(CLASSROOM_ID)
);
select sysdate, ‘SYEDA YUMNA ZAIDI’ from dual;
ENROLLMENT
CREATE TABLE ENROLLMENT
(ENROLLMENT_ID NUMBER(9) CONSTRAINT ENROLLMENT_ID_PK PRIMARY KEY,
COURSE_ID NUMBER(22),
STUDENT_ID CHAR(9),
GRADE VARCHAR2(16),
CONSTRAINT FK_COURSE_ID FOREIGN KEY (COURSE_ID) REFERENCES COURSE(COURSE_ID),
CONSTRAINT FK_STUDENT_ID FOREIGN KEY (STUDENT_ID) REFERENCES STUDENT(STUDENT_ID)
);
select sysdate, ‘SYEDA YUMNA ZAIDI’ from dual;
CLASSROOM
CREATE TABLE CLASSROOM
(CLASSROOM_ID CHAR(9) CONSTRAINT CLASSROOM_ID_PK PRIMARY KEY,
ROOM_CAPACITY NUMBER(9)
);
select sysdate, ‘SYEDA YUMNA ZAIDI’ from dual;
