A Simple Data Modeling Tool with entities, relations, attributes, Classes/Objects

• Download source code - 41 Kb

Introduction

This source code is a small prototype tool for data modeling that supports entities, relations and attributes. More features are described in the Help File Contents below. Commercial data modeling tools like Erwin & SA are used to design, modify & maintain databases.

Some crunchy technical tidbits from the source files:

• HTML include multiple JScript source files - file dbtv3.hta, lines 7-18
• HTML HTA tag for local applications - file dbtv3.hta, lines 9
• Connect HTML initialize & terminate to JScript function - file dbtv3.hta.js, lines 19
• Connect HTML buttons to JScript functions - file dbtv3.hta, lines 22-33
• Connect HTML listboxes to JScript functions - file dbtv3.hta, lines 34-37
• HTML read single line string - file dbtv3.hta, lines 38-45
• HTML read multiline listbox - file dbtv3.hta, lines 46-52
• HTML STYLE absolute positioning for widgets - file dbtv3.hta, lines 22-55
• ADO Connect strings for Jet 4.0 and SQL Server 2000 - file global3.js, lines 7-8
• Create new XML object - file global3.js, lines 93
• Read XML text file into memory - file global3.js, lines 94-102
• Open new text file for disk output - file global3.js, lines 155-156
• Write line to disk file - file global3.js, lines 192
• Open new HTML window for output - file global3.js, lines 145-147
• Write line to HTML window - file global3.js, lines 185
• Clear HTML listbox - file screen3.js, lines 12,65
• Get selected index from HTML listbox - file screen3.js, lines 21,74
• Get selected item from HTML listbox - file screen3.js, lines 43-48,74-80
• Add item to HTML listbox - file screen3.js, lines 56-57,109-110
• Nested function calls - file screen3.js, lines 27,80
• Hide HTML widgets - file screen3.js, lines 192-195,360-361
• Show HTML widgets - file screen3.js, lines 294-295,417
• Set cursor focus on HTML widget - file screen3.js, lines 296
• Call JScript function using a variable - file screen3.js, lines 266-270,473-476
• ADO compact Jet MDB file - file adodb3.js, lines 40-41
• ADO create new Jet MDB file - file adodb3.js, lines 110-112
• ADO connect to database - file adodb3.js, lines 69-70
• ADO disconnect from database - file adodb3.js, lines 86-91
• ADO create new SQL tables - file adodb3.js, lines 159-184,200-239
• ADO create new SQL table columns - file adodb3.js, lines 159-184,200-239
• ADO create new SQL table autoincrements - file adodb3.js, lines 159-184,200-239
• ADO create new SQL table indexes - file adodb3.js, lines 159-184,200-239
• ADO run SQL and return recordset - file adodb3.js, lines 565,610-637
• ADO run SQL and don't return recordset - file adodb3.js, lines 516-534
• ADO get table, column & index database info - file adodb3.js, lines 729-731,754-782
• Use try/catch for error handling - file adodb3.js, lines 67-77,84-98
• Run DOS command from JScript - file adodb3.js, lines 42-48
• Parse file path - file adodb3.js, lines 26-36
• ADO add new record to table - file ents3.js, lines 26-35,509-518
• ADO get @@IDENTITY from newly added record - file ents3.js, lines 37,520
• XML nested function calls - file ents3.js, lines 138,144,148
• XML to get node list - file ents3.js, lines 138
• XML to get node text value - file ents3.js, lines 148
• ADO loop over recordset - file ents3.js, lines 313-322,356-364
• SQL to delete a record - file ents3.js, lines 67
• SQL to update a record - file ents3.js, lines 105
• SQL to read a record - file ents3.js, lines 216
• SQL query with triple joins - file ents3.js, lines 691-697

Help File Contents

1. Concepts
2. Screen Layout
3. Quick Demo Tour
4. Button Details
5. Constraints
6. Test Data
7. SQL Tables
8. ZIP Files

1. Concepts

The database tool supports simple data modeling with entities,  relations, attributes, classes & objects.  Classes are like type definitions. Objects are like instances of the classes.

Some websites with much more info about data modeling

•  www.samoore.com/teach/db/er
•  www2.bus.orst.edu/faculty/brownc/lectures/db_tutor
•  www.utexas.edu/cc/database/datamodeling

2. Screen Layout

+----------------------------------------+
| Database Tool                          |
| H  +--------+  +--------+  +--------+  |
| C  |        |  |        |  |        |  |
| T  |  ENTS  |  |  RELS  |  | ATTRS  |  |
| I  |        |  |        |  |        |  |
| E  |        |  |        |  |        |  |
| M  +--------+  +--------+  +--------+  |
|                                        |
| C  +--------------------+  +--------+  |
| O  |                    |  |        |  |
| C  |  OUTPUT            |  | MULTI  |  |
| D  |                    |  |        |  |
| U  |                    |  |        |  |
| Q  +--------------------+  +--------+  |
|    |  QUERY             |    O  C      |
|    +--------------------+              |
|                                        |
+----------------------------------------+

Buttons are aligned down the left edge of screen

Screen areas are named as follows.  Clicking on data  in the ENTS, RELS & ATTRS areas will then show detail  information about the data in the OUTPUT area.

3. Quick Demo Tour

Follow these steps

1. Start database tool (dbtv3.hta)
2. Click CONFIG button
3. Select option "JET4.0"
4. Enter MDB file name
5. Select option "create new MDB & new tables"
6. Click OK button

A newly created MDB file should have been written to disk.  See the OUTPUT screen area for more detailed information.

