Business Rules and Data Models
A database is any collection of information that is organized in a particular way on order to make it possible to access and retrieve the information stored therein in a very efficient manner (Ramakrishnan and Gehrke, 1997). With efficient retrieval of information for use being the important cardinal essence of a database, there is need to arrange and organize the data in a manner that makes it fit to be easily retrieved. A system that helps in organizing a database through creation, update and other related activities on a database is known as a database management system (DBMS).
Information is stored in a database in so many different ways. The oldest and simplest way is by the use of a flat file. The use of relational databases offers a more robust solution to the storage and access of databases. The information stored in the database is related and as such are organized in a way to make it easy to retrieve by group.
Within the context of a college, the database helps to manage and coordinate the information that is available about the different entities in the college and the interactions between these entities in order to carry out the functions of the institution.
In the modeling of a relational database, the information to be stored in the database is modeled as entities and relationships. The entities are the objects of interest and the relationships are the interactions between these objects. These are both represented as tables holding the attributes and relationships of the tables.
For a college, the entities that are identifiable are highlighted as follows
i. Student: The attributes that can be ascribed to a student include name, student ID, date of birth, sex, address and phone number.
ii. Instructor: An instructor can have the attributes name, staff ID, department, designation, date employed etc.
iii. Course: course attributes include course ID, course title, credit units, semester taught, department and contact period.
iv. Department: department ID, department name, faculty, address are some of the attributes of the department entity.
v. Faculty: attributes include faculty ID, faculty name and location.
The rules guiding the design of a database determine the cardinality between the entities. In designing the database for a college, the following business rules are to be enforced in order to ensure integrity of the database and the correctness of the data contained therein;
i. A student can enroll for many courses and many students can enroll for a course. The cardinality between students and courses is many to many.
ii. A single instructor can teach multiple courses and only one instructor teaches a course, the cardinality between courses and instructors then is many to one.
iii. A department has many courses but a course can only be taught in a department, making the cardinality between the two entities one to many.
iv. A department can have many instructors but an instructor can belong to only one department.
Based on the entities, attributes and the business rules for the college as specified above, the physical model of the database is then presents as follows;
first_name: VarChar(30)
last_name: VarChar(30)
date_of_birth: Date
Sex: Integer(1)
Phone_number: VarChar(15)
Staff_ID: Integer(6)
first_name: VarChar(30)
last_name: VarChar(30)
Designation: VarChar(10)
date_employed: Date
Course_ID: Integer(2)
course_title: VarChar(30)
credit_units: Integer(1)
semester_taught: Integer(1)
contact_period: VarChar(10)
Department_ID: Integer(2)
department_name: varchar(30)
Faculty_ID: Integer
faculty_name: VarChar(30)
Student_course
Course_ID: Integer(2)
Instructor_course
Staff_ID: Integer(6)
Course_ID: Integer(2)
year_taught: VarChar(4)
REFERENCES
Ramakrishnan, R. and Gehrke, J. (1997). Database Management Systems (2nd ed.). McGraw-Hill.
