Discuss the role of a high-level data model in the database design process. High-level data models assist in conceptual design and helps express data requirements of the users and includes detailed descriptions of the entity types, relationships, and constraints. The high-level data model is also used as a reference to ensure that all users’ data requirements are met and that the requirements do not include conflicts.
List the various cases where use of a null value would be appropriate. Null value would be appropriate when a particular entity does not have an applicable value for an attribute. There are two cases for such situations. The first case arises when it is known that the attribute value exists but is missing. An example for cases like such would be if the Height attribute of a person is listed as null. The second case arises when it is not known whether the attribute value exists. An example would be is the HomePhone attribute of a person is null.
Define the following terms: entity, attribute, attribute value, relationship instance, composite attribute, multivalued attribute, derived attribute, complex attribute, key attribute, value set (domain). Entity Entity is a “thing” in the real world with an independent existence. It can also be an object with physical existence (i. e. person, car) or conceptual existence (i. e. company, job). Attribute An attribute is a particular property that describes entity (i. e. person name company name). Attribute Value Attribute values are major data stored in the database. Relationship Instance Relationship Instance is an association of entities, where the association includes exactly one entity from each participating entity type. Composite Attribute Composite attribute is an attribute that can be divided into meaningful components.
Multivalued Attribute Multivalued attribute is an attribute that can have many values. Derived Attribute Derived attribute is an attribute whose value is computed from another attribute or combination of attributes. Complex Attribute Complex attribute is composite and multivalued attributes nested in an arbitrary manner. Key Attribute Key attribute is an attribute whose values are distinct for each individual entity in the collection. Value Set (Domain) Value set specifies the set of values that may be assigned to that attribute for each individual entry.
What is an entity type? What is an entity set? Explain the differences among an entity, an entity type, and entity set. Entity type defines a collection of entities that have the same attributes. Entity set is a named collection of related data. The differences between entity, an entity type, and entity set are:
- Entity is a person, place, thing, event, or even a concept. It may be tangible or intangible. Examples of an entity are employees,
- Entity type describes the schema or intension for a set of entities that share the same structure.
- Entity set is the extension of the entity type.
Explain the difference between an attribute and a value set. An attribute is a particular property that describes entity. A value set specifies the set of values that may be assigned to that attribute for each individual entry. An example of an attribute is EmployeeAge and we can set the value set for this attribute to a range of integer numbers.
What is a relationship type? Explain the differences among a relationship instance, a relationship type, and a relationship set. Relationship type is the nature of a relationship between entities, expressed by the number of their possible occurrences in the related tables. The differences between a relationship instance, a relationship type, and a relationship set are:
- Relationship instance is an association of entities, where the association includes exactly one entity from each participating entity type.
- Relationship type defines a relationship set among entities from these types.
- Relationship set is a set of associations.
What is a participation role? When is it necessary to use role name in the description of relationship type? Participation role is the part that each entity participates in a relationship. It is necessary to use role name in the description of relationship type when the same entity type participates more than once in a relationship type in different roles. In other words, role names are necessary in recursive relationships.
Describe two alternatives for specifying structural constraints on relationship types. What are the advantages of each. Two alternatives for specifying structural constraints on relationship types are cardinality ratio and participation. Cardinality ratio for a binary relationship specifies the number of relationship instances that an entity can participate in. The participation constraint specifies whether the existence of an entity depends on its being related to another entity via the relationship type.
Under what conditions can an attribute of a binary relationship type be migrated to become an attribute of one of the participating entity type? An attribute of a binary relationship type can be migrated to become an attribute of one the participating entity type when the relationship type is 1:1 or 1:N. This is because each entity participates in at most one relationship instance. However, for 1:N relationship type, a relationship attribute can be migrated only to the entity type at the N-side of the relationship.
When we think of relationships as attributes, what are the value sets of these attributes? What class of data models is based on this concept? The value sets of these attributes 11. What is meant by a recursive relationship type? Give some examples of recursive relationship types. A recursive relationship type exists if an entity can be related to itself or in other words, the same entity type participates more than once in a relationship type in different roles.
An example of a recursive relationship type is courses that require one or more other courses as prerequisites. The course entity is related to another course entity. In this case, the recursive relationship “course is a prerequisite to course” also happens to be a M:N relationship. This is because a course can have more than one prerequisite, and a course can be a prerequisite to many other courses. Another example of a recursive relationship type is in a supervision relationship type between an employee and a supervisor. Both entities are members of the same Employee entity type. In this example, the employee entity type participates twice in the supervision relationships, once in the role of a supervisor, and once in the role of supervisee.
When is the concept of weak entity used in data modeling? Define the terms owner entity type, weak entity type, identifying relationship type, and partial key. The concept of weak entity is used in data modeling when there are many attributes or when entity types do not have key attributes of their own. Owner Entity Type Owner entity type is an entity type that is related to weak entity types through combination of some of their attribute values.
Weak Entity Type Weak entity type is an entity type that does not have key attributes of their own. Identifying Relationship Type Identifying relationship type is the relationship type that relates a weak entity type to its owner. Partial Key Partial key is a set of attributes that can uniquely identify weak entities that are related to the same owner entity.
Can an identifying relationship of a weak entity type be of a degree greater than two? Give examples to illustrate your answer. An identifying relationship of a weak entity type can be of a degree greater than two. For a ternary identifying relationship type, the weak entity type has several owner entity types. An example for a ternary relationship such as Supply must be represented as weak entity type, with no partial key and with three identifying relationships. These participating entity types Supplier, Part, and Project are together the owner entity types. Hence, an entity in the weak entity type Supply is identified by the combination of its three owner entities from Supplier, Part, and Project.
Discuss the convention for displaying an ER schema as an ER diagram. Entity types are shown in rectangular boxes. Relationship types are shown in diamond-shaped boxes attached to the participating entity types with straight lines. Attributes are shown in ovals, and each attribute is attached by a straight line to its entity type or relationship type. Component attributes of a composite attribute are attached to the oval representing the composite attribute. Multivalued attributes are shown in double ovals and key attributes have their names underlined. Derived attributes are shown in dotted ovals.
Weak entity types are distinguished by being placed in double rectangles and by having their identifying relationship placed in double diamonds. The partial key of the weak entity type is underlined with a dotted line. The cardinality ratio of each binary relationship type is specified by 7attaching a 1, M, or N on each participating edge. The participation constraint is specified by a single line for partial participation and by double lines for total participation. We show the role names when one entity type plays both roles in a relationship.
