Transformation Rule(逻辑实现成关系表的规则）
Transformation Rule(逻辑实现成关系表的规则） word underlined is primary key
Rule 1.Each entity in an E-R diagram is mapped to a single table in a relational database;the table is name after the entity.The columns of the table represent all the single-valued simple attributes that are attached to the entity(possibly through a composite attribute,although a composite attribute itsself does not become a column of the table)。
Rule2: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 table is named after the plural multi-valued attribute.The columns of this new table are named after p and a,and rows of the table correspond to (p,a) value pairs,representing all pairings of attribute values of a associated with entity occurrences in E.The primary key attribute for this table is the set of columns in p and a.
EG: (here p = = eid,a == hobby)
employes(eid,staddress,city,states,zipcodes) (197,7 Beacon,Boston,MA,02102) (221,19 Brighton St,Boston,MA,02103)
hobbies(eid,hobby) (197,chess)(197,painting)(221,reading)
Rules3:N-N Relationships.When two entities E and F take part in a many-to-many binary relationship R,the relationship is mapped to a representative table T in the related relational database design.The table contains columns for all arrtibutes in the primary keys of both tables transformed from entities E and F,and this set of columns forms the primary key for the table T.
EG:(Employees －(1,N)－works_on－(0,N)－Projects －－－N-N(many-to-many) )
employees(eid,straddr,city,state,zipcode) －－woks_on(eid,prid,percent) －－projects(prid,proj_name,due_date)
Rules4:N-1 Relationships.When two entities E and F take part in a many-to-one binary relationship R,the rlationship will not be mapped to a table of its own in a relational database design.Instead,if we assume that the entity F has max-card(F,R)=1 and thus represents the “many“ side of the relationship,the relational table T transformed from the entity F should include colums constituting the primary key for the table transformed from the entity E;this is known as a foreign key in T.
EG:instructors(insid,lname,office_no,ext) －－ cours_sections(secid,insid,course,room,preriod).
Rules5:1-1 Relationships,optional Participation.Given two entities E and F that take part in a one-to-one binary relationship R,where participation is optional on either side,we wish to translate this situation into a relaional design -- Use foreign key on either or both sides.
Rules6:1-1 Relationships,Mandatory Participation on Both Sides. In the case of a one-to-one relationship with mandatory particpation on both sides,it is most appropriate to combine the tables for the two entities into one,and in this way avoid any foreign keys.
