FIT2094-FIT3171 Databases Session 6 Tutorial Activities
LOGICAL MODELLING
FIT Database Teaching Team
Complete the week 3 session 6 activities:
6.1 SQL Data Modeler Configuration
6.1.1 Configuring Data Modeler
6.1.2 Configuring Data Types
6.1.3 Configuring System Types Directory
6.2 Using SQL Developer Data Modeler
6.2.1 Accessing Data Modeler
6.2.2 Configuring Logical Model Project
6.2.3 Develop a model – Stage 1 The Logical Model
a. Adding Entity/Relation
b. Saving Logical Model
c. Configuring Logical Model Notation
d. Adding Relationships
e. Commit/Push Model onto GitLab Server f. Drawing Rest of the Model
g. Adding Constraint
h. Adding Lookup Table
i. Adding Surrogate Key (where appropriate)
6.2.4 Develop a model – Stage 2 The Relational (Physical) Model
6.3 Property-Rental Model
6.3.1 Drawing Logical Model
6.3.2 Generate Relational (Physical) Model
6.4 SQL Developer Data Modeller Issues/Problems Note: this section describes some of the software problems you may experience, please check this section first if you have a problem.
6.4.1 Relational Model Disappears
6.4.2 Having problems navigating into a folder 6.4.3 Logical Data Types Disappear
FIT2094-FIT3171 2021 Summer B
FIT2094-FIT3171 Databases
Author: FIT Database Teaching Team
License: Copyright © Monash University, unless otherwise stated. All Rights Reserved.
COPYRIGHT WARNING
Warning
This material is protected by copyright. For use within Monash University only. NOT FOR RESALE. Do not remove this notice.
Page 1 of 31
Important
Remember before starting any lab activity which involves working with files (here we will be creating data models), first use SQL Developer to pull from the FITGitLab server so as to ensure your local files and the FITGitLab server files are in sync.
After preparing a conceptual model the next stage is to select the type of database we will use (for us relational) and convert our conceptual model into an appropriate logical model. For logical modelling, we will make use of Oracle SQL Developer Data Modeler.
6.1 SQL Data Modeler Configuration
This software is a commercial level tool with an extensive set of features including support for Subversion versioning and source control thus permitting teams of developers to work collectively on a design. Given the extensive range of features, we are only going to be looking at a subset of these for our study. SQL Developer Data Modeler begins with a (relational) LOGICAL model and then creates what it calls a RELATIONAL model from which the schema file can be generated. The relational model is essentially a graphical representation of the physical model.
6.1.1 Configuring Data Modeler
A number of features must be configured using the SQL Developer preferences: Windows/MoVE: Tools-Preferences
OSX: Oracle SQL Developer-Preference
Page 2 of 31
In preferences, select the Data Modeler:
We wish to modify three features:
(a) select DDL and select the option to “Generate Short Form of NOT NULL Constraint” – this will cause not null constraints to be not named
Page 3 of 31
(b) Select Diagram, Relational Model and choose “Foreign Key Arrow Direction” as:
(c) select Model, Logical and check both items in “FK Attribute synchronization”
There are a large number of other settings available to configure, you might like to investigate these if you are using your own copy of SQL Developer, for the tutorials we will leave the remaining settings at the default values.
6.1.2 Configuring Data Types
Attributes on the logical model have a number of possible data types, the main ones for us being “Domain” and “Logical”:
● Domain types are domains which you create via the menu items – Tools – Data Modeler – Domains Administration. No domain types are supplied, you must create any you need
● Logical types are not actual data types – they are names which are mapped to native types at a later stage. These logical types are pre-populated with several Oracle types
Page 4 of 31
For our work we will not make use of domain types, instead, you should always use logical types. You can speed up the entry of attributes by restricting Data Modeler to logical types and a preferred range of types.
In your SQL Developer preferences set:
The ‘Preferred Logical Types’ are populated by selecting the item in the ‘All Logical Types” and clicking on the right-pointing arrow between All and Preferred.
6.1.3 Configuring System Types Directory
To set up the System Types Directory:
● In file explorer, locate a folder called SYSTEMTYPES which has been created/cloned under DocumentsUNITSDatabaseyourLocalRepo folder
Page 5 of 31
● In the Data Modeler preferences point the “Default System Types Directory” to this folder and click OK
If you see this error message when you save your model:
you need to set up a folder to save the Modeler System Files. This is necessary since Data Modeler needs to write the logical data types you define when it saves a project. The error message appears if the default System Types Directory is a location Data Modeler cannot write to (e.g. c:program files). Please follow the steps discussed above.
Page 6 of 31
6.2 Using SQL Developer Data Modeler 6.2.1 Accessing Data Modeler
Data Modeler is installed as part of the standard SQL Developer installation. To work with Data Modeler it is important to always have the Data Modeler browser open in the left panel of SQL Developer. To open this panel select View – Data Modeler – Browser:
The browser opens with a new (unnamed) model:
To begin creating a logical model, right-click Logical Model and select Show.
This will open a blank model in the main working panel of SQL Developer and add a range of new icons to the main toolbar. Hover over each of these new icons to become familiar with what they represent.
Page 7 of 31
When using Data Modeler it is very important that you save and close your model before exiting SQL Developer or shutting your laptop. Failure to do so may result in loss of parts of your model.
6.2.2 Configuring Logical Model Project
Everytime you work on a new project you must set the new project property such that the foreign key name is being named based on the PK attribute they were copied from. First right-click the project in the Data Modeler browser and select properties.
Within the Design Properties sheet select Settings – Naming Standard – Templates and modify the “Column Foreign Key” setting from {ref table}_{ref column} to {ref column}:
Page 8 of 31
This will result in FK’s being named based on the PK attribute they were copied from. If this is not set the FK names will have the form TABLE_attribute – e.g. CUSTOMER_custno, we wish the FK to be simply custno. Note that you must do this for every new project you create.
6.2.3 Develop a model – Stage 1 The Logical Model
For your first model, we will implement a Customer-Orders system, represented by the following entities, where customers place orders for products. This is basically the conceptual model you created in the session 3 tutorial and normalisation done in session 4 workshop translated to a logical model.
Customer-Order conceptual model (taken from Session 3 Tutorial Sample Solution)
Customer-Order 3NF (taken from Session 4 Workshop)
ORDER ( order_no, order_date, cust_no)
ORDER_PRODUCT ( order_no, p rod_no, ol_qtyordered, ol_lineprice) PRODUCT (prod_no, prod_desc)
CUSTOMER (cust_no, cust_name, cust_address)
Page 9 of 31
Further discussion with the client has revealed the following:
– CUSTOMERs are grouped into three different levels based on the total value of the orders they have placed in the previous financial year – Gold, Silver and Bronze
– PRODUCTs are categorised into Dairy, Household, Cleaning, Frozen, Fruit and Vegetables, and Bakery. Flexibility is required in the design so as to be able to add new and remove current product categories as circumstances change
– The client wishes to keep the name and address as simple attributes. a. Adding Entity/Relation
On your logical model add an entity, click entity icon and click anywhere in the main working panel.
Named CUSTOMER (name the entity under the General features in the Entity Properties dialogue box) and then select the attributes feature.
Add the following attributes (click green + button to add another attribute):
● cust_no – Logical type: Numeric Precision 7, Scale 0, Primary UID
● cust_name – Logical type: VARCHAR(50), Mandatory
● cust_address – Logical type: VARCHAR(50), Mandatory
● cust_phone – Logical type: CHAR(10)
Note 1: you must check the ‘Preferred’ tick box so that you only see your selected range of logical types that you set on page 5:
Page 10 of 31
Note 2: for each attribute add a meaningful description of the attribute under the attribute option – “Comments in the RDBMS”. Comments in the RDBMS are required for ALL data models in this unit.
Then add an ORDERS entity with attributes:
● order_no – Logical type: Numeric Precision 7, Scale 0, Primary UID
● order_date – Logical type: DATE, Mandatory
Note here that the entity is being named as ORDERS since ORDER is a reserved SQL word – remember that as a general rule entity names must not be pluralised.
b. Saving Logical Model
You must regularly save your model as you proceed through the development. To save your model, right-click the model in the browser panel and select “Save Design”
For your first save your design will be titled “Untitled” – before making this first save, create a folder (eg. custorders_oraclemodel) in the Tut 6 folder of your local repo to save your model in, then navigate and save into this folder. As you save give the model a name eg. cust_orders:
Page 11 of 31
When you initially save a model ensure it is always saved into a folder specifically created for the model. This folder must be empty prior to you making your first save.
If an error message appears when you save a model, please refer to section 6.1.3 to fix the issue. When saved you will see that the folder contains both a file (cust_orders.dmd) and folder
(cust_orders) named after your model name:
BOTH of these files are required components of your model. Please note you MUST NOT manually edit/rename/move/delete etc any of the files in this folder. This folder must be managed entirely through the use of SQL Developer Data Modeler.
c. Configuring Logical Model Notation
For all the logical models you create you MUST set the following (right click on the blank area in the logical model and then select these options):
● Notation to “Information Engineering Notation”:
● View Details to “Attributes”, and
● Show to “Labels” and “Legend”
When modelling you are required to include the “Legend” on all models (the panel may be moved to fit your model layout).
Page 12 of 31
d. Adding Relationships
Now add a 1:N Relationship between CUSTOMER and ORDERS – click in CUSTOMER (the parent) and then in ORDERS. In the General Relations Properties name the relationship “CUSTOMER_ORDERS”. For this unit, we name relationships in the one to many direction using the entities at each end – if the combined name is over 30 characters in length you should use an abbreviation of each entity name, such as CUST_ORD.
Enter a name for the source (this will be our relationship label), and set up the participation (Customer – optional, Order – mandatory ie. not optional):
e. Commit/Push Model onto GitLab Server
The model must be saved before you use GIT to push your model to the FITGitLab server. It is a good idea to regularly save and push your model so that you have a clear development history. Here are the steps:
1. Save the model (Save Design)
2. Click Team → Git → Add All (it might output “There are no files to add.”, you can ignore this
message, simply click OK)
3. Click Team → Git → Commit, write a meaningful commit message and click ok
4. Click Team → Git → Push, key in your GitLab password, click Finish
5. Use the Web UI (login to the web interface of the server) to check that your files and folders
are correctly being pushed
Page 13 of 31
f. Drawing Rest of the Model
Proceed and add the PRODUCT entity. The PRODUCT attributes are (product number, product description and product unit price), you should select a suitable data type and size for each of these attributes, remembering we try to use a numeric value for a PK.
Data Modeler can draw M:N relationships on its logical models, however, you cannot add attributes to such relationships – do not use the M:N relationships in our models, they are NOT acceptable on a logical model.
Add a new entity ORDERLINE and then connect this with ORDERS and PRODUCT via 1:N identifying relationships. Do not add any attributes until you have added the relationships. Now complete the ORDERLINE attributes (quantity ordered, total line price) – again, you should select a suitable data type and size for each of these attributes.
The conceptual model, which you have been mapping here, now needs to be re-examined in the light of further information that your client might provide. This can arise as feedback on your conceptual model (conceptual models are an excellent client-designer communication tool) and/or based on further details which you have collected. For our unit we are enhancing the conceptual model (assignment 1A) with normalisation of a number of client sample forms and potentially some further requirements (assignment 1B).
Your full logical model will have the form:
Page 14 of 31
g. Adding Constraint
The client requirements indicate that the customer level must be recorded as Gold, Silver or Bronze. The level of the customer is determined by “total value of the orders they have placed in the previous financial year” – this is not something that will be managed by your model, it will need some coding logic which is beyond the scope of your modelling – your model provides the data structure/source from which such a determination will be able to be made (here data from the orders and oderline relation will be used). However, within the model we need to ensure that the level is constrained to the allowed values.
This should be enforced by adding a CHECK CONSTRAINT. In this case, the check constraint will ensure that the acceptable values for cust_level attribute are only Gold, Silver or Bronze. This constraint becomes part of the database structure, if the user wishes to add a new option, say ‘Diamond’ as the highest level then the database structure must be altered.
To add the check constraint, select the CUSTOMER entity, then select attributes and double click on cust_level. In the left hand list of the pop-up window select “Default and Constraint”. Give the constraint a name for example “chk_custlevel” (be careful to select an informative name eg. chk_columnname), click Constraint “List Of Values”, add Value and Description for each level:
Where a specific range is needed, select “List Of Ranges” and directly enter the required values into the dialogue boxes which appear.
h. Adding Lookup Table
The client also indicates that flexibility is required in the design to be able to add new, and remove current, product categories as circumstances change. This should be handled by adding a LOOKUP table. Lookup tables are a good approach where there is likely to be a need to extend the possible values for an attribute.
Page 15 of 31
To add a product category lookup table in our model, we create a new relation called PROD_CATEGORY and then add a relationship between PROD_CATEGORY and PRODUCT relation as shown below.
i. Adding Surrogate Key (where appropriate)
In developing a logical model, where appropriate and documented, you are free to introduce surrogate primary keys.
If a surrogate PK is introduced into your design you must ensure that the original key’s uniqueness is still maintained by enforcing a unique identifier which includes the attributes which compose the original key.
As an example, we will look at the Monash Software case study. In the Monash Software model we have an EMPLOYEE_TRAINING relation (entity):
Page 16 of 31
Here the PK of EMPLOYEE_TRAINING is composed of three attributes, all of different datatypes – in such a situation we need to add a surrogate PK.
The first step is to add a new attribute (here et_no) to be the new PK – do not select/use the SQL Developer option “Create Surrogate Key”. As designers we need to be in control of surrogates added not leave this to the software.
As part of this addition, we need to uncheck et_date_completed as “Primary” since it will not be part of the new primary key.
Next, we need to modify the incoming relationships – completed_by and completes into non-identifying relationships. For both, double click the relationship and untick the ‘identifying’ box in the relationships’ properties.
We now need to protect our natural key (the previously declared key or natural key).
On the EMPLOYEE_TRAINING relation (entity), you need to select Unique Identifiers and add a new identifier (click the green add icon), this new unique identifier should be named appropriately. Double click the new unique identifier and add attributes in your previously identified Natural Key
Page 17 of 31
(here emp_no, training_code and et_date_completed) by bringing these attributes to the right box as shown in the picture below:
Here is the EMPLOYEE_TRAINING relation with an added surrogate key.
Note that the PK is shown as et_no, our introduced surrogate key, the natural key emp_no, training_code and et_date_completed have a U alongside the attributes to indicate these must be unique in this way protecting the natural key.
Important:
Before proceeding to develop a physical model, prior to implementation in our database of choice, we need to validate every relation shown on our final logical model. In this step we are ensuring the primary key selections are correct, the relations are in at least 3NF and that no insert/update/delete anomalies remain.
Page 18 of 31
6.2.4 Develop a model – Stage 2 The Relational (Physical) Model
This completed logical model is now “Engineered” to a “Relational Model”.
In the pop-up window which appears, on the “General Options” tab ensure “Apply name translation” is not checked:
With this option not checked, Data Modeler will use exactly the same table and attribute names for both your logical and relational models (the names you selected), rather than apply its own set of rules and potentially change a name on you. If a naming error occurs you will need to correct it on your logical model and regenerate the relational model.
Select the bottom left button, “Engineer”. The logical model will then be engineered and a Relational Model is opened:
Page 19 of 31
If you are re-engineering a model (ie. trying to generate a previous relational model, after changes to your logical model) it is very important that you note that SQL Developer does not automatically sync deletions from your logical model – such changes must be individually selected to be synced to your relational model. When re-engineering a previous model carefully check the “Engineer to Relational Model” for any triangles with an exclamation mark symbol:
Such symbols represent issues you must address before generation.
For example here under relations (where we are removing the “appear on” relation as a demonstration of what occurs):
Page 20 of 31
the removal of “appear on” has not been selected to be engineered to the relational model. You need to check the box if you wish it to be engineered (which we normally would).
If your relational model gets very confused you can select the relational model tab, do a ctrl+A or Apple+A and delete all the objects. The model can then be regenerated. Under no circumstances should you delete the relational model itself (in the left browser navigator), a bug in several versions of the software can result in such an action causing your relational model to “disappear”.
When you have configured the relational model as you wish, select Generate DDL from the top toolbar:
Select “Generate” in the pop-up window, specify the DDL Generation Options you wish (Drop tables should be included) and then select OK to generate the DDL.
Page 21 of 31
The generated file can be Saved as an Oracle schema script by selecting “Save”. You MUST use an extension of .sql, not the default .ddl – rename the file extension after saving if you use this approach (SQL Developers SQL client cannot load .ddl files). The simplest way to save this is to not use the “Save” button, use Ctrl+A to select all the script, swap to an SQL Window and use Ctrl+V, and then save the file from the SQL Window. Save the file as custorders_schema.sql.
You must add your student id and student name at the top of the generated SQL file. Additionally, to capture the full run of your schema file you must insert a SPOOL and ECHO on command at the top of your file, and a SPOOL OFF and ECHO off at the end of your script.
For example – added to the top of your script:
–student id: 12345678
–student name: Firstname Lastname
— Capture run of script in file called custorders_schema_output.txt set echo on
SPOOL custorders_schema_output.txt
….. the SQL Developer Generated Script goes here ……
—————————————————– SPOOL off
set echo off
Test your generated file against Oracle and confirm it operates correctly. This script must be saved in an appropriate folder of your local repo and pushed to the FITGitLab server.
Page 22 of 31
It is also possible to configure Data Modeler to directly synchronise the design into the Data Dictionary of a database connection (we will not use this approach) .
Important Note:
To submit your final assignment model to Moodle, you must zip at the top folder lever (here the CustomerOrder folder) – right click on the folder and use your zip tool. The zip archive must contain the top level folder and both the .dmd file and the folder matching the project name
BEFORE submitting your assignment you MUST copy your zip archive to a new EMPTY folder, extract it and then ensure that you can navigate to this new folder and open your model successfully.
Page 23 of 31
6.3 Property-Rental Model 6.3.1 Drawing Logical Model
Map the conceptual model (ERD) and normalisation result (3NF) of “Property Rental System” case study into a logical model in Oracle SQL Developer Data Modeler. In doing so make use of at least one check clause and at least one surrogate key.
Case Study (taken from session 3 tutorial):
● Properties are rented by tenants. Each tenant is assigned a unique number by the Agency. Data held about tenants include family name, given name, property rented, contact address – street, city, state, postcode & telephone number. A tenant may rent more than one property and many tenants may rent parts of the same property (eg. a large shopping complex).
● Properties are owned by owners. Each property is assigned a unique property number. The agency only recognises a single owner for any of the properties it handles. The owner, address, and value are recorded for each property. Also, the lease period and bond are recorded for each property or sub-property rented. An owner may own several properties. For each owner an owner number is assigned, the owner name is also recorded.
● Properties are subject to damage and the agency records all instances of damage to its properties – property, date, type of damage and repair cost are recorded. Repair costs are charged directly to tenants
● Tenants pay accounts to the Agency – these consist of weekly rental payments, bond payments (for new properties) and damage bills. The date of payment, tenant, property, type of account (Rental, Bond, Damage) and amount are recorded. Each payment is assigned a payment number.
Extra Description:
The following requirements are provided by the agency as a follow up of the property-rental case study discussed in Session 3 Tutorial:
● The agency indicates that tenant and owner names must be decomposed into title, given name and family name, and property address and owner address must be treated as simple attributes.
● The agency records normal property maintenance. Maintenance costs are charged to the property owner. A sample of property maintenance report has been provided in session 5 tutorial.
● The agency has indicated that it needs flexibility in the design to be able to add new, and remove current, paid by methods as circumstances change.
Page 24 of 31
Conceptual Model (taken from session 3 tutorial):
Normalisation Result (taken from session 5 tutorial):
OWNER(owner_no, owner_title, owner_givname, owner_famname, owner_address) PROPERTY(prop_no, prop_address, owner_no)
MAINTENANCE(prop_no, maint_datetime, maint_desc, maint_cost)
TENANT(tenant_no, tenant_title, tenant_givname, tenant_famname)
PAYMENT(prop_no, rent_lease_startdate, pay_no, pay_date, pay_type, pay_amount, pay_paidby) PROPERTY_TENANT(prop_no, r ent_lease_startdate, rent_weekly_rate, rent_bond, tenant_no) Note that PROPERTY_TENANT is the same relation as RENT, thus you should use RENT as the relation name in logical model
6.3.2 Generate Relational (Physical) Model
Engineer your Logical Model to a Relational Model, generate DDL and then create the tables etc in Oracle from the generated DDL.
Page 25 of 31
6.4 SQL Developer Data Modeller Issues/Problems
This document lists some of the issues you may experience when using SQL Developer
6.4.1 Relational Model Disappears
You may see an error:
or observe that you relational model (if you had created one has disappeared)
This situation may arise under two circumstances:
(a) where you have not engineered to a logical model as yet – ie. you are about to do it for the first time. In such a situation an error has occurred at some stage of the project creation/save and the relational model has been removed. To correct this, right-click – Relational model in the Data Modeller Browser and select “New Relational Model”:
(b) where you have engineered a logical model but it has disappeared. When a model is saved SQL Developer Data Modeller sometimes fails to save your relational model fully. The Relational diagram is still present in your model but does not show within your project when you open it.
Before proceeding please ensure your model is closed and you have exited from SQL Developer.
Page 26 of 31
To correct this problem, open your model folder and navigate to the rel folder (this is where your relational model is saved):
The model shown above is complete, it does not have the missing relational diagram problem. The rel folder should contain a folder and an xml document of the same name. If there is a folder inside rel (it will have a different name, they are all unique) but no xml file of the same name then this is a save error.
Your relational model has not gone, the problem is that the xml document was not saved correctly, follow the steps below to correct this problem:
Make sure your model is closed
1. Copy the supplied XML file in Moodle (session 6) into the rel folder
2. Rename the file from “CHANGE_TO_FOLDER_NAME.xml” to have the same name as the
folder in your rel folder, with the .xml extension added eg. as above
1D583D53-B2C458D2350C.xml (yours will be different)
3. Open the file in a text editor and change the id=”CHANGE_TO_FOLDER_NAME” on line 2
to be the same as your folder name
When you now open your model the relational model should be back.
Page 27 of 31
6.4.2 Having problems navigating into a folder
Sometimes the java based file manager of SQL Developer Data Modeler has problems navigating through and selecting your folders.
If this occurs, or as a standard alternative, you can type/obtain in the full path name that you wish to open or navigate to. For example, if you have a Customer-Orders project you wish to open use the following steps:
In your file manager navigate to where your .dmd file for the project is and obtain it’s full path name.
Under Mac OS, right click the dmd file and right click and while holding this, the “option” key, this will result in:
under MS Windows, hold the shift and while holding it, right click on the file or folder, “Copy as path” will appear:
Page 28 of 31
This path can then be pasted directly into the SQL Developer dialogue box – to cause it to go to a particular folder or open a particular project. For example, in MS Windows:
Note that in MS Windows you must first remove the opening and closing quotes (“) from the path name.
Page 29 of 31
6.4.3 Logical Data Types Disappear
“My data types have disappeared” – when you check in preferences you have no Logical Types:
This has occurred because your default Systems Type Directory is now set incorrectly, or you have modified the folder in some manner.
This can occur if you have accidentally deleted the files in your Default System Types Directory if you have placed other files or subfolders in it, if you have a space in the pathname, or if SQL Developer has not correctly saved them at some stage.
To fix this:
● Go back to this setting and remove the current value in this entry ie. in the above, as used in
the on-campus labs, it is DocumentsUNITSDatabaseyourLocalRepoSYSTEMTYPES –
make this entry blank (have no path)
● Save your settings and exit SQL Developer
● Reopen SQL Developer and the types will be back.
You will need to reset up the Default System Types Directory as you previously did (ensure there is no space in the pathname).
Page 30 of 31
Important
After you have completed your current lab activities, at the end of each session of work, remember to add files, commit and push any changes you have made to the FITGitLab server. We would expect a revision history as you build your model step by step. For Assignment 1 B this version history will be assessed. Remember when working on models you MUST ENSURE the model is saved BEFORE attempting any GIT activities.
You need to get into the habit of establishing this as a standard FIT2094-FIT3171 workflow – Pull at the start of your working session, work on the activities you wish to/are able to complete during this session, stage/commit changes and then push the changes back to the FITGitLab server
Page 31 of 31
Reviews
There are no reviews yet.