Structure of the Demodata Sample Database
The physical structure of the database consists of the elements of a relational database: tables, columns, rows, keys, and indexes.
The database contains 10 tables with various relationships between them. It contains data on students, faculty, classes, registration, and so forth.
Assumptions
Following are some assumptions around which the database was built:
•
The scope of the database is one semester.
•
A student cannot take the same course more than once. For example, a student cannot enroll in Algebra I, Sections 1 and 2.
•
A faculty member can be a student, but a faculty member cannot teach and enroll in the same class.
•
Any course is offered by only one department.
•
In order for a student to receive a grade, they must be enrolled in a class, and a faculty member must be assigned to teach the class.
•
Faculty members belong to a single department, but they can teach for many departments.
•
All students have a Student ID that is based on the US standard of a social security number.
•
All faculty members have a Faculty ID that is based on the US standard of a social security number.
•
All other persons have a Person ID that is based on the US standard of a social security number.
•
Rooms are unique within the same building.
•
Two classes cannot be taught in the same room at the same time.
•
A faculty member can only be teaching one class at a given time.
•
Prerequisites are not required for enrollment in a class.
•
Departments imply majors.
•
A course can only be taught by one faculty member throughout the semester.
•
A telephone number or zip code does not correlate to a state.
•
A registrar cannot be a faculty member or student.
•
When a person is entered into the database, they can complete a survey of which they must answer all the questions or none of the questions.
•
Credit hours for a course are not necessarily equal to the number of hours that a class convenes.
•
An e-mail address does not have to be unique.
Entity Relationships
Entities are objects that describe primary components in the database. When designing a database, it is important to define the entities and their relationships to one another before proceeding further. In the university database, CLASSES, STUDENTS, FACULTY, GRADES, and so forth, are entities. The entities and their relationships to one another are outlined in Figure 3.
Figure 3 Entity Relationships
GRADES is a weak entity. It is dependent upon a student taking a class, so its existence is dependent upon the validity of other entities. The STUDENT and FACULTY tables create common information, since a student could be a faculty member and vice versa. The common information is in the PERSON table.