The question you raise has to do with the definition of first normal form (1NF). Whether the answer directly involves functional dependencies depends in part on the definitions you accept. Wikipedia has a fairly simple article about 1NF.
title author year category
--
An Introduction to Database Systems CJ Date 2003 databases, modeling, storage, retrieval
If you look at the column "category" one way, it contains a single value. Depending on your dbms and your design, that value might be the string "databases, modeling, storage, retrieval", or it might be the array "{databases, modeling, storage, retrieval}".
If you look at the column "category" another way, it contains four values. Those values are the four strings "databases", "modeling", "storage", and "retrieval".
In database design, the solution is to use two tables. But I don't think you can decompose the "bad" table by projection (which CJ Date identifies as the decomposition operator), because projection doesn't split the content of a column into multiple rows. (Projection doesn't give you four rows from the single value "databases, modeling, storage, retrieval", which is what you need to do. "Join", the recomposition operator, doesn't yield a single value like "databases, modeling, storage, retrieval", either.)
The inability to decompose by projection suggests that the solution to this problem doesn't have to do with functional dependencies. The resulting table would have three attributes, {title, author, category}, the only key would also be {title, author, category}, and that table would be in 5NF.
Start using a ERD tool, or use paper to draw the relationships, those you have already written.
For example, "A tribe has many clans" and "one clan, which belongs to one tribe" means there's a 1:N relationship between Tribe and Clan, so there should be a tribe_id in Clan table (FK to Tribe).
Another issue would be the "a family has many Sims" and "a Sim belongs to at least 2 families". You'll have to use a Sim_Family joint table for the many-to-many association between the Sim and Family but the "at least 2" is hard (or impossible?) to enforce with DRI alone.
After you have drawn all relationships between tables (entities), check if there are circular paths in the Foreign Keys. It's hard to deal with that and there's usually a way to avoid it by minor alterations in the design.
Best Answer
I think if you look at your model and you consider the requirements that it doesn't handle (i.e. your questions) then you will find that you need to expand your model somewhat. Consider the following ERD:
(Note I use the James Martin crows foot notation which is a little more compact than what you have used but should be pretty simple to understand. The only slightly distinct feature is the use of an upper case "I" to indicate that a relationship is part of the identifier of the entity)
Here are some points that this ERD addresses:
A
BOOKwhich can be rented is a physical thing (assuming you're dealing with paper books). That means that you need to allow for having more than one copy. Therefore a book has a title, but it isn't the same thing as the title, so my model makesTITLEits own entity.Depending on whether you want a snapshot of which books are rented and to whom or whether you want an audit trail of which books have been rented to whom, you need to have either a 0,1-to-many or a many-to-many relationship to represent the rental status of each (physical) book.
Each
BOOKhas anEDITION. This doesn't have to be a special edition. It could be first edition, paperback, special edition, etc. etc.An
EDITIONwill have aTITLE. Every edition of the same book will have the same title so the relationship toTITLEbelongs at theEDITIONlevel.Similarly, every edition will be published by the same publisher (or group of publishers) whereas different editions may have different publishers. Since you can have more than one publisher, this needs to have a many-to-many relationship between
PUBLISHERandEDITION.A
TITLEcan have multiple authors, so a many-to-many relationship is required betweenTITLEandWRITER.