Exam 1
How are subtypes used in an ER diagram?
When entities in a data model share some common properties (attributes) within themselves apart from having one or more distinct attributes
Should a hierarchy (subtypes) be used if there are relationships that all sub-types should be able to participate in and why/why not?
Yes, so that the relationship can be designated as being between the super-type and the other participating entity. In the University example, both students and faculty can take courses, so this is an argument for creating a hierarchy.
If the relationship set is one to one, what is the primary key?
Either candidate key can be used as the primary key
How is the schema for the relationship set linking a weak entity set to its corresponding strong entity set in an ER diagram?
It's redundant and doesn't need to be present in a relational database design
What's cardinality?
Specifies how many instances of an entity relate to one instance of another entity.
If the relationship set is many to one from entity B to entity A, what is the primary key?
The advisor relationship is many-to-one from B to A then the primary key of the relationship is the primary key of B (when it's many-to-one the primary key is "from")
How is aggregation used within an E-R diagram?
-Allows me to specify relationship and treat it as if it's an entity; associate or group two or more entities/relationships (ex: replace Rental entity with relationship between Customer and Vehicle)
In the relational model what is the relationship between a key and an entire relation vs. individual tuples?
A key (whether primary, candidate, or super) is a property of the entire relation, rather than of the individual tuples.
What does taking all the primary-key attributes from all the related entity sets identify when going from ER to Relational?
A particular tuple; that's why for one-to-one, many-to-one, and one-to-many relationship sets, this turns out to be a larger set of attributes than we need in the primary key
What's a foreign key in the relational model?
A relation, r1, may include among its attributes the primary key of another relation, r2. So the foreign key is the primary key "attribute" of another relationship, r2, from r1, referencing r2.
What's the discriminator of a weak entity set?
A set of attributes that allows you to distinguish the weak entity set from other sets; it's also called the partial key of the weak entity set
What's a weak entity?
An entity set that does not have sufficient attributes to form a primary key
What's a strong entity?
An entity set that has a primary key
How do you treat attributes when going from ER to Relational?
Attributes in an E-R diagram generally map directly into attributes for the relation schemas
How are composite attributes handled when going from an ER to Relational?
By creating a separate attribute for each of the component attributes; also, don't create a separate attribute for the composite attribute itself
How is the primary key of a weak entity set formed?
By the primary key of the identifying entity set, plus the weak entity set's discriminator. In the case of the weak entity set "section", its primary key is {course id, sec id, year, semester}, where course id is the primary key of the identifying entity set, "course", and {sec id, year, semester} is the discriminator
How is the relation schema for the relationship in the case of one-to-one relationships?
Can be combined with the schemas for either of the entity sets; we can combine schemas even if the participation is partial by using null values. For example, if inst dept were partial, then we would store null values for the dept name attribute for those instructors who have no associated department.
What's the difference between cardinality and ordinality?
Cardinality specifies the occurrences of a relationship, while ordinality describes the relationship as either mandatory or optional. In other words, cardinality specifies the maximum number of relationships and ordinality specifies the absolute minimum number of relationships.
Interpret the Customer and Store relationship from the E-R diagram
Customers must prefer at least one Store, and each Store may have any number of Customers that prefer it, including none
True or false: you use the primary key of an entity set as an attribute of another entity set, instead of using a relationship.
False; for example, it's incorrect to model the ID of a student as an attribute of an instructor even if each instructor advises only one student. The relationship advisor is the correct way to represent the connection between students and instructors, since it makes their connection explicit, rather than implicit via an attribute.
What's an identifying entity set and why is it important?
For a weak entity set to be meaningful, it must be associated with another entity set, the identifying or owner entity set (it makes the weak entity meaningful)
How do you create foreign key constraints on a relation schema, R, from an ER diagram?
For each entity set E(i) related to relationship set R, we create a foreign-key constraint from relation schema R, with the attributes of R that were derived from primary-key attributes of E(i) referencing the primary key of the relation schema representing E(i)
What does a foreign key constraint ensure when going from an ER to Relational?
For each tuple representing a weak entity, there is a corresponding tuple representing the corresponding strong entity
What does the "on delete cascade" specification on the foreign key constraint do?
If an entity with the foreign key constraint is deleted, then so are all the associated weak entities
When might you not need a hierarchy?
If there aren't separate attributes nor sub-type specific relationships
If the relationship set is many to one from entity A to entity B, what is the primary key?
Many-to-one from A to B means that each A can have at most one B and the primary key of the relationship is the primary key of A (when it's many-to-one the primary key is "from")
What's the cardinality of the identity relationship from the weak entity set to the identifying set?
Many-to-one from the weak entity set to the identifying entity set; the identifying relationship set should not have any descriptive attributes since those could instead be associated with the weak entity set
When going from an ER to Relational, what is the primary key of the schema for schemas derived from a weak entity set?
The combination of the primary key of the strong entity set and the discriminator of the weak entity set
What does the structure of the primary key for a relationship set depend on?
The mapping cardinality of the relationship set
When going from ER to Relational for a binary one-to-one, what forms the primary key?
The primary key of either entity set can be chosen as the primary key. The choice can be made arbitrarily.
When going from ER to Relational for a binary many-to-one or one-to-many, what forms the primary key?
The primary key of the entity set on the "many" side of the relationship set
When going from ER to Relational for an n-ary set with an arrow on one of its edges, what forms the primary key?
The primary keys of the entity sets not on the "arrow" side of the relationship set (we allowed only one arrow out of a relationship set)
What's an identifying relationship?
The relationship associating the weak entity set with the identifying entity set; every weak entity must be associated with an identifying entity because it's "existence dependent" on the identifying entity set
If the relationship set is nonbinary/no cardinality constraints are present, what is the primary key?
The superkey formed is the only candidate key, and it is chosen as the primary key
When going from ER to Relational for a binary many-to-many, what forms the primary key?
The union of the primary key attributes from the participating entity sets
When going from ER to Relational for an n-ary set without any arrows on its edges, what forms the primary key?
The union of the primary key-attributes from the participating entity sets becomes the primary key
If the relationship set is many to many, what is the primary key?
The union of the primary keys of instructor and student
If a weak entity set has more than one identifying entity set, what would the primary key be?
The union of the primary keys of the identifying entity sets, plus the discriminator of the weak entity set
T/F: a weak entity set can participate in relationships other than the identifying relationship.
True
Should a hierarchy (subtypes) be used if there entities where different sub-types would have attributes that others don't?
Yes; for example, in the University example, we said that both students and faculty could be sub-types of a "person" class. For faculty we need to be able to keep track of a salary, something that generally isn't applicable for a student. However, if there aren't separate attributes, you might choose to model Type as an attribute of a "person".
Should a hierarchy (subtypes) be used if there are relationships that only some of the sub-types can participate in?
Yes; in the student/faculty relationship, only faculty can teach courses, serve as an advisor, be related to an office, etc.
How are weak entities related to other entities and to themselves?
You can set weak entities apart from other entities and sometimes each other (discriminators), but you can't always tell them apart from each other (no primary key)
