Learning SQL on SQL Server 2005

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

   

   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 ...