Database Model Design

This article discusses the conceptual and logical database model design with examples. It explains the purpose of data from a user viewpoint and the types of data modelling tools. It also provides insights into the entity-relationship model and foreign key constraints.

Running head: DATABASE MODEL DESIGN
Database Model Design
Name of the Student
Name of the University
Author’s note:

1DATABASE MODEL DESIGN
Conceptual Model:
Figure 1: Conceptual Database Model
(Source: Created by Author)
The conceptual database modelling refers to the understanding of purpose of data from
user viewpoint. The conceptual data model provides the definition of the system it contains. The
data architects and business stakeholders are the target audience of this model. The purpose of
creating the logical model is defining the business rules and concepts. There are three concepts in
conceptual database modelling such as model, schema and instance. The model is referred to as

2DATABASE MODEL DESIGN
the collection of concepts for describing the database structure and operations. The schema is a
visualization that is utilized for offering the modelling construct. The instance is description of
reality for a specific given time. There are two types of data modelling tools such as entity-
relationship diagram or Object-oriented diagram. Identifying the entity, relationships and
attributes are the primary objective of the conceptual model. The conceptual model does not
include details of the final structure of the actual database.
There are four concepts in entity relational model such as entity, relationship, attribute
and constraints. The entity can be referred to as the place, person, event, concept or an object.
Entities can be of two types such as string and weak entity. The weak entity is referred to that
entity which does not have its own attribute as a primary key. The student, course, staff,
professor or similar objects that can have set of attributes can be considered as entity. The sole
purpose of the relationship is connecting one attribute with another. Relationships can be of
many types such as many-to-many, one-to-one, one-to-many and money-to-one. The
relationships has two factors such as multiplicity and cardinality. Multiplicity, cardinality and
relationship type together defines the relation between the entities. The attributes are part of
entity that characterize the entity. The constraints are considered as the protocols or policies
which are used for defining the how the processes in the database will be executed. An example
of constraint is that a user cannot be less than 18 years old.
The conceptual model offers business-concept coverage of organization wide scope. The
business audiences are the main destination of these kind of conceptual models. This is because
the business persons do not have any knowledge of database technologies and conceptual model
helps them understand only the concept behind the database. The conceptual model does not
require any hardware specifications such as location, data storage capacity.

3DATABASE MODEL DESIGN
At the beginning of designing the conceptual model, the information of the organization
was collected. Understanding the case study of the selected organization was extremely
important as the business rules, attributes and relationships are developed as per the organization
case study. One of the important factors of the conceptual model is making decisions for creating
the structure of the database. The first thing that was found in the case study was categorization
of the employees. There are three types of employees in Sun Valley College such as staff, admin
and professor. Therefore, the first task to implement generalization in the database. The
generalization means that database will have child entities for a single parent entity. In this case
the parent entity is employee entity and the child entities are staff, administrator and professor.
These children entities had different relations with other entities in the database. There were two
more categories for the staff member entity. However, this categorization was shown through an
attribute named level. The main purpose of the database design was making the model simple as
possible. Implementing another generalization rule would have only made the design complex
which can later be resulted as complex SQL queries. Moreover, the level 1 and level 2 staff has
only one difference that is level. As there is only one difference in two entities, using an attribute
that can identify the difference is the most appropriate approach.
The case study describes that the professor can be admin but the admin position is not
restricted to the administrators only. The business rule associated with this condition is a
professor can be admin once. This makes the relationship between the administrator and
professor one-to-one and optionality is zero. Professor can be also the dean of the faculty. The
business rule is that one professor can be dean as a faculty at a time. Professors will be head of
the department. Only professor can be head of the department but not all professors are eligible
for becoming H.O.D. All professors will tech in a single department. A newly created

4DATABASE MODEL DESIGN
department may not have any teacher working under it but a teacher should be registered under a
department. Faculty will be divided into various departments. As the departments must be under
a faculty and each faculty must have at least one department, the relationship between faculty
and department is one-to-many. Each department has various courses and each courses have
many classes.
Only one many-to-many relationship exists in the database, between student and the
classes. As student can choose course under only one department, no matter how much classes
the student attends, all that classes has to be associated with one single course. The Sun Valley
College allocates each staff as the advisor to the students. One staff can advise many students but
a single student will get only one advisor. Each class is conducted only in one room and each
room is located in a single building. The classes-room and room-building entities have one-to-
one relationship among those.

5DATABASE MODEL DESIGN
Logical Model:
Figure 2: Logical Database Model
(Source: Created by Author)
The database management systems has various data types for storing different types of
data in the database. In order to store the string data, database management system offers char,
varchar and many other data types. The basic difference between the char and varchar is that

