Advanced Database And Applications: Data Modeling And Entity Relationship Approach

Data Modelling Using the Concept of the ER Approach

In the modern era, data storage has been considered as a very critical area and that needs to be taken care of in a very serious manner. The reason to as why database exists is for helping in storage. The advancements of such technology has really helped in creation of databases, thus helping us to save our files in the machines, servers and even in the cloud.  The urge to have a good database for the storage of information belonging to any given company or an organization or even to an individual has led to many seeking the databases services or applications available in the market.  Database can be defined as the data structure that is used to store organized and ordered information [1]. Many database systems are known for them consisting of multiple tables where each table may have different fields and records. A good example is the database of a company that may have the following tables respectively; Employee Table, Financial Records table and the Products table where each table may have other different fields that are so much relevant to the information that is to be stored in the table.

The definition of a database comes from the file systems where it is defined as the concept of gathering of information. In this topic it is defined as data which must be stored in a server. In this concept the data is ordered and organized in a manner that it will be easily retrieved, easily managed and much more edited in many ways may be  significant to the end user [2]. It will be good to understand that when one is using a DB, data will not be stored in the hard drive of the computer but it will be stored in the cloud on remote server located somewhere. The application of a DDMS will be applied in accessing or when in need to retrieve the information where one must query or call a given function or command for a certain retrieval. In a database the data required to be stored will be stored on how it is related to each other, where it takes the form of tables that are made of columns also known as fields and rows which are also known as records(items). The most common language that is used for querying and managing the databases which are relational is referred to as Structured Query Language (SQL) [3].

Data Modeling in DB design

In this concept of study about advanced database and applications the topic to be covered will be data modelling and the relationship it has with the Entity relationship approach. At the end of the concept one is supposed to have an idea to what are some of the basic concepts of Entity Relationship Model, the data model components, the constructs for the entity relationship modeling, the database design process and its parts such as the data modeling, the steps used in building the data model and lastly is the understanding of how to develop a basic schema.

In the definition of a data model it is stated as the data structure conceptual representation that is always needed by a DB. The constructions in the data or data structures consists of:

• The data objects
• The association among and between the related data objects
• The rules which are used for governing all the operations with regards to the related objects

The concept in the data model is not how the data is used or organized rather than how the operations are performed on the specified related data [4]. Data model is always liberated of the software constraints and those of the hardware. The main idea of data model is presenting the data to the user as it is seen in the real world. This has made it to become essence in serving as the interface between all the notions making up the events in the real world, processes and the physical representation of the database concepts [5]. There are two main methods that are applied when creating a data model and this are the ER approach and the Object model. In our case study the idea will be the use of the ER approach.

DB design is referred to as the concept whereby we design the structures logically and physical of one or even to many databases that can accommodate the information needed by the users in any organization for all sets of defined applications The process of the design can be explain using the five phases below.

One part that is applied in the conceptual design process is the data model. Also the fucntional model is also used in the Conceptual design process. Data model is known for focusing on what type of data to be strored in DB while the Functional model is for dealing on how the data will be processed. In putting the above context with relation to the RBDMS, using data model will assist when one need to design databases which are relational. In the other hand functional model is used for designing the queries or calls which are used for accessing and performing all operations on the related tables.

The planning and the analysis phases gives the data model its inputs. In this stage both the analyst and the data modeler will collect info with reference to the DB requirements where they will review all the documentation in existence and interview all the end-users [6]. Data Model is known to have two outputs.

1. ER Diagram (ERD) is a kind of data diagram that represents the structures of the data in a form that is pictorial[7]. The reason behind this is because a picture or a diagram will be easy to learn and thus a very valuable tool when there is need to communicate directly with the end-user with regards to the model [8].
2. Data document is a document that is known for describing in details the data objects, their relationships and the database rules required. In many cases a database dictionary will provide the details the database developer will require when constructing the database physically.

This is the most intensive part in the development of a database in terms of labor, and time consumption [9]. Data model main objective is to make sure the data objects required by the database are complete and accurate when presenting them. Data model can understand the notations and the natural language thus making it easy to review and verify correctly when it comes to the end-users [10].

Data model is detailed in full as it allows the developers of database in using a blueprint to build the database physically [11]. Data model information is used when defining the relational-tables, the foreign & the primary keys, procedures which are stored and the triggers. If a database identity designed poorly then it may need long time to implement [12].

ER Model is the conceptual model of the data that has been viewed in things in the real world, comparing it to the entities and how such entities are related (Entities and Relationship) [13].  For a designer of the database the Entity relationship model utility will be:

1. It will be used for mapping the relational model.  The constructs in the application applied in model of Entity Relationships undergo transformation very easily in relational tables.
2. Very simple and easy to understand this requiring less training time. This has helped the designers of databases for using the ER model when communicating with the DB users.
3. Last utility to the design of database in the ER model is used in designing the plan by the developer of the DB who are doing the implementation of the specific DBMS[14].

1. Entities- this are the principal data objects about the type of information that is to be gathered. The most popular and recognizable concepts are entities which can be concrete or even abstract such as a person, may be a place, things or even events with relevance to DB. A good example is the entities such as PROJECTS, INVOICES, PAYROLL, and EMPLOYERS. Entity is always analogous to the table in related models. They are classified to be independent or even also dependent where in other terms it is also referred to as weak or strong respectively[15]. The entity which is independent is that one which does not rely on any other entity for it to be identified while the entity which is dependent the one that relies on another entity for it identification. There are special types of entities namely.

• Associative entity- this is an entity that is used for associating two or even more entities for them to reconcile to relationship of many-to-many. It is also referred to as the intersection entity.
• Subtype Entity- this is an entity that is used in the generalization hierarchy for representing the subset instances of the parent entity which is referred to as the supertype and it has attributes or relationships that will only apply to the specifics subsets.

1. Relationships- a relationship is any association between any two or more entities. A good example of a relationship in this context will be as follows. Employees have been assigned to perform some given tasks. The assigned projects have tasks and subtasks. Each department can be able to manage one or even projects.

In DB a relationship can be categorized in terms of how they are connected to each other (connectivity), degree, cardinality and the existence.

1. Attributes- this is a feature of database that gives description of the entity to which it is associated with. A good example of illustrating what is an attribute is the value e.g. “Marcus Garvey” is a good example of the attribute name. The attributes domain is termed as the collection of all values possible that an attribute can have. Character string is the name of the domain. They can be classified either as identifier or a descriptor.

Identifier attributes- they are normally called keys as they are known for uniquely identifying any entity instance.

Descriptor attributes- are those that describes the characteristics which are non-unique to any instance in an entity.

1. Classification of Relationships

Relationships can be categorized to either degree, connectivity, the direction, type, the cardinality and the existence [16].

• The relationship degreecan be termed as the number of the entities that are associated with the relationship. N-ray relationship will be the general form through which the degree of the entity will be expressed “n”. Some of the cases which are special are such as the use of the binary, ternary with a degree of two and three respectively.  Binary Relationshipsare those relationships whose association is between the two entities which is the most common type of relationship degree used in the real world. When an entity is related to itself it is thus referred to as the recursive binary relationship. The good example is when we have employees getting married to other employees in the same organization or even department. Ternary relationship is the one that involves 3 entities and it is only applied when the binary relationship has inadequate information.
• Connectivity and the cardinality relationships- connectivity is known for describing the relationship by giving a description of the map of all entities associated in any instance in their relationships. Connectivity values can be one or even many. Cardinality of a relationship can be termed to as the actual number or figure of all occurrences which are related for any two entities. The major connectivity which are used for relations are as explained here below.
• One-to-one (1:1) – this is where there is at most one instance for each specific entity. A good example will be that an entity A will be associated with one instance at entity B. In a company the employees are each assigned their own office.
• One-to-many- in this one the relationships is for one single instance of the entity D, there can be 0, 1 or even many instances of entity E, but for the instance of entity E, there can be only 1 instance of entity D. it is denoted as 1:N
• Many-to-Many- in this type of the relationship  for one single instance of the entity D, there can be 0, 1 or even many instances of entity E and vice versa.
• Direction of Relationship- this used for indicating the entity originality of any binary relationship. Parent entity is the entity from which the relationship always originates from and the child entity is the entity where relationship is terminated. There is application of the connectivity in determining the direction of the relationship. A good example for such entities is that in 1:1 relationship the direction id from the entity which is independent to that which is dependent. In a situation where both are independent then the direction of the relationship will be termed as arbitrary.
• Type- this is the identifying relationship in which 1 child entity is a dependent entity. The non-identifying type relationship is the one which both the entities are termed to be independent.
• Existence- this is used to denote if existence of the entity instance is dependent upon the existence of another, relationship of the entity instance. It can either mandatory or even optional.
• Generalization Hierarchies- is a form kind of abstraction that is known for specifying more entities sharing the attributes which are common and can be generalized in level that is high with reference to the entity type and thus it is referred to as the generic entity or supertype.

