Constraints and Weak Entity Sets

• The lecture includes constraints, subclasses and weak entity sets

Constraints

• Constraints are conditions that entity sets and relationships should follow
• Key constraints, referential integrity constraints, and degree constraints are focused

Key Constraints

• Keys are one or more attributes that are underlined
• Keys uniquely represent each entity in the entity set
• For example, the “name” uniquely represents each and every person, meaning that one or more of the attributes has to be unique
• Each product has a unique <name, category> combination

Referential Integrity

• Guarantees relationships between tables remain consistent
• Every product must be involved in the Make relationship
• The referential integrity constraint can only apply to the "one" side of a many-to-one or one-to-one relationship
• Every company should make at least one product if every company should make at least one product

Degree Constraint

• Each and every company should make at least one product.
• Each and every company makes at most 1000 products
• Degree constraints are not as easy to enforce as key and referential integrity constraints in a DBMS

ER Diagram Design Exercise

• Consider a mail order database in which employees take orders for parts from customers, where
  • Each employee is identified by a unique employee number, and has a first name, a last name, and a zip code
  • Each customer is identified by a unique customer number, and has a first name, last names, and a zip code
  • Each part being sold is identified by a unique part number; it has a part name, a price, and a quantity in stock
  • Each order placed by a customer is taken by one employee and is given a unique order number
  • Each order may contain certain quantities of one or more parts.
  • The shipping date of each part is also recorded

Subclasses

• Subclass is defined as a special type within a special type of students
• The connection between a subclass and its superclass is captured by the isa relationship, which is represented using a triangle
• Key of a subclass = key of its superclass, for example: Key of Phds = Students.ID
• Students is referred to as the superclass of PhDs

Weak Entity Sets

• Weak entity sets are a special type of entity sets that cannot be uniquely identified by their own attributes
• Weak entity sets needs attributes from other entities to identify themselves
• Cities in USA can be an example if there are cities that have identical names

Weak Entity Sets Example

• There can be cities with identical state names
• Cities in the same state have different names
• Make Cities a weak entity set associated with the entity set States, where
  • The relationship In is called the supporting relationship of Cities Weak entity set = Double-lined rectangle
• Supporting relationship = Double-lined diamond
• The key of Cities = (State.name, Cities.name), which means that it is a single composite key

Exercise 1

• Consider two entity sets: Players and Teams
  • Each player has a name and a number
  • Each team has a name and a manager
  • Each player plays for exactly one team, and is uniquely identified within the team by his/her number
  • Each team is uniquely identified by its name
  • Different players may have the same name
  • Key of Players is (Players.number, Teams.name)

Exercise 2: ER Diagram Design

• Record info about teams, players, and their fans, including:
  • For each team, its name, its players, its team captain (who is also a player)
  • For each player, his/her name, and the history of teams on which he/she has played, including the start and ending dates for each team
  • For each fan, his/her name, favorite teams, favorite players
• Each team has at least one player and exactly one captain
• Each team has a unique name
• Two players (or two fans) may have the same name
• Each fan has at least one favorite team and at least one favorite player

This lecture discusses constraints in entity sets and relationships, including key constraints, referential integrity constraints, and degree constraints. It also covers the importance of keys for uniquely representing entities and maintaining relationship consistency between tables. Weak entity sets are also covered to have a better understanding of database concepts.