4.10.1 Convert model from hands-on 1 to SQL/DDL
From Geostandards
- 4.1.1 Goal and scope of course
- 4.1.2 Pre-requisites: knowledge of OO-modeling, DBMS DDL/SQL and/or XML schema, specific domain/theme
- 4.1.3 Introduction of participants & teachers
- 4.1.4 Tools
- 4.1.5 Organization of course
- 4.1.6 Course reading material
- 4.1.7 Purpose IM: 1. enable communication, 2. built system
- 4.1.8 Inventory of participants' most favorite domains/themes
- 4.3.1 Explanation of initial model assignment
- 4.3.2 Create UML class diagram for model with 3 or 4 classes
- 4.3.3 Define database tables for these objects (by hand)
- 4.3.4 Create XML schema for these objects (by hand?)
- 4.3.5 Evaluation of the results
- 4.3.6 Use of Enterprise Architect
- 4.4.1 OGC/ISO/CEN/NEN
- 4.4.2 Focus on INSPIRE Generic Conceptual Model
- 4.4.3 GII context
- 4.4.4 Generic aspects: id's, references, time,etc
- 4.4.5 Reusable model patterns:
- 4.4.6 Generic models, e.g. the 34 themes of INSPIRE
- 4.5.1 User requirements, use cases
- 4.5.2 Inventory of available related data sets
- 4.5.3 Analyze the differences (data components, checklists)
- 4.5.4 Take initial decisions and develop model:
- 4.5.5 Cost-benefit analysis
- 4.5.6 Review with stakeholders (and revise if needed)
- 4.5.7 Test model, develop prototype data (and revise if needed)
- 4.6.1 xx
- 4.6.2 Real world example from INSPIRE cadastral parcels
- 4.6.3 Link to ISO 19152 LADM
- 4.6.4 In total 8 Categories of use cases identified
- 4.6.5 Closer look at 2 use cases
- 4.6.6 Check list with summary of all use cases
- 4.6.7 Conflicts of interest, feasibility
- 4.6.8 Vision within a model (growing options)
- 4.7.1 Identify and create two use case descriptions
- 4.7.2 Go over the data components and describe needs
- 4.7.3 Explore information content
- 4.7.4 Analyze differences between needs and availables
- 4.7.5 Develop UML class diagram for your UML model
- 4.8.1 OMG MDA principles PIM, PSM
- 4.8.2 Generate implementations
- 4.8.3 Generic PIM - Specific PIM
- 4.8.4 Run SQL/DDL within DBMS to set up model, load/create data
- 4.8.5 Generate XML/GML according to XSD
- 4.9.1 Add business rule, i.e. constraints on the data within the model
- 4.9.2 Classification main categories of constraint types
- 4.9.3 Describe in natural text using the literal entities from UML class diagrams (classes, attributes, associations)
- 4.9.4 Formalize the constraints into OCL (object constraint language)
- 4.9.5 Implementation/use of constraints ? non trivial
4.10 Hands-on 3: convert model
- 4.10.1 Convert model from hands-on 1 to SQL/DDL
- 4.10.2 Load the script into the DBMS
- 4.10.3 Insert data and perform some queries
- 4.10.4 Convert model to XML schema and inspect resulting XSD
- 4.10.5 Create XML data document from DBMS export to XML
- 4.10.6 Validate XML data against XML schema (optional)
- 4.10.7 Same steps as above but now for own model of hands-on 2 (with spatial data), manual corrections...
Convert model from hands-on 1 to SQL/DDL for PostgreSQL (specify needed keys and indices), and inspect the resulting script.
To automatically generate a database schema from the UML model perform the following steps:
1. Go to EA, one of the earlier UML diagram of the first hands-on assignment (example with Person, Address, etc in Figure 7), select this diagram in the EA 'Project Browser'. Make sure that the names of classes, attributes and associations do not contain spaces anymore (remove spaces or replace them with an '_' character).
2. In the EA 'Project' menu, select 'Transformations' and then 'Transform Current Package...'.
3. Then the 'Model Transformation' window pops up and you should them select the 'DDL' option before hitting the 'Do Transform' button.
4. The result should be an added DDL folder (in the 'Project Browser') with a DDL diagram, containing 'Table' elements. Select this diagram by clicking on it in the 'Project Browser'. It might look messy, so rearrange the diagram so it looks clear again. Compare this model to the original UML diagram.
5. Next step is to generate the SQL scripts to define these tables later on in PostgreSQL. In the EA 'Project' menu, select 'Database Engineering' and then 'Generate Package DDL'.
6. From the list of options in the pop-up window select: 'Create Primary/Foreign Key Constraints', 'Generate Index/Constraints', and 'Create Drop SQL'. Specify a file name for the 'single file' option and then push the button 'Generate' and the result should be a SQL DDL script to define your tables in PostgreSQL (store the script in file auto_db.sql).
| ← previous | Information modeling | next → |