6DATABASE MODEL DESIGN
char is a fixed length string but varchar is a variable length string. To store numbers INT,
decimal, double and more attributes are available. The INT only stores integer numbers which
range from -2147483648 to 2147483647. Double and decimal stores floating numbers such as
8.01. The database allows the user to set the highest number of digits in the total number and the
limit to right side of decimal. The dates are also essential for the database for Sun Valley
College. The dates can be save in various ways such as month/day/year basis or date with time or
only month or year. The database also allows storing the time. However, date is only required
which stores month, date and year.
The concept of foreign key was developed for efficiently connecting all the entities in the
database. The foreign key is an attribute in the database that refers to only primary key of another
table. The foreign key is created when the database is decomposed. The normalization integrates
the concept of foreign key constraints for implementing relationship between entities. The very
basic idea of normalization is decomposing the tables into smaller tables so that database
anomalies can be prevented. However, decomposing the tables means that separated attributes
will no longer be related to each other.
The database allows the designer to create, modify and delete a foreign in the database. It
is not essential to declare a foreign key at the time of creating the table. The foreign keys can be
added later to the database. If a database entity has more than attribute as primary key, known as
composite primary key, the database can use only a subset or single attribute of primary key as
foreign key. Various referential actions can be taken by the database based on the action
specified at the time of creating the foreign key. These actions are cascade, restrict, no action, set
null and set default. The cascade action specifics that the row that refers to the deleted primary
key of parent table will also be deleted. This is applied for the update. The restrict action

7DATABASE MODEL DESIGN
resembles its name. It does not allow the database to delete or update the parent row if that
specific primary key is available in the child entity. ‘No Action’ is applied when the developer
wants the child row to be unaffected when the parent row is deleted or updated.
The first foreign key used in the database is the professor primary key. The professor can
be admin that is why the primary key of professor is used as the foreign key in administrator
entity. The professor entity primary key is also used in the faculty and department tables as dean
and head of the department. However, each faculty and department will have a single dean and
head of the department, not all the professor records will be selected for creating the foreign key.
Moreover, the department id will also be used as foreign key in the professor table because each
professor will be working under a single department. Each professor will have a foreign key of
department table. A single department provides many courses to the students. Department and
course will have many-to-many relation. This implies that each course will have a department id
as foreign key. This is because using course id in the department entity will cause repetition of
same department row for course under it. Course have many-to-one relation among those. This
also restrict the database to use course id in the class entity as foreign key. This foreign key will
have cascade action as if the parent course will be deleted then the classes will not be required.
The same follow for the department course relation. This is because, the college may want to
shift some courses to other departments if the department is deleted.
There is an issues in the class and student entities. As student has many-to-many relation
with the class entity, it is essential to implement a bridge table to decompose the many-to-many
relation into one-to-many and many-to-one relations. This bridge table is known as the student
enrollment. This entity has student and class entity’s primary key as foreign keys. These two
foreign keys are also used as composed primary key. This composite primary key does not

8DATABASE MODEL DESIGN
include any attribute of this table. That is why this bridge table is weak entity. Each student will
get a single staff as advisor which implies that staff id has to be used as the foreign key in student
table. Each student will have a foreign key value. Each class has a room id and each room has
building number as foreign key.
Taken as an example, if a database has student and class related attributes in single table.
After 2nd normalization, two separate tables are created which holds student and class attributes.
Though the anomalies are not present at database but the database cannot understand which
student is studying in which class. In order to prevent this situation, the database designer uses
the concept of foreign key constraint. Based on the relationship type, the foreign key is chosen.
Suppose, a student can be in only class and one class has many students. Therefore, if student id
is considered as foreign key then for every student in the class, the class primary key has to be
repeated. However, if class primary key is chosen as foreign key then for each student the class
key has to be mentioned. However, if many students can be studying in many classes then one-
to-many relationship appears. As the real life relational database management system does not
support the many-to-many relationship, a bridge table is generally created that holds both the
student and class entity’s primary key as foreign key.

9DATABASE MODEL DESIGN
Bibliography:
Connolly, T. and Begg, C., 2015. Database systems. Pearson Education UK.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Jalaei, F. and Jrade, A., 2015. Integrating building information modeling (BIM) and LEED
system at the conceptual design stage of sustainable buildings. Sustainable Cities and
Society, 18, pp.95-107.
Karagiannis, D., Mayr, H.C. and Mylopoulos, J., 2016. Domain-specific conceptual modeling.
Springer International Publishing.
Lukyanenko, R., Wiersma, Y., Huber, B., Parsons, J., Wachinger, G. and Meldt, R., 2017.
Representing crowd knowledge: Guidelines for conceptual modeling of user-generated
content. Journal of the Association for Information Systems, 18(4), p.297.
Mior, M.J., Salem, K., Aboulnaga, A. and Liu, R., 2017. NoSE: Schema design for NoSQL
applications. IEEE Transactions on Knowledge and Data Engineering, 29(10), pp.2275-2289.