Logical Design (map ER to Relational) The full set of normalized tables for the

Question

Logical Design (map ER to Relational) The full set of normalized tables for the University Database is as follows: Schema Department {dept_id, dept_name} Course {course_id, title, credits} Faculty {faculty_id, name, room_id, phone, dept_id} Student {student_id, last_name , first_name, street, city, state, zip, start_term, birth_date, major_id, Phone, student_type} Term {term_id , term_desc , start_date , end_date} Section (section_id , course_id , section , term_id , faculty_id , day , max_count , start_time , end_time , room_id} Registration (student_id , section_id , midterm_grade, final_grade} Major {major_id , major_desc} Location {room_id , building, room_no, capacity, room_type , room_description}

1) Create the tables for the university database using MySQL DBMS. To your report, you will add a list of the CREATE TABLE statements for the university database. Specify as many constraints (key, referential integrity) as you can in the relational schema. Choose appropriate data types for each attribute. a. Define the primary key, foreign key, NOT NULL, CHECK and UNIQUE constraints in the CREATE TABLE statement. If not possible, use ALTER TABLE statement to add a constraint. b. If a table does not have a foreign key, leave the entry blank. (Note: Some tables have a composite primary key. Identify all composite key attributes for such tables.)

Load the records provided to you into each of the tables that you have created. Your data should be kept in a file so that it can be easily reloaded.

Explanation / Answer

DEPARTMENT {DEPT_ID, DEPT_NAME}

--CREATE DEPARTMENT TABLE

CREATE TABLE DEPARTMENT(DEPT_ID NUMBER(10) NOT NULL,

                        DEPT_NAME VARCHAR2(50) NOT NULL,

                        CONSTRAINT DEPT_ID_PK PRIMARY KEY (DEPT_ID)

                        );

