Using Microsoft Visio you can easily design and document logical database schemas for relational and object-relational databases. Visio enables you to create the Object Role Modeling (ORM) source model for an application, using which you can generate the logical database schema. You can use the logical database schema to create physical database schemas in the form of an SQL DDL file. The SQL DDL file provides the SQL script to create the database.
This chapter explains how to use an ORM source model and the corresponding database model diagrams that create physical database schemas to create an airlines database. The Airlines Reservation application implements the airlines database and reserve seats for travelers.
The Airlines Reservation application consists of an AirlinesReservation project that enables an end user to add and delete airlines and aircrafts to the application, and reserve seats for travelers.
Figure 5-1 shows the architecture of the Airlines Reservation application:
ORMModeling.vsd file defines the ORM source model for the Airlines Reservation application. The ORMModeling.vsd file defines the Airlines, Aircrafts, Travelers, and Seats entities, roles of these entities, relationships between these entities, and the business rules to which these entities conform.
Figure 5-2 shows the ORM source model, which the ORMModeling.vsd file defines:
The DBModeling.vsd file defines the database model diagram for the ORM source model defined in the ORMModeling.vsd file. You can generate the DBModeling.vsd file from the ORMModeling.vsd file using Visio. The DBModeling.vsd file displays the conceptual design of the ORM source model in the form of tables and the relationships between these tables. The entities in the ORM source model map to the tables and the business rules map to the constraints in the database model diagram.
Figure 5-3 shows the database model diagram, which the DBModeling.vsd file defines:
To create the DBModeling.vsd file that defines the Database model diagram:
Open Microsoft Visio.
Select File->New->Database->Database Model Diagram.
Select Database->View->Project. The project window appears.
Right-click the project icon in the project window and select Add Existing Document.
Specify the path of the ORMModeling.vsd file and click Open.
Select Database->Project->Build and specify the file name as DBModeling.vsd.
Drag and drop the entities in the Tables and Views window onto the Visio page to view the database model diagram.
The AIRLINES.DDL file defines the physical database schema to generate the airlines database. The DBModeling.vsd file generates the AIRLINES.DDL file using the Database Generate wizard of Visio. To create the AIRLINES.DDL file:
Open Microsoft Visio.
Open DBModelling.vsd file.
Select Database->Generate. The Generate Wizard opens.
Specify the File name as AIRLINES.DDL and follow the wizard steps to generate the AIRLINES.DDL file.
The AirlinesReservation project consists of the following files:
Shared.vb : Provides the main() function that establishes a connection with the airlines database and invokes the Application Manager window.
frmApplicationManager.vb : Provides the ApplicationManager class that defines the Application Manager window, which enables an end user to browse through the Airlines Reservation application.
frmAirlines.vb : Provides the Airlines class that defines the Airlines window, where an end user can add new Airlines, and view and delete existing Airlines.
frmAircrafts.vb : Provides the Aircrafts class that defines the Aircrafts window, where an end user can add new aircraft for an Airline, and view and delete existing aircrafts.
frmReservation.vb : Provides the Reservation class that defines the Reservation window where an end user can reserve seats for travelers.
frmConfirmation.vb : Provides the Confirmation class that defines the Confirm Reservation Information window that enables an end user to view the reservation information and confirm the reservation.
Figure 5-4 shows the class diagram for the Airlines Reservation application: