06Logical design

Logical Design

Outline

Methodology
Transformations
Example

A logical design transforms a conceptual schema, into a set of relational schemas. Transformation of a conceptual schema is controlled by a collection of simple rules replacing the components of a simplified class diagram with linear specifications relational schemas, i.e. schema-name( attribute, attribute, ... ) primary key = ... foreign key = ...

Application of the transformation rules is divided into few steps each one replacing one type of the components of a conceptual schema.

A logical design is performed in the following steps:

First, the multivalued attributes are replaced with the classes of objects, and depending on the semantics of multivalued attributes, either with one to many, or with many to many associations.

In the second step, the association classes and link attributes, are replaced with the triples that consists of: one to many association, class-of-objects, and many-to-one-association.

In the third step, many-to-many associations, are replaced with the triples that consists of: one to many association, class of objects, many to one association.

In the fourth step, the qualifications are replaced with one to many associations, and with composite identifiers in object classes on "many" side of one to many associations.

The remaining steps are listed on the next slide.

Methodology

The logical design transforms a conceptual schema into a set of relational schemas
The logical design is performed as a sequence of the following steps:
(1) The multivalued attributes are replaced with the classes of objects and depending on the semantics of multivalued attribute either with one-to-many or many-to-many associations
(2) The associaton classes and link attributes are replaced with the triples (one-to-many association:class-of-objects :many-to-one-association)
(3) Many-to-many associations are replaced with triples (one-to-many association:class-of-objects:many-to-one association)
(4) The qualifications are replaced with one-to-many associations and composite identifiers in object classes on "many" side of one-to-many associations

Continuation of transformation steps.

In the fifth step, the selected identifiers are copied from the classes of objects on "one" side of associations, to the classes of objects on "many" side of associations, and later on, are tagged with FKn, where an index "n" is used to distinguish between different foreign keys.

In the sixth step, the triples, that consists of: class of objects, one to one association, and class of objects, are merged into one class of objects. Then, one to one associations are removed.

In the seventh step, the superset, and/or subset, and/or association methods are used to transform the generalisations.

In the eighth step, the primary and candidate keys are created.

In the ninth step, the associations are removed.

In the tenth step, the relational schemas with referential integrity constraints are created.

Methodology

(5) The selected identifiers are copied from the classes of objects on "one" sides of association to the classes of objects on "many" side of the associationds and such identifierds are tagged with FKn tag (an index "n" is used to distinguish between different foreign keys)

(6) The triples (class-of-objects:one-to-one asociation:class of objects) are merged into one class of objects and one-to-one associations are removed
(7) The superset, subset and association methods are used to transform the generalizations
(8) The primary and candidate keys are created
(9) The associations are removed
(10) The relational schemas with the referential integrity constraints are created

Logical Design

We start from a case 1 where a multivalued attribute is transformed into into a class of objects and many-to-many association. The second case where a multivalued attribute is transformed into a class of objects and one-to-many-association is presented later.

Let us consider a class of objects: BOOK, and a multivalued attribute: author name. An attribute: author name in a class of objects: BOOK, is transformed in the following way.

An attribute: author name is promoted to a new class of objects: AUTHOR. Such transformation is commonly called as a "reification". Next, a single valued attribute: author name, is added as an identifier to a class: AUTHOR.

A multivalued attribute: author name is removed from a class of objects: BOOK. A many to many association: BOOK Is-written-by AUTHOR, is created between the classes of objects: BOOK and AUTHOR.

Multivalued attributes

Case 1: Transformation into a class of objects and many-to-many association

Multivalued attribute

A multivalued attribute author-name in a class of objects BOOK is transformed in the following way:
- An attribute author-name is promoted to a class of objects AUTHOR (such step is called as reification)
- A single valued attribute author-name is added as an identifier to a class AUTHOR
- A multivalued attribute author-name is removed from a class BOOK
- A many-to-many association Is-written-by is created between the classes BOOK and AUTHOR

An association BOOK Is-written-by AUTHOR is many-to-many because a book can be written by many authors and an author can write many books. Information about multiplicity of an association is obtained from semantics of the association. Note, that multivalued attribute provides only information about of a book having many authors. Information, whether an author can contribute to many books is not included in a multivalued attribute and it must be discovered from semantics of the association and our knowledge of the reality

The next slide presents a case where a reification provides a new association that is one-to-many.

Elimination of multivalued attributes needs additional information not included in a conceptual schema. It is why we need to look at the semantics/meaning of a multivalued attribute. It is impossible to correctly transform a multivalued attribute without knowing its semantics.

Multivalued attributes

Multivalued attribute

An association Is-written-by is many-to-many because a book can be written by many authors and an author can write many books

Multivalued attributes

Case 2: Transformation into a class of objects and one-to-many association

Multivalued attribute

A multivalued attribute phone-number in a class of objects PERSON is transformed in the following way:
- An attribute phone-number is promoted to a class of objects PHONE (such step is called as reification)
- A single valued attribute number is added as an identifier to a class PHONE
- A multivalued attribute phone-number is removed from a class PERSON
- A one-to-many association Has is created between the classes PERSON and PHONE

Multivalued attributes

Multivalued attribute

An association Has is one-to-many because a person can have many mobile phones and a mobile phone is owned by one person

