Shulou(Shulou.com)06/01 Report--

ContentConceptual model-ERDLogical model-schema, data dictionary, attributes mapping,... Physical modelE-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.

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)

(compete for "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

(Acub)-> C, D

B)-> D

D is partially dependent on primary key (Abel 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

(Acub)-> C, D

(Atroc)-> 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.

Author: jin2017

Link: https://www.jianshu.com/p/88dfcbc48106

Source: brief book

The copyright belongs to the author. Commercial reprint please contact the author for authorization, non-commercial reprint please indicate the source.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.