Home > Articles > Networking

  • Print
  • + Share This
This chapter is from the book

An Example

An LDAP schema can be designed that holds the same information as a relational database schema. Using the above recommendations for the same problems can be avoided in LDAP schema designs that are avoided using normalized relational database schemas. Consider the relational database schema in Table 4.1.7

Table 4.1 Sample relational database schema.

Table Name

Column Name

Syntax

Supplier

Supplier Number

Character String

Supplier

City Name*

Character String

City Status

City Name

Character String

City Status

Status

Integer

Part

Part Number

Character String

Part

Part Name

Character String

Part

Color

Character String

Part

Weight

Integer

Supplier Shipment

Supplier Number*

Character String

Supplier Shipment

Part Number*

Character String

Supplier Shipment

Quantity

Integer

This schema represents information about parts that can be ordered from various suppliers. Columns with an asterisk ("*") at the end of their names are nonkey columns that are primary keys of another table. These columns are called foreign keys. A company has offices in various cities and can order parts from various suppliers that have parts available. The different suppliers and their locations are represented in the Supplier table (Table 4.2). The status of each of the company's offices is represented in the City Status table (Table 4.3). Information about the different parts that can be ordered is represented in the Part table (Table 4.4). Finally, the current list of parts that have been ordered from various suppliers is represented in the Supplier Shipment table (Table 4.5). It is assumed that any part can be ordered from any supplier and that each supplier is located in only one city. This schema is already in third normal form.

Table 4.2 Data in the Supplier table

Supplier Number

City

S1

London

S2

Paris

S3

Paris

S4

London

S5

Athens


Table 4.3 Data in the City Status table

City

Status

Athens

30

London

20

Paris

10

Rome

50


Table 4.4 Data in the Part table

Part Number Part Name Color Weight
P1
Nut Red
12
P2
Bolt Green
17
P3
Screw Blue
17
P4
Screw Red
14
P5
Cam Blue
12
P6
Cog Red
19

Table 4.5 Data in the Supplier Shipment table

Supplier Number

Part Number

Quantity

S1

P1

300

S1

P2

200

S1

P3

400

S1

P4

200

S1

P5

100

S1

P6

100

S2

P1

300

S2

P2

400

S3

P2

200

S4

P2

200


Assume that the database tables have the information in Tables 4.2 through 4.5. Now, how can we create an LDAP schema to represent this same information? It is straightforward to create an object class that has the same attributes as each table. However, it is crucial to understand the foreign key relationships, as this will help in creating the DIT structure. Whenever possible, if a table holds a foreign key then LDAP entries that are in the object class that correspond to that table should be children of LDAP entries that correspond to the table in which the foreign key is a primary key. For example, the City column of the Supplier table is a foreign key. It is the primary key in the City Status table. Thus, Supplier entries in LDAP should be children of City Status entries. This leads to the first two object class definitions:

( NAME 'cityStatus' SUP top STRUCTURAL MUST (cityName $ status) ) 
( NAME 'supplier' SUP top STRUCTURAL MUST supplierNumber )

Notice that the supplier object class does not include the cityName attribute. This is because the attribute is implied by the parent-child relationship and can be retrieved from the DN of the supplier entry. Figure 4.4 illustrates the portion of the resulting DIT that is made up of entries from these two object classes.

Figure 4.4Figure 4.4 DIT for Supplier and City information.

In Figure 4.4, notice that each supplier entry is a child of the city entry in which it is located. Thus, the DNs for the supplier entries are

  • SupplierNumber = S1, cityName = London, o =acme.com

  • supplierNumber = S2, cityName = Paris, o = acme.com

  • supplierNumber = S3, cityName = Paris, o = acme.com

  • supplierNumber = S4, cityName = London, o = acme.com

  • supplierNumber = S5, cityName = Athens, o = acme.com

One of the properties of the relational database schema is that cities can be represented in the database, even when there are no known suppliers in that city. Notice that this representation preserves that property.

Notice too that the Part table has no foreign keys. Its entries can reside at the same level in the DIT as the City entries. But for clarity, they will all be placed in an organizationalUnit entry called ou = parts. Supplier Shipment entries are another matter. It has two foreign keys. One of the foreign keys is chosen as the parent entry in the DIT, while the other is used as a reference. In LDAP object classes references to other entries are always represented as DNs. This leads to the other two object class definitions:

( NAME 'part' SUP top STRUCTURAL MUST (partNumber $ partName $ color $ weight) ) 
NAME 'supplierShipment' SUP top STRUCTURAL MUST
partNumberDN $ quantity ) )

These object classes will use these attribute type definitions:

NAME 'cityName' SUP name )
NAME 'status' SYNTAX 1.3.6.1.4.1.1466.115.121.1.27 )
NAME 'supplierNumber' SUP name )
NAME 'partNumber' SUP name )
NAME 'partName' SUP name )
NAME 'color' SUP name )
NAME 'weight' SYNTAX 1.3.6.1.4.1.1466.115.121.1.27 )
NAME 'partNumberDN' SYNTAX 1.3.6.1.4.1.1466.115.121.1.12 )
NAME 'quantity' SYNTAX 1.3.6.1.4.1.1466.115.121.1.27 )

The syntaxes used in these attribute type definitions are defined in RFC 2252. The complete DIT that corresponds to the information in the relational database defined in Tables 4.2 through 4.5 is shown in Figure 4.5.

Figure 4.5Figure 4.5 Complete DIT for Supplier database.

Notice that this representation allows for only a single order from each supplier. If there is a requirement for multiple orders from a single supplier, the supplierShipment object class would have an order number attribute. As long as each order number was unique, this new attribute would allow for the creation of multiple orders from a single supplier. Since this was not a requirement in the relational database schema, it was not added as a requirement for the LDAP schema. In the DIT, each supplierShipment entry is a child of the corresponding supplier and has a pointer to the part number that was ordered from the supplier. Searching for various items in this DIT is straightforward. Consider the following problems and the resulting LDAP searches:

  • Find all orders for part number P3

    • Base Object = "o = acme.com," Scope = subtree, Filter = "(&(object-Class = supplierShipment) (partnumberDN = 'partnumber-P3, ou = parts, o = acme.com'))"

  • Find all orders for supplier number S2

    • Base Object = "supplierNumber = S2, cityName = Paris, o = acme .com," Scope = single-level, Filter = "objectClass = supplierShipment"

When new orders need to be entered into the system, then new supplier-Shipment entries need to be created in the DIT. For example, to add an order for 500 units of part number P4 from supplier number S3, the following Add operation should be initiated.

  • New entry name.—"partnumberDN = 'partnumber-P4, ou = parts, o = acme.com', supplierNumber = S3, cityName = Paris, o = acme.com"

  • Attributes:

    • ObjectClass—Top, supplierShipment

    • Quantity—500

Notice that the DN of the part number is used as part of the DN of the sup-plierShipment entry. While this makes for long DNs, it is not illegal. However, for convenience in the real world, order numbers would probably be used as discussed above.

  • + Share This
  • 🔖 Save To Your Account