Transformation Rules (Cont.)

---

Transformation Rule 2
Rule 2 is to convert a multivalued attribute in an E-R diagram into relational schema by creating a separate table for multivalued attribute along with the primary key of the base table:

• Given an entity E with primary identifier p, a multi-valued attribute a attached to E in an E-R diagram is mapped to a table of its own.


• The columns of this new table are named after p and a.


• The primary key attribute for this table is the set of columns in p and a.

Below shows the hobbies table and base table employee after transformation from an E-R diagram, where the double lines ═ represent a multi-valued attribute hobbies.

⇒

hobbies eid hobby 197 chess 197 painting 197 science fiction 221 reading 303 bicycling ... ... employee eid staddress city state zipcode 197 7 Beacon St Boston MA 02122 221 19 Brighton St Boston MA 02103 303 153 Mass Ave Cambridge MA 02123 ... ... ... ... ...

The order3 table in CAP database does not represent a relationship because

• Some triples of (cid,aid,pid) values occur more than once.
• The order table represents an entity in its own right.