E/R to Relational Schema Converter
E/R to relational schema conversion is a software tool which generates relational database schema based on user defined Entity-Relation conceptual schema.
INPUT
The input of the software is the Entity-Relation (E/R) schema in program specific syntax.
Syntax is specified for this program is following format:
For entities
The options for entities are E (for entity) and WE (for weak entity).
For relationships
The options for relationships are R (for relationship) and WR (for weak relationship).
OUTPUT
The outputs of the software are:
• The E/R model in graphical fashion
• Relational schema based on E/R model
• SQL queries for the schema generation
PROGRAM SPECIFICATION
Software is realized using C# programming in .Net environment.
The process of relational schema creation consists of following main stages:
• Parsing the input
• Filling the Data Structures:
• Applying the step-by-step E/R to relational schema conversion algorithms from Chapter 7, of textbook “Fundaments of Database Systems” by Elmasri And Navathe.
• Visualizing the output.
Let’s now view examples of relational schema generation.: First let’s consider an office routine manipulation database:
E/R for office database
Here names in squares are entities: EMPLOYEE, DEPARTMENT, and PROJECT.
Names in double squares are weak entities: DEPENDENT.
Names in rhombs are relationships: SUPERVISION, MANAGES, etc.
Names in double rhombs are weak relationships: DEPENDENTS_OF.
User of the E/R converter software needs to present the office database from figure above in specific input format, and run the program.
The E/R to Relational Schema converter input window
The program has Parse output window, which shows any lexical or semantic errors occurred during the execution of parser. It has 3 options View E/R, View Schema and Generate SQL. The usage of all those tools is presented below:
View E/R option
The purpose of this software tool is to allow user to visually check that the input in text format is same as the E/R he/she started with. The entities, weak entities, relationships and weak relationships are all colored differently. Each of them have section ATTRIBUTES with specific attributes for each, in addition to ATTRIBUTES section relationship types have also CONNECTIONS section, that present the entities they are connecting.
View Relational Schema
This tool actually generates the relational schema based on E/R description. Relational schema presents the tables that the database should have (although does not guarantee that table satisfies any of normal forms BCNF,3NF, 2NF, etc.) and indicate the fields that will be foreign keys with reference to corresponding fields in other tables in database.
The last tool of the software is generating SQL commands (using syntax of MS SQL Server 2000), for creating tables based on relational schema:
Generate SQL
The software is scalable and is tested to handle much larger E/R diagrams descriptions.