DBMS-4,Entiry Relationship Model - Part II

Entiry Relationship Model - Part II

A relationship set consists of all relations of a given relationship type. Relationships may be binary, linking two entities, ternary, linking three entities, or n-ary, linking n entities. Binary relationship instances can be represented as ordered pairs, ternary instances as ordered triples, and n-ary instances as ordered n-tuples. A relationship set is a subset of Cartesian product of the related entity sets. A relationship set always has primary keys of the related entity sets as attributes, but some also has descriptive attributes as well. Descriptive attributes appear in an oval connected to the relationship diamond.   Relationships have cardinality constraints, which specify how many entity instances may be related.  These may be one-to-one, one-to-many, many-to-one, or many-to-many.  In a relationship, each entity has a function called its role in the relationship.  When a single entity is related to itself then the relationship is recursive.

In student-Course-Enroll database, two entity sets are student and course. The entity set student is described with four attributes, namely rollno, name, address and  city. The entity set course is described with course-no, and Course-name and credits attributes.

 SAMPLE ER DIAGRAM  

This is a typical Entity-Relationship diagram representing Conceptual Model of a university database. The university has four entities – Student, Department, Class and Faculty. Each of these has several attributes like a student has a name (StuName), a faculty has a designation (Rank) etc.   
The entities and attributes are:

Student: StuId, StuName, Major, and Credits.

Assumption: Each student has a unique ID and has at most one major.

Department: DeptName, Office.

Assumption:  Each department has a unique name and that each department has one office designated as departmental office.

Faculty: FacId, FacName, DeptName, Rank.

Assumption: FacId is unique and that every faculty member must belong to a department, so faculty is a weak entity with referential dependent on Department.

Class: Course#, Ctitle, FacId, Sched, Room.

Assumptions: The course consists of a department code, a number that identifies the course within the department, and a section code. Also each class is taught by only one faculty member.

Four relationships – Enroll, Dept-Student, Dept-Faculty, Faculty-Class

Dept-Student: is a one-to-many relationship that connects to their major departments.

Assumption: Students have only one major.

Enroll: is a many-many relationship that connects students to the classes in which they are enrolled.

Assumption: Only current enrollments are kept in the database. Grade is a descriptive attribute for this relationship set.

Dept-Faculty: is a one-to-many relationship that connects departments to faculty members assigned to them.

In the above recursive relationship,

Faculty-Class: is a one-to-many relationship that connects faculty members to the Class they teach.

Chair-Member: is a one-to-many recursive relationship on faculty that connects the chairperson of each department to the members of that department.

ER diagram for composite, multi-valued, derived and single attribute for an entity customer is shown below:

 

n the above diagram, age is a derived attribute, phone number is multi valued attribute and name is composite attribute, etc.

Roles:

Entity sets of a relationship need not be distinct. The labels “manager” and “worker” are called roles; they specify how employee entities interact via the works-for relationship set. Roles are indicated in E-R diagrams by labeling the lines that connect diamonds to rectangles. Role labels are optional, and are used to clarify semantics of the relationship. The following is another example for defining roles.

 Participation of entity set in relationship set can be either total or partial.  Total participation is denoted by double line. When every entity in the entity set participates in at least one relationship in the relationship set, then it is called total.  In Partial participation, some entities may not participate in any relationship in the relationship set.

For further reading, on this topic, readers  are advised to refer  Chapter 7 from the  prescribed book entitled “ Database System Concepts”,  by Abraham Silberschatz, Henry F Korth and S Sudarshan, Sixth edition, Mc Graw Hill International edition, 2011.