Studio I.2 CASE Tool

(Revised: December 28, 2007)

• This assignment will familiarize you with the concept and technique of data normalization by using a CASE-tool, IBMS.
• IBMS is installed in the PC lab in CII and will soon also be in the Nugent PC lab.

Objective

• To learn to use the CASE-tool IBMS.
• To understand the basic concepts behind TSER modeling.
• To understand Functional Dependencies.
• Key Terms -- SER, OER, FDs, Data Normalization.

Part I

Do the Hospital Example given below (also, see p.45 of Enterprise Integration and Modeling: The Metadatabase Approach by C. Hsu for more information) by using the CASE-tool IBMS, and compare the results with the same example in the class notes entitled, "Data Normalization."

Scenario

Get-well-soon hospital wants to develop a database to keep track of the surgeries performed on its patients. Every patient is given a unique patient number which uniquely determines the patient's name and address. Every surgeon has a unique number which determines her/his name. The post-operative drug that a patient is given has some side effects and those are tracked too.

You will find the details of the modeling problem here.

Here are the step-by-step directions to carry out this assignment. Please follow the instructions to download the CASE-tool, and make sure that you store the program in a correct folder as instructed. Detailed instructions about using the CASE-tool are given in the manual which you can also download from the Enterprise Integration and Modeling Homepage. Please also refer to the on-line help available with the CASE-tool.

Please note that you are free to use different names than we have used here. Also, please feel free to change the names of data items, their length and type, you are the modeler and you know the system requirements best! However, please do not use "date" as a data item name because date is a reserved word in oracle.

• Double click on the IBMS icon.
• You will see the welcome screen (with the moving spaceship). Click anywhere inside the welcome screen to bring up the first screen.
• Under "tools", select SER Modeling.
• The tool box on the right contains 10 icons, which going from left to right and top to bottom represent: pointer, cut, subject, context, zoom-out, zoom-in, re-compose and decompose operations.
• Select the Subject icon and drop it on the work area.
• A dialogue box will open asking you to name the application. Call it Hospital, abreviation: HOS.
• Under the item list window, click the add button. A window will pop up asking you to enter the (data)item. Enter:
  1. p_name, type character, length 30
  2. p_num, type character, length 10
  3. p_add, type character, length 60
  4. date_surg, type character, length 10
  5. surgery, type character, length 30
  6. s_num, type character, length 10
  7. s_name, type character, length 30
  8. po_drug, type character, length 20
  9. side_effect, type character, length 30
• Close the application definition window after entering all the (data)items.
• Decompse the subject Hospital by first selecting the decompose icon and then the subject Hospital.
• At this second level, create a subject and call it p_surgery.
• You can add as many of the data items defined in the pervious section to the subject p_surgery as you choose. Click the "add" button under data itmes window and a window "Assign Items" will pop up. Click on the items you want assigned to p_surgery an d click "add".
• Click the "add" button under the functional dependency list window. This will open a window which will help you define the FDs amongst data items. Define the following FDs:
  • p_num --> p_add p_name
  • s_num --> s_name
  • date_surg p_num s_num --> po_drug surgey
  • po_drug --> side_effect

Your SER model is done at this point. Save the file as hospital.ser and exit the SER modeling module.

Under "tools", select SER -->OER mapping and select hospital.ser file.

The system will ask you for key verificaton. Skip it.

Next you a dialog box will ask you if a particular Foreign Relationship (FR) is a Mandatory Relationship (MR). Choose "no" and the system will map the SER file to OER for you. Save it as hospital.oer.

Exit this module and under tools, select OER Modeling. Choose the file hospital.oer. You will see the jumbled up OER diagram on the upper left hand side of the screen. Under "tool", select the "redraw" option, and then the fully optimized option. If y ou perform all the steps correctly, you should see an OER similar to the one given below:

Use the right mouse button to change the entity and relation names to those given above from the ones you get in your OER diagram.

• Instructions for "Printing OER diagrams" and "Printing the data structures of entities and relationship" using IBMS.

Schema Generation

• Exit the OER Modeling module, click on SchemaGen and choose Oracle.
• Under File, select Generate Schema option.
  • The system will prompt you to select the .ser and the .oer files in that order.
• The software will generate the required schema file and prompt you to name it with a .sql extension. Save the file with some appropriate name, hospital.sql, for example.
• Use any text editor, notepad for example, to view your schema (hospital.sql) file.

Part II

Use the same procedure to normalize the PROJECT-USE relation shown below (see Problem #4 of the problem set given in class for more details). That is, first define all data items and enter their functional dependencies into a subject representing PROJECT-USE. Then, follow the example of the Hospital case to obtain the same types of results for this case.

PROJECT-USE (proj_no, item_no, proj_start_date, proj_end_date, item_cost, qty_of_item_used_in_proj) subject to the FD set (proj_no --> proj_start_date, proj_end_date; item_no --> item_cost; proj_no, item_no --> qty)

Part III

In this section you will import the schema into PostgreSQL and manipulate the database tables by adding relevant data items and adhering to the integrity constraints of the database schema. See Studio 1.1 on how to install PostgreSQL. Since the hospital.sql file contains an Oracle schema, a few, but slight changes must be made to make this compatible with PostgreSQL, which can be accomplished manually. Make the following changes to the hospital.sql file in a text editor such as Notepad:

• Remove all Oracle REMARK statements
• Remove all AUTOCOMMIT statements

Save the file. From the command line (e.g. Microsoft Windows XP - START > RUN > Type cmd). In the window that pops up change to the PostgreSQL directory (cd C:\Program Files\PostgresQL\8.0\bin) and execute the following command: (N.B. To execute these commands you must have completed Studio 1.1 in its entirety.)

createdb hospital

The PostgreSQL Server will confirm the creation of this database. Then execute the following command to import the hospital.sql file into the hospital database.

psql -d hospital < hospital.sql

The PostgreSQL Server will confirm the of creation of the database objects in the hospital database.

You can then manipulate the database hospital via the pgAdmin III application, or via the command line.

Deliverables

1. Write a summary of your findings to address the above questions in both parts I and II. Print out the Entity-Relationship diagram from each part. Finally, list all relations implied in the diagrams and indicate their keys (primary and alternate).