| Name: |
Database Design |
| Defining Qualities: |
- Add fields to the underlying (main) table 'items'
- Add tables and relate them to a field in the underlying (main) table 'items'
- Add fields to any other table in the hierarchy below table 'items'
- Add tables and relate them to a field in any other table in the hierarchy below table 'items'
- Edit a given field/table by changing name or data type
- Delete a given field/table; tables branching from a deleted field are recursively deleted
- View current database design in tree-like manner
|
Behaviors:
(Algorithms) |
Add fields to table |
- Check for existing fields of same name
- If non-existant, alter table by adding the new field
- If type of new field requires a new table, create that table. This occurs for single/multiple selects and radio buttons
- Add entries to administrative tables
|
| Edit field in table |
- If only name change, check for existing fields of same new name
- If non-existant, alter field name in table
- If type of field changed:
- from non-branching to branching field: create new table
- from branching to non-branching field: delete branching table
- unchanged: no operation
- Modify entries in administrative tables to reflect the changes
|
| Delete field in table |
- Alter table by removing the field (column). Data is removed in the process
- If the field branched to other tables, remove those recursively
- Delete entries in administrative tables to reflect the changes
|
| View database design (Java Applet) |
- Applet connects to Database and reads the database structure from its administrative tables
- Construct internal data structure to reflect database tree
- Compute statistics of width, height, number of levels, etc.
- On a newly created, empty image canvas, draw each table with all its fields and types
- Using the tree structure, find all relations and indicate them by drawing lines, so as to link fields with tables
- Place image canvas in a customized scroller, which scrolls by dragging the mouse (not a native Java component)
- A selection box contains all table names; upon selecting one, the image is centered on that table
|
