data 5

Subtype Discriminator

Attribute in supertype entity Determines to which entity subtype each supertype occurrence is related

Primary Key Guidelines

Attribute that uniquely identifies entity instances in an entity set，Could also be combination of attributes，Main function is to uniquely identify an entity instance or row within a table，Guarantee entity integrity, not to "describe" the entity，Primary keys and foreign keys implement relationships among entities。Behind the scenes, hidden from user

Summary

Composite keys are useful to represent M:N relationships 。 Weak (strong-identifying) entities。 Surrogate primary keys are useful when no suitable natural key makes primary key。 In a 1:1 relationship, place the PK of mandatory entity: As FK in optional entity As FK in entity that causes least number of nulls。 As FK where the role is played。

When to Use Composite Primary Keys

Composite primary keys useful in two cases: As identifiers of composite entities，In which each primary key combination is allowed once in M:N relationship。 As identifiers of weak entities，In which weak entity has a strong identifying relationship with the parent entity，Automatically provides benefit of ensuring that there cannot be duplicate values。

Design Cases: Learning Flexible Database Design

Data modeling and design requires skills acquired through experience，Experience acquired through practice，Four special design cases that highlight: Importance of flexible design， Proper identification of primary keys， Placement of foreign keys，

Specialization Hierarchy

Depicts arrangement of higher-level entity supertypes and lower-level entity subtypes， Relationships described in terms of "IS-A" relationships。Subtype exists only within context of supertype，Every subtype has only one supertype to which it is directly related，Can have many levels of supertype/subtype relationships

Design Case 3: Fan Traps

Design trap occurs when relationship is improperly or incompletely identified。 Represented in a way not consistent with the real world。Most common design trap is known as fan trap，Fan trap occurs when one entity is in two 1:M relationships to other entities。Produces an association among other entities not expressed in the model

Constraints

Disjointed/Overlapping answers the question of how many. Partial/Total answers the question of is it required.

Inheritance

Enables entity subtype to inherit attributes and relationships of supertype，All entity subtypes inherit their primary key attribute from their supertype，At implementation level, supertype and its subtype(s) maintain a 1:1 relationship，Entity subtypes inherit all relationships in which supertype entity participates，Lower-level subtypes inherit all attributes and relationships from all upper-level supertypes。

Summary

Entity supertype is a generic entity type， related to one or more entity subtypes。 Specialization hierarchy: depicts arrangement and relationships between entity supertypes and entity subtypes。 Inheritance means an entity subtype inherits attributes and relationships of supertype。 Subtype discriminator determines which entity subtype the supertype occurrence is related to. Natural keys are identifiers that exist in real world: Sometimes make good primary keys。 Characteristics of primary keys: Must have unique values， Should be nonintelligent， Must not change over time， Preferably numeric or composed of single attribute。

Entity Supertypes and Subtypes

Entity supertype父型 ：Generic entity type related to one or more entity subtypes。 Contains common characteristics。 Entity subtype子型：Contains unique characteristics of each entity subtype

Design Case 1: Implementing 1:1 Relationships

Foreign keys work with primary keys to properly implement relationships in relational model。Put primary key of the "one" side on the "many" side as foreign key。 Primary key: parent entity。 Foreign key: dependent entity。

Design Case 1: Implementing 1:1 Relationships

In 1:1 relationship, there are two options: Place a foreign key in both entities (not recommended)。 Place a foreign key in one of the entities 。 Primary key of one of the two entities appears as foreign key of other。

Bottom line...

Keys are a significant source of coupling within a relational schema, and as a result， they are difficult to change. The implication is that you generally want to avoid keys with business meaning because business meaning changes. Use natural keys for lookup/reference tables, particularly when you suspect that the key values won't change any time soon，Fundamentally, there is no clear answer as to whether or not you should prefer natural keys over surrogate keys, regardless of what the zealots on either side of this religious argument may claim, and that your best strategy is be prepared to apply one strategy or the other whenever it makes sense.

Natural vs. Primary Keys

Natural Key。 Advantages: Already exist, you don't need to introduce a new "unnatural" value to your data schema. Disadvantages: Because they have business meaning they are effectively coupled to your business: you may need to rework your key when your business requirements change. Surrogate Key Advantages: They aren't coupled to your business and therefore will be easier to maintain ， A common key strategy across most, or better yet all, tables can reduce the amount of source code that you need to write,reducing the total cost of ownership (TCO) of the systems that you build. Disadvantages: Not "human readable", making them difficult for end users to work with. The implication is that you might still need to implement alternate keys for searching, editing, and so on.

Natural Keys and Primary Keys

Natural key is a real-world identifier used to uniquely identify real-world objects。Familiar to end users and forms part of their day-to-day business vocabulary ，Generally, data modeler uses natural identifier as primary key of entity being modeled，May instead use composite primary key or surrogate代理 key

Design Case 2: Maintaining History of Time-Variant Data

Normally, existing attribute values are replaced with new value without regard to previous value， Time-variant data:Values change over time， Must keep a history of data changes，Keeping history of time-variant。 data equivalent to having a multivalued attribute in your entity。Must create new entity in 1:M relationships with original entity，New entity contains new value, date of change

Entity Integrity: Selecting Primary Keys

Primary key is the most important characteristic of an entity。 Single attribute or some combination of attributes。Primary key's function is to guarantee entity integrity。Primary keys and foreign keys work together to implement relationships，Properly selecting primary key has direct bearing on efficiency and effectiveness

Design Case 4: Redundant Relationships

Redundancy is seldom a good thing in database environment。Occurs when there are multiple relationship paths between related entities。Main concern is that redundant relationships remain consistent across model，Some designs use redundant relationships to simplify the design。

The Extended Entity Relationship Model

Result of adding more semantic语义的 constructs to original entity relationship (ER) model， Diagram using this model is called an EER diagram (EERD)

Key concepts

Supertypes and subtypes Specialization 专门化Hierarchy - generalization versus specialization Inheritance Subtype discriminator 鉴别 and Completeness constraint约束 Selecting a primary key - natural key versus surrogate代理 替代 key Implementing 1:1 relationships, fan traps, redundant relationships, and time variant data.

When To Use Surrogate Primary Keys

Surrogate Primary key:A key with no business meaning，Especially helpful when there is: No natural key， Selected candidate key has embedded内涵 semantic 语义contents， Selected candidate key is too long or cumbersome累赘， If you use surrogate key: Ensure that candidate key of entity in， question performs properly， Use "unique index" and "not null" constraints，

Summary

Time-variant data 。 Data whose values change over time， Requires keeping a history of changes， To maintain history of time-variant data: Create entity containing the new value, date of change, other time-relevant data， Entity maintains 1:M relationship with entity for which history maintained。 Fan trap:One entity in two 1:M relationships to other entities。Association among the other entities not expressed in model。 Redundant relationships occur when multiple relationship paths between related entities。

When to Use Composite Primary Keys

When used as identifiers of weak entities ，normally used to represent: Real-world object that is existent-dependent on another real-world object。 Real-world object that is represented in data model as two separate entities in strong identifying relationship。Dependent entity exists only when it is related to parent entity。

Questions to Answer

。