Part 1 Table
(If the Instructors have a unique ID number it can be used in this table as a foreign key in place of Instructor Name.)
Part II – Business Rules
Data integrity is an important part of database design. Data models define the structure of the data, help maintain integrity and define events and event triggers. Business rules describe the events that occur in a business and the flow of data from one event to another. They define the dependencies of data elements and entities on one another. Changes in the way business is run leads to changes in the computer system that supports the business. In order to keep these changes minimum, the changes should be limited to the interface and should not affect the underlying database. For this, the underlying data base should be flexible enough to accommodate most of the changes. The rules should be such that the changes can be implemented quickly without disrupting the day to day activities of the business.
A business rule defines a relationship as a condition which will either always be true or always be false. For example a rule stating that every employee must receive a salary can be defined by making the SALARY column in the EMPLOYEES table NOT NULL (it should have a value) and > 0 (Value cannot be 0 or less than 0). Another example is that every EMPLOYEE must belong to a DEPARTMENT. This can be achieved by defining the DEPARTMENT column in the EMPLOYEE table a FOREIGN KEY to the DEPARTMENT table. This means that the column cannot be left blank and must conform to the values of the DEPARTMENT ID column in the DEPARTMENT table.
Business rules vary from model to model depending on the user requirements. In our example, we can identify the following business rules –
- Every COURSE is managed by a DEPARTMENT
- Every INSTRUCTOR teaches at least 1 COURSE
- An INSTRUCTOR may teach many COURSES
- An INSTRUCTOR may teach more than 1 COURSE.
- A DEPARTMENT may manage many COURSES.
- A COURSE can be managed by only 1 DEPARTMENT
These business rules define the following events –
- Teaching
- Management
Cardinality defines how entities in a data model relate to one another. It specifies how many instances of one entity relate to one instance of another.
In our example the following Cardinal relationships can be identified -
ENTITY RELATIONSHIP DIAGRAM
An Entity Relation Diagram (ER Diagram) is a pictorial representation of the relationships between entities in a data model. The following diagram is a representation of our example -
