schema.txt
Relationships
The relationships and grouping between the different tables are as shown on the figure below.
Database Design for Dowling College Student Advisement System
Introduction
Tables
Database Creation File for MySQL
Relationships
Detailed Description
Introduction
In order to build the Student Advisement System we need a database to store
important data and other information. After carefully examining Dowling College
Catalog 2002-2003 we came up with the following database structure that serves
the need of our project independent of any other systems already in place at
the college.Any future references to the catalog in this document refer to the
Dowling College Catalog 2002-2003.
Tables
Our database consists
of 18 tables listed alphabetically below.
COLLEGEWIDE_REQ
CORE_REQ
COURSES
DEGREES
DEPARTMENTS
DISCIPLINES
DIVISIONS
DOUBLEFLAGS
GENERALS
LEVELS
MAJORS
MAJOR_REQ
MINORS
MINOR_REQ
SCHOOLS
TRANSFERS
USERS
Database
Creation File for MySQL
schema.txt
Relationships
The relationships and grouping between the different tables are as shown on
the figure below.
COLLEGEWIDE_REQ This table stores all college wide requirments as descibed on p.84 in the Catalog. Total of 3 fields as described below: |
||||||
| Field Name | Description | Data Type | Null | Key | Default | Extra |
|
ID
|
Unigue
primary key for each separate college wide requirment.
|
int(11)
|
-
|
PRI
|
NULL
|
auto_increment
|
|
CreditsRequired
|
The
number of credits required to satisfy this requirement. Please note that
it has to be satisfied by equal or more credits but not by less.
|
int(11)
|
-
|
-
|
0
|
-
|
|
CourseList
|
This
field uses the requirements language specifications as described in the
documentation to describe the requirement. Usually involves one or more
classes with different relationships betewwen them
|
varchar(255)
|
-
|
-
|
-
|
-
|
CORE_REQ This table stores all core requirments as descibed on p.84 in the Catalog. Total of 3 fields as described below: |
||||||
| Field Name | Description | Data Type | Null | Key | Default | Extra |
|
ID
|
Unigue
primary key for each separate core requirment.
|
int(11)
|
-
|
PRI
|
NULL
|
auto_increment
|
|
CreditsRequired
|
The
number of credits required to satisfy this requirement. Please note that
it has to be satisfied by equal or more credits but not by less.
|
int(11)
|
-
|
-
|
0
|
-
|
|
CourseList
|
This
field uses the requirements language specifications as described in the
documentation to describe the requirement. Usually involves one or more
classes with different relationships betewwen them
|
varchar(255)
|
-
|
-
|
-
|
-
|
|
|
||||||
| Field Name | Description | Data Type | Null | Key | Default | Extra |
|
CourseID
|
Unigue
primary key for each separate course.
|
int(11)
|
|
PRI
|
NULL
|
auto_increment
|
|
CourseNumber
|
The
course number such as 009, 171 etc.
|
varchar(5)
|
-
|
-
|
-
|
-
|
|
CourseNumberNew
|
The
new course numbers that are expected to get in place soon
|
varchar(5)
|
-
|
-
|
-
|
-
|
| CourseLetter | A,C or A/N as descibed in the catalog | varchar(5) | Yes | - | - | - |
| CourseCode |
Complete Course Code
such as |
varchar(12) | Null | Key | Default | Extra |
| CourseName | The full wordy name of the Course such as Introduction to Computer Science | varchar(100) | - | - | - | - |
| Credits | The number of credits a course carriers | int(11) | - | - | - | - |
| LevelID | The Level of the Course, 1 for lower, 2 for upper. All existing levels are described in the LEVELS table and this is in fact a foreign key pointing to the appropriate record in the LEVELS table | int(11) | - | - | 0 | - |
| CrossList | This includes the CourseID of one or more courses that can be cross listed with this course. The Requirement Language Specifications can be used to populate this field. Example 1*2*3 - meaning that this course may be crosslisted wth course number 1 and course number 2 and course number 3 from our database. | varchar(255) | Yes | - | NULL | - |
| PreReqList | This includes the CourseID of one or more courses that can be prerequisites for this course. The Requirement Language Specifications can be used to populate this field. Example 1*2 - meaning that course number 1 and course number 2 from our database are prerequisites for this course | varchar(255) | Yes | - | NULL | - |
| CoReqList | This includes the CourseID of one or more courses that can be corequisites for this course. The Requirement Language Specifications can be used to populate this field. Example 1*2 - meaning that course number 1 and course number 2 from our database are corequisites for this course | varchar(255) | Yes | - | NULL | - |
| PostReqList | This includes the CourseID of one or more courses that can be postrequisites for this course or a course that is required to be taken after this course is taken.. The Requirement Language Specifications can be used to populate this field. Example 1*2 - meaning that course number 1 and course number 2 from our database are postrequisites for this course | varchar(255) | Yes | - | NULL | - |
| DivisionID | This shows the division under which this course is usually offered. It is a foreign key pointing to one of the records in the DIVISIONS table. | int(11) | Yes | - | NULL | - |
| DisciplineID | Each course in our database is associated the a discipline e.g. CSC for Computer Science. It is a foreign key pointing to one of the records in the DISCIPLINES table. | int(11) | Yes | - | NULL | - |
DEGREES This table stores all degrees and certificate and awords that the college offers such as B.B.A, B.S. etc. This table is referenced from the MAJORS table. Total of 5 fields as described below: |
||||||
| Field Name | Description | Data Type | Null | Key | Default | Extra |
|
DegreeID
|
Unigue
primary key for each separate record.
|
int(11)
|
-
|
PRI
|
NULL
|
auto_increment
|
|
DegreeName
|
The
name of the degree e.g. Bachelor of Arts
|
varchar(100)
|
-
|
-
|
-
|
-
|
|
TotalCredits
|
Total
number of credits required for this degree completion e.g. 120 for B.S.
|
int(11)
|
-
|
-
|
0
|
-
|
| LiberalArts | Total number of liberal arts credits required for this degree completion e.g. 60 for B.S. | int(11) | - | - | 0 | - |
DIVISIONS This table stores all divisions in the college such as Natural Science and Mathematics. This table is referenced from the COURSES table. This table is referenced from the DEPARTMENTS table. This table references SCHOOLS tables. Total of 3 fields as described below: |
||||||
| Field Name | Description | Data Type | Null | Key | Default | Extra |
|
DivisionID
|
Unigue
primary key for each separate record.
|
int(11)
|
-
|
PRI
|
NULL
|
auto_increment
|
|
DivisionName
|
The
name of the division such as Natural Science and Mathematics.
|
varchar(100)
|
-
|
-
|
-
|
-
|
|
SchoolID
|
The
School under which this divisions exists. It is a foreign key pointing
to one of the records in the SCHOOLS table.
|
int(11)
|
-
|
-
|
0
|
-
|
DEPARTMENTS This table stores all departments in the college such as Mathematics. This table references DIVISIONS tables. Total of 3 fields as described below: |
||||||
| Field Name | Description | Data Type | Null | Key | Default | Extra |
|
DepartmentID
|
Unigue
primary key for each separate record.
|
int(11)
|
-
|
PRI
|
NULL
|
auto_increment
|
|
DepartmentName
|
The
name of the department such as Mathematics.
|
varchar(100)
|
-
|
-
|
-
|
-
|
|
DivisionIID
|
The
Divison under which this department exists. It is a foreign key pointing
to one of the records in the DIVISIONS table.
|
int(11)
|
-
|
-
|
0
|
-
|
DISCIPLINES This table stores all disciplines in the college such as MTH. Every course is associated with a discipline e.g. College Orientation with CLG. This table is referenced from COURSES tables. Total of 3 fields as described below: |
||||||
| Field Name | Description | Data Type | Null | Key | Default | Extra |
|
DisciplineID
|
Unigue
primary key for each separate record.
|
int(11)
|
-
|
PRI
|
NULL
|
auto_increment
|
|
DisciplineName
|
The
name of the discipline such as Mathematics.
|
varchar(100)
|
-
|
-
|
-
|
-
|
|
DisciplineCode
|
The
three letter code of the discipline such as MTH
|
varchar(5)
|
-
|
-
|
-
|
-
|
DOUBLEFLAGS This table stores all options for the MAJORS table such as Single Major, Double Major for Natural Science and Math, Certificate, etc. This table is referenced from MAJORS table. Total of 2 fields as described below: |
||||||
| Field Name | Description | Data Type | Null | Key | Default | Extra |
|
DoubleflagID
|
Unigue
primary key for each separate record.
|
int(11)
|
-
|
PRI
|
NULL
|
auto_increment
|
|
DoubleflagName
|
The
name of the flag such as Single Major
|
varchar(100)
|
-
|
-
|
-
|
-
|
GENERALS This table stores all additional requirements for a degree such as 2.0 GPA to graduate. Total of 3fields as described below: |
||||||
| Field Name | Description | Data Type | Null | Key | Default | Extra |
|
GeneralID
|
Unigue
primary key for each separate record.
|
int(11)
|
-
|
PRI
|
NULL
|
auto_increment
|
|
GeneralName
|
The
name of the requirement e.g. GPA
|
varchar(255)
|
-
|
-
|
-
|
-
|
| Description | The text description of the requirment | varchar(255) | - | - | - | - |
LEVELS This table stores all options for the Course Levels such as Lower Level, Upper Level This table is referenced from COURSES table. Total of 2 fields as described below: |
||||||
| Field Name | Description | Data Type | Null | Key | Default | Extra |
|
LevelID
|
Unigue
primary key for each separate record.
|
int(11)
|
-
|
PRI
|
NULL
|
auto_increment
|
|
LevelName
|
The
name of the level such as such as Lower Level
|
varchar(100)
|
-
|
-
|
-
|
-
|
MAJORS This table stores all majors that the college offers such as Computer Science This table is referenced from the MAJOR_REQ table. This table references the DEGREES table. Total of 5 fields as described below: |
||||||
| Field Name | Description | Data Type | Null | Key | Default | Extra |
|
MajorID
|
Unigue
primary key for each separate record.
|
int(11)
|
-
|
PRI
|
NULL
|
auto_increment
|
|
MajorName
|
The
name of the major e.g. Computer Science
|
varchar(100)
|
-
|
-
|
-
|
-
|
|
MajorCode
|
The
three letter major code such as CSC
|
char(3)
|
-
|
-
|
-
|
-
|
| DoubleFlagID | The type of major single or double for example. It is a foreign key pointing to one of the records in the DOUBLEFLAGS table. | int(11) | - | - | 0 | - |
| DegreeID | The type of degree that this major belongs to. It is a foreign key pointing to one of the records in the DEGREES table. | int(11) | - | - | 0 | - |
MAJOR_REQ This table stores all major requirements as described in the catalog. This table references the MAJORS table. Total of 4 fields as described below: |
||||||
| Field Name | Description | Data Type | Null | Key | Default | Extra |
|
ID
|
Unigue
primary key for each separate record.
|
int(11)
|
-
|
PRI
|
NULL
|
auto_increment
|
|
Credits
|
The
number of credits required to satisfy this requirement. Please note that
it has to be satisfied by equal or more credits but not by less.
|
int(11)
|
-
|
-
|
0
|
-
|
|
CourseList
|
This
field uses the requirements language specifications as described in the
documentation to describe the requirement. Usually involves one or more
classes with different relationships beteween them
|
varchar(255)
|
-
|
-
|
-
|
-
|
| MajorID | The major to which this requirement belongs. It is a foreign key pointing to one of the records in the MAJORS table. | int(11) | - | - | 0 | - |
MINORS This table stores all minors that the college offers such as Computer Science This table is referenced from the MINOR_REQ table. Total of 4 fields as described below: |
||||||
| Field Name | Description | Data Type | Null | Key | Default | Extra |
|
MinorID
|
Unigue
primary key for each separate record.
|
int(11)
|
-
|
PRI
|
NULL
|
auto_increment
|
|
MinorName
|
The
name of the minor e.g. Computer Science
|
varchar(100)
|
-
|
-
|
-
|
-
|
|
MinorCode
|
The
three letter major code such as CSC
|
char(3)
|
-
|
-
|
-
|
-
|
| NotAvailableList | This field contains a list of MajorIDs from the MAJORS table for which this minor cannot be used e.g. 1*8 meaning this minor cannot be used by students majoring in major number 1 or 8 | varchar(100) | - | - | - | - |
MINOR_REQ This table stores all minor requirements as described in the catalog. This table references the MINORS table. Total of 4 fields as described below: |
||||||
| Field Name | Description | Data Type | Null | Key | Default | Extra |
|
ID
|
Unigue
primary key for each separate record.
|
int(11)
|
-
|
PRI
|
NULL
|
auto_increment
|
|
Credits
|
The
number of credits required to satisfy this requirement. Please note that
it has to be satisfied by equal or more credits but not by less.
|
int(11)
|
-
|
-
|
0
|
-
|
|
CourseList
|
This
field uses the requirements language specifications as described in the
documentation to describe the requirement. Usually involves one or more
classes with different relationships beteween them
|
varchar(255)
|
-
|
-
|
-
|
-
|
| MajorID | The minor to which this requirement belongs. It is a foreign key pointing to one of the records in the MINORS table. | int(11) | - | - | 0 | - |
SCHOOLS This table stores all schools in the college such as School of Business This table is referenced from DIVISIONS table. Total of 2 fields as described below: |
||||||
| Field Name | Description | Data Type | Null | Key | Default | Extra |
|
SchoolID
|
Unigue
primary key for each separate record.
|
int(11)
|
-
|
PRI
|
NULL
|
auto_increment
|
|
SchoolName
|
The
name of the school such as School of Business
|
varchar(100)
|
-
|
-
|
-
|
-
|
TRANSFERS This table stores information about the number of core class credits required for the appropriate number of transfer credits Total of 3 fields as described below: |
||||||
| Field Name | Description | Data Type | Null | Key | Default | Extra |
| TransferID |
Unigue
primary key for each separate record.
|
int(11)
|
-
|
PRI
|
NULL
|
auto_increment
|
|
TrasnferlName
|
The
name of the transfer options e.g. 80 means up to 80 transfer credits
|
int(11)
|
-
|
-
|
0
|
-
|
| CoreRequired | The number of core credits required. | int(11) | - | - | 0 | - |
USERS This table stores information about users that are authorized to use the Administration Module. Total of 4 fields as described below: |
||||||
| Field Name | Description | Data Type | Null | Key | Default | Extra |
| UserID |
Unigue
primary key for each separate record.
|
int(11)
|
-
|
PRI
|
NULL
|
auto_increment
|
|
UserName
|
UserName
used for login
|
varchar(50)
|
-
|
-
|
-
|
-
|
| Password | The user password | varchar(50) | - | - | - | - |
| Name | The real name of the user | varchar(50) | - | - | - | - |