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:

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

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.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.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.

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.

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)