Database Design with ER Model: Concepts, Relationships, and Mapping to Relational Schema

What is the purpose of the Entity-Relationship (ER) model in database design?

To represent entities, their attributes, and relationships in a structured way for database schema design.

What are the three main concepts of the ER model?

Entities, Attributes, and Relationships.

What is an entity in the context of the ER model?

A specific thing or object represented in the database, such as an EMPLOYEE or DEPARTMENT.

What are attributes in the ER model?

Properties used to describe an entity, such as Name, SSN, Address for an EMPLOYEE.

What is a key attribute?

An attribute of an entity type that must have a unique value for each entity, such as SSN for EMPLOYEEs.

What is the difference between a relationship type and a relationship set?

A relationship type is the schema description of a relationship, while a relationship set is the current set of relationship instances in the database.

What is a composite attribute?

An attribute that is composed of several components, such as Address which may include Apt#, House#, Street, etc.

What is a multi-valued attribute?

An attribute that can have multiple values for an entity, such as Color of a CAR.

What does the term 'entity set' refer to?

A collection of entities of the same entity type stored in the database.

What is the significance of the three-schema architecture in database design?

It provides a framework for understanding the structure of a database at different levels of abstraction.

What is the role of a department in the COMPANY database?

Each department has a name, number, and a manager, and may have several locations.

What information does the COMPANY database store for each employee?

Social security number, address, salary, sex, birthdate, and their department and project assignments.

How is the relationship between EMPLOYEEs and PROJECTs defined in the ER model?

Through a WORKS_ON relationship type where employees participate in multiple projects.

What are the initial entity types identified for the COMPANY database?

DEPARTMENT, PROJECT, EMPLOYEE, and DEPENDENT.

What is an example of a composite multi-valued attribute?

PreviousDegrees of a STUDENT, which includes components like College, Year, Degree, and Field.

What is the purpose of keeping track of an employee's direct supervisor in the COMPANY database?

To establish a hierarchy and manage reporting relationships within the organization.

What is the significance of the notation used in ER diagrams?

It visually represents entities, attributes, and relationships to facilitate understanding of the database schema.

What is the purpose of the initial conceptual design in database schema development?

To outline the basic structure and relationships of the database based on requirements.

What does the term 'degree of a relationship type' refer to?

The number of participating entity types in a relationship, such as binary relationships involving two entities.

What is an example of a simple attribute?

An attribute that has a single atomic value, such as SSN or Sex.

How can attributes be nested in the ER model?

Composite and multi-valued attributes can be nested to form complex structures, although this is rare.

What is the role of the PROJECT entity in the COMPANY database?

Each project has a unique name, number, and is located at a single location, and is controlled by departments.

What is the relationship between EMPLOYEEs and DEPENDENTs in the COMPANY database?

Each employee may have multiple dependents, with records kept for each dependent's details.

What is represented by a diamond-shaped box in ER diagrams?

A relationship type.

What type of relationships are identified in the COMPANY database schema?

Binary relationships (degree 2).

What are the six identified relationship types in the COMPANY database?

WORKSFOR, MANAGES, CONTROLS, WORKSON, SUPERVISION, DEPENDENTS_OF.

What does the WORKS_FOR relationship connect?

EMPLOYEE and DEPARTMENT.

What does the MANAGES relationship connect?

EMPLOYEE and DEPARTMENT.

What does the CONTROLS relationship connect?

DEPARTMENT and PROJECT.

What does the WORKS_ON relationship connect?

EMPLOYEE and PROJECT.

What is a recursive relationship?

A relationship type between the same participating entity type in distinct roles.

What is an example of a recursive relationship in the COMPANY schema?

SUPERVISION, where an EMPLOYEE can be both a supervisor and a supervisee.

What is a weak entity type?

An entity that does not have a key attribute and is identification-dependent on another entity type.

How is a weak entity identified?

By the combination of a partial key of the weak entity and the related identifying entity.

