4.3.3 Define database tables for these objects (by hand)
- 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.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...
Install PostgreSQL (follow the guidelines as explained in the Introduction - Tools).
Write installation report, what did go ok/wrong?
Try creating the table ‘aap’ and after that also try to create another table; e.g. with your own name).
From UML classes to DBMS tables
Convert the conceptual model as depicted in class diagram of the Borland (Embarcadero) paper in on section ‘class diagrams’ UML Introduction on the Borland Developer's Network, also see first part of the hands-on into a logical model by specifying the relational DBMS ‘create table’ statements of the object classes ‘customer’, ‘order’ , ‘orderdetail’, and ‘item’.
Apply the following rules:
1. every object class should become a table with at least the attribute ‘id’;
2. try to use meaningful data type for all attributes; some examples are: integer (for natural number), date (for date attributes), varchar(20) (for strings of max 20 characters, float (for floating point numbers), etc.;
3. create reference attributes for the associations (may be implemented one directional, that is only from one table to another and not back);
4. do not use the name 'order' for a table name as it is an SQL keyword (e.g. use 'orders' instead).
For example for the table ‘orderdetail’ this would look like:
create table orderdetail(id integer, quantity float, taxstatus varchar(3), order_id integer, item_id integer);
After creating the model with 4 tables, insert in every table at least 4 instances (and make sure that the references are correct). Show the log of the model creation and data population in the report of day 2 (add text to explain what is going on); e.g.
insert into orderdetail values (5, 10.3, 'btw', 12, 100);
Now add default (btree) indices (for fast access) on id of all tables (that is one of the steps into the direction of a technical model: indexing); e.g.
create index orderdetail_idx on orderdetail(id);
Perform 5 different selections; e.g.
select id, quantity, item_id from orderdetail where id < 10; select od.id, od.quantity*i.shippingWeight as totalWeight from orderdetail od, item i where item_id = i.id;
(this second query is based on a join of two tables)
Write report including the log file of the session together with explanations what is going on.
Spatial data in the DBMS
Read sections 4.1 until 4.6 from the PostGIS manual (http://postgis.refractions.net/download/postgis-1.3.5.pdf) and browse trough chapter 6.
Use the conceptual model of week 4 related to ‘building registrations’ a country. Convert this to a logical and technical model (that is derive ‘create table’ statements) for at least classes, which are associated and also have geometry. For example: the object types building, and residential unit (and perhaps also something for mobile objects on water or terrain). Use the proper data types for the attributes; do not yet specify the geometry attributes in the ‘create table’ statements.
Add to both tables at least one geometry attribute; e.g. polygon for building and point for residential unit (see PostGIS manual on how to do this).
Convert the logical model to technical model by creating indices on the ‘id’ (‘create index’ statements) and on the geometry attributes (read the PostGIS manual on how to do this).
Populate the tables with at least 4 and 12 object instances.
Write report including the log file of the PostGIS session together with explanations what is going on.
|← previous||Information modeling||next →|