ERDRequirementsV2

Requirements for ERD Module, Version 2


Created on 28.06.07 by Wessam Abdrabo


Development Team:
Wessam Abd Rabo
Nada El-Newehy
Ibrahim Foudah
Youmna Hussein
Aisha Khairat
Maha Shawki

Add your comments in the review page here

[[{TableOfContentsTitle=TableOfContents} | {TableOfContents title='Table of Contents'}]]

Introduction


Using this module, users can do the following main functionalities in netBeans:

  • Graphically model ER diagrams using drag and drop components
  • Generate SQL schemas from the designed ER diagrams (optional)
  • Generate a relational database from an ER diagram (optional)
  • Generate an ER diagram from an existing database


Core User Requirements


  • Add an ERD file to a nb project
  • Generate the SQL DDL that corresponds to an ER diagram
  • Convert an Imported database to its corresponding editable ER diagram
  • Create a relational database from an ER diagram
  • Graphically Design ERD using Drag and drop ER components (see components table below)
Shape Description Priority Picture
Entity An object or concept about which you want to store information. They represent a database table. 1
Error creating thumbnail: Image type not supported
Weak Entity It is an entity that cannot be uniquely identified by its own attributes alone. For example, A Course-Section can't exist without a Course. A Course is an Entity and the Course-Section is a Weak entity to the Course 2
Error creating thumbnail: Image type not supported
Key attribute The unique, distinguishing characteristic of the entity. For example, an employee's social security number might be the employee's key attribute. 1
Error creating thumbnail: Image type not supported
Composite attribute A single attribute that is composed of a specific set of identifiable pieces of information; e.g., the employee’s name is composed of his first and last name 3 File:compostie_ERDRequirementsV2.jpg
Multivalued attribute A multivalued attribute can have more than one value. For example, an employee entity can have multiple skill values. 3
Error creating thumbnail: Image type not supported
Non-Identifying Relationship Illustrates a regular relationship between 2 independent entities 1
Error creating thumbnail: Image type not supported
Identifying Relationship Specifies that a weak entity can not exist without the parent entity 2 File:Identifying rel.jpg
Recursive relationship Entities can be self-linked. For example, employees can supervise other employees. 1
Error creating thumbnail: Image type not supported
  • Set Entity properties (Priority 1) :
Property Desciption
Entity Name Specifies the name of the entity as it appears on the design
Table Name Specifies the name of the table generated from this entity (could be the same as Entity name or different)
Author person created the db tables
Documentation comments and table description
Indexes
Indexes that are specified on scope of entity/table
Index Name name given to the index may have a prefix
Unique check box to choose that the attribute/s is unique
Field/s the attirbute that this index is applied to
Attributes
Attribute Name name of attribute as it appears on the design
Column Name name of the column in the physical db
Key Attribute specifies if the attribute is part of the primary key of the table
Data Type the physical data type of the db column
Default Value default value of this column
Allow Null sets (NOT NULL) constraint true or false, automatically set false to primary key
Check Constraint validation rules that are applied to the attribute/column
Constraint name name given to the constraint with a prefix
Expression SQL Expression to validate the column
  • Set Relationship Properties (Priority 1):
Property Description
Name Name of relation as appears on design
Type identifying, non-identifying
Cardinality 1..N - N..1 - 1..1 - N..M
Attribute of Relation
Keys/Columns Columns of relationship
On Update refrential integrity constraint values (CASCADE,SET NULL,SET DEFAULT)
On Delete same
On Insert same
Documentation comments and description
  • Remove components (Priority 1)
  • Zoom in and out (subviews): this feature will be useful in viewing a subview of a large diagrams (Priority3)
  • Preview diagram: a pane will be included for the user to see a preview of the diagram in smaller scale (Priority2)
  • Auto align diagram components (Priority 3)


Add-on features


These features will not be included in the first release of the module but are considered as future enhancments

  • Changes to the DB reflects on the generated ERD (the diagram is altered instead of regenerated)
  • Import ER models designed in external design environments (based on a survey which will tell us which tools we need to support)
  • Export ER Diagrams to Popular Formats (same tools supported for importing)
  • Validating the ERD
  • Changes to the ER diagram reflects on the generated DB: altering the tables instead of regenerating (priority 1)
  • Changes to the ER diagram reflects on the generated SQL
  • Handling hand-modification of the generated SQL
  • Export the ERD in HTML format
  • Export the ERD in JPEG format
  • Defining column/attribute domains
  • Supporting more than one ER modelling notation
  • Generating entity classes based on the type of project the model is in
  • Drag and drop between files in different projects
  • Automatic associative entity production. If the user specifies a many-to-many relationship, the system should automatically create an intermediary ("junction") table to implement the relationship.
  • Adding Data Dictionary

Scenario 1: Generating a Database from an ER-diagram


Initial assumption:

The user has created a new project or opened an existing project in nb and has added an ERD file to the project.