What attribute can a relationship type have?

For example, HoursPerWeek in the WORKS_ON relationship.

What does the cardinality ratio specify?

The maximum participation of entities in a relationship.

What are the types of cardinality ratios?

One-to-one (1:1), One-to-many (1:N), Many-to-one (N:1), Many-to-many (M:N).

What do total and partial participation constraints indicate?

Total indicates mandatory participation, while partial indicates optional participation.

What notation is used for specifying structural constraints on relationships?

(min, max) notation.

What is the default value for min and max in relationship constraints?

min=0, max=n (indicating no limit).

What are higher-degree relationship types called?

Ternary for degree 3, and n-ary for degree n.

What is the difference between n-ary and binary relationships?

An n-ary relationship is not equivalent to n binary relationships.

What does the (min, max) notation specify for each entity type in a relationship?

It specifies the minimum and maximum number of relationship instances each entity can participate in.

What is an example of a constraint specification for an employee in a department?

An employee can work for exactly one department, specified as (1,1) for EMPLOYEE in WORKS_FOR.

What does the HoursPerWeek attribute represent in the WORKS_ON relationship?

The number of hours per week that an EMPLOYEE works on a PROJECT.

How can multiple relationship types exist between the same participating entity types?

By having distinct meanings and different relationship instances, such as MANAGES and WORKS_FOR between EMPLOYEE and DEPARTMENT.

What is the significance of displaying role names in a recursive relationship?

To distinguish between the different roles of the same entity type in the relationship.

What does the term 'identifying relationship type' refer to?

A relationship type that identifies a weak entity type.

What is the role of the identifying entity type in relation to a weak entity?

It provides the context needed to identify the weak entity.

What is the purpose of a university database?

To keep track of enrollments in classes and student grades, including colleges, departments, courses, sections, and instructors.

What is a superclass in the context of subclasses?

A superclass is an entity type that can have additional meaningful subgroupings, such as EMPLOYEE being the superclass for SECRETARY, ENGINEER, and TECHNICIAN.

What is a subclass?

A subclass is a subset of a superclass that represents a specific grouping of entities, such as SECRETARY or ENGINEER under the EMPLOYEE superclass.

What type of relationship do subclasses and superclasses represent?

They represent IS-A relationships, where a subclass is a specific type of the superclass.

What is attribute inheritance in superclass/subclass relationships?

A subclass inherits all attributes and relationships of the superclass, meaning every entity in the subclass has values for inherited attributes.

What is the disjointness constraint in subclasses?

It specifies that subclasses must be disjoint, meaning an entity can be a member of at most one subclass.

What does the completeness constraint specify?

The total completeness constraint specifies that every entity in the superclass must be a member of some subclass, while partial completeness allows entities not to belong to any subclass.

What is the first step in the ER-to-Relational Mapping Algorithm?

Mapping of Regular Entity Types, where each regular entity type creates a relation that includes all simple attributes and selects a primary key.

How is a weak entity type mapped in the ER-to-Relational Mapping Algorithm?

A weak entity type creates a relation that includes its simple attributes and foreign key attributes from its owner entity type, with a primary key formed from the owner's primary key and the weak entity's partial key.

What is the mapping approach for binary 1:1 relationship types?

Identify the relations corresponding to the entity types and include a foreign key in one of the relations, preferably the one with total participation in the relationship.

What tools support ER/EER diagrams?

Software tools like ERwin, MySQL Workbench, and dbdiagram.io support ER/EER diagrams.

What is an example of a subclass relationship?

An EMPLOYEE can be a SECRETARY, TECHNICIAN, or MANAGER, representing different roles within the same entity type.

What is the significance of the primary key in a relation?

The primary key uniquely identifies each record in a relation and is essential for maintaining data integrity.

What is an example of a subclass that inherits attributes from its superclass?

A SECRETARY inherits attributes like Name and SSN from the EMPLOYEE superclass.

