Local view examples

The following examples show how to design local views including the schedule of courses, the instructor skills report, and the instructor schedules.

Each example shows the following parts of designing a local view:

  1. Gather the data. For each example, the data elements are listed and two occurrences of the data aggregate are shown. Two occurrences are shown because you need to look at both occurrences when you look for repeating fields and duplicate values.
  2. Analyze the data relationships. First, group the data elements into a conceptual data structure using these three steps:
    1. Separate repeating data elements in a single occurrence of the data aggregate by shifting them to a lower level. Keep data elements with their keys.
    2. Separate duplicating values in two occurrences of the data aggregate by shifting those data elements to a higher level. Again, keep data elements with their keys.
    3. Group data elements with their keys. Make sure that all the data elements within one aggregate have the same key. Separate any that do not.
  3. Determine the mappings between the data aggregates in the data structure you have developed.

Example 1: schedule of courses

Headquarters keeps a schedule of all the courses given each quarter and distributes it monthly. Headquarters wants the schedule to be sorted by course code and printed in the format shown in the following figure.

Figure 1. Schedule of courses 
                COURSE SCHEDULE

      COURSE:   TRANSISTOR THEORY       COURSE CODE:    418737
      LENGTH:   10 DAYS                 PRICE:          $280

      DATE                      LOCATION

      APRIL 14                  BOSTON
      APIRL 21                  CHICAGO
      .
      .
      .
      NOVEMBER 18               LOS ANGELES
  1. Gather the data. The following table lists the data elements and two occurrences of the data aggregate.
    Table 1. Course schedule data elements
    Data elements Occurrence 1 Occurrence 2
    CRSNAME TRANS THEORY MICRO PROG
    CRSCODE 41837 41840
    LENGTH 10 DAYS 5 DAYS
    PRICE $280 $150
    DATE multiple multiple
    EDCNTR multiple multiple
  2. Analyze the data relationships. First, group the data elements into a conceptual data structure.
    1. Separate repeating data elements in one occurrence of the data aggregate by shifting them to a lower level, as shown in the following table
      Figure 2. Course schedule after step 1
      Begin figure description. Data aggregates: Course points to Class. End figure description.
    2. Next, separate duplicate values in two occurrences of the data aggregate by shifting the data elements to a higher level.

      This data aggregate does not contain duplicate values.

    3. Group data elements with their controlling keys.

      Data elements are grouped with their keys in the present structure. No changes are necessary for this step.

      The keys for the data aggregates are shown in the following table.
      Table 2. Data aggregates and keys for course schedule after step 1
      Data aggregate Keys
      Course aggregate CRSCODE
      Class aggregate CRSCODE, EDCNTR, DATE
  3. When you have developed a conceptual data structure, determine the mappings for the data aggregates.

    The mapping for this local view is: Course ◄────────►► Class

Example 2: instructor skills report

Each Ed Center needs to print a report showing the courses that its instructors are qualified to teach. The report format is shown in the following figure.

Figure 3. Instructor skills report 
                           INSTRUCTOR SKILLS REPORT

   INSTRUCTOR              COURSE CODE             COURSE NAME
   
   BENSON, R. J.           41837                   TRANS THEORY
   MORRIS, S. R.           41837                   TRANS THEORY
                           41850                   CIRCUIT DESIGN
                           41852                   LOGIC THEORY
   .
   .
   .
   REYNOLDS, P. W.         41840                   MICRO PROG
                           41850                   CIRCUIT DESIGN
  1. Gather the data. The following table lists the data elements and two occurrences of the data aggregate.
    Table 3. Instructor skills data elements
    Data elements Occurrence 1 Occurrence 2
    INSTR REYNOLDS, P.W. MORRIS, S. R.
    CRSCODE multiple multiple
    CRSNAME multiple multiple
  2. Analyze the data relationships. First, group the data elements into a conceptual data structure.
    1. Separate repeating data elements in one occurrence of the data aggregate by shifting to a higher level as shown in the following figure.
      Figure 4. Instructor skills after step 1
      Begin figure description. Data aggregates: Instructor points to Course. End figure description.
    2. Separate any duplicate values in the two occurrences of the data aggregate.

      No duplicate values exist in this data aggregate.

    3. Group data elements with their keys.

      All data elements are grouped with their keys in the current data structure. There are no changes to this data structure.

  3. Determine the mappings for the data aggregates.

    The mapping for this local view is: Instructor ◄────────►► Course

Example 3: instructor schedules

Headquarters wants to produce a report showing the schedules for all the instructors. The following figure shows the report format.
Figure 5. Instructor schedules 
                          INSTRUCTOR SCHEDULES
 
INSTRUCTOR          COURSE            CODE     ED CENTER     DATE

BENSON, R. J.       TRANS THEORY      41837    CHICAGO       1/14/96
MORRIS, S. R.       TRANS THEORY      41837    NEW YORK      3/10/96
                    LOGIC THEORY      41852    BOSTON        3/27/96
                    CIRCUIT DES       41840    CHICAGO       4/21/96
REYNOLDS, B. H.     MICRO PROG        41850    NEW YORK      2/25/96
                    CIRCUIT DES       41850    LOS ANGELES   3/10.96
  1. Gather the data. The following table lists the data elements and two occurrences of the data aggregate.
    Table 4. Instructor schedules data elements
    Data elements Occurrence 1 Occurrence 2
    INSTR BENSON, R. J. MORRIS, S. R.
    CRSNAME multiple multiple
    CRSCODE multiple multiple
    EDCNTR multiple multiple
    DATE(START) multiple multiple
  2. Analyze the data relationships. First, group the data elements into a conceptual data structure.
    1. Separate repeating data elements in one occurrence of the data aggregate by shifting data elements to a lower level as shown in the following figure.
      Figure 6. Instructor schedules step 1
      Begin figure description. Root segment includes INSTR; dependent segment includes CRSNAME, CRSCODE, EDCNTR, and DATE. End figure description.
    2. Separate duplicate values in two occurrences of the data aggregate by shifting data elements to a higher level as shown in the following figure.

      In this example, CRSNAME and CRSCODE can be duplicated for one instructor or for many instructors, for example, 41837 for Benson and 41850 for Morris and Reynolds.

      Figure 7. Instructor schedules step 2
      Begin figure description. Data aggregates: Instructor points to Course. Course points to Class. End figure description.
    3. Group data elements with their keys.

      All data elements are grouped with their controlling keys in the current data structure. No changes to the current data structure are required.

  3. Determine the mappings for the data aggregates.

    The mappings for this local view are: Instructor ◄────────►► Course Course ◄────────►► Class

    An analysis of data requirements is necessary to combine the requirements of the three examples presented in this topic and to design a hierarchic structure for the database based on these requirements.

    Related Reading: For more information on analyzing data requirements, see IMS Version 15.2 Database Administration.