Normal flow of events:

  • The user designs an ER diagram using drag and drop ER components
  • The user then clicks a menu button to generate the relational database from the diagram
  • The system prompts the user to select an existing connection or create a new one
  • The system generates the database

What can go wrong :

Clicking generate db on an empty or erroneous diagram

  • In case of an empty diagram, the error "unable to generate a database from an empty diagram" will be reported to the user
  • If there are failures due to errors in the diagram, they should be reported to the log file, and then we can pop up a dialog saying "Unable to generate database. Please see log file for details"

Database Tables are partially created then an error is encountered

As a result, the user has a half-created database. The action will be to drop the existing tables as part of error recovery.

An unauthoritive user tries to generate a database

Report error to the user

Generating a table that already exists in the db

if there already exists tables in the database. The following message will be reported to the user "A table of name 'foo' already exists, do you want to recreate it? Yes No Do this for all tables".According to the user's input, action is taken. 

Other Attempts:

Hand-Modifying the generated database

If the user adds a new table, changes a column’s name or type, or any action of that sort, then switches to design view, the changes made to the db are not reflected to the diagram. The ER diagram no longer represents the same instance of the db.

Hand-Modifying the ER diagram after generating the database

If the user modifies the design after the database is generated then views the tables in the db explorer, the changes will not be reflected to the db. The user will have to regenerate the database. In such case, the old database is not replaced with the new one. They will both exist as different versions of the design, unless the older gets deleted by the user. 

System state on completion:

A database is created and the user can view the generated tables in the db explorer


Scenario 2: Generating SQL DDL from an ER diagram


Initial assumption:

The user has created a new project or opened an existing project in nb and has added an ERD file to the project.

Normal flow of events:

  • The user designs an ER diagram using drag and drop ER components.
  • The user then clicks a menu button to generate SQL DDL of the database that the diagram represents
  • The system prompts the user to specify the type of the database for which the SQL is to be generated.
  • The system generates DDL
  • The user can view and hand-modify the generated DDL.

What can go wrong :

Clicking generate SQL on an empty or erroneous diagram

  • In case of an empty diagram, the error "unable to generate SQL from an empty diagram" will be reported to the user
  • If there are failures due to errors in the diagram, they should be reported to the log file, and then we can pop up a dialog saying "Unable to generate SQL. Please see log file for details"

Other Attempts:

Hand-Modifying the generated SQL

The user can hand-modify the generated SQL but for this release this will not be handled by the system. The ER diagram no longer represents the SQL. In this case, the user will have to load the SQL schema in the datbase (for example MS access), the import this database into netbeans and generate a new ER diagram.

Hand-Modifying the ER diagram after generating the SQL

If the user modifies the design after the SQL is generated then views the SQL, the changes will not be reflected to the SQL. The user will have to regenerate the SQL. In such case, the old SQL DDL is not replaced with the new one. They will both exist as different versions of the design unless the older is deleted by the user.  

System state on completion:

An SQL schema file is created and the user can view and modify it.


Scenario 3: Generating an ER diagram from an existing database or an SQL Schema


Initial assumption:

  • The user has created a connection to the database that he wants to generate a diagram for
  • The database tables, stored procedures and views are loaded in the db explorer
  • The user has created a new project or opened an existing project in nb

Normal flow of events:

  • The user adds a blank ERD file to the project
  • The system prompts the user to choose a database from existing connections or an SQL DDL File to convert to an ER diagram
  • The user chooses a connection or locates the SQL DDL file and clicks generate ER
  • The user views the generated diagram in the design view.
  • The user can then edit the generated diagram.

What can go wrong:

The user is unauthorized to connect to the database

an error message will be reported to the user

The imported SQL file includes errors

If there are failures due to errors in the SQL DDL file, they should be reported to the log file, and then we can pop up a dialog saying "Unable to generate ERD. Please see log file for details"

The ERD is partially created then an error is encountered

Error is reported to the user asking to regenerate the diagram and the diagram is cleared.

Other Attempts:

Hand-Modifying the generated ER diagram

If the user edits the generated diagram, this instance of the diagram no longer represents the database. Any changes to the diagram is not reflected to the database. To see the effect of changing the diagram, the user has to generate a new database that corresponds to the edited version of the diagram. The old database is not replaced by the new one.

System state on completion:

An ER diagram is generated and the user can view and edit it in the design view


Non-Functional Requirements


Defining Target times for a demo size diagram (5 entities) standard size diagram (maybe 20 entities) and for a larger one (e.g. 100 entities) to do the following:

  • Open the Diagram
  • Save the Diagram
  • Generate Tables
  • Import Database

For "standard" developer machine(2GB of memory, 7200 rpm disk and a reasonably fast CPU), the following timings are speculated for such operations:

Demo size diagram : 2 seconds Standard size diagram: 5 seconds Large Diagram: 10 seconds



Not logged in. Log in, Register

By use of this website, you agree to the NetBeans Policies and Terms of Use. © 2012, Oracle Corporation and/or its affiliates. Sponsored by Oracle logo