Analysis for Gerund Entity Anomalies in Data Modeling

Des Suryani

, Yudhi Arta

and Erdisna

Department of Informatics Engineering, Universitas Islam Riau, Pekanbaru, Indonesia

Department of Information System, Universitas Putra Indonesia, Padang, Indonesia

Keywords:

Anomaly, Data Redundancy, Data Inconsistency, Gerund Entity, Entity Relationship Diagram.

Abstract:

Data is the most important component of an information system development. Collected data that will be

used in future needs should be kept well to make it easy to inquire. The data stored in a database consists of

several groups of data relations. These relations should be connected through ﬁelds which are unique to the

relations linked. In designing database itself, it is very important to note how data is organized and stored to

minimize data redundancy. The tools used in depiction of the relationship between tables or entities are Entity

Relationship Diagram (ERD) that can have one to one, one to many and many to many relationships. Gerund

entity will be formed if the relationship between the entities is many to many. However, the new entity is still

a possible anomaly. The reanalysis is needed to be free of anomalies. Gerund entity that still has an anomaly

will form a new entity again, which in this case referred to as a sub gerund entity which is a derivative of

a gerund entity. The result of a good database design or free of anomalies will increase the optimization of

memory usage, consistency and integrity of data.

1 INTRODUCTION

Database is the most important component in the de-

velopment of Information Systems because it is a

place to accommodate and organize all data in the sys-

tem, so that it can be explored to compile information

in various forms (Sutedjo and Oetomo, 2002). The

data will be organized in such a way that there is no

unnecessary duplication, so that it can be processed

or explored quickly and easily to produce the infor-

mation needed. From several existing database mod-

eling, relational database modeling is still the most

used model by various Database Management Sys-

tem (DBMS) software. This is because it is easy to

manage data (Barioni et al., 2011; Stonebraker and

Moore, 1995).

Entity Relationship Diagram (ERD) is a model

diagram that is used as a representation of database

structure in which table information includes and the

existence of relationships between tables and the form

of the relation itself based on existing standard nota-

tions (Date, 1977). ERD is used to express the rela-

tionship between an entity or object in the form of a

table with another entity. In database design, logically

is done by transforming an ER diagram developed

during conceptual design into a relational database

scheme (Ramakrishnan and Gehrke, 2000; Gehrke

and Ramakrishnan, 2003).

Relationships that occur between entities have a

type of relationship: one-to-one (1: 1), one-to-many

(1: N) and many-to-many (M: N). Based on the many-

to-many relationship, it will form a new entity called

Gerund Entity or Associative Entity. But in this case,

the gerund entity still allows for irregularities (anoma-

lies) in storing data, namely the occurrence of du-

plication or waste of data. No writer has found a

study that examines the anomalies in the gerund en-

tity yet, so that further analysis needs to be done so

that the database created is really in accordance with

the objectives of the database itself including avoid-

ing or minimizing data redundancy, because the waste

of data will result in waste of memory usage and

can cause problems in the process of accessing data

such as data inconsistency, longer access times and

problems in data integrity (Gehrke and Ramakrish-

nan, 2003; Silberschatz et al., 1997).

2 DATA MODELLING

In describing ER diagrams, it takes the existence of

entities, attributes and relationships between entities.

Entity is a set of objects in the real world whose ex-

istence does not depend on others and has the same

146

Suryani, D., Arta, Y. and Erdisna, .

Analysis for Gerund Entity Anomalies in Data Modeling.

DOI: 10.5220/0009145601460150

In Proceedings of the Second International Conference on Science, Engineering and Technology (ICoSET 2019), pages 146-150

ISBN: 978-989-758-463-3

property. Examples of objects in an entity that can be

uniquely identiﬁed are called entity occurrence. Enti-

ties can be something real, such as: Members, Films,

Ofﬁce Branch or abstract (concepts), such as: Rental,

Registration, Role. (Kadir, 2000)

Transforming or mapping ER diagrams into rela-

tions is a mechanical process, in the sense that the

process has certain regularities. To transform from

the ER diagram to the relational scheme there are 3

(three) entities that need to be understood, namely

(Kroenke and Dolan, 1983; Silberschatz et al., 1997):

The document margins must be the following:

• Ordinary entities (regular entities) are entities that

are independent of their existence and generally

describe real objects in the real world. Ordinary

entities are often also called strong entities de-

picted with four single-striped rectangles.

• Weak entities (week entity) are entities whose ex-

istence depends on other entities (usually strong

entities). Weak entities are represented by four

double-striped rectangles.

• Associative entities (associative entities) or

gerund entities are generally formed from many to

many relationships between other entities. Asso-

ciative entities are generally represented by rect-

