TU Dresden - Institut für Bauinformatik

1. BIWO-04Software Engineering (Software Systems) 4. Exercise EXPRESS-G „MS Access“ Introduction TU Dresden - Institut für Bauinformatik

2. Example 3 • Please develop an object orientated model using EXPRESS-G to describe the following aspects: • A Room is a SpatialStructureComponent defined by 2 Slabs and at least 4 Walls. Thereby, a Slab encloses more than 1 Room and a Wall is associated to 1 or 2 Rooms. Rooms have a certain volume and a certain ID-number (inherited from their super class). • Slabs and Walls are BuildingElements further described by a Material. The Material identified by an ID-number inform about the material type. Furthermore, Material can include Information about the E-Modul. • The class Element comprises the class BuildingElement and the class FeatureElement. Each Element has an ID-number, a length, a width, a height and certain coordinates. Here, global or local coordinates(x,y,z) are selectable. • The class FeatureElement is the generalization of the class Openings, which is specialized by the classes DoorOpenings and WindowOpenings. Walls can have up to 4 Openings.

3. Example 3 • A Room is a SpatialStructureComponent defined by 2 Slabs and at least 4 Walls. Thereby, a Slab encloses more than 1 Room and a Wall is associated to 1 or 2 Rooms. Rooms have a certain volume and a certain ID-number (inherited from their super class).

4. Example 3 • Slabs and Walls are BuildingElements further described by a Material. The Material identified by an ID-number inform about the material type. Furthermore, Material can include Information about the E-Modul. String

5. Example 3 • The class Element comprises the class BuildingElement and the class FeatureElement. Each Element has an ID-number, a length, a width, a height and certain coordinates. Here, global or local coordinates(x,y,z) are selectable. String

6. Example 3 • The class FeatureElement is the generalization of the class Openings, which is specialized by the classes DoorOpenings and WindowOpenings. Walls can have up to 4 Openings.

7. Example 3 String

8. Example 4 • Connect the given Classes by using the following relations: • Inheritance-relationships • Attribute-relationships • InConstructionPhaseUsedFor • requiresForConcretePouring • hasParts • requiresInConstructionPhase • isPartOf • asMachineUsedFor • BuildingHeight • Add the cardinalities

9. Example 4

10. General Microsoft Access is a Database Management System (DBMS) for the management of data in a database and for the development of database applications. MS Access supports the relational data model. An Access-database consists of several objects: 1. Tables: The basic objects of a relational DB; storage of data 2. Queries: Utilization of data from one or more tables. 3. Forms: Input and editing of tables and queries data 4. Reports: data summary from tables or queries, clear illustration

11. Relational database The data in a relational database will be stored in tables. The tables are linked to each other using relations. This enables to combine the data from different tables using queries. Each row (tuple) in a table is a data record. Each tuple consists of a set of attribute values. Each attribute type build a column in the table. The design and the management of a database are carried out using a DBMS (such like MS ACCESS)

12. Relational database - relations • The relations between the tables are created using KEYS: • The primary key of a table, is a unique field within the table. No another data record in its column has the same value. This uniqueness helps to differentiate a data record from other data records in the same table. • The Foreign key represents the value of a primary key from a linked table. Foreignkey primary key primary key Hans 1 n • 1:1-relation • 1:n-relation • m:n-relation

13. DB design Entity / Relationshipmodel (E/R model) EXPRESS-G model Attribute Class No Integer construction vehicle – driver EXPRESS-G model (object-oriented model) name String surname String Transformation Each table represents a class Table: construction vehicle driver Each row (data record) is a class instance (object) Each column in the table represents the values of an attribute.

14. Example No Integer construction vehicle – driver Name String surname String operates S[1:?] V-No Integer name String construction vehicle type String Manufacture year Table: construction vehicle- driver Integer 1 Table: construction vehicle 123 driver 351 n 351 123 351

15. Example.. • Task 1: Create the table „construction vehicle- driver “ and the table • „ construction vehicle “ in the design view of MS ACCESS • Click table (Create tab, Tablesgroup) • Definecolumns in the design view (Start tab, viewsgroup) • Task 2: Insert the values from the both tables using the data sheet view of MS ACCESS • Change viewtodatasheetview(Start tab, viewsgroup) • Insert values Table: construction vehicle Table: construction vehicle- driver

16. Example….. • Task 3: Create an (1-n) relation between both tables(a driver can operate several construction vehicles). • Select thedatasheetviewoftheconstructionvehicletable • Click „Add Existing Fields“ (Datasheet tab, Fields&Columnsgroup) • Drag thefieldtobeaddedfromthefieldlistpanetothe open table • Follow theinstructionsofthe Lookup Wizard • Task 4: Retrieve all construction vehicles that have a Manufacture year >= 2000 • Click Query Design (Create tab, Other group) • Select theconstructionvehicletablefromthedialog box toaddandusethistableforthequery • Add thetablefields („Manufactureyear“ isobligatory) • Follow theinstructionsofthe Lookup Wizard • Enter „>=2000“ in thecriteriafieldoftheManufactureyear • Click the Run icon (Design tab, results) • Retrieve all construction vehicles that have a Manufacture year < 2000 • Retrieve all construction vehicles that have a Manufacture year that is between two given values. • Use “[parameter]” for parameter input and the “and” operator for connecting conditions • Task 5: Query the number of all drivers • Click the Totals icon(Design tab, Show/Hide group) and select the “Count”-function • Task 6: Create a query that shows all the construction vehicles which can be operated by the driver „Müller“ • Select the „constructionvehicle“ andthe „constructionvehicle-driver“ tablefromthedialog box toaddandusebothtablesforthequery; enter „‘Müller‘“ in thecriteriafieldoftheSurname

17. Example……. Table: Construction Project • Create the table „Construction Project“in the design view of MS ACCESS and Insert the values using the data sheet view of MS ACCESS. • Task 7: Query the number of construction projects. • Task 8: Calculate the total volume of all construction projects. • Task 9: Update the table Construction Project (rename the Waste-to-energy plant • as Waste-fueled Power Station) • Task 10: Retrieve the maximum and the minimum construction volume • Task 11: Increase the construction volume 10% for all the projects • Click Update Query icon(Design tab, Query type) • Enter „[Construction Project].[Construction volume]*1,1“ in thecriteriafieldofConstructionvolume • Task 12: Increase the construction volume 5% for all the projects that have a constrution volume more than 2000000

18. Data types in MS ACCESS numericdatatypes