Table of Contents Link to heading

• Introduction
• Logical Design
• Classes
• Associations - Multiplicities
• Recursive Relationship or Self Association
• Multivalued Attributes
• Inheritance
  • Vertical Inheritance
  • Horizontal Inheritance

Introduction Link to heading

After the conceptual design, the next phase is to create functional relational schemas based on the conceptual design.

• A written description of how the relational database will be implemented.
• This includes deciding which CK(s) will become the PK.

Logical Design Link to heading

The logical design of the database is the UML translation process.

UML can often be translated automatically into relational schemas, using:

• MS-SQL Diagrams feature
• Lucid chart
• DBDesigner Fork
• ArgoUML

In this post, we will explore how to translate UML into relational schemas manually following some basic rules.

Classes Link to heading

Every UML Class becomes its own relation of the same name.

The Primary Key (PK) becomes the relations PK.

For example:

Relational Schema 1:

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

Relational Schema 2:

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

Associations - Multiplicities Link to heading

There are three different types of associations (One-to-One, One-to-Many, Many-to-Many) which follow the rules as discussed in here.

1. One-to-One (1:1)

For example:

Relational Schema 1:

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

Relational Schema 2:

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

2. One-to-Many (1:*) and Many-to-One (*:1)

For example:

Relational Schema 1:

Course(courseID, courseName)
PK(courseID)

Relational Schema 2:

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

3. Many-to-Many (*:*) or (0:*) or (m:n)

For example:

Relational Schema 1:

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

Relational Schema 2:

Course(courseID, courseName)
PK(courseID)

Relational Schema 3:

Enrolment(StudentID, CourseID, dateCommenced, mark)
PK(StudentID, CourseID)
FK(StudentID) ~> Student(StudentID)
FK(CourseID) ~> Course(CourseID)

Recursive Relationship or Self Association Link to heading

For example:

Relational Schema 1:

Employee(ID, name)
PK(ID)

Relational Schema 2:

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

Multivalued Attributes Link to heading

For example:

Student(studentID, emailID, studentName, address)

The address attribute is a multivalued one that consists of three sub-components.

To resolve this issue, separate the multivalued attribute from the table and place it in a new one. Then create a Surrogate Key (act as a FK) in the original table and the new table to link them together.

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

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

Inheritance Link to heading

For example:

Vertical Inheritance Link to heading

• A superclass is constructed.
• FK is used.
• Each subclass is translated into its own relation with the PK(s) inherited from the superclass.

Relational Schema 1: Superclass

Person(personID, personName)
PK(personID)

Relational Schema 2: Subclasses

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)

Horizontal Inheritance Link to heading

• No superclass is constructed.
• No FK is used.
• Each subclass is translated into its own relation with all the attribute(s) inherited from the ‘superclass’.

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

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

Contractor(personID, personName)
PK(personID)