Participation Constraints
Participation constraints play a crucial role in defining the relationship between entities in a database schema. Total vs. partial participation, weak entities, and key constraints are essential concepts to consider when designing database structures. Explore these concepts through examples and illustrations to enhance your understanding of database relationships.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
Participation Constraints Consider again the Employees and Departments entity sets as well as the Manages relationship set Should every department have a manager? If so, this is a participation constraint Such a constraint entails that every Departments entity must appear in an instance of the Manages relationship The participation of Departments in Manages is said to be total (vs. partial) since since name name dname dname ssn did did budget budget lot partial Departments Employees Manages Works_In Total participation + key constraint are denoted by a double lines Total participation is denoted by a double lines since
Total vs. Partial Participations Participation Constraints Total Participation Each entity is involved in the relationship. Total participation is represented by double lines. Partial participation Not all entities are involved in the relationship. Partial participation is represented by single lines.
Total vs. Partial Participations The relationship shown below means that each student, without exception, must be guided by one chosen professor, and one but not every professor can guide many students. So there is no student that is not guided by a professor, and on the other hand there can be professors who don't guide any students
Weak Entities A weak entity can be identified uniquely only by considering the primary key of another (owner) entity Owner entity set and weak entity set must participate in a one-to- many relationship set (one owner, many weak entities) Weak entity set must have total participation in this identifying relationship set The set of attributes of a weak entity set that uniquely identifies a weak entity for a given owner entity is called partial key. An entity type should have a key attribute which uniquely identifies each entity in the entity set, but there exists some entity type for which key attribute can t be defined. These are called Weak Entity type.
Weak Entities: An Example Loan has no unique key of its own Loan is a weak entity with partial key loan name Borrows is an identifying relationship set loan name + Cust_ID are the primary key of Loan
ISA (`is a) Hierarchies Entities in an entity set can sometimes be classified into subclasses (this is kind of similar to OOP languages) If we declare B ISA A, every B entity is also considered to be an A entity name ssn lot Hourly_Emps and Contract_Emps are generalized by Employees Employees is specialized into subclasses Employees hours_worked hourly_wages ISA contractid Contract_Emps Hourly_Emps
Extensions to the Model: Specialization and Generalization n An Example: Customers can have checking and savings accts Checking ~ Savings (many of the same attributes) n Old Way: Has1 Savings Accts Customers balance interest acct_no Has2 Checking Accts balance overdraft acct_no
Extensions to the Model: Specialization and Generalization n An Example: Customers can have checking and savings accts Checking ~ Savings (many of the same attributes) n New Way: balance acct_no superclass Accounts Has Customers ISA Checking Accts Savings Accts overdraft interest subclasses
Overlap and Covering Constraints Overlap constraints Can an entity belong to both B and C ? A Covering constraints Can an A entity belong to neither B nor C ? B C
More Details on ISA Hierarchies Attributes are inherited (i.e., if B ISA A, the attributes defined for a B entity are the attributes for A plus B) We can have many levels of an ISA hierarchy Reasons for using ISA: To add descriptive attributes specific to a subclass To identify entities that participate in a relationship
E/R Data Model Extensions to the Model: Aggregation n E/R: No relationships between relationships E.g.: Associate loan officers with Borrows relationship set Loans Customers Borrows ? Loan_Officer Employees n Associate Loan Officer with Loan? What if we want a loan officer for every (customer, loan) pair?
E/R Data Model Extensions to the Model: Aggregation n E/R: No relationships between relationships E.g.: Associate loan officers with Borrows relationship set Loans Customers Borrows Loan_Officer Employees n Associate Loan Officer with Borrows? Must First Aggregate
Aggregation Aggregation allows indicating that a relationship set (identified through a dashed box) participates in another relationship set name ssn lot Aggregation allows us to treat a relationship set as an entity set for purposes of participation in (other) relationships. Employees Monitors until since started_on dname pid pbudget did budget Sponsors Departments Projects
Conceptual Design Choices Conceptual designs help to create a clear user interface which is easy to understand and interpret. It helps to describe the roles of different users and their requirements in detail so that the project is better understood. Should a concept be modeled as an entity or a relationship? How should we identify relationships? Binary or ternary? Ternary or aggregation? Constraints in the ER Model: A lot of data semantics can (and should) be captured But some constraints cannot be captured in ER diagrams
Entity vs. Attribute Should address be an attribute of Employees or an entity (connected to Employees by a relationship)? This depends upon the use we want to make of address information, and the semantics of the data If we have several addresses per an employee, address must be an entity (since attributes cannot be set-valued) If the structure (city, street, etc.) is important (e.g., we want to retrieve employees in a given city), address must be modeled as an entity
Entity vs. Attribute (Contd) Consider the following ER diagram: to from name dname ssn lot did budget Departments Works_In Employees A problem: Works_In does not allow an employee to work in a department for two or more periods Similar to the problem of wanting to record several addresses for an employee: We want to record several values of the descriptive attributes for each instance of this relationship. Accomplished by introducing new entity set, Duration.
Entity vs. Attribute (Contd) Solution: introduce Duration as a new entity set name dname ssn lot did budget Works_In4 Departments Employees Duration to from
Entity vs. Relationship since dbudget name dname ssn lot did budget Departments Employees Manages Consider the ER diagram, whereby a manager gets a separate optional budget for each department ER diagram OK if a manager gets a separate optional budget for each dept. What if a manager gets a optional budget that covers all managed depts? Redundancy: dbudget stored for each dept managed by manager. Misleading: Suggests dbudget associated with department-mgr combination.
This fixes the problem! name lot ssn dname since did Employees budget Departments Manages2 ISA Managers dbudget
Binary vs. Ternary Relationships But sometimes ternary relationships cannot be replaced by a set of binary relationships qty VS. Parts Contract Departments Suppliers
Aggregation Aggregation allows indicating that a relationship set (identified through a dashed box) participates in another relationship set name ssn lot Employees Monitors until since started_on dname pid pbudget did budget Sponsors Departments Projects
Ternary vs. Aggregation Relationships When to use aggregation? If we want to attach a relationship to a relationship name ssn lot Employees What if we do not want to record the until attribute of Monitors relationship? Monitors until since started_on dname pid pbudget did budget Sponsors Departments Projects
Ternary vs. Aggregation Relationships (Cont d) We might reasonably use a ternary relationship instead of an aggregation name ssn lot Employees started_on dname pid pbudget did budget Sponsors2 Departments Projects What if each sponsorship (of a project by a department) is to be monitored by at most one employee?
![Read⚡ebook✔[PDF] Life in the Universe: Expectations and Constraints (Springer P](/thumb/21626/read-ebook-pdf-life-in-the-universe-expectations-and-constraints-springer-p.jpg)
