RelationshipDatabaseDesign

Content

• Conceptual model - ERD
• Logical model - schema, data dictionary, attributes mapping, ...
• Physical model

E-R Diagram (Conceptual Model)

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.

成都創(chuàng)新互聯(lián)是一家集網(wǎng)站建設(shè),嘉蔭企業(yè)網(wǎng)站建設(shè),嘉蔭品牌網(wǎng)站建設(shè),網(wǎng)站定制,嘉蔭網(wǎng)站建設(shè)報(bào)價(jià),網(wǎng)絡(luò)營(yíng)銷,網(wǎng)絡(luò)優(yōu)化,嘉蔭網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強(qiáng)企業(yè)競(jìng)爭(zhēng)力?？沙浞譂M足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時(shí)我們時(shí)刻保持專業(yè)、時(shí)尚、前沿，時(shí)刻以成就客戶成長(zhǎng)自我，堅(jiān)持不斷學(xué)習(xí)、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實(shí)用型網(wǎng)站。

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)

Schemarepresents 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 dictionarycontains metadata of attributes, with which you could understand the what, where, how, how much, who, when questions about data.

A good logical model should follow normal formof 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 BC 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 BC 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.