温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

Relationship Database Design

发布时间:2020-08-12 16:40:05 来源:ITPUB博客 阅读:142 作者:pentium 栏目:关系型数据库

Content

  • Conceptual model - ERD
  • Logical model - schema, data dictionary, attributes mapping, ...
  • Physical model
E-R Diagram (Conceptual Model)
Relationship Database Design
Relationship Database Design
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
来源:简书
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI