Develop the Logical Model

The logical model for the database includes three entities: Books (describes each book in the library), Patrons (describes each person who has a library card), and Transactions (describes each transaction involving a patron and a book). However, before you create the entities, create some domains that will make the entity creation (and later DDL generation) more meaningful and specific.

To start developing the logical model, go to Adding Domains.

Adding Domains

In planning for your data needs, you have determined that several kinds of fields will occur in multiple kinds of records, and many fields can share a definition. For example, you have decided that:

You therefore decide to add appropriate domains, so that you can later use them to specify data types for attributes when you create the entities. (These added domains will also be available after you exit Data Modeler and restart it later.)

  1. Click Tools, then Domains Administration.

  2. In the Domains Administration dialog box, add domains with the following definitions. Click Add to start each definition, and click Apply after each definition.

    Name Logical Type Other Information
    Person Name VARCHAR Size: 25
    Address Line VARCHAR Size: 40
    City VARCHAR Size: 25
    State VARCHAR Size: 2
    Zip VARCHAR Size: 10
    Book Id VARCHAR Size: 20
    Numeric Id NUMERIC Precision: 7, Scale: 0
    Title VARCHAR Size: 50

  3. When you have finished defining these domains, click Save. This creates a file named defaultdomains.xml in the datamodeler/domains directory or datamodeler\domains folder under the location where you installed Data Modeler.

  4. Optionally, copy the defaultdomains.xml file to a new location (not under the Data Modeler installation directory), and give it an appropriate name, such as library_domains.xml. You can then import domains from that file when you create other designs.

  5. Click Close to close the dialog box.

  6. Go to Creating the Books Entity.

Creating the Books Entity

The Books entity describes each book in the library. Create the Books entity as follows:

  1. In the main area (right side) of the Data Modeler window, click the Logical tab.

  2. Click the New Entity icon.

  3. Click in the logical model pane in the main area; and in the Logical pane press, diagonally drag, and release the mouse button to draw an entity box. The Entity Properties dialog box is displayed.

  4. Click General on the left, and specify as follows:

    Name: Books

  5. Click Attributes on the left, and use the Add (+) icon to add the following attributes, one at a time. (For datatypes, select from the Domain types except for Rating, which is a Logical type.)

    Name Datatype Other Information and Notes
    book_id Domain: Book Id Primary UID (unique identifier). (The Dewey code or other book identifier.)
    title Domain: Title M (mandatory, that is, must not be null).
    author_last_name Domain: Person Name M (mandatory, that is, must not be null).
    author_first_name Domain: Person Name (Author's first name; not mandatory, but enter it if the author has a first name.)
    rating Logical type: NUMERIC (Precision=2, Scale= 0) (Librarian's personal rating of the book, from 1 (poor) to 10 (great).)

  6. Click OK to finish creating the Books entity.

  7. Go to Creating the Patrons Entity.

Creating the Patrons Entity

The Patrons entity describes each library patron (that is, each person who has a library card and is thus able to borrow books). Create the Patrons entity as follows:

  1. In the main area (right side) of the Data Modeler window, click the Logical tab.

  2. Click the New Entity icon.

  3. Click in the logical model pane in the main area; and in the Logical pane press, diagonally drag, and release the mouse button to draw an entity box. (Suggestion: draw the box to the right of the Books box.) The Entity Properties dialog box is displayed.

  4. Click General on the left, and specify as follows:

    Name: Patrons

  5. Click Attributes on the left, and use the Add (+) icon to add the following attributes, one at a time. (For datatypes, select from the Domain types, except for location, which uses the structured type SDO_GEOMETRY.)

    Attribute Name Type Other Information and Notes
    patron_id Domain: Numeric Id Primary UID (unique identifier). (Unique patron ID number, also called the library card number.)
    last_name Domain: Person Name M (mandatory, that is, must not be null). 25 characters maximum.
    first_name Domain: Person Name (Patron's first name.)
    street_address Domain: Address Line (Patron's street address.)
    city Domain: City (City or town where the patron lives.)
    state Domain: State (2-letter code for the state where the patron lives.)
    zip Domain: Zip (Postal code where the patron lives.)
    location Structured type: SDO_GEOMETRY Oracle Spatial geometry object representing the patron's geocoded address.

  6. Click OK to finish creating the Patrons entity.

  7. Go to Creating the Transactions Entity.

Creating the Transactions Entity

The Transactions entity describes each transaction that involves a patron and a book, such as someone checking out or returning a book. Each record a single transaction, regardless of how many books the patron brings to the library desk. For example, a patron returning two books and checking out three books causes five transactions to be recorded (two returns and three checkouts). Create the Transactions entity as follows:

  1. In the main area (right side) of the Data Modeler window, click the Logical tab.

  2. Click the New Entity icon.

  3. Click in the logical model pane in the main area; and in the Logical pane press, diagonally drag, and release the mouse button to draw an entity box. (Suggestion: Draw the box below and centered between the Books and Patrons boxes.) The Entity Properties dialog box is displayed.

  4. Click General on the left, and specify as follows:

    Name: Transactions

  5. Click Attributes on the left, and use the Add (+) icon to add the following attributes, one at a time. (For datatypes, select from the Domain types, except for transaction_date, which uses a Logical type.)

    Attribute Name Type Other Information and Notes
    transaction_id Domain: Numeric Id Primary UID (unique identifier). (Unique transaction ID number)
    transaction_date Logical type: Datetime M (mandatory, that is, must not be null). Date and time of the transaction.
    transaction_type Domain: Numeric Id M (mandatory, that is, must not be null). (Numeric code indicating the type of transaction, such as 1 for checking out a book.)

    Note that you do not explicitly define the patron_id and book_id attributes, because these will be automatically added to the Transactions entity after you create relations between the entities (see Creating Relations Between Entities); they will be added as foreign keys when you generate the relational model (see Develop the Relational Model).

  6. Click OK to finish creating the Transactions entity.

  7. Go to Creating Relations Between Entities.

Creating Relations Between Entities

Relations show the relationships between entities: one-to-many, many-to-one, or many-to-many. The following relationships exist between the entities:

Create the relationships as follows. When you are done, the logical model pane in the main area should look like the following figure. Note that for this figure, Bachman notation is used (you can change to Barker by clicking View, then Logical Diagram Notation, then Barker Notation).

Logical model representation
  1. In the logical model pane in the main area, arrange the entity boxes as follows: Books on the left, Patrons on the right, and Transactions either between Books and Patrons or under them and in the middle. (If the pointer is still cross-hairs, click the Select icon at the top left to change the pointer to an arrow.)

    Suggestion: Turn off auto line routing for this exercise: right-click in the Logical pane, and ensure that Auto Route is not checked.

  2. Click the New 1:N Relation icon.

  3. Click first in the Books box, then in the Transactions box. A line with an arrowhead is drawn from Books to Transactions.

  4. Click the New 1:N Relation icon.

  5. Click first in the Patrons box, then in the Transactions box. A line with an arrowhead is drawn from Patrons to Transactions.

  6. Optionally, double-click a line (or right-click a line and select Properties) and view the Relation Properties information.

  7. Go to Develop the Relational Model.

Related Topics

Data Modeler Tutorial: Modeling for a Small Database

Data Modeler User Interface