angles with parallelograms in them.

Types of relations can be classiﬁed as follows:

• one-to-one (1: 1)

• one-to-many (1: M)

• many-to-many (M: N)

Gerund Entity or Associative Entity is formed

from many to many relationships. Example: Student

entity with the subject matter, Customer entity with

the Goods entity and so on.

In logical database design, it can be done by:

• Applying Normalization to a known table struc-

ture.

• Directly create the Entity-Relationship (ER

model) model.

Logical data model is a source of physical de-

sign information. This model provides designers with

a vehicle for consideration in designing an efﬁcient

database.

Physical database design is the process of produc-

ing a description of database implementation on sec-

ondary storage, describing storage structures and ac-

cessing methods to improve access effectiveness. At

this stage, physical design is intended for a particular

DBMS. Physical level database design has been asso-

ciated with database management systems and plat-

forms where the database is implemented (Connolly

and Begg, 2005).

Well-organized data can produce good informa-

tion Organizing data to prevent unnecessary duplica-

tion. Data that is organized and correlated each other

called as a database, whereas to manage and orga-

nize databases that are built in a system, a database

management is called a database management system

(DBMS). DBMS is software that will determine how

data is organized, stored, modiﬁed, retrieved, regu-

lated data security mechanisms, and mechanisms for

sharing data together (Date, 1983).

2.1 Role of Normalization in Database

Design

Normalization is a formal technique that can be used

in database design. The main purpose of normaliza-

tion is to identify the suitability of relationships that

support data to meet the needs of a particular com-

pany or institution. The role of normalization in this

case is in the use of bottom-up approaches and vali-

dation techniques. The validation technique is used to

check whether the relation structure produced by the

ER model is good or not. For more details, it can be

shown in ﬁgure 1.

Figure 1: Role of normalization in database design.

In Figure 1 it can be seen that the data source con-

sists of users, speciﬁcations of various user require-

ments, various forms or reports, data dictionary and

enterprise data models. Then there is the top-down

and bottom-up approach where the approach will re-

sult in the design of relations, then the role of normal-

ization on bottom up and validation techniques (In-

drajani, 2011).

Analysis for Gerund Entity Anomalies in Data Modeling

147

Figure 2: The relationship of many to many between mem-

ber entity and book entity.

3 RESULT AND DISCUSSION

3.1 Entity Relationship Model

In relational data modelling using the ER diagram can

be described in ﬁgure 2.

If the entities relationship described as many to

many, then it will make a new entity called Gerund

Entity or Associative Entity. The ﬁeld key which con-

nected each entities should be there in the new entity.

Then, it continues by more relevant attributes added.

It can be seen in ﬁgure 3.

Figure 3: Gerund entity from the relationship of book entity

to the member entity.

The diagram in ﬁgure 3 can be described more de-

tail in ﬁgure 4.

Figure 4: New entity formed (gerund entity).

Based on diagram in ﬁgure 4, can be transformed

into tables/relations by sample data shown in table 1

to table 3.

The Member relation in the table 1 can be save

member data with member id as primary key. The

relation doest not have redudancy data.

The member relation has the form:

Table 1: Member Relation

member id name address phone

1001 John Sudirman 10 654534

1002 Dannis Dt. Setia 15 742345

1003 Betty M.Yamin 12 653421

Table 2: Book Relation

book id title Author Publisher year

C-001

Concepts of

Database

Management

Philip

J.Pratt,

Joseph J

Adamski

Course

Technology

2012

C-002

Principles of

Distributed

Systems

M. Tamer

Ozsu,

Patrick

Valduriez

Springer 2015

A-002

Fundamental

Accounting

Principles

John J.

Wild, Ken

W.Shaw

Mc Grow

Hill

2015

Member (member id, name, address, phone)

The Book relation in the table 1 can be save book

data with book id as primary key. The relation also

does not have redudancy data.

The book relation has the form:

Book (book id, title, author, publisher, year)

Table 3: Borrowing Relation

borrowi

ng id

membe

r id

borrow

date

book i

due da

return

date

19001 1001

05/02/

2019

C-001

05/09/

2019

05/08/

2019

19001 1001

05/02/

2019

C-002

05/10/

2019

06/12/

2019

19004 1003

06/10/

2019

C-002

06/15/

2019

06/10/

2019

Cardinality relation between member and book re-

lation is many to many so it creates the new table as

gerund entity. In this case is called borrowing rela-

tion (shown in the table 3). Borrowing relation has

borrowing id as primary key while member id and

book id is a foreign key.

The borrowing relation has the form:

Borrowing (borrowing id, member id, book id,

due date, return date)

3.2 Analysis Anomalies of Gerund

Entity