COURSE {COURSE_ID, TITLE, CREDITS

--CREATE COURSE TABLE

CREATE TABLE COURSE(COURSE_ID NUMBER(10) NOT NULL,

                    TITLE VARCHAR2(50) NOT NULL,

                    CREDITS NUMBER(5) NOT NULL,

CONSTRAINT COURSE_ID_PK PRIMARY KEY (COURSE_ID)

                    );

FACULTY {FACULTY_ID, NAME, ROOM_ID, PHONE, DEPT_ID}

--CREATE FACULTY TABLE

CREATE TABLE FACULTY(FACULTY_ID NUMBER(10) NOT NULL,

   NAME VARCHAR2(50) NOT NULL,

                     ROOM_ID NUMBER(4) NOT NULL,

   PHONE NUMBER(15) UNIQUE,

   DEPT_ID NUMBER(10) NOT NULL,

   CONSTRAINT FACULTY_ID_PK PRIMARY KEY (FACULTY_ID),

                     CONSTRAINT FK_EMP_DEPT_ID FOREIGN KEY (EMP_DEPT_ID) REFERENCES DEPARTMENT(DEPT_ID)                      

   );

STUDENT {STUDENT_ID, LAST_NAME , FIRST_NAME, STREET, CITY, STATE, ZIP, START_TERM, BIRTH_DATE, MAJOR_ID, PHONE, STUDENT_TYPE}

--CREATE STUDENT TABLE

CREATE TABLE STUDENT(STUDENT_ID NUMBER(10) NOT NULL,

   LAST_NAME VARCHAR2(50) NOT NULL,

   FIRST_NAME VARCHAR2(50) NOT NULL,

   STREET VARCHAR2(50) NOT NULL,

   CITY VARCHAR2(50) NOT NULL,

   STATE VARCHAR2(50) NOT NULL,

   ZIP NUMBER(6) NOT NULL,

   START_TERM DATE NOT NULL,

   BIRTH_DATE DATE,

   MAJOR_ID NUMBER(5) NOT NULL,

   PHONE NUMBER(15) UNIQUE,

   STUDENTY_TYPE VARCHAR2(50) NOT NULL,

   CONSTRAINT STUDENT_ID_PK PRIMARY KEY (COURSE_ID)

  );

TERM {TERM_ID , TERM_DESC , START_DATE , END_DATE}

--CREATE TERM TABLE

CREATE TABLE TERM(TERM_ID NUMBER(6),
                TERM_DESC VARCHAR2(20),
                START_DATE DATE,

                END_DATE DATE,
                CONSTRAINT TERM_ID_PK PRIMARY KEY (TERM_ID)
                );

SECTION (SECTION_ID , COURSE_ID , SECTION , TERM_ID , FACULTY_ID , DAY , MAX_COUNT , START_TIME , END_TIME , ROOM_ID}

--CREATE SECTION TABLE

CREATE TABLE SECTION(SECTION_ID NUMBER(6),
                     COURSE_ID VARCHAR2(7) CONSTRAINT       SECTION_COURSE_ID_NN NOT NULL,
                   SECTION NUMBER(2) CONSTRAINT SECTION _NN NOT NULL,

                     TERM_ID NUMBER(6) CONSTRAINT TERMID_NN NOT NULL,
                     FACULTY_ID NUMBER(6),
                     DAY VARCHAR2(10),

                     MAX_COUNT NUMBER(4) CONSTRAINT MAX_COUNT_NN NOT NULL,
                     START_TIME DATE,
                     END_TIME DATE,

                     ROOM_ID NUMBER(6),
                     CONSTRAINT SECTION_ID_PK PRIMARY KEY (SECTION_ID),
                     CONSTRAINT SECTION_COURSE_ID_FK FOREIGN KEY (COURSE_ID) REFERENCES COURSE(COURSE_ID),      
                     CONSTRAINT SECTION_TERM_ID_FK FOREIGN KEY (TERM_ID) REFERENCES TERM(TERM_ID),
                   CONSTRAINT SECTION_FACULTY_ID_FK FOREIGN KEY (FACULTY_ID) REFERENCES FACULTY(FACULTY_ID)

                     );

REGISTRATION (STUDENT_ID , SECTION_ID , MIDTERM_GRADE, FINAL_GRADE}

--CREATE REGISTRATION TABLE                     

CREATE TABLE REGISTRATION(STUDENT_ID VARCHAR2(6),
                        SECTION_ID NUMBER(6),
                        MIDTERM_GRADE CHAR(1),
                        MIDTERM_GRADE CHAR(1),

                        CONSTRAINT REGISTRATION_PK PRIMARY KEY (STUDENT_ID, SECTION_ID),
                        CONSTRAINT REGISTRATION_STUDENT_ID_FK FOREIGN KEY (STUDENT_ID) REFERENCES STUDENT(STUDENT_ID),
                        CONSTRAINT REGISTRATION_SECTION_ID_FK FOREIGN KEY (SECTION_ID) REFERENCES SECTION (SECTION_ID));

MAJOR {MAJOR_ID , MAJOR_DESC}

--CREATE MAJOR TABLE

CREATE TABLE MAJOR(MAJOR _ID VARCHAR2(6),

                   MAJOR_DESC VARCHAR2(200),

CONSTRAINT MAJOR_ID_PK PRIMARY KEY (MAJOR_ID)

);

LOCATION {ROOM_ID , BUILDING, ROOM_NO, CAPACITY, ROOM_TYPE , ROOM_DESCRIPTION}

--CREATE LOCATION TABLE

CREATE TABLE LOCATION(ROOM_ID NUMBER(6),
                      BUILDING VARCHAR2(10),
                      ROOM_NO NUMBER(6),
                      CAPACITY NUMBER(5),

    ROOM_TYPE VARCHAR2(10),

    ROOM_DESCRIPTION VARCHAR2(250),
    CONSTRAINT LOCATION_ROOM_ID_PK PRIMARY KEY (ROOM_ID)

    );
                     

Related Questions

Navigate

• Browse (All)
• Browse L
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.