Relationship Database Design

Superclass/Subclass Hierarchy

"O" is overlapping, meaning there could be overlapping between each subclass entities. (A manager could also be a salesperson) Besides, we can use "D" for disjoint subclasses.A single line between Employee and its subclasses implies an optinal participation (an employee may not belong to any subclass), while a double line implies a mandatory participation (an employee must be in one or more subclasses).An U-like symbol identify subclasses by pointing to them. (the entity the bottom of "U" points to is a subclass)

Logical Model

Schema represents abstract (strong and weak) entities and associated attributes and necessary relationships between entities.A schema looks like this: STUDENT( Stu_num, Stu_Lname, Stu_Fname, program_num@, age, gender) (PK "Stu_num" should be underscored. I use italic instead) Data dictionary contains metadata of attributes, with which you could understand the what, where, how, how much, who, when questions about data.

Normal Form

A good logical model should follow normal form of database.

UNF Contains repeating values.

1NF Each cell in the table contains only one value (so that the table can be represented in a relational database).

2NF Satisfies 1NF; No partial dependencies. Meaning: A non-primary-key attribute cannot be dependent on part of primary-key.

For example, in R( A B C D), if (A+B) -> C, D, (B) -> D, D is partially dependent on primary key (A+B), thus not satisfying 2NF.

3NF Satisfies 2NF; No transitive dependencies. Meaning: A non-primary-key attribute cannot determine non-primary-key attributes. (But a non-primary-key attribute can determine a primary-key attribute)

BCNF Satisfies 3NF; No non-CK dependencies. Another way to say this: For all functional dependencies exist in the table, all their determinants are candidate keys. Meaning: the second and third normal forms normalize non-prime attributes, and BCNF normalizes relationships between prime attributes.

For example (a model in 3NF but not in BCNF) In R( A B C D), there are (A+B) -> C, D; (A+C) -> B, D; (C) -> B. With a non-primary-key attribute C determines primary-key attribute B, the model does not satisfy BCNF.

4NF Satisfies BCNF; No multi-value attributes. 作者：jin2017 链接：https://www.jianshu.com/p/88dfcbc48106 来源：简书 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。