1. Click TEST button
2. Select option "employees projects resources"
3. Click OK button

The sample test data should have been written to disk.  See the OUTPUT screen area for more detailed information.

1. Click CLASSES button
2. Click OBJECTS button
3. Click records in ENTS, RELS, ATTRS screen areas

The screen areas are refreshed when switching between the  classes & objects.  Clicking on a record will refresh OUTPUT  screen area with detailed info about the record.

4. Button Details

• HELP Button
  
     Show this help file
  
  
• CONFIG Button
  
     Configure database for Jet40 or SQL Server 2000
  
     Jet40
     Create new MDB & new tables
     Existing MDB & existing tables
     Existing MDB & create new tables
     Existing MDB & compact MDB
  
     SQL Server 2000
     Create new tables
     Use existing tables
     Truncate existing tables
  
  
• TEST Button
  
     Write test data to tables
  
     Employees resources projects
     Alphanumeric random
  
  
• IMPORT Button
  
     Import XML data from flat file
  
  
• EXPORT Button
  
     Export XML data to flat file
  
  
• MISC Button
  
     Turn trace window on/off
  
  
• CLASSES Button
  
     Switch to class data
  
  
• OBJECTS Button
  
     Switch to object data
  
  
• CREATE Button
  
     Create Class Entity
     Create Class Relation
     Create Class Attrib
     Create Object Entity
     Create Object Relation
     Create Object Attrib
  
  
• DELETE Button
  
     Delete Class Entity
     Delete Class Relation
     Delete Class Attrib
     Delete Object Entity
     Delete Object Relation
     Delete Object Attrib
  
  
• UPDATE Button
  
     Add Attrib to Class Entity
     Add Attrib to Class Relation
     Add Attrib to Object Entity
     Add Attrib to Object Relation
     Remove Attrib from Class Entity
     Remove Attrib from Class Relation
     Remove Attrib from Object Entity
     Remove Attrib from Object Relation
     Change Class Entity name
     Change Class Relation name
     Change Class Attrib name
     Change Object Attrib value
  
  
• QUERIES Button
  
     Run SQL statements
  
  

5. Constraints

The database tool implements a few useful constraints.

1. Names must be unique for entities, relations & attributes.
2. Relations have a source & destination, either of which can be an entity or another relation.
3. Attributes can be added to both entities & relations.
4. Object attributes can only be added to the object entities & relations if the class entity/relation already has the class attribute.
5. Source & destination objects for object relations must be same class as the source & destination of the class relation.
6. Deleting class entities, relations & attributes also automatically deletes any objects created for these classes.
7. Deleting entities or relations also automatically deletes their used attributes, but not the attribute itself.
8. Deleting class/object entities/relations will leave dangling source & destination pointers if they are used by relations.

6. Test Data

Use the TEST button to create sample data.

Option "alphanumeric random" generates lots of test data.

Option "employees projects resources" has 3 class entities, 2 class relations, 7 class attributes, 9 object entities, 9 object relations & 27 object attributes.  The TEST1.MDB file in ZIP already has this sample data created.

• Class Entities:  Employees, Resources, Projects
• Class Relations:  AssignedTo, BudgetedFor
• Class Attributes:  Name, JobTitle, PayRate, Equipment,Supplies, Location, Priority

7. SQL Tables

The database tool uses these 8 SQL tables.  Column headings for data are shown in the ENTS, RELS, ATTRS & OUTPUT areas.

Table:   CLSENTS                Table:   OBJENTS
Column:  ID                     Column:  ID
Column:  NAME                   Column:  CLSID

Table:   CLSRELS                Table:   OBJRELS
Column:  ID                     Column:  ID
Column:  NAME                   Column:  CLSID
Column:  SRCID                  Column:  SRCID
Column:  SRCTYP                 Column:  SRCTYP
Column:  DSTID                  Column:  DSTID
Column:  DSTTYP                 Column:  DSTTYP

Table:   CLSATTRS               Table:   OBJATTRS
Column:  ID                     Column:  ID
Column:  NAME                   Column:  CLSID
                                 Column:  ATTRVAL

Table:   CLSHASATTRS            Table:   OBJHASATTRS
Column:  ATTRID                 Column:  ATTRID
Column:  USEDBYID               Column:  USEDBYID
Column:  USEDBYTYP              Column:  USEDBYTYP

8. ZIP FILES

The DBTV3.ZIP file should contain these files

10/12/2002  12:55 AM            17,974 adodb3.js
10/12/2002  12:55 AM            19,564 attrs3.js
10/12/2002  12:55 AM            37,716 buttns3.js
10/12/2002  12:55 AM             1,926 dbic1.ico
10/12/2002  12:55 AM             1,926 dbic2.ico
10/12/2002  12:55 AM             1,926 dbic3.ico
10/12/2002  12:55 AM             1,926 dbic4.ico
10/12/2002  12:55 AM             6,483 dbtv3.hta
10/12/2002  12:55 AM            20,462 ents3.js
10/12/2002  12:55 AM             6,331 global3.js
10/12/2002  12:55 AM            19,764 gnugpl.js
10/12/2002  12:55 AM             9,803 readme3.txt
10/12/2002  12:55 AM            30,166 rels3.js
10/12/2002  12:55 AM            19,488 screen3.js
10/12/2002  12:55 AM           249,856 test1.mdb

DATABASE TOOL VERSION 3
COPYRIGHT(C) 2002 GCH Productions
GNU GENERAL PUBLIC LICENSE included by reference
WWW.GREGHOP.COM