论文部分内容阅读
Abstract: In this paper, the authors present the development of a data modelling tool that visualizes the transformation process of an“Entity-Relationship” Diagram (ERD) into a relational database schema. The authors’ focus is the design of a tool for educational purposes and its implementation on e-learning database course. The tool presents two stages of database design. The first stage is to draw ERD graphically and validate it. The drawing is done by a learner. Then at second stage, the system enables automatically transformation of ERD to relational database schema by using common rules. Thus, the learner could understand more easily how to apply the theoretical material. A detailed description of system functionalities and algorithm for the conversion are proposed. Finally, a user interface and usage aspects are exposed.
Key words: E-learning course, data modelling tool, entity-relationship model, relational model.
1. Introduction
Data modelling is the most difficult and essential process that represents visually data organization in Database Management System (DBMS). A data model that is wrongfully formulated could make the implemented application ineffective and useless. That is why every database course begins with an introduction of data design concepts and gives a theoretical foundation of “Entity-Relationship” (ER) and relational models. However, some students, especially those involved in distance learning courses, find theoretical notions and rules difficult to understand. A better way for students to comprehend the theory is to put it in practice. Designing small learning units that contain a short theoretical basis, examples and tasks to be performed, appear to be most suitable in distance education [1] as well as in e-learning education. Hence, a need of interactive data modelling tool for learning arises. Existing data modelling tools allow database designers to create, visualize, and save complex data structures, but they are not suitable for visual explanation of the design modelling principles and stages.
The idea is to design and implement an educational computer-tool that will illustrate the transformation of an ERD into a relational schema. The tool is developed for educational purposes mainly and not as a powerful modelling tool for enterprise projects.
The commercial products [2-5] offer to users a wide range of functionalities that could be applied for a professional usage, rather than gaining fundamental knowledge. These products are very expensive for educational purposes.
On the other hand, researches in the field o designing data modelling tools for learning are conducted in some universities. A web-based tool is proposed in Ref. [6], which is developed to correct conceptual database schema automatically. A student has to design an ER schema and enter it into the system through a user friendly interface specifically designed for it. The tool corrects the design and shows detected errors giving advice on how to solve them. Another approach is an e-learning tool that allows defining and practising with e-learning exercises for event driven process chain modelling. That is a representative for graphical modelling techniques. The tool could automatically mark the student’s solutions by using temporal logic and model checking [7].
The paper is organized as follows: Section 2 reviews theoretical background; section 3 introduces the design of the data modelling tool; section 4 is implementation of the data modelling tool; section 5 gives conclusions.
2. Theoretical Background
The process of designing a database begins with an analysis of what information the database should hold and what are the relationships among components of that information. The first stage is to formulate ideas about the information that will be modelled and render them in the E/R model. Then, the abstract E/R design is converted to a schema in the data-specification language of some DBMS. Most commonly, this DBMS uses the relational model. If so, then by a fairly mechanical process the abstract design is converted to a concrete, relational design, called a “relational database schema”. Fig. 1 suggests how the E/R and relational models are used in database design.
In the E/R model, the structure of data is represented graphically, as an “entity-relationship diagram” using three principle elements: entity sets, attributes and relationships. An entity is a “thing” which can be distinctly identified [8], and a collection of similar entities forms an entity set. It has associated attributes, which are properties of the entities in that set and a relationship is an association among entities.
The relational model gives a single way to represent data: as a two dimensional table called a relation. Names for the columns of the relation are called attributes and the rows of relations, other than the header row containing the attribute names, are called tuples. A tuple has one component for each attribute of the relation.
The two main rules, converting an E/R design to a relational database schema are
(1) Turn each entity set into a relation with the same set of attributes, and
(2) Replace a relationship by a relation whose attributes are the keys for the connected entity sets:
For each entity set involved in relationship R, we take its key attribute or attributes as a part of schema of the relation for R.
If the relationship has attributes, then they are also attributes of relation R.
If one entity set is involved several times in a relationship, in different roles, then its key attributes each appear as many times as the number of roles [9].
3. Design of the Data Modelling Tool
The data modelling tool is designed to offer simple and intuitive interface to the learner. The tool is used on a Databases course and its aim is to illustrate the transformation process of the two diagrams. The functionalities of the educational unit are presented in Fig. 2.
The learner could represent an entity by drawing a rectangle; an attribute by drawing an ellipse and a relationship with a rhomb. A line denotes the type of the relationship between these E/R elements. The correctness of a drawn E/R diagram (ERD) is validated during the generation of relational diagram (RD).
The learner could also save the drawn ERD and open it later. The saved file extension is .dat, and at this stage of implementation these files could not be opened by another data modelling tool or text editor.
After drawing the ERD or opening it, the learner could generate the RD. The tool draws the RD without any interaction of the user by analogy with ERD.
Transformation of an E/R Diagram into a Relational Schema:
The variables presented in Table 1 identify the ER and relational schemas. Each schema is described by two variables of type List: One of them belongs to the class Element and the other one to the class Relation.
An algorithm for the transformation of an ERD into a relational schema is presented in Fig. 3 and described in the next paragraph.
(1) Read ER diagram elements (m_Elements);
(2) Create a new RD element of type List in m_SchemeElements. This element is a table that will be formed in the next steps;
(3) Check the type of element, if it is a rectangle;
(4) If so, the title of rectangle becomes a title of created RD element, i.e., the table. Subsequently, read all elements in m_Relations that refer to the rectangle.
If the element is not an entity, then go to Step 5;
(5) Check ERD element which is linked to the rectangle;
(6) If its type is an ellipse, then the title of ellipse becomes RD table attribute. Add one more element to the m_SchemeElements;
(7) If the element is not an ellipse, then go to step seven;
(8) Check the type of ERD element, if it is a rhomb;
(9) If so, describe a new RD element of type List in m_SchemeRelation and a new table is created. Subsequently, read all elements in m_Relations that refer to the rhomb;
(10) If the element is not a relationship, then go to Step 11;
(11) Add key attributes of the linked entities to the relationship entities;
(12) Add relationship attributes;
(13) Validate the correctness of ERD table.
The conditions validating the correctness of ERD table are the following:
? Each element of type Rectangle should be linked to elements of type ellipse which number could be between two and eight;
? Each element of type Rectangle should be linked to at least one element which title consists of symbol“*”. Thereby, key elements of RD are defined;
? Each element of type Rhomb should be linked to two or three elements of type Rectangle.
If one of these conditions is not true, the data modelling tool will alert for an error in the sketched ERD.
4. Implementation of the Data Modelling Tool
The data modelling tool is experimental and its educational aim is to demonstrate the transformation of an ERD into a RD. A user interface is presented in Figs. 4-5. It is divided into three sections. The learner could design the ERD at the upper right field. The upper left field shows to the user the ERD elements’ title and type. This information will help him/her to follow the designing process and also correct or delete its elements. The result of transformation of the ERD is visualized on the section below.
The learning unit offers two functional toolbars horizontally designed. There are two menus at the first toolbar: File and Schema. The File menu consists of the following functions: New, Save and Open, which relate to the ERD. The other toolbar consists of five buttons: Move, Rectangle, Ellipse, Rhomb and Line. To draw one of these shapes, we should choose it and then to point where to put the element in the upper right field. The next step is to write a title of the element in a pop-up window, which appears after drawing the element (Fig. 4).
After designing the ERD, the learner could save it in a file or generate the relational schema. To do the last operation, he/she should choose from the Schema menu the option Generate. The view of the two diagrams is presented in Fig. 5.
5. Conclusions
An algorithm for transformation of an E/R diagram into a relational schema is proposed. Its educational purpose is to visualize the theoretical rules. The algorithm is used for the implementation of program modules which are contained in the data modelling tool.
References
[1] S. Ivanov, J. Peneva, Distance learning courses in computer science: initiation and design, in: Proceedings of CompSysTech’2007, Ruse, June 14-15, 2007.
[2] Avolution, available online at: http://www.avolution.com.au/products.html, accessed: July 15, 2009.
[3] Data Modelling from CA, available online at: http://www.ca.com/us/data-modeling.aspx#overview, accessed: July 15, 2009.
[4] Design for Databases Database Modelling Tool, available online at: http://www.datanamic.com/dezign/index.html, accessed: July 15, 2009.
[5] SmartDraw—The World’s Most Popular Business Graphics Software, available online at: http://www.smartdraw.com/, accessed: July 15, 2009.
[6] F. Prados, I. Boada, J. Soler, J. Poch, A web-based tool for entity-relationship modeling, in: International Conference on Computational Science and Its Applications, Glasgow, 2006.
[7] S. Markus, R. Lackes, C. B?rgermann, Using model checking to automatically mark graphical e-learning exercises, in: Proceedings of World Conference on Educational Multimedia, Hypermedia and Telecommunications, Chesapeake, 2008, pp. 5302-5307.
[8] P. Chen, The entity-relationship model: toward a unified view of data, ACM Transactions on Database Systems 1(1) (1976) 9-36.
[9] H. Molina, J. Ullman, J. Widom, Database Systems: The Complete Book, Prentice Hall, New Jersey, USA, 2002.
Key words: E-learning course, data modelling tool, entity-relationship model, relational model.
1. Introduction
Data modelling is the most difficult and essential process that represents visually data organization in Database Management System (DBMS). A data model that is wrongfully formulated could make the implemented application ineffective and useless. That is why every database course begins with an introduction of data design concepts and gives a theoretical foundation of “Entity-Relationship” (ER) and relational models. However, some students, especially those involved in distance learning courses, find theoretical notions and rules difficult to understand. A better way for students to comprehend the theory is to put it in practice. Designing small learning units that contain a short theoretical basis, examples and tasks to be performed, appear to be most suitable in distance education [1] as well as in e-learning education. Hence, a need of interactive data modelling tool for learning arises. Existing data modelling tools allow database designers to create, visualize, and save complex data structures, but they are not suitable for visual explanation of the design modelling principles and stages.
The idea is to design and implement an educational computer-tool that will illustrate the transformation of an ERD into a relational schema. The tool is developed for educational purposes mainly and not as a powerful modelling tool for enterprise projects.
The commercial products [2-5] offer to users a wide range of functionalities that could be applied for a professional usage, rather than gaining fundamental knowledge. These products are very expensive for educational purposes.
On the other hand, researches in the field o designing data modelling tools for learning are conducted in some universities. A web-based tool is proposed in Ref. [6], which is developed to correct conceptual database schema automatically. A student has to design an ER schema and enter it into the system through a user friendly interface specifically designed for it. The tool corrects the design and shows detected errors giving advice on how to solve them. Another approach is an e-learning tool that allows defining and practising with e-learning exercises for event driven process chain modelling. That is a representative for graphical modelling techniques. The tool could automatically mark the student’s solutions by using temporal logic and model checking [7].
The paper is organized as follows: Section 2 reviews theoretical background; section 3 introduces the design of the data modelling tool; section 4 is implementation of the data modelling tool; section 5 gives conclusions.
2. Theoretical Background
The process of designing a database begins with an analysis of what information the database should hold and what are the relationships among components of that information. The first stage is to formulate ideas about the information that will be modelled and render them in the E/R model. Then, the abstract E/R design is converted to a schema in the data-specification language of some DBMS. Most commonly, this DBMS uses the relational model. If so, then by a fairly mechanical process the abstract design is converted to a concrete, relational design, called a “relational database schema”. Fig. 1 suggests how the E/R and relational models are used in database design.
In the E/R model, the structure of data is represented graphically, as an “entity-relationship diagram” using three principle elements: entity sets, attributes and relationships. An entity is a “thing” which can be distinctly identified [8], and a collection of similar entities forms an entity set. It has associated attributes, which are properties of the entities in that set and a relationship is an association among entities.
The relational model gives a single way to represent data: as a two dimensional table called a relation. Names for the columns of the relation are called attributes and the rows of relations, other than the header row containing the attribute names, are called tuples. A tuple has one component for each attribute of the relation.
The two main rules, converting an E/R design to a relational database schema are
(1) Turn each entity set into a relation with the same set of attributes, and
(2) Replace a relationship by a relation whose attributes are the keys for the connected entity sets:
For each entity set involved in relationship R, we take its key attribute or attributes as a part of schema of the relation for R.
If the relationship has attributes, then they are also attributes of relation R.
If one entity set is involved several times in a relationship, in different roles, then its key attributes each appear as many times as the number of roles [9].
3. Design of the Data Modelling Tool
The data modelling tool is designed to offer simple and intuitive interface to the learner. The tool is used on a Databases course and its aim is to illustrate the transformation process of the two diagrams. The functionalities of the educational unit are presented in Fig. 2.
The learner could represent an entity by drawing a rectangle; an attribute by drawing an ellipse and a relationship with a rhomb. A line denotes the type of the relationship between these E/R elements. The correctness of a drawn E/R diagram (ERD) is validated during the generation of relational diagram (RD).
The learner could also save the drawn ERD and open it later. The saved file extension is .dat, and at this stage of implementation these files could not be opened by another data modelling tool or text editor.
After drawing the ERD or opening it, the learner could generate the RD. The tool draws the RD without any interaction of the user by analogy with ERD.
Transformation of an E/R Diagram into a Relational Schema:
The variables presented in Table 1 identify the ER and relational schemas. Each schema is described by two variables of type List: One of them belongs to the class Element and the other one to the class Relation.
An algorithm for the transformation of an ERD into a relational schema is presented in Fig. 3 and described in the next paragraph.
(1) Read ER diagram elements (m_Elements);
(2) Create a new RD element of type List
(3) Check the type of element, if it is a rectangle;
(4) If so, the title of rectangle becomes a title of created RD element, i.e., the table. Subsequently, read all elements in m_Relations that refer to the rectangle.
If the element is not an entity, then go to Step 5;
(5) Check ERD element which is linked to the rectangle;
(6) If its type is an ellipse, then the title of ellipse becomes RD table attribute. Add one more element to the m_SchemeElements;
(7) If the element is not an ellipse, then go to step seven;
(8) Check the type of ERD element, if it is a rhomb;
(9) If so, describe a new RD element of type List
(10) If the element is not a relationship, then go to Step 11;
(11) Add key attributes of the linked entities to the relationship entities;
(12) Add relationship attributes;
(13) Validate the correctness of ERD table.
The conditions validating the correctness of ERD table are the following:
? Each element of type Rectangle should be linked to elements of type ellipse which number could be between two and eight;
? Each element of type Rectangle should be linked to at least one element which title consists of symbol“*”. Thereby, key elements of RD are defined;
? Each element of type Rhomb should be linked to two or three elements of type Rectangle.
If one of these conditions is not true, the data modelling tool will alert for an error in the sketched ERD.
4. Implementation of the Data Modelling Tool
The data modelling tool is experimental and its educational aim is to demonstrate the transformation of an ERD into a RD. A user interface is presented in Figs. 4-5. It is divided into three sections. The learner could design the ERD at the upper right field. The upper left field shows to the user the ERD elements’ title and type. This information will help him/her to follow the designing process and also correct or delete its elements. The result of transformation of the ERD is visualized on the section below.
The learning unit offers two functional toolbars horizontally designed. There are two menus at the first toolbar: File and Schema. The File menu consists of the following functions: New, Save and Open, which relate to the ERD. The other toolbar consists of five buttons: Move, Rectangle, Ellipse, Rhomb and Line. To draw one of these shapes, we should choose it and then to point where to put the element in the upper right field. The next step is to write a title of the element in a pop-up window, which appears after drawing the element (Fig. 4).
After designing the ERD, the learner could save it in a file or generate the relational schema. To do the last operation, he/she should choose from the Schema menu the option Generate. The view of the two diagrams is presented in Fig. 5.
5. Conclusions
An algorithm for transformation of an E/R diagram into a relational schema is proposed. Its educational purpose is to visualize the theoretical rules. The algorithm is used for the implementation of program modules which are contained in the data modelling tool.
References
[1] S. Ivanov, J. Peneva, Distance learning courses in computer science: initiation and design, in: Proceedings of CompSysTech’2007, Ruse, June 14-15, 2007.
[2] Avolution, available online at: http://www.avolution.com.au/products.html, accessed: July 15, 2009.
[3] Data Modelling from CA, available online at: http://www.ca.com/us/data-modeling.aspx#overview, accessed: July 15, 2009.
[4] Design for Databases Database Modelling Tool, available online at: http://www.datanamic.com/dezign/index.html, accessed: July 15, 2009.
[5] SmartDraw—The World’s Most Popular Business Graphics Software, available online at: http://www.smartdraw.com/, accessed: July 15, 2009.
[6] F. Prados, I. Boada, J. Soler, J. Poch, A web-based tool for entity-relationship modeling, in: International Conference on Computational Science and Its Applications, Glasgow, 2006.
[7] S. Markus, R. Lackes, C. B?rgermann, Using model checking to automatically mark graphical e-learning exercises, in: Proceedings of World Conference on Educational Multimedia, Hypermedia and Telecommunications, Chesapeake, 2008, pp. 5302-5307.
[8] P. Chen, The entity-relationship model: toward a unified view of data, ACM Transactions on Database Systems 1(1) (1976) 9-36.
[9] H. Molina, J. Ullman, J. Widom, Database Systems: The Complete Book, Prentice Hall, New Jersey, USA, 2002.