Business Rules and Data Models.
Business Rules and Data Models.
The purpose of designing the local college database is to provide a means for the school to collect and maintain accurate records of the students enrolled and the courses they take. Additionally, the database will also keep track of the instructors that teach every course in the school. By using a central database, the school administrators will be able to monitor and report student achievements in class, overall attendance, and grade transcripts. On the other hand, monitoring the instructors will help evaluate their performance, class attendance, and the effectiveness of their assessment and evaluation techniques. Due to the scope of this paper, the database is limited to records involving students, course, and lecturers. However, if expanded, the information that can be tracked using the college database is extensive and includes campus buildings, room numbers, hostel accommodation details, inventory (chairs, desks, computers, vehicles), and financial information such as donations, grants, project budgets among others.
Entities and Attributes of a simple College Database.
Entities are real world objects/things modeled in a database. That said, entities model conceptual elements/categories as opposed to specific objects. For example, Dave and Mary are students and thus take up the entity “student” which is the role they both play. Therefore, the names Mary and Dave in a database represent specific instances of the “student” concept in a system (ITL Education Solutions Limited, 2010). A common way of identifying entities is looking at the nouns such as class, instructor, student, etc. Attributes, on the other hand, are the characteristics describing each entity. For example, the students Mary and Dave (entities) have important attributes such as the last name, first name, age, date of birth, blood type and so on. When modeling, it is important to understand that some attributes may not be relevant to the system at hand and thus the data modeler has a responsibility of deciding which attributes apply to their model. For example, Mary and Dave may have blue and brown eyes but such information is not relevant in the school database, while their ages and contact information are important.
When designing the college database, the basic education model can be used to identify entities and attributes. In a typical college setup, students enroll in various courses, and these courses are facilitated by instructors. There are identification numbers for instructors, students, and courses. When a new student is registered, the school needs to know their last name, first name, age, and date of birth. When a new instructor is hired, the school also needs to know their last name, first name, qualifications, titles, and date of employment. On the other hand, all courses that students can enroll to have course names, starting dates, and descriptions so that students can know what will be covered.
As evidenced above, the highlighted nouns help describe important entities and attributes that can be modeled in the system. In the first line, it is easy to identify the main nouns (people and objects) in the system, and these qualify as entities i.e. student, course, and instructors. The additional nouns represent attributes i.e. students have identification numbers, last and first names, age and dates of birth. Instructors also have ID numbers, last and first names, qualifications, titles and employment dates. Courses also have identification numbers, names, start dates and descriptions. The information above is represented in a structured form as shown below. The rectangles represent the entities, while their attributes are listed inside them with their respective data types e.g. text, number, date, etc.
Business Rules for the College Database.
Business rules define the conditions/constraints that should be met by the database, and these are usually obtained in the requirements gathering stages from the system users. In this case, the rules should be verified by the users before the database is designed, and they must also be understood by the data modeler (Eller College, 2014; Watt, 2017). If the business rules are incorrect, inconsistent, ambiguous or contradictory, then the database will not function as desired.
Examples of business rules include:
A student can be enrolled in zero, one or many courses.
An instructor can teach many students.
A course can have a maximum of 50 students.
A student cannot enroll in the same course more than once, simultaneously.
An instructor can teach zero, one or many courses.
A course can be taught many times, but handled by only one instructor.
Conceptual vs physical database models.
In the conceptual database model, only the information gathered from the user requirements is added, and this includes the entity names and their relationships. For example, a student can enroll in many courses; a course can have multiple students, etc. At this stage, the idea is to get a general overview of the entities involved and the need to satisfy comprehensive database design and specifications is not a major consideration at this stage. For these reasons, a conceptual model is the easiest database model. In the physical database model, the actual database blueprint is presented as an entity relationship diagram (ERD). In the physical model, the ERD represents how data is to be structured and related in a specified database management system (DBMS) and thus it is important to consider the constraints and conventions of the DBMS when designing the physical database model. Here, accurate data types are used to represent entity attribute values, and the use of key terms is avoided when naming entities and their attributes. Other information added to the physical model include primary and foreign keys as well as design constraints (1keydata.com, 2017; Visual Paradigm, 2016).
References:
1keydata.com. (2017). Data Modeling - Conceptual, Logical, and Physical Data Models. 1keydata.com. Retrieved from http://www.1keydata.com/datawarehousing/data-modeling-levels.html
Eller College. (2014). Modeling Business Rules: Research Projects: Advanced Database Research Group: Eller College of Management: The University of Arizona. Adrg.eller.arizona.edu. Retrieved from https://adrg.eller.arizona.edu/research/research/br.asp
ITL Education Solutions Limited. (2010). Introduction to database systems (1st ed., pp. 28-31). Delhi: Pearson.
Visual Paradigm. (2016). Conceptual, Logical and Physical Data Model. Visual-paradigm.com. Retrieved from https://www.visual-paradigm.com/support/documents/vpuserguide/3563/3564/85378_conceptual,l.html
Watt, A. (2017). Chapter 9: Integrity Rules and Constraints | Database Design. Opentextbc.ca. Retrieved from https://opentextbc.ca/dbdesign/chapter/chapter-9-integrity-rules-and-constraints/