What does the term 'overlapping subclasses' mean?

Overlapping subclasses allow the same entity to be a member of more than one subclass.

What is the role of EER diagrams?

EER diagrams extend ER diagrams to represent additional subgroupings and relationships, including subclasses and superclasses.

What is the primary key of a weak entity relation?

The primary key of a weak entity relation is the combination of the primary key(s) of its owner(s) and the weak entity's partial key.

What is the purpose of the ER-to-Relational Mapping Algorithm?

To convert ER diagrams into relational schemas while preserving information and minimizing null values.

What is meant by 'total participation' in a relationship?

Total participation means that every entity in the superclass must be included in at least one subclass.

What is the difference between a regular entity and a weak entity?

A regular entity can exist independently, while a weak entity depends on a regular entity for its identification.

What is a common notation used in ER diagrams?

EER diagrams use specific symbols to represent entities, relationships, and constraints like disjointness and completeness.

What is an alternate mapping option for a 1:1 relationship type?

Merging the two entity types and the relationship into a single relation.

How is a binary 1:N relationship type mapped?

Include the primary key of the relation representing the N-side entity as a foreign key in the relation representing the 1-side entity.

What is the primary key of a relation mapping an M:N relationship type?

The combination of the primary keys of the relations representing the participating entity types.

What is created for each multivalued attribute in ER-to-relational mapping?

A new relation that includes an attribute for the multivalued attribute and a foreign key for the primary key of the related entity.

What is the primary key of a relation created for a multivalued attribute?

The combination of the multivalued attribute and the foreign key.

How is an n-ary relationship type mapped?

Create a new relation that includes foreign keys of the participating entity types and any attributes of the n-ary relationship.

What are the four options for mapping subclasses in ER-to-relational mapping?

1. Multiple relations for superclass and subclasses, 2. Multiple relations for subclasses only, 3. Single relation with one type attribute, 4. Single relation with multiple type attributes.

What does Option 8A involve in mapping subclasses?

Creating a relation for the superclass and separate relations for each subclass with their respective attributes.

What is the requirement for using Option 8B for mapping subclasses?

It only works for total subclasses, where every entity in the superclass must belong to one of the subclasses.

What does Option 8C entail in subclass mapping?

Creating a single relation that includes attributes from the superclass and all subclasses along with a type attribute.

What is the purpose of the type attribute in Option 8C?

To indicate the subclass to which each tuple belongs.

What does Option 8D involve for mapping subclasses?

Creating a single relation with multiple Boolean type attributes indicating subclass membership.

What is the primary key of the relation created for an M:N relationship type?

The combination of the foreign keys from the participating entity types.

What is a relationship relation in the context of M:N relationships?

A new relation created to represent the M:N relationship, including foreign keys from both participating entities.

What is the mapping process for binary relationships in ER-to-relational mapping?

Identify the relation representing the N-side entity and include its primary key as a foreign key in the relation representing the 1-side entity.

What is the significance of the primary key in the relation mapping a multivalued attribute?

It ensures uniqueness by combining the multivalued attribute with the foreign key.

What is an example of a binary 1:N relationship type?

WORKS_FOR, where an employee works for a department.

What is the mapping approach for an overlapping specialization?

Using multiple type attributes to indicate membership in different subclasses.

What does the mapping of subclasses aim to achieve?

To accurately represent the inheritance and attributes of entities in a relational schema.

What is the role of foreign keys in the mapping of n-ary relationships?

To link the primary keys of the participating entity types in the new relation.

What is a composite attribute in the context of multivalued attributes?

An attribute that can be broken down into simpler components, which can also be included in the new relation.

What is the primary key of the WORKS_ON relation in an M:N mapping?

The combination of the foreign keys ESSN (Employee) and PNO (Project).

What is the purpose of creating a relationship relation for M:N relationships?

To manage the complexity of relationships between multiple entities in a structured way.