1

I was reading the book "Database Design" by Teorey, Simison et al. At a certain point it explains how to map an entity with a multivalued attribute into a relational table. The example is the following, where the multivalued attribute is hobby.

 Employee(Employee_ID(PK),name,surname,hobby)

which leads to two tables

 Employee(Employee_ID(PK),name,surname)
 Hobby(Employee_ID(PK),hobby(PK))

The book says more or less that "given an entity E with a primary key p, a multivalued attribute E attached to an ER diagram is mapped to a a table of its own with a primary key composed of p and the attribute value(s) a". Is this a general rule ? Conisder the follwing relation with a multivalued attribute book.

 Author(Author_ID(PK),name,book)

Wouldn't it be enough to create the follwing two tables where the PK of book is simply Book_ID and Author_ID is a FK without becoming part of the Book's PK ?

 Author(Author_ID(PK),name)
 Book(Book_ID,book,Author_id(FK))
tagomago
  • 85
  • 1
  • 11

2 Answers2

0

You're right; you don't have to use a composite primary key in Hobby or in Book, though you certainly could if you wanted to. The accepted answer to this question has a good overview of when you might want to use one over the other.

As an aside, if you were to actually implement your example, you'd probably want three tables: Author, Book, and an associative entity between them, since an author may write many books and a book may have many authors.

Community
  • 1
  • 1
Joe Farrell
  • 3,502
  • 1
  • 15
  • 25
  • 1
    But what if different Employees have the same hobby ? If only the hobby name is used as PK this would lead to duplicated keys. I have seen this hobby example in other places too, like here http://www.tomjewett.com/dbdesign/dbdesign.php?page=hobbies.php – tagomago May 13 '14 at 05:02
  • I'm not sure how Teorey deals with this, but I think it's useful to distinguish between a "multivalued attribute" and a "many to many relationship". I also think it's useful to distinguish between ER modeling and relational modeling. In relational modeling, FKs are the means of implementing relationships. In ER modeling, relationships are indicated, but not implemented. – Walter Mitty May 13 '14 at 11:37
  • @AlfioCastorina: You're correct that in the two-table structure you posed in your question, you can't use the hobby name alone as the primary key since it might be repeated for different employees. But, as with your Book/Author example, you could introduce another field to act as the primary key, like Hobby_ID. This is called a [surrogate key](http://en.wikipedia.org/wiki/Surrogate_key), a value whose only purpose is to uniquely identify a hobby; it doesn't tell you anything about the hobby itself. – Joe Farrell May 13 '14 at 13:16
  • Obviously if only the name hobby is used as a key a problem arises because I could have something like (001,fishing), (002,fishing) but if both values are used as key everything is ok. In a certain way the composed key acts like the attributes of an associative table in a many to many relationship. – tagomago May 13 '14 at 13:18
  • @JoeFarrel Wouldn't the surrogate key lead to the same problem ??? Different employess can obviously have the same hobby (i.e. 03) , so I'll have something like (01,03), (02,03). – tagomago May 13 '14 at 13:24
  • If you used a surrogate key, your table would have three columns: Hobby_ID, which is your primary key; Employee_ID, which is a foreign key to the Employee table; and Hobby, which is a description of the hobby. So if you have two employees with the same hobby, the records would look something like (Hobby_ID=1, Employee_ID=1, Hobby='Fishing') and (Hobby_ID=2, Employee_ID=2, Hobby='Fishing'). Hobby_ID will typically be either an integer set up to increment automatically or a GUID. Either way, the values are always unique. – Joe Farrell May 13 '14 at 13:31
  • Hmmmm, ok, but doesn't look very clean imo. This would lead to a lot of redundancy, right ? I'll have, at least, a different Hobby_ID for each employee ? – tagomago May 13 '14 at 13:42
  • You'd have a different Hobby_ID for every employee/hobby combination. It does make the table larger; now you've got three columns where before you had two. It's not necessarily better or worse than the two-column implementation; which is better will depend on your specific needs. I'd refer you again to that other question I linked in my answer for an overview of when you might prefer one implementation or the other. – Joe Farrell May 13 '14 at 13:48
0

Wouldn't it be enough to create the follwing two tables where the PK of book is simply Book_ID and Author_ID is a FK without becoming part of the Book's PK ?

You are wrong that it is necessarily enough to have candidate key {book_id} because that is only so if the book sets of authors don't overlap. If they do overlap you need {book_id, author_id}.

Your book is wrong though that the new PK is necessarily a combination of the two columns. (Similarly if the hobbies of employees don't overlap then hobby_id is a candidate key of Hobby.) So the book's column transformation is correct, but you have to determine the candidate keys of each new table on its own.

See this answer re relationalizing designs.

Community
  • 1
  • 1
philipxy
  • 14,867
  • 6
  • 39
  • 83