Enterprise Integration & Modeling: Metadatabase Research Home
 home || MDB Research | Virtual Lab | Case Tool | Downloads | Publications | Researchers | Links

Studio III.4 Database design using IBMS tools

This studio continues from the Studio 3. It shows how to use IBMS tools (including IBMS/DFD, IBMS/SER, and IBMS/OER) to design database.

Objective:

  • Learn how to design information systems’ database using IBMS tools.

Steps:

Define data items of data stores

In this step, we define data items, required for processes,for each data store as follows:

  • Define the following data items for the data store “Seminar”: seminar_city_name, seminar_end_date, seminar_latest_registrants, seminar_name, seminar_site_address, seminar_site_name, seminar_size, seminar_start_date, seminar_type, seminar_consultant_id, seminar_consultant_name
  • Define the following data items for the data store “Logistics_Database”: consultant_pref_airline_name, consultant_airport_code, consultant_pref_flight_eqp, facility_name, facility_qty, seminar_consultant_id, seminar_type, facility_id
  • Define the following data items for the data store “Contract”: contract_date, contract_no, facility_qty, seminar_name, facility_id
  • Define the following data items for the data store “Flight_Schedule”: flight_arrival_time, flight_departure_time, flight_equipment_id, flight_equiptment_name, flight_frequency, flight_from, flight_number, flight_to
  • Define the following data items for the data store “Material”: material_code, material_name, material_qty, seminar_type
  • Define the following data items for the data store “Material_Request”: seminar_consultant_id, flight_number, ct_arrival_date, ct_arrival_time, ct_departure_date, ct_departure_time, ct_from_city, ct_to_city
  • Define the following data items for the data store “Consultant_Travel”: seminar_consultant_id, flight_number, ct_arrival_date, ct_arrival_time, ct_departure_date, ct_departure_time, ct_from_city, ct_to_city

Covert the DFD to SER model

We covert the DFD into SER model in Figure 4.1 and 4.2.

Figure 4.1: The level 1 generated SER model.

Figure 4.1: The level 1 generated SER model.

Figure 4.2: The level 2 generated SER model.


Figure 4.2: The level 2 generated SER model.

Modify the SER model

We modify the SER model in Figure 4.1 and Figure 4.2 to Figure 4.3 and Figure 4.4.

Figure 4.3: The level 1 modified SER model.

Figure 4.3: The level 1 modified SER model.

Figure 4.4: The level 2 modified SER model.

Figure 4.4: The level 2 modified SER model.

Design database using IBMS/SER and IBMS/OER tool

  • Define equivalent data items: material_code = request_mat_code
  • Define the following FDs for the subject “Seminar”: seminar_name ---> seminar_city_name, seminar_consultant_id, seminar_end_date, seminar_latest_registrants, seminar_site_address, seminar_site_name, seminar_size, seminar_start_date, seminar_type; seminar_consultant_id ---> seminar_consultant_name;
  • Define the following FDs for the subject “Logistics_Database”: facility_id ---> facility_name; facility_id, seminar_type ---> facility_qty; seminar_consultant_id ---> consultant_airport_code, consultant_pref_airline_name, consultant_pref_flight_eqp;
  • Define the following FDs for the subject “Contract”: contract_no ---> contract_date, seminar_name; contract_no, facility_id ---> facility_qty;
  • Define the following FDs for the subject “Flight_Schedule”: flight_number ---> flight_arrival_time, flight_departure_time, flight_equipment_id, flight_equiptment_name, flight_frequency, flight_from, flight_to;
  • Define the following FDs for the subject “Material”: material_code ---> material_name; material_code, seminar_type ---> material_qty;
  • Define the following FDs for the subject “Material_Request”: request_no ---> request_date, request_ship_to, request_status; request_mat_code, request_no ---> request_mat_qty;
  • Define the following FDs for the subject “Consultant_Travel”: flight_number, seminar_consultant_id ---> ct_arrival_date, ct_arrival_time, ct_departure_date, ct_departure_time, ct_from_city, ct_to_city;

Map the SER model into the OER model:

We map the SER to OER model in Figure 4.5.

Figure 4.5: The generated OER model.

Figure 4.5: The generated OER model.

Modify the OER model and generate the database schema:

The last step is to modify the generated OER model to generate the system database schema as desired.

Suppose we make the following modifications to the OER model:

  • Remove the functional relationship “FR_Seminar_OE_seminar_type,” the entity “OE_seminar_type,” and the plural relationship “Logistics_Databases”
  • Change the name of entity “OE_request_no” to “Request”
  • Change the name of plural relationship “Material” to “Seminar_Material”
  • Change the name of entity “OE_material_code” to “Material”
  • Change the name of entity “OE_seminar_consultant” to “Consultant”
  • Change the name of functional relationship “FR_Seminar_OE_seminar” to “conducted by”
  • Change the name of entity “OE_facility_id” to “Facility”
  • Change the name of plural relationship “PR_facility_id_seminar” to “Seminar_Material”
  • Change the name of functional relationship “FR_OE_contract_no_Seminar” to “of”
  • Change the name of plural relationship “Contract” to “Contract_Facility”
  • Change the name of entity “OE_contract_no” to “Contract”
  • Redraw the OER model as shown in Figure 4.6

Figure 4.6: The modified OER model.

Figure 4.6: The modified OER model.

The database schema, generated from the OER in Figure 4.6, would consist of the following tables:

  • Consultant (seminar_consultant_id, consultant_airport_code, consultant_pref_airline_name, consultant_pref_flight_eqp, seminar_consultant_name)
  • Facility (facility_id, facility_name)
  • Flight_Schedule (flight_number, flight_arrival_time, flight_departure_time, flight_equipment_id, flight_equiptment_name, flight_frequency, flight_from, flight_to)
  • Material (material_code, material_name)
  • Request (request_no, request_date, request_ship_to, request_status)
  • Seminar (seminar_name, seminar_city_name, seminar_consultant_id, seminar_end_date, seminar_latest_registrants, seminar_site_address, seminar_site_name, seminar_size, seminar_start_date, seminar_type)
  • Contract (contract_no, contract_date, seminar_name)
  • Seminar_Facility (facility_id, seminar_type, facility_qty)
  • Contract_Facility (contract_no, facility_id, facility_qty)
  • Seminar_Material (material_code, seminar_type, material_qty)
  • Material_Request (request_mat_code, request_no, request_mat_qty)
  • Consultant_Travel (flight_number, seminar_consultant_id, ct_arrival_date, ct_arrival_time, ct_departure_date, ct_departure_time, ct_from_city, ct_to_city)
 

viu.eng.rpi.edu is hosted by Professor Cheng Hsu.
Rensselaer Polytechnic Institute
Department of Industrial and Systems Engineering (formally Decision Sciences & Engineering Systems)
110 8th St., Center for Industrial Innovation, Room 5123, Troy, NY 12180-3590

Copyright © 1997-2016. MetaWorld, Nothing on this site may be commercially used without written consent.

Valid XHTML 1.0! Valid CSS!