In the second step, we eliminate association classes and many to many associations through the transformations into one to many and many to one associations. An association class becomes and ordinary class of objects involved in both one-to-many and many-to-one associations.

For example. we consider a many to many association: STUDENTS Enrols SUBJECT, represented by an association class: ENROLMENT. We assume an optimistic case, a student enrols in a subject only one time. the pessimistic case, when a subject is enrolled by the same student many times is considered later.

First, an association class: ENROLMENT, is transformed in the following way. Many to many association: STUDENTS Enrols SUBJECT, is removed. A new one-to-many association: STUDENT Performs ENROLMENT, is added between a class of objects: STUDENT, and a class of objects: ENROLMENT.

Next, a one-to-many association: SUBJECT is-involved-in ENROLMENT, is added between a class of objects: SUBJECT and a class of objects: ENROLMENT.

A qualification with an attribute: subject code (code), is added on a student side of association: STUDENT Performs ENROLMENT and a qualification with an attribute: student number (s#), is added on a subject side of an association: SUBJECT is-involved-in ENROLMENT. The qualifications are need to provide information about a derived identifier of a class ENROLMENT.

In a general case, every many-to-many association can be replaced with one-to-many association CLASS OF OBJECTS (that represents an association) and many-to-one association. A derived identifier of CLASS OF OBJECT is a pair of identifiers taken from "one' sides of both associations. In our example it is a pair of attributes (student number, subject code).

Association classes

Association class

An association class Enrols is transformed in the following way:
- Many-to-many association Enrols is removed
- A one-to-many association Performs is added between a class STUDENT and a class ENROLMENT
- A one-to-many association Is-involved-in is added between a class SUBJECT and a class ENROLMENT
- A qualification with an attribute code is added on a STUDENT side of association Performs and a qualification with an attribute s# is added on SUBJECT side of association Is-involved-in

A qualification with an attribute: subject code (code) of an association STUDENT Performs ENROLMENT contributes to an identifier that consists of: student number (s#) and subject code (code) in a class: ENROLMENT. It is because the association is qualified on STUDENT class side and an attribute student number (s#) together with an attributes used in the qualification forms an identifier of a class ENROLMENt on the unqualified side of the association.

A qualification with an attribute: student number (s#) of an association: SUBJECT Is-involved-in ENROLMENT, also contributes to an identifier that consists of: student number (s#), and subject code (code), in a class ENROLMENT. It is because the association is qualified on SUBJECT class side and an attribute subject code (code) together with an attributes used in the qualification forms an identifier of a class ENROLMENt on the unqualified side of the association.

Association classes

Association class

A qualification with an attribute code of an association Performs contributes to an identifier (s#, code) of a class ENROLMENT
A qualification with an attribute s# of an association Is-involved-in also contributes to an identifier (s#, code) of a class ENROLMENT

Now, we assume a pessimistic case, where a student must enrol a subject many times. In such a case, an association: STUDENT Enrols SUBJECT, must be qualified in the middle by an attribute: enrolment date (edate). This is, because an identifier of an association class consists of three attributes: students number (s#), subject code(code), and enrolment date (edate).

A many to many association: STUDENT Enrols SUBJECT, and association class: ENROLS, qualified with an attribute: enrolment date (e ate) is transformed in the following way.

Many to many association: STUDENT Enrols SUBJECT.

A one-to-many association: STUDENT Performs ENROLMENT, is added between a class: STUDENT and a class: ENROLMENT.

A one-to-many association: SUBJECT Is-involved-in ENROLMENT, is added between a class: SUBJECT and a class: ENROLMENT.

A qualification with the attributes: subject code (code), and enrolment date (e date), is added on STUDENT side of an association: STUDENT Performs ENROLMENT.

A qualification with the attributes: student number (s#), and enrolment date (e date), is added on a SUBJECT side of an association: SUBJECT Is-involved-in ENROLMENT.

In a general case any many-to-many association is equivalent to to one-to-many association a class objects representing an association class and many-to-one association on the other side. If many-to-many association is not represented by an association class it is always possible to add an empty association class that has no attributes. Such association class is later on used as a class of objects in-between one-to-many and many-to-one associations , that replace one-to-many association.

Qualified association classess

Association class

An association class Enrols qualified with an attribute edate is transformed in the following way:
- Many-to-many association Enrols is removed
- A one-to-many association Performs is added between a class STUDENT and a class ENROLMENT
- A one-to-many association Is-involved-in is added between a class SUBJECT and a class ENROLMENT
- A qualification with the attributes (code,edate) is added on STUDENT side of association Performs and a qualification with the attributes (s#,edate) is added on SUBJECT side of association Is-involved-in

Qualified association classes

An attribute edate is removed from a class ENROLMENT

Association class

Link attributes

Link attribute

A link attribute edate is transformed in the following way:
- A link attribute edate is promoted to an association class ENROLMENT
- An association class ENROLMENT is transformed in a way explained earlier

Next, we look at many to many associations, without association classes.

A many-to-many association: SUPPLIER Supplies PART, is transformed in the following way. First, we create a new class of objects: SHIPMENT. The new class of objects has no attributes.

Next, one-to-many association: SUPPLIER Performs SHIPMENT, is added between a class of objects: SUPPLIER and a class of objects: SHIPMENT.

The second one-to-many association: PART Is-involved-in Shipment, is added between a class of objects PART and a class of objects: SHIPMENT.

Next, we add a qualification with an attribute: part number (p#), to a SUPPLIER side of an association: SUPPLIER Performs SHIPMENT.

Finally, we add a qualification with an attribute: supplier number (s#), to a PART side of an association: PART Is-involved-in SHIPMENT.

Many-to-many associations

M:N associations

A many-to-many association Supplies is transformed in the following way:
- A new class of objects SHIPMENT is created
- A one-to-many association Performs is added between a class SUPPLIER and a class SHIPMENT
- A one-to-many association Is-involved-in is added between a class PART and a class SHIPMENT
- A qualification with the attributes (p#) is added on SUPPLIER side of association Performs and a qualification with the attributes (s#) is added on PART side of association Is-involved-in

Many-to-many associations

M:N associations

A qualification with an attribute p# of an association Performs contributes to an identifier (p#, s#) of a class SHIPMENT
A qualification with an attribute s# of an association Is-involved-in also contributes to an identifier (p#, s#) of a class SHIPMENT

In step four of the logical design, we replace the qualifications.

In the present slide, a qualification with an attribute: room number (room#) is eliminated in the following way.

The attributes: building number (bldg#), and room number (room#), are copied to a class of objects: ROOM.

A pair of attributes that consists of building number (bldg#), and room number (room#), is tagged with IDn in a class of objects room where "n" depends on the total number of identifiers already determined in a class. Usually "n" is equal to 1 because its is why qualifications has been used: to determine an identifier fro a class that has no identification system.

In this way, a pair (bldg#, room#) becomes an identifier in a class of objects: ROOM.

An attribute: building number (bldg#), is tagged with FKn to denote a foreign key referencing an identifier building number (bldg#) in a class of objects: BUILDING.

A multiplicity of an association: BUILDING Has ROOM, on a ROOM side of qualified association is changed to * or 1..*.

It is because buildings have zero or more rooms.

At the end, a qualification with an attribute: room number (room#) is removed.

In a general case a qualification is removed through application of its definition. A qualification provides information'

about an identifier of a class, that has no own identification system, for example a class ROOM. Hence elimination of qualification must create an identifier for a class that has no own identification system. An identifier is created from the attributes used in qualification and from an identifier of a class located on a qualified side of association.

In our case, it is a class BUILDING with an identifier bldg#.

An identifier from a class on a qualified side of association (e.g. bldg#) becomes a future foreign key in a relational table ROOM. It is why it is tagged with FKn.

Qualifications

A qualification with the attribute room# is transformed in the following way:
- The attributes (bldg#, room#) are copied to a class ROOM
- A pair of attributes (bldg#, room#) is tagged with IDn in a class ROOM (it becomes an identifier)
- An attribute bldg# is tagged with FKn to denote a foreign key referencing an identifier bldg# in a class BUILDING
- A multiplicity on a class ROOM side of qualified association has is changed to * or 1..*
- A qualification with an attribute room# is removed

Qualifications

Qualification

The attributes (bldg#, room#) form an identifier of a class ROOM
A attribute bldg# becomes a foreign key referencing an attribute bldg# in a class BUILDING

A double qualification is transformed in a similar way.

For example, a double qualification with a pair of attributes: subject code (code), and enrolment date (edate), on one side of association: STUDENT Performs ENROLMENT, with a pair of attributes student number (s#), and enrolment date (edate) on the other side of association: SUBJECT Is-involved-in ENROLMENT is transformed in the following way.

A pair of attributes, that consists of: subject code (code), and enrolment date (edate) and a pair of attributes that consists of: student number (s#), and enrolment date (edate) are copied to a class of objects: ENROLMENT.

It is the same transformation as before. The attributes used in qualification and an identifier of a class on a qualified side of association are copied on the other side of association,

Then, a triple of attributes, that consists of: subject code (code), student number (s#), and enrolment date (edate) is tagged with IDn in a class of objects: ENROLMENT. It means that such triple becomes and identifier in a class ENROLMENT.

An attribute: subject code (code), is tagged with FKn to denote a foreign key referencing an identifier: subject code (code), in a class of objects: SUBJECT.

An attribute: student number (s#) is tagged with FKm, where m is not necessarily equal to n, to denote a foreign key referencing an identifier: student number (s#), in a class of objects: STUDENT.

Both 0..1 multiplicities on the left and right side of a class of objects: ENROLMENT, are changed to * or to 1..* depending on whether the associations SUBJECT Is-involved-in ENROLMENT and STUDENT Performs ENROLMENT are mandatory or optional on ENROLMENT side. To decide what multiplicities should be sued we have to ask the question: "is it possible, that a student has no enrolments ?" and "is it possible, that a subject has no enrolments ?". If the answers to both questions are positive than we use an optional multiplicity * or 0..*.

Qualifications

A double qualification with the attributes (code, edate) and (s#, edate) is transformed in the following way:
- The attributes (code, edate) and the attributes (s#, edate) are copied to a class ENROLMENT
- A triple of attributes (code, s#, edate) is tagged with IDn in a class ENROLMENT (it becomes an identifier)
- An attribute code is tagged with FKn to denote a foreign key referencing an identifier code in a class SUBJECT
- An attribute s# is tagged with FKm (where n <>m) to denote a foreign key referencing an identifier s# in a class STUDENT
- Both 0..1 multiplicities on the left and right side of a class ENROLMENT are changed to * or 1..*

Qualifications

- Both qualifications with the attributes (code, edate) and (s#, edate) are removed

In a step five of logical design, the foreign keys are created by copying the identifiers from one side of association, to a many side of association. It is a moment when we implement one-to-many associations in a relational data model.

For example, an identifier that consists of a pair of attributes: building number (bldg#), and room number (room#) is copied from "one" side of an association: EQUIPMENT Is-located- in ROOM, to a class of objects: EQUIPMENT, located on a many side of the association.

A pair of attributes: building number (bldg#), and room number (room#) is tagged with FKn in a class of objects: EQUIPMENT.

It becomes a composite foreign key referencing a pair of attributes: building number (bldg#) and room number (room#), that is an identifier in a class of objects ROOM.

Foreign keys

FKs

Foreign keys are created in the following way:
- An identifier (bldg#, room#) from "one" side of an association Is-located-in is copied to a class EQUIPMENT on "many" side of the association
- A pair of attributes (bldg#, room#) is tagged with FKn in a class EQUIPMENT (it becomes a composite foreign key) referencing (bldg#, room#) in a class ROOM

In the sixth step of logical design, one to one associations are transformed in the following way.

The object classes from both sides of one to one association are merged into a single class of objects.

For example a triple, that consists of the objects classes: CHAIRMAN, and SCHOOL, and an association: CHAIRMAN Is-head-of SCHOOL, is merged into one class of objects: SCHOOL. The attributes: employee number (emp#), first name, last name, and date of birth (dob), that come from an optional 0..1 side of the association: CHAIRMAN Is-head-of SCHOOL, are tagged with 0..1 tag as optional attributes.

An attribute like: name of school (name), that was an identifier of a class of objects on mandatory side, 1..1, of the association obtains a tag IDn.

A tag IDn means, that it is an identifier of a new class obtained from the merge of the classes CHAIRMAN and SCHOOL. It means that every instance of an object of the new class must have a value of an attribute school name (name). Later on IDn will be converted into PK, i.e. a primary key of a relational table implementing the new class of objects

An attribute like: employee number (emp#), that was an identifier of a class of objects on an optional side of association obtains a tag CKn. It is because it may happen that a school does not have a chairman due to an optional side of association on CHAIRMAN class side. Then a tag IDn is not appropriate because a value of an attribute employee number (emp#) maybe missing. In such a case and attribute employee number (emp#) can be at most a candidate key and not primary key.

Finally, one to one association is removed.

One-to-one associations

FKs

One-to-one associations are transformed in the following way:
- A triple (CHAIRMAN:Is-head-of:SCHOOL) is merged into one class of objects SCHOOL
- The attributes (emp#, first-name, last-name, dob) that come from an optional 0..1 side of association Is-head-of are tagged with [0..1] as optional attributes
- An attribute like name, that was an identifier of a class on mandatory side (1..1) of and association obtains a tag IDn
- An attribute like emp#, that was an identifier of a class on an optional side of association obtains a tag CKn
- One-to-one association is removed

One-to-one associations

FKs

Note, that a candidate key emp# may have no value, it means, that it can be NULL

In the seventh step of logical design, the generalisation hierarchies are transformed with the superset, subset and association methods.

We start from a superset method.

A superset method transforms entire generalisation hierarchy into a single class of objects by moving all attributes to the immediate higher level class of objects, changing multiplicities of attributes, and adding categorisation attributes.

All attributes from the classes of objects at the lower levels of generalisation hierarchy are copied to the topmost level in the hierarchy and then such attributes become the optional attributes marked with 0..1 tag there.

For example, the attributes: project and thesis, are copied from the classes of objects: UNDERGRADUATE STUDENT and POSTGRADUATE STUDENT, to a class of objects: STUDENT.

A categorisation attribute, that determines a subtype of a superclass is added to a superclass.

For example, an attribute: type of student, is added to a class of objects: STUDENT.

Such attribute is necessary to distinguish between postgraduate and undergraduate students.

Generalizations - superset method

Generalization-superset

A superset method transforms entire generalization hierarchy into a single class of objects in the following way:
- All attributes from the classes of objects at the lowest level of generalization hierarchy are copied to an immediate higher level and become optional attributes ([0..1] tag) there, e.g. the attributes project and thesis are copied from the classes UNDERGRADUATE-STUDENT and POSTGRADUATE-STUDENT to a class STUDENT
- An attribute type-of-superclass is added to a superclass, e.g. and attribute type-of-students is added to a class STUDENT

After the attributes from lower levels of generalisation are moved to their parent class all classes below the parent class are removed from a conceptual schema.

The process is repeated until only the topmost class is left. It is important to remember that each time a set of attributes from subclasses is moved to a superclass we must remember about categorisation attribute. If a generalisation hierarchy has several levels then categorisation attributes from the lower levels must be moved to the higher levels. For example, of a class UNDERGRADUATE STUDENT has subclasses IT STUDENT and CS STUDENT then first elimination of the lowest level in the hierarchy contributes to a categorisation attribute it-or-cs. Then, elimination of the subclasses at the higher level contributes to a categorisation attribute pg-or-ug. Please remember that an attribute it-or-cs must be moved to the topmost class STUDENT as an optional attribute. i.e. it must be tagged with [0..1]. It is because we do not distinguish between it and cs students at postgraduate level.

Generalizations - superset method

- All classes at the lowest level are removed
- The steps above are repeated until only one class of objects is left

Next, to eliminate a generalisation hierarchy, we look at a subset method.

A subset method transforms the entire generalisation hierarchy, into several classes of objects in the following way. All attributes from the classes of objects at the higher levels of generalisation hierarchy, are copied to the classes of objects at the lowest levels of generalisation hierarchy.

For example, the attributes student number (s#), first name, last name and date of birth (dob) are copied from a class of objects: STUDENT, to the classes of objects: POSTGRADUATE STUDENT, and UNDERGRADUATE STUDENT.

Copying the attributes from a superclasses to subclasses is nothing else as application of inheritance. If a postgraduate student is a student then all attributes and associations of a student are inherited by a postgraduate students accordingly to a rule postgraduate student ISA student.

Generalizations - subset method

Generalization-subset

A subset method transforms entire generalization hierarchy into a number of classes of objects in the following way:
- All attributes from the classes of objects at the higher levels of generalization hierarchy are copied to the classes of objects at the lowest levels of generalization hierarchy e.g. the attributes s# and first-name last-name, dob are copied from a class STUDENT to the classes POSTGRADUATE-STUDENT and UNDERGRADUATE-STUDENT

Generalizations - subset method

- All classes of objects except those at the lowest levels of generalization hierachy are removed, e.g. a class STUDENT is removed

Finally, to eliminate a generalisation hierarchy, we look at an association method.

An association method transforms the entire generalisation hierarchy, into several of classes of objects in the following way. One of the identifiers from a superclass of objects is copied to all subclasses of objects located one level below in a hierarchy.

For example, an identifier: student number (s#) is copied from a class of objects: STUDENT, to the classes of objects: UNDERGRADUATE STUDENT, and POSTGRADUATE STUDENT. An identifier that has been copied obtains a tag FKn in the subclasses.

The same procedure is repeated for all subclasses of objects at the same level in a generalisation hierarchy.

After application of association method the classes STUDENT, UNDERGRADUATE STUDENT, and POSTGRADUATE STUDENT change their semantics/meaning. A class STUDENT contains the objects that are the common descriptions of postgraduate and undergraduate students. A class UNDERGRADUATE STUDENT contains the objects that are the descriptions of undergraduate students only. A class POSTGRADUATE STUDENT contains the objects that are the descriptions of postgraduate students only.

A meaning of an association is Consists_of. For example STUDENT Consists-of UNDERGRADUATE STUDENT description or STUDENT Consists-of POSTGRADUATE STUDENT description.

Generalizations - association method

Generalization-partitioning

An association method transforms entire generalization hierarchy into a number of classes of objects in the following way:
- One of the identifiers from a superclass is copied to subclasses one level below a superclass, e.g. an attribute s# is copied from a class STUDENT to the classes UNDEGRADUATE-STUDENT and POSTGRADUATE-STUDENT
- The copied identifier obtains a tag FKn in the subclasses

Generalizations - association method

- A generalization level is removed from a diagram

In the eighth step of logical design, the primary keys and candidate keys, are created in the following way.

In each class, one of the identifiers, either a single attribute identifier, or a composite identifier, is tagged with PK tag to become a primary key.

For example, an attribute: student number (s#), in a class: STUDENT, is tagged with PK tag.

All other identifiers in the same class are tagged with CKn tags and they become the candidate keys.

For example, an attribute: social security number (ssno), is tagged with CK1 tag, and a triple of attributes: first name, last name, date of birth (dob), are all tagged with CK2 tags (this is because the triple of attributes forms one composite candidate key.)

After the replacement of ID tags with either Pkn or CKm the identifiers of classes of objects dispapper and in the next step it is possible to replace a class of objects with a relational table.

Primary keys and candidate keys

PK/CKs

primary keys and candidate keys are created in the following way:
- In each class one of the identifiers (identifier or composite identifier), e.g. s# attribute is tagged with PK tag
- All other identifiers like ssno, (first-name, last-name, dob) are tagged with Ckn tags

In the ninth step of logical design, we remove the associations from a conceptual schema.

In this step, the associations are eliminated, and FKn tags are extended with the reference clauses in the following way.

Each time, one to many association is removed, a respective, FKn tag is extended with a reference clause referring an attribute, or a group of attributes with a tag PK on the other side of the removed association.

For example, when an association: EQUIPMENT Is-located-in ROOM, is removed, a pair of attributes: building number (bldg#) and room number (room#) tagged with FK1, is extended with a clause: REFERENCES ROOM(blgd#, room#).

A REFERENCE clause is required by the specifications of the foreign key. It is mandatory to provide information about the structures of the foreign keys and about the referenced structures of the repsective primary keys.

Associations

In this step association are eliminated and FKn tags are extended with REFERENCE clauses in the following way:
- Each time "on-to-many" association is removed a respective FKn tag is extended with REFERENCE clause refering PKn on the other side of the removed association, for example when an association Is-located-in is removed FK1:(bldg#, room#) is extened with a clause REFERENCES ROOM(blgd#, room#)

In the final step of the logical design, the relational schemas are created in the following way.

A relational schema is created from each object class left, such that in each class the attributes tagged with PK become a primary key, the attributes tagged with CKn become candidate key, and the attributes tagged with FKn become a foreign key.

A class of objects: ROOM, is transformed into a relational schema: ROOM, that consists of the attributes: building number, (bldg#), room number (room#), and area.

A relational schema: ROOM, has one primary key, that consists of two attributes: building number (bldg#) and room number (room#).

A class of objects: EQUIPMENT, is transformed into a relational schema: EQUIPMENT, that consists of the attributes: serial number (serial num), name, installation date, building number (bldg#), room number (room#),

An attribute: serial number (serial num) becomes a primary key in a relational schema: EQUIPMENT.

A pair of attributes: building number (bldg#), room number (room#) becomes a single and a composite foreign key referencing a primary key: building number (bldg#), room number (room#), in a relational schema: ROOM.

Relational schemas

Relational Schemas

In the final step the relational schemas are created in the following way:
- A relational schema is created from each object class left such that in each class the attributes tagged with PKn become a primary key, the attributes tagged with CKn become a candidate key and attributes tagged with FKn become a foreign key

Logical Design

Outline

Methodology
Transformations
Example

A conceptual schema given in the present slide represents a simple database domain, that contains information about: parts, customers, orders submitted by customers, contents of each order, suppliers and parts shipped by suppliers.

We plan to apply the transformations of the logical design to convert a conceptual schema into a collection of relational schemas.

We start from the transformations of multivalued attributes. There is only one multivalued attribute: phone number (phone#), in a class of objects: SUPPLIER.

A transformation rule for multivalued attributes says: create a new class of objects that represents the values of a multivalued attribute, add a single valued attribute to describe a new class and create either one-to-many or many-to-many-association between a new class and a class earlier desccribed by a multivalued attribute.

Example

The following conceptual schema represents a simple database domain that contains information about parts, customers, orders submitted by customers, contents of each order, suppliers and parts shipped by suppliers

Example

First, we transform multivalued attributes

The following conceptual schema, is obtained after the transformations of multivalued attributes.

A new class of objects: phone, has been created.

The class of objects is described by an attribute: number, and the attribute is an identifier of the class, see ID1 tag.

An association SUPPLIER Has PHONE has been added to the design. The new association is on-to-many because a supplier may have many phones and a phone has only one owner.

In the next step, we transform the association classes and link attributes.

There is one association class: PART-SHIPPED-BY and there are no link attributes.

A transformation rule for association classes says: replace many-to-many association represented by an association class with the following structure: one-to-many association connected on many side to a class of object the same as an association class and many-to-one association connected on many side to a class of objects the same as an association class. Then use qualifications to determine an identifier of a new class in between one-to-many and many-to-one associations.

Example - multivalued attributes

The following conceptual schema is obtained after transformation of multivalued attributes

Multivalued attributes

Next, we transform association classes and link attributes

The following conceptual schema, is obtained after transformation of association classes.

Transformation of an association class: PART-SHIPPED-BY, created a new class of objects: PART-SHIPPED-BY and one-to-many association PART Shipped-in PART-SHIPPED-BY, and one-to-many association SUPPLIER Involved-in PART-SHIPPED-BY.

The qualifications determine an identifier of a class of objects: PART-SHIPPED-BY. A qualification of an association PART Shipped-in PART-SHIPPED-BY with the attributes shipment-date and sname on PART side of association means, that an identifier of a class PART-SHIPPED-BY consists of the attributes (p#, shipment-date, sname).

A qualification of an association SUPPLIER Involved-in PART-SHIPPED-BY with the attributes shipment-date and p# on SUPPLIER side of association means, that an identifier of a class PART-SHIPPED-BY consists of the attributes (p#, shipment-date, sname).

Next, we transform many to many associations.

A transformation rule for many-to-many associations says: create a new association class that represents many-to-many association and later on eliminate a new associations class. It means, that the transformation is almost the same as a transformation of association classes,

Example - Association classes and link attributes

The following conceptual schema is obtained after transformation of association classes

Multivalued attributes

Next, we transform many-to-many associations

The design does not have any many-to-many associations.

Next, we transform the qualifications.

There are three qualifications. An association: ORDER Consists-of LINEITEM, is qualified with an attribute: line number (line#) on a side of ORDER class of objects to provide information about an identifier in a class of objects: LINEITEM.

An association: PART Shipped-in PART-SHIPPED-BY, is qualified with the attributes: shipment-date and supplier name (sname) to provide information about an identifier of a class of objects: PART-SHIPPED-BY.

An association: SUPPLIER Involved-in PART-SHIPPED-BY, is qualified with the attributes: shipment-date and part number (p#) to provide information about an identifier of a class of objects: PART-SHIPPED-BY.

A transformation rule for qualifications says: copy the attributes used in a qualification and one of the identifiers of a class on a qualified side of association to a class on an unqualified side of association. Append ID tag to all attributes copied, i.e. create a composite identifier, that consists of the attributes used in qualification and identifier borrowed from a class on a qualified side of association. Append a tag FKn to an attributed borrowed from a class on a qualified side of association. FKn means, that later on, it is going to be a foreign key referencing a primary key.

Example - Many-to-many associations

The following conceptual schema is obtained after transformation of many-to-many-associations

Multivalued attributes

Next, we transform qualifications

The following conceptual schema is obtained after the transformations of qualifications.

Elimination of qualifications adds the attributes: order number (order#), and line number (line#), to a class of objects: LINEITEM. A pair of attributes: order number (order#), and line number (line#), becomes an identifier in a class of objects: LINEITEM.

An attribute: order number (order#), becomes a foreign key referencing the future primary key in the future relational schema: ORDER.

A class of objects: PART-SHIPPED-BY, obtains an identifier that consists of the attributes: supplier name (sname), part number (p#), and shipment -ate.

An attribute: supplier name (sname) becomes a foreign key referencing the future primary key in the future relational table: SUPPLIER.

An attribute: part number (p#) becomes a foreign key referencing the future primary key in the future relational table: PART.

A transformation rule for implementation of one-to-many association says: copy the identifiers from one side to many side of one-to-many association.

There are two one to many associations: CUSTOMER Submits ORDER, and LINEITEM Lists PART-shipped-by.

Example - Qualifications

The following conceptual schema is obtained after transformation of qualifications

Multivalued attributes

Next, we create foreign keys

A conceptual schema given in the present slide is created after migration of the foreign keys.

An attribute: phone number (phone), has been copied from a class of objects: CUSTOMER, to a class of objects: ORDER, and it has been tagged with FK1 to denote the future foreign key referencing a primary key in the future relational schema: CUSTOMER.

The attributes: supplier name (sname), part number (part#), and shipment date have been copied from class of objects: PART-SHIPPED-BY, to a class of objects: LINEITEM.

The copied attributes has been tagged with FK2 to denote the future foreign key, referencing a primary key in the future relational schema: PART-SHIPPED-BY.

In the next step we transform one-to-one associations.

A transformation rule fro one-to-one associations says: the classes of objects involved in one-to-one association are merged into a single class. One of identifier from a class of object on mandatory side of one-to-one association obtains a tag IDn. The attributes describing a class on optional sode of one-to-one association obtain a tag [0..1].

In the present design there is no one-to-one associations.

Example - Foreign keys

The following conceptual schema is obtained after creation of foreign keys

Multivalued attributes

Next, we transform one-to-one asociations

Example - One-to-one associations

The following conceptual schema is obtained after transformation of one-to-one associations

Multivalued attributes

Next, we transform generalizations

A transformation of generalisation hierarchy: FREQUENT CUSTOMER ISA CUSTOMER with an association method is performed in the following way.

We copy an identifier: phone number (phone) from a class of objects: CUSTOMER, to a class of objects: FREQUENT CUSTOMER.

Then, we tag with FK1 an attribute phone number (phone) in a class of objects: FREQUENT CUSTOMER, and we add a clause: REFERENCES CUSTOMER(PHONE) to denote the future foreign key referencing a primary key in the future relational schema: CUSTOMER.

Next, we remove a generalisation hierarchy.

In the next step of logical design, we create the primary keys and candidate keys.

The transformation rule for creating primary candidate keys says: All attributes tagged with the IDn tag from either primary key or candidate key. If one of such attributes is option then may contribute to a candidate key only. The attributes forming a primary kay are tagged with PK. The attributes forming candidate keys are tagged with CKn. A value of n depends on how many candidate keys can be created.

Example - Generalizations

The following conceptual schema is obtained after transformation of generalizations with an association method

Multivalued attributes

Next, we create primary keys and candidate keys

The following primary keys and candidate keys through transformation of identifiers and optional identifiers.

An attribute: phone number (number), in a class: CUSTOMER, becomes a primary key, and it is tagged with a tag PK.

An attribute: phone number (number), in a class: FREQUENT CUSTOMER, becomes a primary key, and it is tagged with a tag PK.

An FK tag of an attribute: phone number (number), in a class: FREQUENT CUSTOMER, is extended with a clause: REFERENCES CUSTOMER (PHONE).

An attribute: order number (order#) in a class: ORDER, is tagged with PK to denote a primary key in the future relational schema: ORDER.

The attributes: supplier name (sname), part number (p#), and shipment date are tagged with PK to denote a composite primary key in the future relational schema: PART SHIPPED BY.

An attribute: part number (p#) is tagged with PK to denote a primary key in the future relational schema: PART.

An attribute: supplier name (sname) is tagged with PK to denote a primary key in the future relational schema: SUPPLIER.

An attribute: phone number (number) has been tagged with PK to denote a primary key in the future relational schema: PHONE.

Next, we transform the associations.

A rule, that transforms the associations says: append a clause REFERENCES (list-of attribute-names) to the attributes tagged with FKn.

Example - Primary keys and Candidate keys

The following conceptual schema is obtained after creation of primary keys and candidate keys

Multivalued attributes

Next, we transform associations

A transformation of the associations contributes to the following modification with REFERENCES clause of the attributes tagged with FKn

An association: CUSTOMER Submits ORDER is replaced with a clause: REFERENCES CUSTOMER(phone), attached to a foreign key: phone number (phone).

An association: ORDER Consists-of LINEITEM is replaced with a clause: REFERENCES ORDER(order number) (order#), attached to a foreign key: order number (order#).

An association: PART Shipped-in part SHIPPED-BY is replaced with a clause: references PART(part number) (p#), attached to a foreign key: part number (p#).

An association: SUPPLIER Involved-in PART-SHIPPED-BY, is replaced with a clause: REFERENCES SUPPLIER(supplier name) (s name), attached to a foreign key: supplier name (s name).

An association: SUPPLIER Has PHONE, has been replaced with a clause: REFERENCES SUPPLIER(supplier name) (s name), attached to a foreign key: supplier name (s name).

An association: LINEITEM Lists PART-SHIPPED-BY, has been replaced with a clause: REFERENCES PART-SHIPPED-BY(supplier name) (s name), part number (p#), and shipment date, attached to a composite foreign key, that consists of: supplier name (s name), part number (p#) and shipment date.

Finally, we create the relational schemas.

Example - Associations

The following conceptual schema is obtained after transformation of associations

Multivalued attributes

Next, we create relational schemas

At the end we obtain the following collection of relational schemas.

A relational schema: CUSTOMER, that consists of the attributes customer name (name), phone number (phone) and address.

An attribute: phone number (phone), is a primary key in a relational schema CUSTOMER.

A relational schema: FREQUENT-CUSTOMER, that consists of the attributes: phone number (phone) and points.

An attribute: phone number (phone), is a primary key in a relational schema: FREQUENT-CUSTOMER.

An attribute: phone number (phone) is a foreign key in a relational schema: FREQUENT-CUSTOMER, and it references a primary key: phone number (phone) in a relational schema: CUSTOMER.

A relational schema: ORDER, that consists of the attributes: order number (order#), order date (o date) and phone number (phone).

An attribute: order number (order#), is a primary key in a relational schema: ORDER.

An attribute phone number (phone), is a foreign key in a relational schema: ORDER, and it references and a primary key: phone number (phone), in a relational schema: CUSTOMER.

A relational schema: SUPPLIER that consists of the attributes: supplier name (s name) and fax number (fax).

An attribute: supplier name (s name), is a primary key in a relational schema: SUPPLIER.

A relational schema: PHONE, that consists of the attributes: phone number (number) and supplier name (s name).

An attribute: phone number (number), is a primary key in a relational schema: PHONE.

An attribute supplier name (s name), is a foreign key in a relational schema: PHIONE and it references a primary key: supplier name (sname) in a relational schema SUPPLIER

Example - Relational schemas (1)

At the end we obtain the following collection of relational schemas:

CUSTOMER(name, phone, address)
PRIMARY KEY = (phone)

FREQUENT-CUSTOMER(phone, points)
PRIMARY KEY = (phone)
FOREIGN KEY = (phone) REFERENCES CUSTOMER(phone)

ORDERS(order#, odate, phone)
PRIMARY KEY = (order#)
FOREIGN KEY = (phone) REFERENCES CUSTOMER(phone)

SUPPLIER(sname, fax)
PRIMARY KEY = (sname)

PHONE(number, sname)
PRIMARY KEY = (number)
FOREIGN KEY = (sname) REFERENCES SUPPLIER(sname)

A relational schema: PART, that consists of the attributes: part number (p#), part name (name) and price.

An attribute: part number (p#) is a primary key in a relational schema: PART.

A relational schema: PART-SHIPPED-BY, that consists of the attributes: supplier name (s name), part number (p#) and shipment date.

A triple of attributes: supplier name (s name), part number (p#), and shipment date, is a primary key in a relational schema: PART-SHIPPED-BY.

An attribute: supplier name (s name), is a foreign key in a relational schema: PART-SHIPPED-BY, and it references a primary key: supplier name (s name) in a relational schema: SUPPLIER.

An attribute: part number, is a foreign key in a relational schema: PART-SHIPPED-BY, and it references a primary key: part number (p#), in a relational schema: PART.

A relational schema: LINEITEM, that consists of the attributes: order number (order#), line number (line#), supplier name (s name), part number (p#) and shipment date.

A pair of attributes: order number (order#), and line number (line#), is a primary key in a relational schema: LINEITEM.

An attribute: order number (order#), is a foreign key in a relational schema: LINEITEM, and it references a primary key: order number (order#), in a relational schema: ORDER.

A triple of attributes: supplier name (s name), part number (p#), and shipment date, is a foreign key in a relational schema: LINEITEM, and it references a primary key supplier name (s name), part number (p#) and shipment date in a relational schema: PART-SHIPPED-BY.

Example - Relational schemas (2)

At the end we obtain the following collection of relational schemas:

	  
PART(p#, name, price)
PRIMARY KEY = (p#)

	  
PART-SHIPPED-BY(sname, p#, shipment-date)
PRIMARY KEY = (sname, p#, shipment-date)
FOREIGN KEY1 = (sname) REFERENCES SUPPLIER(sname)
FOREIGN KEY2 = (p#) REFERENCES PART(p#)

	  
LINEITEM(order#, line#, sname, p#, shipment-date)
PRIMARY KEY = (order#, line#)
FOREIGN KEY1 = (order#) REFERENCES ORDER(order#)
FOREIGN KEY2 = (sname, p#, shipment-date)
                              REFERENCES PART-SHIPPED-BY(sname, p#, shipment-date)

References

C. Coronel, S. Morris, A. Basta, M. Zgola, Data Management and Security, Chapter 2 Cengage Compose eBook, 2018, eBook: Data Management and Security, 1st Edition
T. Connoly, C. Begg, Database Systems, A Practical Approach to Design, Implementation, and Management, Chapter 17 Methodology - Logical Database Design for the Relational Model, Pearson Education Ltd, 2015