Database Management System course

Design of an E-R Database Schema The E-R data model gives us much flexibility in designing a database schema to model a given enterprise. In this section, we consider how a database designer may select from the wide range of alternatives. Among the designer’s decisions are: • Whether to use an attribute or an entity set to represent an object (discussed earlier in Section 2.2.1) • Whether a real-world concept is expressed more accurately by an entity set or by a relationship set (Section 2.2.2) • Whether to use a ternary relationship or a pair of binary relationships (Section 2.2.3) • Whether to use a strong or a weak entity set (Section 2.6); a strong entity set and its dependent weak entity sets may be regarded as a single “object” in the database, since weak entities are existence dependent on a strong entity • Whether using generalization (Section 2.7.2) is appropriate; generalization, or a hierarchy of ISA relationships, contributes to modularity by allowing c

Extended E-R Features Although the basic E-R concepts can model most database features, some aspects of a database may be more aptly expressed by certain extensions to the basic E-R model. In this section, we discuss the extended E-R features of specialization, generalization, higher- and lower-level entity sets, attribute inheritance, and aggregation. Specialization An entity set may include subgroupings of entities that are distinct in some way from other entities in the set. For instance, a subset of entities within an entity set may have attributes that are not shared by all the entities in the entity set. The E-R model provides a means for representing these distinctive entity groupings. Consider an entity set person , with attributes nam e , street , and cit y . A person may be further classified as one of the following: • customer • employee Each of these person types is described by a set of attributes that includes all the at- tributes of entity set person p

Entity-Relationship Diagram As we saw briefly in Section 1.4, an E- R diagra m can express the overall logical structure of a database graphically. E-R diagrams are simple and clear — qualities that may well account in large part for the widespread use of the E-R model. Such a diagram consists of the following major components: • Rectangle s , which represent entity sets • Ellipse s , which represent attributes • Diamond s , which represent relationship sets • Line s , which link attributes to entity sets and entity sets to relationship sets • Doubl e ellipses , which represent multivalued attributes • Dashe d ellipses , which denote derived attributes • Doubl e lines , which indicate total participation of an entity in a relation- ship set • Doubl e rectangles , which represent weak entity sets (described later, in Section 2.6.) Consider the entity-relationship diagram in Figure 2.8, which consists of two entity sets, customer and loan , related th

Weak Entity Sets An entity set may not have sufficient attributes to form a primary key. Such an entity set is termed a wea k entity set . An entity set that has a primary key is termed a strong entit y set . As an illustration, consider the entity set payment , which has the three attributes: payment-numbe r , payment-dat e , and payment-amoun t . Payment numbers are typically sequential numbers, starting from 1, generated separately for each loan. Thus, al though each payment entity is distinct, payments for different loans may share the same payment number. Thus, this entity set does not have a primary key; it is a weak entity set. For a weak entity set to be meaningful, it must be associated with another entity set, called the identifying or owner entity set . Every weak entity must be associated  with an identifying entity; that is, the weak entity set is said to be existenc e depen den t on the identifying entity set. The identifying entity set is said to ow n the weak enti

Design Issues The notions of an entity set and a relationship set are not precise, and it is possible to define a set of entities and the relationships among them in a number of different ways. In this section, we examine basic issues in the design of an E-R database schema. Section 2.7.4 covers the design process in further detail. Use of Entity Sets versus Attributes Consider the entity set employee with attributes employee-name and telephone-number . It can easily be argued that a telephone is an entity in its own right with attributes telephone-number and location (the office where the telephone is located). If we take this point of view, we must redefine the employee entity set as: • The employee entity set with attribute employee-name • The telephone entity set with attributes telephone-number and location • The relationship set emp-telephone , which denotes the association between employees and the telephones that they have What, then, is the main difference b

Keys We must have a way to specify how entities within a given entity set are distinguished. Conceptually, individual entities are distinct; from a database perspective, however, the difference among them must be expressed in terms of their attributes. Therefore, the values of the attribute values of an entity must be such that they can uniquel y identify the entity. In other words, no two entities in an entity set are allowed to have exactly the same value for all attributes. A key allows us to identify a set of attributes that suffice to distinguish entities from each other. Keys also help uniquely identify relationships, and thus distinguish relationships from each other. Entity Sets A superkey is a set of one or more attributes that, taken collectively, allow us to identify uniquely an entity in the entity set. For example, the customer-id attribute of the entity set customer is sufficient to distinguish one customer entity from another. Thus, customer-id is a superkey. Sim

Constraints An E-R enterprise schema may define certain constraints to which the contents of a database must conform. In this section, we examine mapping cardinalities and participation constraints, which are two of the most important types of constraints. Mapping Cardinalities Mappin g cardinalities , or cardinality ratios, express the number of entities to which another entity can be associated via a relationship set. Mapping cardinalities are most useful in describing binary relationship sets, al- though they can contribute to the description of relationship sets that involve more than two entity sets. In this section, we shall concentrate on only binary relationship sets. For a binary relationship set R between entity sets A and B , the mapping cardinality must be one of the following: • On e to one . An entity in A is associated with at most one entity in B , and an entity in B is associated with at most one entity in A . (See Figure 2.4a.) • On e to many . An en