In designing the database it will be wise for the user in identifying the business rules, then creating the database design and thus implementing the design using the available DBMS. For decreasing of the level of abstraction the following order of models will need to be applied.

• They are known for representing the views of the user in the database.
• Contain the multiple different views externally
• There are closely related with the real world as they are perceived by the user

• Providing flexibility for the data-structure capabilities
• Contain some data stored in the DB
• Shows the relationships in between or among the data, constraints, semantic information and the security and the information integrity.

There are other models such as the internal and the physical model.

Relational database management system is the most popular data model [17]. It is kind of model that is more scientific than the others. The model is on the basis of the first order logic which are predicate thus defining the table as the N-ray relation [18].

Some of the main highlights in the relation model are such as the following.

• Data is always stored in the tables and thus it is referred to as the Relations
• There is normalization of the relations.
• In relations which are normalized the saved values are referred to as the atomic values
• Each row in any relation always contain the values from the same domain.
• Each column in any relation may contain some values for the same domain.

Entity Relationship models can be conceptualized in to pictures and diagrams, thus giving a good view of the ER, hence making it easy in understanding the concept [19]. Entity Relationship Diagrams are mapped to relational schema, this make it possible in creating the relational schema using ERD. In the relational model one cannot imports the constraints of relational model and thus the approximate schema is auto generated [20]. There are numerous algorithms and processes available in converting ERD into relational schema. ERD mainly comprises of the;

• Entity and their attributes
• The relationship and how they are associated to entities.

Mapping Entity

In defining an entity, it was termed as the real world objects which have some attributes or features that are describing them in details.

Mapping Process Algorithm.

• This is creating the table for each and every entity.
• The attributes of the entity usually becomes fields of the specific tables with their respective types of data.
• Primary key declaration

Mapping the Relationship 

A relationship is defined as the association between or among the related entities.

In doing the mapping process the following must be followed.

1. Creation of the table that is to be used for the relationship
2. Adding the primary keys to entities as table fields with their respective data types
3. In case any relationship may be having any attribute, thus add each attribute as the table field
4. Primary Key declaration which may compose all participating entities primary keys
5. Foreign key constraints declaration.

Mapping the Weak Entity sets

Weak entity is the sets which lacks the primary key that is associated with it in any way.

The mapping process for the explanation of the weak entity sets is as follows:

1. Creating the table for the specific weak entity sets
2. Adding all the accompanying attributes to table as the fields
3. Adding the primary key in identification of the entity set
4. Declaration of the constraints of the foreign key

Mapping the Hierarchical Entities

Entity relationship (ER) specialization (generalization) always come in the form of entity sets which are in a hierarchical manner.

The mapping process is as follows.

1. The creation for the tables of higher level entities
2. Creating tables for the lower-level entities
3. Addition of primary keys for the higher and lower-level entities in the table
4. Adding all the lower-level entities in the lower-level tables
5. Declaration of the primary key of both the higher and lower-level table
6. Declaration of the constraints of the foreign keys.

1. They are of higher quality. A data model has helped in defining the problems and enabling one to consider the different approaches and thus choosing the most appealing one.
2. They are at a reduced cost. It is possible for one to build applications at cost that is lower via the data models. This is because it consumes less than 10% of the budget and thus reducing up to 70% which is devoted to programming. It is used for catching errors and doing an oversight at an early stage, making them easy to fix.
3. They are quick to market. One can be able to catch the errors at an early stage. The tools for the design have taken model as the input and thus generating the initial database and the programming code[21].
4. They have a clear scope. Data model for offering provisions with the main focus on the determination of the scope.
5. The have faster performance. The sound model is known for simplifying the tuning of the database.
6. Better documentation. The document of data model has important jargons and the concepts which is proved on the basis of the long term maintenance [22].

