Core Concepts PDF

Core Concepts: Entities and Entity Sets: A database is modeled using entities, which are distinguishable objects, and entity sets, which are collections of similar entities sharing the same properties. For example, a specific person is an entity, while a set of all persons is a...

Core Concepts: Entities and Entity Sets: A database is modeled using entities, which are distinguishable objects, and entity sets, which are collections of similar entities sharing the same properties. For example, a specific person is an entity, while a set of all persons is an entity set. Attributes: Entities have descriptive properties called attributes. For instance, people have names and addresses. Attributes can be simple or composite (e.g., name consisting of first, middle, last), single-valued or multivalued (e.g., phone numbers), and derived (e.g., age from date of birth). Relationships and Relationship Sets: Relationships are associations among entities, and relationship sets are mathematical relations among entities from different entity sets. For example, an advisor relationship between an instructor and a student. Relationships can also have attributes, such as a date tracking when a student started with an advisor. Degree of Relationships: Relationships can be binary (between two entity sets) or n-ary (among more than two). Most relationships are binary. Ternary relationships can often be better represented by multiple binary relationships. Mapping Cardinality Constraints: These express the number of entities to which another entity can be associated via a relationship set. For binary relationships, these are one-to-one, one-to-many, many-to-one, or many-to-many. Keys: A super key uniquely determines each entity, while a candidate key is a minimal super key. One candidate key is chosen as the primary key. The combination of primary keys of participating entity sets forms a super key for a relationship set. E-R Diagrams: Graphical Representation: E-R diagrams use rectangles for entity sets, diamonds for relationship sets, and ovals for attributes. Primary key attributes are underlined. Cardinality Constraints: Cardinality constraints are shown by directed lines (→) for "one" and undirected lines (—) for "many". Participation Constraints: Total participation (double line) means every entity in a set participates in at least one relationship, while partial participation means some entities may not. Weak Entity Sets: Dependent Entities: Weak entity sets do not have a primary key and depend on an identifying entity set. They are depicted by a double rectangle, and their identifying relationship is represented by a double diamond. The primary key of a weak entity set is derived from the primary key of its strong entity set and its own discriminator (partial key). Extended E-R Features: Specialization/Generalization:  o Specialization: A top-down process of creating subgroups within an entity set (e.g., instructor "is a" person). Lower-level entity sets inherit attributes and relationships. o Generalization: A bottom-up process of combining entity sets with similar features. o They are represented using a triangle labeled ISA. Constraints: o Condition-defined: membership based on a condition (e.g., customers over 65). o User-defined: membership specified by users. o Disjoint: entity belongs to only one lower-level set. o Overlapping: entity can belong to multiple lower-level sets. o Total: entity must belong to a lower-level set. o Partial: entity may not belong to any lower-level set. Aggregation: Aggregation treats a relationship as an abstract entity, allowing relationships between relationships. This helps eliminate redundancy. Reduction to Relational Schemas: Schema Creation: Entity and relationship sets are expressed as relation schemas (tables). Strong Entity Sets: Strong entity sets have a corresponding schema with the same attributes. Weak Entity Sets: Weak entity sets have a schema that includes a column for the primary key of the identifying strong entity set. Relationship Sets: Many-to-many relationship sets have schemas with the primary keys of the participating entity sets and any descriptive attributes. Many-to-one or one-to- many relationship sets that are total on the "many" side can be represented by adding an extra attribute to the “many” side table. Composite and Multivalued Attributes: Composite attributes are flattened into separate attributes. Multivalued attributes are represented by a separate schema containing the primary key of the entity and an attribute for the multivalued attribute. Design Issues: Entity vs. Attribute: Choosing whether to represent a concept as an entity set or an attribute. Entity vs. Relationship Sets: Determining if a concept is best described by an entity or a relationship. Binary vs. N-ary: Choosing between binary and n-ary relationships. N-ary relationships can be converted to binary. Strong vs. Weak: Deciding whether to use a strong or weak entity set. Specialization/Generalization and Aggregation: Utilizing specialization/generalization for modularity and aggregation to treat relationships as single units.