{"id":24,"date":"2023-01-23T16:29:02","date_gmt":"2023-01-23T16:29:02","guid":{"rendered":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/chapter\/module-3-chapter-notes\/"},"modified":"2023-03-24T14:17:52","modified_gmt":"2023-03-24T14:17:52","slug":"module-3-chapter-notes","status":"publish","type":"chapter","link":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/chapter\/module-3-chapter-notes\/","title":{"raw":"Module 3 Chapter Notes","rendered":"Module 3 Chapter Notes"},"content":{"raw":"<h2>Chapter 4 Notes for Module 3<\/h2>\n<strong>Entity Relationship Model<\/strong>\n<ul>\n \t<li>Basis of an entity relationship diagram (ERD)<\/li>\n \t<li>ERD depicts the:\n<ul>\n \t<li>\u00a0 \u00a0 Conceptual database as viewed by end user<\/li>\n \t<li>\u00a0 \u00a0 Database\u2019s main components\n<ul>\n \t<li>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Entities<\/li>\n \t<li>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Attributes<\/li>\n \t<li>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Relationships<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n \t<li>Entity - Refers to the entity set and not to a single entity occurrence<\/li>\n<\/ul>\n<strong>Attributes<\/strong>\n<ul>\n \t<li>Characteristics of entities<\/li>\n \t<li><strong>Required attribute<\/strong>: Must have a value, cannot be left empty<\/li>\n \t<li><strong>Optional attribute:<\/strong>\u00a0Does not require a value, can be left empty<\/li>\n \t<li>Domain - Set of possible values for a given attribute<\/li>\n \t<li><strong>Identifiers:<\/strong>\u00a0One or more attributes that uniquely identify each entity instance<\/li>\n \t<li>\n<div><strong>Composite identifier:<\/strong>\u00a0Primary key composed of more than one attribute<\/div><\/li>\n \t<li>\n<div><strong>Composite attribute:<\/strong>\u00a0Attribute that can be subdivided to yield additional attributes<\/div><\/li>\n \t<li>\n<div><strong>Simple attribute:<\/strong>\u00a0Attribute that cannot be subdivided<\/div><\/li>\n \t<li>\n<div><strong>Single-valued attribute:<\/strong>\u00a0Attribute that has only a single value<\/div><\/li>\n \t<li>\n<div><strong>Multivalued attributes:<\/strong>\u00a0Attributes that have many values<\/div><\/li>\n \t<li>\n<div><strong>Multivalued attributes:<\/strong>\u00a0Attributes that have many values and require creating:<\/div>\n<ul>\n \t<li>\n<div>Several new attributes, one for each component of the original multivalued attribute<\/div><\/li>\n \t<li>A new entity composed of the original multivalued attribute\u2019s components<strong>\n<\/strong><\/li>\n<\/ul>\n<\/li>\n \t<li><strong>Derived attribute:<\/strong>\u00a0Attribute whose value is calculated from other attributes\n<ul>\n \t<li class=\"O1\">Derived using an algorithm<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<strong>Relationships<\/strong>\n<ul>\n \t<li>Association between entities that always operate in both directions<\/li>\n \t<li><strong>Participants:<\/strong>\u00a0Entities that participate in a relationship<\/li>\n \t<li><strong>Connectivity:<\/strong>\u00a0Describes the relationship classification<\/li>\n \t<li><strong>Cardinality:<\/strong>\u00a0Expresses the minimum and maximum number of entity occurrences associated with one occurrence of related entity<\/li>\n<\/ul>\n<strong>Weak Entity<\/strong>\n<ul>\n \t<li>Conditions\n<ul>\n \t<li class=\"O1\">Existence-dependent<\/li>\n \t<li class=\"O1\">Has a primary key that is partially or totally derived from parent entity in the relationship<\/li>\n<\/ul>\n<\/li>\n \t<li>Database designer determines whether an entity is weak based on business rules<\/li>\n<\/ul>\n<strong>Relationship Degree<\/strong>\n<ul>\n \t<li>Indicates the number of entities or participants associated with a relationship<\/li>\n \t<li><strong>Unary relationship:<\/strong>\u00a0Association is maintained within a single entity\n<ul>\n \t<li class=\"O1\">Recursive relationship: Relationship exists between occurrences of the same entity set<\/li>\n<\/ul>\n<\/li>\n \t<li><strong>Binary relationship:<\/strong>\u00a0Two entities are associated<\/li>\n \t<li><strong>Ternary relationship:<\/strong>\u00a0Three entities are associated<\/li>\n<\/ul>\n<strong>Associative Entities<\/strong>\n<ul>\n \t<li>Also known as composite or bridge entities<\/li>\n \t<li>Used to represent an M:N relationship between two or more entities<\/li>\n \t<li>Is in a 1:M relationship with the parent entities\n<ul>\n \t<li class=\"O1\">Composed of the primary key attributes of each parent entity<\/li>\n<\/ul>\n<\/li>\n \t<li>May also contain additional attributes that play no role in connective process<\/li>\n<\/ul>\n<strong>Developing an ER Diagram<\/strong>\n<ul>\n \t<li>Create a detailed narrative of the organization\u2019s\u00a0 description of operations<\/li>\n \t<li>Identify business rules based on the descriptions<\/li>\n \t<li>Identify main entities and relationships from the business rules<\/li>\n \t<li>Develop the initial ERD<\/li>\n \t<li>Identify the attributes and primary keys that adequately describe entities<\/li>\n \t<li>Revise and review ERD<\/li>\n<\/ul>\n<h2><a id=\"chapter5\" href=\"\"><\/a>Chapter 5 Notes for Module 3<\/h2>\n<strong>What is an entity supertype, and why is it used?<\/strong>\n\nAn entity supertype is a generic entity type that is related to one or more entity subtypes, where the entity supertype contains the common characteristics and the entity subtypes contain the unique characteristics of each entity subtype. The reason for using supertypes is to minimize the number of nulls and to minimize the likelihood of redundant relationships.\n\n<strong>What kinds of data would you store in an entity subtype?<\/strong>\n\nAn entity subtype is a more specific entity type that is related to an entity supertype, where the entity supertype contains the common characteristics and the entity subtypes contain the unique characteristics of each entity subtype. The entity subtype will store the data that is specific to the entity; that is, attributes that are unique the subtype.\n\n<strong>What is a specialization hierarchy?<\/strong>\n\nA specialization hierarchy depicts the arrangement of higher-level entity supertypes (parent entities) and lower-level entity subtypes (child entities). To answer the question precisely, we have used the text\u2019s Figure 5.2. (We have reproduced the figure on the next page for your convenience.) Figure 5.2 shows the specialization hierarchy formed by an EMPLOYEE supertype and three entity subtypes\u2014PILOT, MECHANIC, and ACCOUNTANT.\n\n<strong>What is a subtype discriminator?<\/strong>\n\nA subtype discriminator is the attribute in the supertype entity that is used to determine to which entity subtype the supertype occurrence is related. For any given supertype occurrence, the value of the subtype discriminator will determine which subtype the supertype occurrence is related to. For example, an EMPLOYEE supertype may include the EMP_TYPE value \u201cP\u201d to indicate the PROFESSOR subtype.\n\n<strong>What is an overlapping subtype?<\/strong>\n\nOverlapping subtypes are subtypes that contain non-unique subsets of the supertype entity set; that is, each entity instance of the supertype may appear in more than one subtype. For example, in a university environment, a person may be an employee or a student or both. In turn, an employee may be a professor as well as an administrator. Because an employee also may be a student, STUDENT and EMPLOYEE are overlapping subtypes of the supertype PERSON, just as PROFESSOR and ADMINISTRATOR are overlapping subtypes of the supertype EMPLOYEE. The text\u2019s Figure 5.4 (reproduced next for your convenience) illustrates overlapping subtypes with the use of the letter O inside the category shape.\n\n<strong>What is the difference between partial completeness and total completeness?<\/strong>\n\nPartial completeness means that not every supertype occurrence is a member of a subtype; that is, there may be some supertype occurrences that are not members of any subtype. Total completeness means that every supertype occurrence must be a member of at least one subtype.","rendered":"<h2>Chapter 4 Notes for Module 3<\/h2>\n<p><strong>Entity Relationship Model<\/strong><\/p>\n<ul>\n<li>Basis of an entity relationship diagram (ERD)<\/li>\n<li>ERD depicts the:\n<ul>\n<li>\u00a0 \u00a0 Conceptual database as viewed by end user<\/li>\n<li>\u00a0 \u00a0 Database\u2019s main components\n<ul>\n<li>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Entities<\/li>\n<li>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Attributes<\/li>\n<li>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Relationships<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<li>Entity &#8211; Refers to the entity set and not to a single entity occurrence<\/li>\n<\/ul>\n<p><strong>Attributes<\/strong><\/p>\n<ul>\n<li>Characteristics of entities<\/li>\n<li><strong>Required attribute<\/strong>: Must have a value, cannot be left empty<\/li>\n<li><strong>Optional attribute:<\/strong>\u00a0Does not require a value, can be left empty<\/li>\n<li>Domain &#8211; Set of possible values for a given attribute<\/li>\n<li><strong>Identifiers:<\/strong>\u00a0One or more attributes that uniquely identify each entity instance<\/li>\n<li>\n<div><strong>Composite identifier:<\/strong>\u00a0Primary key composed of more than one attribute<\/div>\n<\/li>\n<li>\n<div><strong>Composite attribute:<\/strong>\u00a0Attribute that can be subdivided to yield additional attributes<\/div>\n<\/li>\n<li>\n<div><strong>Simple attribute:<\/strong>\u00a0Attribute that cannot be subdivided<\/div>\n<\/li>\n<li>\n<div><strong>Single-valued attribute:<\/strong>\u00a0Attribute that has only a single value<\/div>\n<\/li>\n<li>\n<div><strong>Multivalued attributes:<\/strong>\u00a0Attributes that have many values<\/div>\n<\/li>\n<li>\n<div><strong>Multivalued attributes:<\/strong>\u00a0Attributes that have many values and require creating:<\/div>\n<ul>\n<li>\n<div>Several new attributes, one for each component of the original multivalued attribute<\/div>\n<\/li>\n<li>A new entity composed of the original multivalued attribute\u2019s components<strong><br \/>\n<\/strong><\/li>\n<\/ul>\n<\/li>\n<li><strong>Derived attribute:<\/strong>\u00a0Attribute whose value is calculated from other attributes\n<ul>\n<li class=\"O1\">Derived using an algorithm<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p><strong>Relationships<\/strong><\/p>\n<ul>\n<li>Association between entities that always operate in both directions<\/li>\n<li><strong>Participants:<\/strong>\u00a0Entities that participate in a relationship<\/li>\n<li><strong>Connectivity:<\/strong>\u00a0Describes the relationship classification<\/li>\n<li><strong>Cardinality:<\/strong>\u00a0Expresses the minimum and maximum number of entity occurrences associated with one occurrence of related entity<\/li>\n<\/ul>\n<p><strong>Weak Entity<\/strong><\/p>\n<ul>\n<li>Conditions\n<ul>\n<li class=\"O1\">Existence-dependent<\/li>\n<li class=\"O1\">Has a primary key that is partially or totally derived from parent entity in the relationship<\/li>\n<\/ul>\n<\/li>\n<li>Database designer determines whether an entity is weak based on business rules<\/li>\n<\/ul>\n<p><strong>Relationship Degree<\/strong><\/p>\n<ul>\n<li>Indicates the number of entities or participants associated with a relationship<\/li>\n<li><strong>Unary relationship:<\/strong>\u00a0Association is maintained within a single entity\n<ul>\n<li class=\"O1\">Recursive relationship: Relationship exists between occurrences of the same entity set<\/li>\n<\/ul>\n<\/li>\n<li><strong>Binary relationship:<\/strong>\u00a0Two entities are associated<\/li>\n<li><strong>Ternary relationship:<\/strong>\u00a0Three entities are associated<\/li>\n<\/ul>\n<p><strong>Associative Entities<\/strong><\/p>\n<ul>\n<li>Also known as composite or bridge entities<\/li>\n<li>Used to represent an M:N relationship between two or more entities<\/li>\n<li>Is in a 1:M relationship with the parent entities\n<ul>\n<li class=\"O1\">Composed of the primary key attributes of each parent entity<\/li>\n<\/ul>\n<\/li>\n<li>May also contain additional attributes that play no role in connective process<\/li>\n<\/ul>\n<p><strong>Developing an ER Diagram<\/strong><\/p>\n<ul>\n<li>Create a detailed narrative of the organization\u2019s\u00a0 description of operations<\/li>\n<li>Identify business rules based on the descriptions<\/li>\n<li>Identify main entities and relationships from the business rules<\/li>\n<li>Develop the initial ERD<\/li>\n<li>Identify the attributes and primary keys that adequately describe entities<\/li>\n<li>Revise and review ERD<\/li>\n<\/ul>\n<h2><a id=\"chapter5\" href=\"\"><\/a>Chapter 5 Notes for Module 3<\/h2>\n<p><strong>What is an entity supertype, and why is it used?<\/strong><\/p>\n<p>An entity supertype is a generic entity type that is related to one or more entity subtypes, where the entity supertype contains the common characteristics and the entity subtypes contain the unique characteristics of each entity subtype. The reason for using supertypes is to minimize the number of nulls and to minimize the likelihood of redundant relationships.<\/p>\n<p><strong>What kinds of data would you store in an entity subtype?<\/strong><\/p>\n<p>An entity subtype is a more specific entity type that is related to an entity supertype, where the entity supertype contains the common characteristics and the entity subtypes contain the unique characteristics of each entity subtype. The entity subtype will store the data that is specific to the entity; that is, attributes that are unique the subtype.<\/p>\n<p><strong>What is a specialization hierarchy?<\/strong><\/p>\n<p>A specialization hierarchy depicts the arrangement of higher-level entity supertypes (parent entities) and lower-level entity subtypes (child entities). To answer the question precisely, we have used the text\u2019s Figure 5.2. (We have reproduced the figure on the next page for your convenience.) Figure 5.2 shows the specialization hierarchy formed by an EMPLOYEE supertype and three entity subtypes\u2014PILOT, MECHANIC, and ACCOUNTANT.<\/p>\n<p><strong>What is a subtype discriminator?<\/strong><\/p>\n<p>A subtype discriminator is the attribute in the supertype entity that is used to determine to which entity subtype the supertype occurrence is related. For any given supertype occurrence, the value of the subtype discriminator will determine which subtype the supertype occurrence is related to. For example, an EMPLOYEE supertype may include the EMP_TYPE value \u201cP\u201d to indicate the PROFESSOR subtype.<\/p>\n<p><strong>What is an overlapping subtype?<\/strong><\/p>\n<p>Overlapping subtypes are subtypes that contain non-unique subsets of the supertype entity set; that is, each entity instance of the supertype may appear in more than one subtype. For example, in a university environment, a person may be an employee or a student or both. In turn, an employee may be a professor as well as an administrator. Because an employee also may be a student, STUDENT and EMPLOYEE are overlapping subtypes of the supertype PERSON, just as PROFESSOR and ADMINISTRATOR are overlapping subtypes of the supertype EMPLOYEE. The text\u2019s Figure 5.4 (reproduced next for your convenience) illustrates overlapping subtypes with the use of the letter O inside the category shape.<\/p>\n<p><strong>What is the difference between partial completeness and total completeness?<\/strong><\/p>\n<p>Partial completeness means that not every supertype occurrence is a member of a subtype; that is, there may be some supertype occurrences that are not members of any subtype. Total completeness means that every supertype occurrence must be a member of at least one subtype.<\/p>\n","protected":false},"author":11,"menu_order":3,"template":"","meta":{"pb_show_title":"","pb_short_title":"","pb_subtitle":"","pb_authors":[],"pb_section_license":""},"chapter-type":[49],"contributor":[],"license":[],"class_list":["post-24","chapter","type-chapter","status-publish","hentry","chapter-type-numberless"],"part":19,"_links":{"self":[{"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/pressbooks\/v2\/chapters\/24","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/wp\/v2\/users\/11"}],"version-history":[{"count":1,"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/pressbooks\/v2\/chapters\/24\/revisions"}],"predecessor-version":[{"id":25,"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/pressbooks\/v2\/chapters\/24\/revisions\/25"}],"part":[{"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/pressbooks\/v2\/parts\/19"}],"metadata":[{"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/pressbooks\/v2\/chapters\/24\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/wp\/v2\/media?parent=24"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/pressbooks\/v2\/chapter-type?post=24"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/wp\/v2\/contributor?post=24"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/wp\/v2\/license?post=24"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}