4.3.3 Define database tables for these objects (by hand)

From Geostandards

PostgreSQL

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 →