Conclusion

In conclusion, as seen from the detailed information with regards to data modelling and the relationship it has with the ER approach has been seen to be a very wide topic. Using diagrams in Entity relationship diagrams (ERD) has been good in helping the end user understand the concept in the language. Entity Relationship model has dominated the database world. When the developers understand the database they will have very less time when designing it and reducing the complexity issues. This will result to no or very minimal cases of errors.

List of References

T. Oskar, “Techterms definitions of advanced database technologies,” Techterms, 01 Dec 2017. [Online]. Available: https://www.techterms.com/definition/database. [Accessed 09 September 2018].

P. Marie, “four Examples Of advanced Databases Applicationa,” Kohezion, 8 August 2014. [Online]. Available: https://www.kohezion.com//four-examples-databases-applications/. [Accessed 09 September 2018].

S. Aravindan, “Relational Databases,” What is an SQL Database? – Definition & Sample, vol. 3, no. 1, pp. 5-7, 2017.

V. C.Storey, “Data and Knowledge,” Big data technologies and Management: What conceptual modeling can do, vol. 108, no. 17, pp. 50-67, March 2017.

M. Tripathi, “A Proof of Concept,” the Big Data, Predictives Analytics, and Quality Improvements in the transplantation of kidney, vol. 19, no. 3, pp. 17-23, 2016.

V. Bagarelloa, “Data Models,” the concept on applicative criterion of the models which are physical when doing an evaluation of the plot, vol. 125, no. 47, pp. 53-58, 2015.

L. Chamoin, “ERD Data Models,” assimilations of data on the systems which are dynamic, vol. 1, no. 1, pp. 5-9, 2018.

A. Haber, “IEEE Xplore Data model Components,” Integrating of all then modeling heteerogeneous languages and components, vol. 97, no. 12, pp. 9-11, 2015.

R. Hirschheim, IS Developments and modeling of data, New York city: University press of cambrifge, 1995.

G. Simision, “Importance of data modeling,” Data Modeling Essentials, vol. 3, no. 9, pp. 7-19, 2005.

D. L. Warren, “Ecological Applications society of America,” The significance of the model complexity, vol. 9, no. 7, pp. 12-17, 2011.

D. R.Gitelman, “NeuroImage,” modeling the data and their importance after modeling, vol. 19, no. 1, pp. 200-207, 2013.

R.Elmasri, “concept of the category,” ER models extensions, vol. 1, no. 1, pp. 75-116, 2015.

Il-Yeol SongPeter P. Chen, “Entity Relationship Model,” Springer , Boston, MA, 2013.

T. J. Teorey, “Journal,” relational dbs logical design methods applying the extended ER model, vol. 18, no. 2, pp. 197-222, 1986.

C. Batini, “An Entity-relationship Approach,” Conceptual Database Design, vol. 12, no. 19, p. 470, 2012.

F. A. Williams, “ACM DIGITAL LIBRARY,” RDBMS concepts in version management, vol. 2, no. 1, pp. 192-200, 2010.

D. Sagalowicz, “Journal ACM Transactions on Database Systems (TODS),” capturing more meaning by extension of the DB R-model, vol. 4, no. 4, pp. 397-434, 2017.

RafiAhmed, “Information Sciences,” Query languages and the application of R-model, vol. 49, no. 3, pp. 147-175, 2018.

M.H.Scholl, “Information systems,” The relational model with relation-valued attributes, vol. 11, no. 2, pp. 137-147, 2016.

R. Blundell, “Journal of Econometrics,” Initial conditions and moment restrictions in dynamic panel data models, vol. 87, no. 1, pp. 115-143, 2018.

Dale L. Goodhue, “: Management Information Systems Research Center,” The Impact of Data Integration on the Costs and Benefits of Information Systems, vol. 16, no. 3, pp. 293-311, 2015.