Table of Contents Link to heading

• What Logical Design Produces
• Translation Rule: Classes
• Translation Rule: Associations and Multiplicities
  • One-to-One (1:1)
  • One-to-Many (1:*) and Many-to-One (*:1)
  • Many-to-Many (*:*)
• Translation Rule: Recursive Relationships
• Translation Rule: Multivalued Attributes
• Translation Rule: Inheritance
  • Vertical Inheritance
  • Horizontal Inheritance
  • Choosing Between Vertical and Horizontal

What Logical Design Produces Link to heading

Logical design is the bridge between conceptual modelling and physical implementation. The input is a validated UML diagram; the output is a set of relational schemas — written descriptions of every table, its attributes, its Primary Key, its Candidate Keys, and its Foreign Key references.

A relational schema uses this notation:

RelationName(attribute1, attribute2, attribute3)
PK(attribute1)
CK(attribute2)
FK(attribute3) ~> OtherRelation(otherAttribute)

This stage also resolves any remaining PK decisions — choosing between Candidate Keys where multiple exist. Refer to the Primary Key selection rules for guidance.

Logical design can be performed manually (as covered here) or assisted by tooling: MS SQL Server Diagram Designer, Lucidchart, DBDesigner Fork, and ArgoUML all support UML-to-schema translation.

Translation Rule: Classes Link to heading

Every UML Class becomes a relation of the same name. The Primary Key defined in the class becomes the relation’s PK.

Example — given this association diagram:

Student(studentID, emailID, studentName)
PK(studentID)
CK(emailID)

Course(courseID, courseName)
PK(courseID)
CK(courseName)

Each class is translated independently first. Associations between classes are handled in the next step.

Translation Rule: Associations and Multiplicities Link to heading

The multiplicity on an association line determines where Foreign Keys are placed and whether a junction table is needed.

One-to-One (1:1) Link to heading

In a 1:1 relationship, either side can hold the FK. The convention is: if one side has a minimum cardinality of 0 (optional participation), that side holds the FK — this way, a NULL FK is acceptable without creating an orphaned mandatory record.

Example:

Person(personID, personName, dateOfBirth)
PK(personID)

AustralianPassport(passportNo, dateIssued, dateExpired, personID)
PK(passportNo)
FK(personID) ~> Person(personID)

AustralianPassport holds the FK because not every Person has an Australian passport — the relationship is optional on the Person side. A Person can exist without a passport, but a passport cannot exist without a person.

One-to-Many (1:*) and Many-to-One (*:1) Link to heading

The FK goes on the “many” side — place the PK from the “one” side into the “many” side as a FK.

Example:

Course(courseID, courseName)
PK(courseID)

Tutorial(classNo, location, courseID)
PK(classNo)
FK(courseID) ~> Course(courseID)

A Course has many Tutorial instances; each Tutorial belongs to exactly one Course. The Tutorial relation holds courseID as a FK — this is the standard pattern for all one-to-many relationships.

Many-to-Many (*:*) Link to heading

A many-to-many relationship requires a junction table (also called an association table or bridge table). The junction table holds the PKs of both participating relations as FKs, and the combination of these FKs typically forms the junction table’s composite PK.

If the association has its own attributes (an Association Class in the conceptual model), those attributes are included in the junction table.

Example — including an Association Class with dateCommenced and mark:

Student(studentID, emailID, studentName)
PK(studentID)
CK(emailID)

Course(courseID, courseName)
PK(courseID)

Enrolment(studentID, courseID, dateCommenced, mark)
PK(studentID, courseID)
FK(studentID) ~> Student(studentID)
FK(courseID)  ~> Course(courseID)

The junction table Enrolment exists because the many-to-many relationship itself carries data (dateCommenced, mark) that belongs to the specific pairing of student and course — not to either entity independently.

Translation Rule: Recursive Relationships Link to heading

A recursive relationship (self association) produces a separate junction table whose two FK columns both reference the same parent relation — but represent different roles.

Example:

Employee(ID, name)
PK(ID)

Supervision(supervisorID, superviseeID)
PK(supervisorID, superviseeID)
FK(supervisorID) ~> Employee(ID)
FK(superviseeID) ~> Employee(ID)

Both supervisorID and superviseeID reference Employee(ID), but they represent different roles in the relationship. The column names (derived from the role names in the UML diagram) make the distinction explicit.

Translation Rule: Multivalued Attributes Link to heading

A multivalued attribute — one that can hold more than one value per entity instance — violates first normal form and must be resolved before the logical schema is finalised.

Example — if Student has a multivalued address attribute:

Student(studentID, emailID, studentName, address)

Resolution: move the multivalued attribute into a new table, linked back to the original by a FK (typically a Surrogate Key generated for the new table):

Student(studentID, emailID, studentName, addressID)
PK(studentID)
CK(emailID)
FK(addressID) ~> Address(addressID)

Address(addressID, street, suburb, postcode)
PK(addressID)

The Address table now holds one address per row. A student with multiple addresses has multiple rows in Address, each referencing the student’s addressID.

Translation Rule: Inheritance Link to heading

Inheritance hierarchies (Generalisation/Specialisation) can be translated using two strategies: vertical or horizontal. The choice depends on query patterns and the degree to which subclasses share common attributes.

Example hierarchy:

Vertical Inheritance Link to heading

In vertical inheritance, the superclass is translated into its own relation. Each subclass is also translated into its own relation, inheriting the superclass PK as both its own PK and as a FK referencing the superclass.

Person(personID, personName)
PK(personID)

Staff(personID, position)
PK(personID)
FK(personID) ~> Person(personID)

Student(personID, gpa)
PK(personID)
FK(personID) ~> Person(personID)

Contractor(personID)
PK(personID)
FK(personID) ~> Person(personID)

Characteristics:

• Superclass table exists and holds shared attributes
• Querying a complete entity (e.g., a staff member with their name) requires a JOIN between Person and Staff
• Supports optional participation — a Person can exist without being in any subclass
• Adding a new subclass requires only a new table; the superclass is unchanged
• Best when: subclasses have many distinct attributes; the superclass is queried independently; entities may belong to multiple subclasses (non-disjoint)

Horizontal Inheritance Link to heading

In horizontal inheritance, no superclass table is created. Each subclass is translated into its own complete relation, inheriting (duplicating) the superclass attributes directly.

Staff(personID, personName, position)
PK(personID)

Student(personID, personName, gpa)
PK(personID)

Contractor(personID, personName)
PK(personID)

Characteristics:

• No superclass table — shared attributes are replicated into every subclass
• Querying a specific subclass requires no JOINs — all data is in one table
• Adding a new shared attribute requires updating every subclass table
• Cannot represent a person who is not in any subclass (mandatory participation assumed)
• Best when: subclasses are always disjoint; all entities must belong to a subclass; query performance on individual subclasses is critical

Choosing Between Vertical and Horizontal Link to heading

Vertical inheritance is the default choice for most enterprise schemas. Horizontal inheritance is appropriate for simple, disjoint hierarchies where query performance on subclass data is the primary concern.