ZeePedia

Relationships:Types of Relationships in databases

<< Attributes, The Keys
Dependencies, Enhancements in E-R Data Model. Super-type and Subtypes >>
img
Database Management System (CS403)
VU
Lecture No. 09
Reading Material
"Database Systems Principles, Design and Implementation"
written by Catherine Ricardo, Maxwell Macmillan.
Overview of Lecture
o Relationships in E-R Data Model
o Types of Relationships
Relationships
After two or more entities are identified and defined with attributes, the participants
determine if a relationship exists between the entities. A relationship is any association,
linkage, or connection between the entities of interest to the business; it is a two-
directional, significant association between two entities, or between an entity and itself.
Each relationship has a name, an optionality (optional or mandatory), and a degree (how
many). A relationship is described in real terms.
Assigning a name, optionality, and a degree to a relationship helps confirm the validity of
that relationship. If you cannot give a relationship all these things, then perhaps there
really is no relationship at all.
Relationship represents an association between two or more entities. An example of a
relationship would be:
·
Employees are assigned to projects
·
Projects have subtasks
·
Departments manage one or more projects
Relationships are the connections and interactions between the entities instances e.g.
DEPT_EMP associates Department and Employee.
·
A relationship type is an abstraction of a relationship i.e. a set of relationships
instances sharing common attributes.
85
img
Database Management System (CS403)
VU
·
Entities enrolled in a relationship are called its participants.
The participation of an entity in a relationship is total when all entities of that set might
be participant in the relationship otherwise it is partial e.g. if every Part is supplied by a
Supplier then the SUPP_PART relationship is total. If certain parts are available without
a supplier than it is partial.
Naming Relationships:
If there is no proper name of the association in the system then participants' names of
abbreviations are used. STUDENT and CLASS have ENROLL relationship. However, it
can also be named as STD_CLS.
Roles:
Entity set of a relationship need not be distinct. For example
phone
name
city
SSN
manager
employee
works-for
worker
The labels "manager" and "worker" are called "roles". They specify how employee
entities interact via the "works-for" relationship set. Roles are indicated in ER diagrams
by labeling the lines that connect diamonds to rectangles. Roles are optional. They clarify
semantics of a relationship.
Symbol for Relationships:
·
Shown as a Diamond
·
Diamond is doubled if one of the participant is dependent on the other
·
Participants are connected by continuous lines, labeled to indicate cardinality.
·
In partial relationships roles (if identifiable) are written on the line connecting the
partially participating entity rectangle to the relationship diamond.
·
Total participation is indicated by double lines
86
img
Database Management System (CS403)
VU
Types of Relationships
o Unary Relationship
An ENTITY TYPE linked with itself, also called recursive relationship. Example
Roommate, where STUDENT is linked with STUDENT
Example 1:
Roommate
1:1
Student
87
img
Database Management System (CS403)
VU
Example
2:
Sponsored
1:1
Person
o Binary relationship
A Binary relationship is the one that links two entities sets e.g. STUDENT-CLASS.
Relationships can be formally described in an ordered pair form.
Enroll = {(S1001, ART103A), (S1020, CS201A), (S1002, CSC201A)}
Entire set is relationship set and each ordered pair is an instance of the relationship.
M:N
Student
Class
o Ternary Relationship
A Ternary relationship is the one that involves three entities e.g.
STUDENT-CLASS-FACULTY.
88
img
Database Management System (CS403)
VU
o N-ary Relationship
Most relationships in data model are binary or at most ternary but we could define a
relationship set linking any number of entity sets i.e. n-ary relationship
Entity sets involved in a relationship set need not be distinct. E.g.
Roommate = {(Student1, Student2) | Student1 Student Entity Set, Student2 Student
Entity Set and Student 1 is the Roommate of Student2}
Relationship Cardinalities
The cardinality of a relationship is the number of entities to which another entity can map
under that relationship. Symbols for maximum and minimum cardinalities are:
Maximum
inside
Entity Type
Minimum
Outside
o One-to-One mapping:
A mapping R from X to Y is one-to-one if each entity in X is associated with at most
one entity in Y and vice versa.
o Many-to-One mapping:
A mapping R from X to Y is many-to-one if each entity in X is associated with at
most one entity in Y but each entity in Y is associated with many entities in X.
89
img
Database Management System (CS403)
VU
o One-to-Many mapping:
A mapping R from X to Y is one-to-many if each entity in X is associated with many
entities in Y but each entity in Y is associated with one entity in X.
o Many-to-Many mapping:
A mapping R from X to Y is many-to-many if each entity from X is associated with
many entities in Y and one entity in Y is associated with many entities in X.
90
Table of Contents:
  1. Introduction to Databases and Traditional File Processing Systems
  2. Advantages, Cost, Importance, Levels, Users of Database Systems
  3. Database Architecture: Level, Schema, Model, Conceptual or Logical View:
  4. Internal or Physical View of Schema, Data Independence, Funct ions of DBMS
  5. Database Development Process, Tools, Data Flow Diagrams, Types of DFD
  6. Data Flow Diagram, Data Dictionary, Database Design, Data Model
  7. Entity-Relationship Data Model, Classification of entity types, Attributes
  8. Attributes, The Keys
  9. Relationships:Types of Relationships in databases
  10. Dependencies, Enhancements in E-R Data Model. Super-type and Subtypes
  11. Inheritance Is, Super types and Subtypes, Constraints, Completeness Constraint, Disjointness Constraint, Subtype Discriminator
  12. Steps in the Study of system
  13. Conceptual, Logical Database Design, Relationships and Cardinalities in between Entities
  14. Relational Data Model, Mathematical Relations, Database Relations
  15. Database and Math Relations, Degree of a Relation
  16. Mapping Relationships, Binary, Unary Relationship, Data Manipulation Languages, Relational Algebra
  17. The Project Operator
  18. Types of Joins: Theta Join, Equi–Join, Natural Join, Outer Join, Semi Join
  19. Functional Dependency, Inference Rules, Normal Forms
  20. Second, Third Normal Form, Boyce - Codd Normal Form, Higher Normal Forms
  21. Normalization Summary, Example, Physical Database Design
  22. Physical Database Design: DESIGNING FIELDS, CODING AND COMPRESSION TECHNIQUES
  23. Physical Record and De-normalization, Partitioning
  24. Vertical Partitioning, Replication, MS SQL Server
  25. Rules of SQL Format, Data Types in SQL Server
  26. Categories of SQL Commands,
  27. Alter Table Statement
  28. Select Statement, Attribute Allias
  29. Data Manipulation Language
  30. ORDER BY Clause, Functions in SQL, GROUP BY Clause, HAVING Clause, Cartesian Product
  31. Inner Join, Outer Join, Semi Join, Self Join, Subquery,
  32. Application Programs, User Interface, Forms, Tips for User Friendly Interface
  33. Designing Input Form, Arranging Form, Adding Command Buttons
  34. Data Storage Concepts, Physical Storage Media, Memory Hierarchy
  35. File Organizations: Hashing Algorithm, Collision Handling
  36. Hashing, Hash Functions, Hashed Access Characteristics, Mapping functions, Open addressing
  37. Index Classification
  38. Ordered, Dense, Sparse, Multi-Level Indices, Clustered, Non-clustered Indexes
  39. Views, Data Independence, Security, Vertical and Horizontal Subset of a Table
  40. Materialized View, Simple Views, Complex View, Dynamic Views
  41. Updating Multiple Tables, Transaction Management
  42. Transactions and Schedules, Concurrent Execution, Serializability, Lock-Based Concurrency Control, Deadlocks
  43. Incremental Log with Deferred, Immediate Updates, Concurrency Control
  44. Serial Execution, Serializability, Locking, Inconsistent Analysis
  45. Locking Idea, DeadLock Handling, Deadlock Resolution, Timestamping rules