ER/EER Diagrams for Company and Sports Team

• These notes cover ER/EER diagrams for database design, relational schema mapping, and SQL integrity constraints, particularly within the context of designing databases for a company and a sports team.

Experiment 1: ER/EER Diagram Design

• Objective is to design ER/EER diagrams for a COMPANY and a SPORTS TEAM database.
• The program outcome is for students to be able to draw conceptual database designs using ERD Plus.

COMPANY Database

• Stores information about employees, departments, and projects.
• Departments control several projects which each have a unique name, number, and location.
• Each department has a unique name and number, is managed by an employee, and can have multiple locations.
• The database tracks the start date of the employee managing the department.
• Employee data includes name, Social Security number, address, salary, sex, and birth date.
• Employees are assigned to one department but can work on several projects.
• The database keeps track of the hours per week an employee works on each project, as well as their direct supervisor.
• Dependent information (first name, sex, birth date, relationship) is tracked for insurance purposes.

SPORTS TEAM Database

• The goal is to keep track of matches played, scores, players in each match, and individual player statistics.
• Summary statistics should be modeled as derived attributes.
• The database should track the same information for all teams in a league.

Key Elements of ER/EER Model

• Entities: Distinguishable real-world objects, can be strong or weak.
• Relationships: Associations between two or more entities (Unary, Binary, Ternary, Recursive).
• Participation Constraints: Total (entity involved in the relationship) or Partial (not all entities involved).
• Attributes: Properties describing an entity, it has a domain.
• Recursive Relations: Relationship where the same entity type participates in distinct roles.
• Mapping Cardinalities: Number of instances of an entity in a relation.
• Binary/Ternary Relationships: Relationships involving two or three entities.
• Specialization/Generalization: Hierarchical relationships between entity types.

ER Diagram Symbols and Notations

• Entity: A real-world object that is distinguishable from other objects.
• Entity Set: A collection of similar entities with the same attributes and a key.
• Attributes Properties used to describe an entity (e.g., Name, SSN, Address for an "EMPLOYEE" entity).
• Relationship: Relates two or more distinct entities with a specific meaning.
• Cardinality Ratio: The number of instances of an entity from a relation.

Preparatory Questions and Answers

• An attribute of an entity can have more than one value.
• A directed line from the relationship set advisor to both entity sets instructor and student indicates one to many cardinality.
• An entity set without sufficient attributes to form a primary key is a Weak entity set.
• Maximum Cardinality indicates the maximum number of entities involved in a relationship.
• Every weak entity must be associated with an identifying entity is True.

Experiment 2: Relational Database Schema Design

• Objective to design a Relational Database Schema for the COMPANY and SPORTS TEAM database from the ER/EER diagram.
• The program outcome is for students to map the conceptual database design to logical (relational) database design.
• Step 1: Mapping of Regular Entity Types
• Step 2: Mapping of Weak Entity Types
• Step 3: Mapping of Binary 1:1 Relation Types
• Step 4: Mapping of Binary 1:N Relationship Types
• Step 5: Mapping of Binary M:N Relationship Types
• Step 6: Mapping of Multivalued attributes
• Step 7: Mapping of N-ary Relationship Types
• Step 8: Mapping EER Model Constructs to Relations
• Step 9: Options for Mapping Specialization or Generalization
• Step 10: Mapping of Union Types (Categories)

Steps for Mapping ER/EER Model to Relational Tables

• Mapping Regular Entity Types: Create a relation for each strong entity type including all simple attributes; choose a primary key.
• Mapping Weak Entity Types: Create a relation for each weak entity type; include all simple attributes and the primary key of the owner entity type as a foreign key.
• Mapping Binary 1:1 Relationship Types: Can be implemented via: Foreign Key Approach, Merged Relation Option, or Cross-reference.
• Mapping Binary 1:N Relationship Types: Include the primary key of the entity on the '1' side as a foreign key in the relation representing the entity on the 'N' side.
• Mapping Binary M:N Relationship Types: Create a new relation to represent the relationship, including the primary keys of both participating entities as foreign keys; their combination forms the primary key of the new relation.
• Mapping Multi-valued Attributes: Create a new relation including an attribute for the multi-valued attribute and the primary key of the original relation as a foreign key.
• Mapping N-ary Relationship Types: Create a new relation for the n-ary relationship, including the primary keys of all participating entities as foreign keys.
• Options for Mapping Specialization or Generalization:
  • Multiple relations - Superclass and subclasses.
  • Multiple relations - Subclass relations only
  • Single relation with one type attribute
  • Single relation with multiple type attributes.
• Mapping of Union Types (Categories): Specify a new key attribute (surrogate key) when the defining superclasses have different keys.

Preparatory Questions and Answers

• In which of the following, a separate schema is created consisting of that attribute and the primary key of the entity set? A multivalued attribute of an entity set.
• The minimum number of tables needed to represent M, N, P, R1, R2 is 3
• {M1, P1, N1} is a correct attribute set for one of the tables for the correct answer to the above question.
• Minimum number of tables required to represent this situation in the relational model is 3
• Min 1 and max 3. What is the min and max number of tables required to convert an ER diagram with 2 entities and 1 relationship between them with partial participation constraints of both entities?

Experiment 3: SQL Integrity Constraints

• Objective is to apply SQL integrity constraints for the COMPANY database, based on given DDL statements.
• The program outcome is to understand database creation and the application of various integrity constraints such as primary key, foreign key, unique key, NOT NULL, default, and CHECK.
• Implement the following types of integrity constraints: Primary Key, Foreign Key, Unique, Default, Auto-increment, Check, Not Null

Types of Integrity Constraints

• Primary Key: Uniquely identifies each row in a table; cannot be duplicate or null.
• Foreign Key: Establishes a relationship between columns in one or more tables; references the primary key in another table.
• NOT NULL: Ensures a column cannot contain a null value.
• Unique Key: Ensures that a column or group of columns has a distinct value for each row; allows one null value.
• Check Constraint: Defines a business rule on a column; all rows must satisfy this rule.

Syntax Examples

• Primary Key (column level): column name datatype [CONSTRAINT constraint_name] PRIMARY KEY
• Primary Key (table level): [CONSTRAINT constraint_name] PRIMARY KEY (column_name1, column_name2, ...)
• Foreign Key (column level): [CONSTRAINT constraint_name] REFERENCES Referenced_Table_name(column_name)
• Foreign Key (table level): [CONSTRAINT constraint_name] FOREIGN KEY(column_name) REFERENCES referenced_table_name(column_name);
• Not Null: [CONSTRAINT constraint name] NOT NULL
• Unique Key (column level): [CONSTRAINT constraint_name] UNIQUE
• Unique Key (table level): [CONSTRAINT constraint_name] UNIQUE(column_name)
• Check Constraint: [CONSTRAINT constraint_name] CHECK (condition)

Rules for Defining Primary Key

• No two rows can have the same primary key value.
• Every row must have a primary key value.
• The primary key field cannot be null.
• The primary key value cannot be modified if any foreign key refers to that primary key.

Preparatory Questions and Answers

• If data in r1 and r2 satisfy referential integrity constraints, which of the following is ALWAYS TRUE? B
• S1 is FALSE and S2 is TRUE: Given the following statements: S1: A foreign key declaration can always be replaced by an equivalent check assertion in SQL. S2: Given the table R(a,b,c) where a and b together form the primary key, the following is a valid table definition.
• Which of the following is not an integrity constraint? Positive
• In order to ensure that the value of budget is non-negative which of the following should be used? Check(budget>=0)