Using the ER Models to generate SQL code is a very powerful feature of QSEE. However a couple of the options were not obvious to me so these notes are atttempt to provide a little additional help.
This diagram can be displayed with either of two notations - the SSADM notation with dotted lines for optionality, or the more common crowsfoot notation. I will be using the latter notation. I also turn off Verbose Cardinality - I think it just clutters things up. This is selectable in the Main background menu under options.
An attribute value is often limited to one of a limited set of values. This can be defined in the ERM by creating an Enumerated Type
and adding the values to the Type. Then you can create an attribute and this type will appear in the list of possible types.
When the SQL is generated, it will include a CHECK constraint listing the defined values. This is useful for documentation too.
The direction of the relationship will be in the direction in which it was drawn - from source to destination.
The names at the ends of the relationship, and of the relationship itself, can be left blank.
QSEE supports many to many relationships and these will be resolved by the creation of a link table on generation of the SQL
One to many relationships are normally optional on the many side. Thus the box 'Optional at destination' will normally be checked if the relationship was drawn from the one to the many side. It doesnt really matter because there is no difference in the generated SQL, but it is more accurate.
If the box 'Key for related Entities' is checked, the primary key of the 'One' side will be become part of the primary key on the 'Many' side. A 'I' bar will be drawn across the relationship to indicate that the Many entity is dependant on the One entity. If the whole primary key is composed of foreign keys, this is a 'weak enity' - weak because the existance of an instance of this entity is totally dependent on the existance of its parent entities.
Provided the 'Do not Generate Foreign Keys' box is not checked, foreign keys will be generated. For each 1:m relationship, the primary key(s) from the one end of a relationship will be added as additional fields to the Many end. Foreign key generation occurs when the SQL is generated and it is advisable to check the SQL to see that it has been done as you intended.
Where possible, foreign key generation should be used since it ensures the foreign key constraint are also generated, and that any change to the primary key can be propagated simply by regenerating the SQL.Foreign key settings can be set globally or specific to a relationship:
If 'Auto Generated' is checked, foreign key fields are prefixed with an autonumbered prefix 'fkn_'. If another prefix is to be used, enter it in the panel. This prefix will be the same for all foreign keys. If no prefix is to be used, for instance so that natural joins can be used, just uncheck 'Auto Generated'
If you want a specific prefix for this relationship, enter it in the box. Foreign keys don't take their name from the names defined in the relationship details.
Right button menu/Target to set the desired target before generating the SQL. For the MySQL database here, use MYSQL 4.1.
Right button menu/Options - check the option Prevent db name generation - this inserts a use statement into the generaetd code which conflicts with the database name you are useing.
You may also want to turn off the verbose code generation once you have seen it.
Use the comand line MYSQL5 and the source command to create the tables in your own database.