Analysis of anomalies in the Borrowing relation by

using normalization technique.

ICoSET 2019 - The Second International Conference on Science, Engineering and Technology

148

Table 4: Borrowing Relation

borrowi

ng id

membe

r id

borrow

date

book i

due da

return

date

19001 1001

05/02/

2019

C-001

05/09/

2019

05/08/

2019

19001 1001

05/02/

2019

C-002

05/10/

2019

06/12/

2019

19004 1003

06/10/

2019

C-002

06/15/

2019

06/10/

2019

In the Borrowing table as gerund entity. This ta-

ble has some anomalies. It can be seen a member

borrows 2 books at 05/02/2019. In here contain data

redundancy in member id and borrow date.

• To insert the book of borrowing id 19004, we

must enter member id and borrow date repeat-

edly.

• If we want to change the value of member id or

borrow date for borrowing id ‘19001’, we must

update the rows of the borrowing id. If this mod-

iﬁcation is not carried out on all the appropriate

rows of the Borrowing relations, the database will

become inconsistent. So that, the borrowing rela-

tion should be separated as a new table that called

is Borrowing Detail relation.

The resulting normalization relation have the form:

Borrowing (borrowing id, member id, borrow date)

Borrowing Detail (borrowing date, book id,

due date, return date)

The Borrowing and Borrowing Detail relations are

shown in Table 5 and Table 6. The result of Gerund

Entity analysis from Borrowing relation can be shown

ER model in Figure 5.

Table 5: Borrowing Relation

borrowing id member id borrow date

19001 1001 05/02/2019

19004 1003 06/10/2019

Table 6: Borrowing Detail Relation

borrowing id book id due date return date

19001 C-001 05/09/2019 05/08/2019

19001 C-002 05/10/2019 06/12/2019

19004 C-002 06/15/2019 06/10/2019

The establishment of a new entity from the gerund

entity above will minimize or eliminate data redun-

dant that can improve optimization of memory usage,

consistency and data integrity.

Figure 5: ERD from analysis of Gerund Entity.

4 CONCLUSIONS

Based on the results of the analysis that has been car-

ried out it can be concluded as follows:

In the gerund entity is still possible for an anomaly

to occur, so that it will create a new entity again as

a derivative of the gerund entity which in this case

the author called the sub gerund entity. In the gerund

entity, it is necessary to provide a connecting ﬁeld to

the unique sub gerund entity. The establishment of

a new entity from the gerund entity will minimize or

not even redundant the data so that it can improve op-

timization of memory usage, consistency and data in-

tegrity. For complex databases, anomalous analysis

of sub gerund entities can still be continued to ensure

that the resulting relations are free from anomalies.

ACKNOWLEDGEMENTS

This research supported by Universitas Islam Riau.

Thank you very much for supported by UIR.

REFERENCES

Barioni, M. C. N., dos Santos Kaster, D., Razente, H. L.,

Traina, A. J., and J

unior, C. T. (2011). Querying mul-

timedia data by similarity in relational dbms. In Ad-

vanced database query systems: techniques, applica-

tions and technologies, pages 323–359. IGI Global.

Connolly, T. M. and Begg, C. E. (2005). Database systems:

a practical approach to design, implementation, and

management. Pearson Education.

Date, C. J. (1977). An introduction to database systems (Vol.

1). Pearson Education India.

Date, C. J. (1983). n introduction to database systems, Vol.

II. Reading, Mass.

Gehrke, J. and Ramakrishnan, R. (2003). Database man-

agement systems. McGraw-Hill.

Indrajani, S. M. (2011). Pengantar dan Sistem Basis Data.

Jakarta: PT Elex Media Komputindo.

Analysis for Gerund Entity Anomalies in Data Modeling

149

Kadir, A. (2000). Konsep Dan Tuntunan Praktis Basis Data,

Ed. 1, Cet. 2. ANDI, Yogyakarta.

Kroenke, D. M. and Dolan, K. (1983). Database Process-

ing: Fundamentals, Design. Implementation. author.

Ramakrishnan, R. and Gehrke, J. (2000). Database man-

agement systems. McGraw Hill.

Silberschatz, A., Korth, H. F., and Sudarshan, S. (1997).

Database system concepts (Vol. 4). McGraw-Hill New

York.

Stonebraker, M. and Moore, D. (1995). Object-relational

DBMS-the next wave. Informix Software (Now Part

of the IBM Corp. Family), Menlo Park, CA, 14.

Sutedjo, B. and Oetomo, D. (2002). Perencanaan dan Pem-

bangunan Sistem Informasi. Yogyakarta: Andi Offset.

ICoSET 2019 - The Second International Conference on Science, Engineering and Technology

150