Appendix A. The Student Database and Other Tables Used in This Book

Table A-1. Table definitions of the tables in the Student_course database

                        STUDENT
    STNO      NOT NULL      SMALLINT      PRIMARY KEY
    SNAME                   NVARCHAR(20)
    MAJOR                   NVARCHAR(4)
    CLASS                   SMALLINT
    BDATE                   SMALLDATETIME

DEPENDENT
    PNO                              SMALLINT
    DNAME                            NVARCHAR(20)
    RELATIONSHIP                     NVARCHAR(8)
    SEX                              CHAR(1)
    AGE                              SMALLINT

GRADE_REPORT
    STUDENT_NUMBER    NOT NULL       SMALLINT
    SECTION_ID        NOT NULL       SMALLINT
    GRADE                            CHAR(1)
    PRIMARY KEY(STUDENT_NUMBER, SECTION_ID)

SECTION
    SECTION_ID        NOT NULL       SMALLINT      PRIMARY KEY
    COURSE_NUM                       NVARCHAR(8)
    SEMESTER                         NVARCHAR(6)
    YEAR                             CHAR(2)
    INSTRUCTOR                       NVARCHAR(10)
    BLDG                             SMALLINT
    ROOM                             SMALLINT

DEPARTMENT_TO_MAJOR
    DCODE             NOT NULL       NVARCHAR(4)   PRIMARY KEY
    DNAME                            NVARCHAR(20)

COURSE
    COURSE_NAME                      NVARCHAR(20)
    COURSE_NUMBER     NOT NULL       NVARCHAR(8)   PRIMARY KEY    NOT NULL
    CREDIT_HOURS                     SMALLINT
    OFFERING_DEPT                    NVARCHAR(4)

 ROOM
    BLDG              NOT NULL       SMALLINT
    ROOM              NOT NULL       SMALLINT
    CAPACITY                         SMALLINT
    OHEAD                            NVARCHAR(1)
    PRIMARY KEY(BLDG, ROOM)

 PREREQ
    COURSE_NUMBER                    NVARCHAR(8)
    PREREQU                          NVARCHAR(8)
    PRIMARY KEY (COURSE_NUMBER, PREREQ)

  1. ER Diagram for the Student_course Database

    Diagram for the Student_course database
    Figure A-1. Diagram for the Student_course database

Brief English Description of the ER Diagram

  1. Student may be registered in one or more (M) Grade_Reports (Grade_report is for a specific course).

    A Grade_Report must relate to one and only one (1) Student.

    (Students may be in the database and not registered for any courses, but if a course is recorded in the Grade_report table, it must be related to one and only one student).

  2. A Section must have one or more (M) Grade_Reports (Sections only exist if they have students in them).

    A Grade_Report must relate to one and only one (1) Section.

  3. A Section must relate to one and only one (1) Course.

    A Course may be offered as one or more (M) Sections.

    (Courses may exist where they are not offered in a section, but a section, if offered, must relate to one and only one course).

  4. A Student may be related to one and only one (1) Department_to_major (A student may or may not have declared a major).

    A Department_to_major may have one or more (M) Students (A department may or may not have student-majors).

  5. A Course must be related to one and only one (1) Department_to_major.

    A Department_to_major may offer one or more (M) Courses.

  6. A Section must be offered in one and only one (1) Room.

    A Room may host one or more (M) Sections.

  7. A Course may have one or more (M) Prereq (A course may have one or more prerequisites).

    A Prereq may be a prerequisite for one or more (M) Courses.

  8. A Student may have one or more (M) Dependents.

    A Dependent must be related to one or more (N) Students.

Table A-2. Table definition of other tables that have been used in this book

                              PLANTS
    COMPANY                  NVARCHAR(20)
    PLANTLO                  NVARCHAR(15)
    PRIMARY KEY(COMPANY, PLANTLO)
 
CAP
    NAME                     NVARCHAR(9)
    LANGU                    NVARCHAR(7)
    PRIMARY KEY(NAME, LANGU)
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.147.44.182