UNIT 1 INTRODUCTION TO DBMS, ER DIAGRAM, RELATION

UNIT 1 INTRODUCTION TO DBMS

1. Unit- I Part B Entity Relationship Model Prof. Bhakti B Pawar Assistant Professor

2. Department of Information Technology, Sanjivani COE Kopargaon What is Data Model? A data model is a collection of conceptual tools for describing data, data relationships, data semantics, and consistency constraints. Data model used to describe the database system at the requirements collection stage high level description. easy to understand for the enterprise managers. rigorous enough to be used for system building.

3. Department of Information Technology, Sanjivani COE Kopargaon Data Model The entity–relationship (E- R) model is a high- level data model. It consists of a collection of basic objects, called entities, and of relationships among these objects. The relational model is a lower- level model. It uses a collection of tables to represent both data and the relationships among those data. Due to its conceptual simplicity, majority of database products are based on the relational model. Designers often formulate database schema design by first modeling data at a high level, using the E-R model, and then translating it into the relational model.

4. Department of Information Technology, Sanjivani COE Kopargaon E- R Model Stands for an Entity- Relationship model High- level data model: Used to define the data elements and relationship for a specified system. Widely used conceptual level Data Model It develops a conceptual design for the database. It also develops a very simple and easy to design view of data. In ER modeling, the database structure is portrayed as a diagram called an entity- relationship diagram. Concepts available in the model Entities Attributes of entities. Relationships between entities. The ER model also has an associated diagrammatic representation, the ER diagram, which can express the overall logical structure of a database graphically.

5. Department of Information Technology, Sanjivani COE Kopargaon Entity Entity - a thing of independent physical or conceptual existence and distinguishable. In the University database context, an individual student, faculty member, a class room, a course are entities. Entity Set or Entity Type: It is a set of entities of the same type that share the same properties, or attributes. Student entity set – collection of all student entities. Course entity set – collection of all course entities. An entity is represented by a set of attributes: Descriptive properties possessed by all members of an entity set. Example: instructor = (ID, name, salary ) course= (course_id, title, credits) Each entity has a value for each of its attributes.

6. Department of Information Technology, Sanjivani COE Kopargaon Attribute Each entity is described by a set of attributes/properties that have associated values and information student entity StudName – name of the student. RollNumber – the roll number of the student. Mobile – Mobile nos of the student etc. All entities in an Entity set/type have the same set of attributes. For each attribute, there is a set of permitted values, called the domain, or value set, of that attribute. The domain of attribute customer- name might be the set of all text strings of a certain length. loan-number might be the set of all strings of the form “L-n” where n is a positive integer

7. Database A database thus includes a collection of entity sets, each of which contains any number of entities of the same type. Figure shows part of a bank database that consists of two entity sets: customer and loan An attribute of an entity set is a function that maps from the entity set into a domain. An entity set may have several attributes, Each entity can be described by a set of (attribute, data value) pairs, one pair for each attribute of the entity set. For example, a particular customer entity may be described by the set {(customer- id, 677- 89- 9011), (customer- name, Hayes), (customer- street, Main)} Department of Information Technology, Sanjivani COE Kopargaon

8. Types of Attributes (1/2) Simple Attributes: having atomic or indivisible values. Example: Dept_Name– a string PhoneNumber– a ten digit number Composite Attributes: having several components in the value. divided attributes into subparts (other attributes). a composite attribute may appear as a hierarchy (Address) Example: Qualification with components- (DegreeName, Year, UniversityName) Land_line_no: (stdcode,areano,actualno) Address: Department of Information Technology, Sanjivani COE Kopargaon

9. Department of Information Technology, Sanjivani COE Kopargaon Types of Attributes (2/3) Derived Attributes : Attribute value is dependent on some other attribute. Example: Age depends on DateofBirth: So age is a derived attribute. Interest amount: Based on Loan Amount Null Value: An attribute takes a null value when an entity does not have a value for it. Example: Middle Name Null can also designate that an attribute value is unknown. An unknown value may be either missing (the value does exist, but we do not have that information) not known (we do not know whether or not the value actually exists).

10. Department of Information Technology, Sanjivani COE Kopargaon Types of Attributes (3/3) Single-valued: having only one value rather than a set of values. for instance, PlaceOfBirth – single string value. Multi-valued: having a set of values rather than a single value. for instance: CoursesEnrolled attribute for student (all courses in semester) EmailAddress attribute for student (personal email, official email) PreviousDegree attribute for student. Attributes can be: Simple single- valued Simple multi- valued Composite single- valued Composite multi- valued.

11. Notations for Entities Entity: Rectangle Attributes: Ellipse connected to Rectangle Multivalued Attributes: Double Ellipse Composite Attributes: Ellipse Connected to ellipse Derived Attributes: Dashed Ellipse Department of Information Technology, Sanjivani COE Kopargaon

12. Department of Information Technology, Sanjivani COE Kopargaon Domains of Attributes Each attribute takes values from a set called its domain For instance, studentAge – {17,18, …, 55} HomeAddress – character strings of length 35 Domain of composite attributes: cross product of domains of component attributes Domain of multi- valued attributes: set of subsets of values from the basic domain

13. Department of Information Technology, Sanjivani COE Kopargaon Entity Sets and Key Attributes Key: an attribute or a collection of attributes whose value(s) uniquely identify an entity in the entity set. Example: RollNumber - Key for Student entity set EmpID - Key for Faculty entity set HostelName, RoomNo - Key for Student entity set A key for an entity set may have more than one attribute. An entity set may have more than one key. Keys can be determined only from the meaning of the attributes in the entity type. Determined by the designers

14. Department of Information Technology, Sanjivani COE Kopargaon Relationship Sets A relationship is an association among several entities. For example, we can define a relationship that associates customer Hayes with loan L- 15. This relationship specifies that Hayes is a customer with loan number L- 15. A relationship set is a set of relationships of the same type. Formally, it is a mathematical relation on n ≥ 2 (possibly nondistinct) entity sets. If E1, E2,...,En are entity sets, then a relationship set R is a subset of where (e1, e2,...,en) is a relationship. {(e1, e2,...,en) | e1 ∈ E1, e2 ∈ E2,...,en ∈ En

15. Relationship Sets We define the relationship set borrower to denote the association between customers and the bank loans that the customers have loan- branch to denote the association between a bank loan and the branch in which that loan is maintained. The association between entity sets is referred to as participation ; that is, the entity sets E1, E2,...,En participate in relationship set R. A relationship instance in an E- R schema represents an association between the named entities in the real- world enterprise that is being modeled Example: The individual customer entity Hayes, who has customer identifier 677-89- 9011, and the loan entity L- 15 participate in a relationship instance of borrower . A relationship may also have attributes called descriptive attributes Entity sets student and course which participate in a relationship set registered- for Department of Information Technology, Sanjivani COE Kopargaon

16. Department of Information Technology, Sanjivani COE Kopargaon Relationships (1/2) When two or more entities are associated with each other, we have an instance of a Relationship. E.g.: student Ramesh enrolls in Discrete Mathematics course Relationship enrolls has Student and Course as the participating entity sets. Formally, enrolls ⊆ Student × Course (s,c) ∈ enrolls ⇔ Student ‘s’ has enrolled in Course ‘c’ Tuples in enrolls – relationship instances enrolls is called a relationship Type/Set

17. Relationships (2/2) Relationship: diamond shaped box Rectangle of each participating entity is connected by a line to this diamond. Name of the relationship is written in the box. Degree of Relationship: the number of participating entities. Degree 2: binary : one that involves two entity sets. Degree 3: ternary: The relationship set works- on among employee , branch , and job is an example of a ternary relationship. Degree n: n-ary Binary relationships are very common and widely used. Department of Information Technology, Sanjivani COE Kopargaon

18. Department of Information Technology, Sanjivani COE Kopargaon Constraints: Cardinality Ratios Mapping cardinalities or Cardinality Ratios: Express the number of entities to which another entity can be associated via a relationship set. For a binary relationship set the mapping cardinality must be one of the following types: One-to- one: An E1 entity may be associated with at most one E2 entity and similarly an E2 entity may be associated with at most one E1 entity. One-to-many : An E1 entity may be associated with many E2 entities whereas an E2 entity may be associated with at most one E1 entity. Many-to- one: An E2 entity may be associated with many E1 entities whereas an E1 entity may be associated with at most one E2 entity. Many-to-many: Many E1 entities may be associated with a single E2 entity and a single E1 entity may be associated with many E2 entities.

19. Mapping Cardinalities Department of Information Technology, Sanjivani COE Kopargaon

20. Representing Cardinality Constraints in ER Diagram We express cardinality constraints by drawing either a directed line (- >), signifying “one,” an undirected line (— ), signifying “many,” between the relationship set and the entity set. One-to- one relationship between an instructor and a student : A student is associated with at most one instructor via the relationship advisor A student is associated with at most one department via stud_dept Department of Information Technology, Sanjivani COE Kopargaon

21. One- to- Many Relationship One-to- Many relationship between an instructor and a student an instructor is associated with several (including 0) students via advisor a student is associated with at most one instructor via advisor Department of Information Technology, Sanjivani COE Kopargaon

22. Many- to- One Relationships Many-to- One relationship between an instructor and a student An instructor is associated with at most one student via advisor, A student is associated with several (including 0) instructors via adviso Department of Information Technology, Sanjivani COE Kopargaon

23. Many- to- Many Relationships Many-to- Many relationship between an instructor and a student An instructor is associated with several (possibly 0) students via advisor A student is associated with several (possibly 0) instructors via advisor Department of Information Technology, Sanjivani COE Kopargaon

24. Reflection Quiz!! Department of Information Technology, Sanjivani COE Kopargaon

25. Reflection Quiz!! Department of Information Technology, Sanjivani COE Kopargaon

26. Constraints: Participation Constraints (1/2) An entity set may participate in relation either totally or partially Total participation (indicated by double line): The participation of an entity set E in a relationship set R is said to be total if every entity in E participates in at least one relationship in R. Participate in some association in tuple or relationship Partial participation : If only some entities in E participate in relationships in R, the participation of entity set E in relationship R is said to be partial Not all entities involved in association or tuple of relationship Department of Information Technology, Sanjivani COE Kopargaon

27. Participation Constraints (1/2) Participation of instructor in advisor is partial Participation of student in advisor relation is total Every student must have an associated instructor Department of Information Technology, Sanjivani COE Kopargaon

28. Department of Information Technology, Sanjivani COE Kopargaon Keys Conceptually, individual entities are distinct A key allows us to identify a set of attributes that suffice to distinguish entities from each other. Keys also help uniquely identify relationships, and thus distinguish relationships from each other.

29. Department of Information Technology, Sanjivani COE Kopargaon Key: Super Key A super key :is a set of one or more attributes that collectively, allow to identify uniquely an entity in the entity set. For example, the customer- id attribute of the entity set customer is sufficient to distinguish one customer entity from another. Thus, customer- id is a superkey. Similarly, the combination of customer- name and customer- id is a superkey for the entity set customer. The customer- name attribute of customer is not a superkey, because several people might have the same name The concept of a super key is not sufficient: a super key may contain extraneous attributes.

30. Department of Information Technology, Sanjivani COE Kopargaon Key: Candidate Key Super keys for which no proper subset is a super key. Such minimal super keys are called candidate keys. It is possible that several distinct sets of attributes could serve as a candidate key. Suppose that a combination of customer- name and customer- street is sufficient to distinguish among members of the customer entity set. Then, both {customer- id} and {customer- name, customer- street} are candidate keys. Although the attributes customer_id and customer- name together can distinguish customer entities, their combination does not form a candidate key, since the attribute customer- id alone is a candidate key.

31. Department of Information Technology, Sanjivani COE Kopargaon Keys Use the term primary key to denote a candidate key that is chosen by the database designer for identifying entities within an entity set. A key (primary, candidate, and super) is a property of the entity set, rather than of the individual entities. Any two individual entities in the set are prohibited from having the same value on the key attributes at the same time.

32. Department of Information Technology, Sanjivani COE Kopargaon Entity- Relationship Diagram Rectangles: which represent entity sets Ellipses: which represent attributes Diamonds: which represent relationship sets Lines: which link attributes to entity sets and entity sets to relationship sets Double ellipses: which represent multivalued attributes Dashed ellipses: which denote derived attributes Double lines: which indicate total participation of an entity in a relationship set Double rectangles: which represent weak entity sets

33. The relationship set borrower may be many- to- many, one- to- many, many- to- one, or one- to-one. An undirected line from the relationship set borrower to the entity set loan specifies that borrower is either a many-to- many or one-to- many relationship set from customer to loan A directed line from the relationship set borrower to the entity set loan specifies that borrower is either a one-to- one or many-to- one relationship set, from customer to loan; borrower cannot be a many-to- many or a one-to- many relationship set from customer to loan. Department of Information Technology, Sanjivani COE Kopargaon

34. Department of Information Technology, Sanjivani COE Kopargaon

35. If a relationship set has also some attributes associated with it, then we link these attributes to that relationship set. E- R diagram with an attribute attached to a relationship set Department of Information Technology, Sanjivani COE Kopargaon

36. Exercise Draw the diagram for customer A composite attribute name, with component attributes first- name, middle- initial, and last- name replaces the simple attribute customer- name of customer. A composite attribute address, whose component attributes are street, city, state, and zip- code replaces the attributes customer- street and customer- city of customer. The attribute street is itself a composite attribute whose component attributes are street- number, street-name, and apartment number. Multivalued attribute phone- number, Derived attribute age, Department of Information Technology, Sanjivani COE Kopargaon

37. Roles: Indicate roles in E- R diagrams by labeling the lines that connect diamonds to rectangles. Example: role indicators manager and worker between the employee entity set and the works- for relationship set. Department of Information Technology, Sanjivani COE Kopargaon

38. Ternary Relationship Department of Information Technology, Sanjivani COE Kopargaon

39. Department of Information Technology, Sanjivani COE Kopargaon Structural Constraints Cardinality Ratio and Participation Constraints are together called Structural Constraints. They are called constraints as the data must satisfy them to be consistent with the requirements. Min- Max notation: pair of numbers (m,n) placed on the line connecting an entity to the relationship. m: the minimum number of times a particular entity must appear in the relationship tuples at any point of time – partial participation ≥ 1 – total participation n: similarly, the maximum number of times a particular entity can appear in the relationship tuples at any point of time

40. Let’s Check understanding Draw a diagram for Library Management system Department of Information Technology, Sanjivani COE Kopargaon

41. Bank Database e. Bank have Customer. Banks are identified by a name, code, address of main office. Banks have branches. Branches are identified by a branch_no., branch_name, address. Customers are identified by name, cust- id, phone number, address. Customer can have one or more accounts. Accounts are identified by account_no., acc_type, balanc Customer can avail loans. Loans are identified by loan_id, loan_type and amount. Account and loans are related to bank’s branch. Department of Information Technology, Sanjivani COE Kopargaon

42. Department of Information Technology, Sanjivani COE Kopargaon Extended E- R Features Specialization Generalization

43. Department of Information Technology, Sanjivani COE Kopargaon Specialization An entity set may include subgroupings of entities that are distinct in some way from other entities in the set. For instance, a subset of entities within an entity set may have attributes that are not shared by all the entities in the entity set. The E- R model provides a means for representing these distinctive entity groupings. An entity set person {name, street, and city} further classified as customer employe The process of designating subgroupings within an entity set is called specialization . The specialization of person allows us to distinguish among persons according to whether they are employees or customers.

44. Specialization Specialization is depicted by a triangle component labeled ISA ,stands for is- a a customer “is a” person. The ISA relationship may also be referred to as a superclass-subclass relationship. Higher and lower-level entity sets are depicted as regular entity set: rectangles containing the name of the entity set. Example: bank employees Officer Teller secretary Account: attributes account- number and balance. savings- account and checking-account. savings- account: All the attributes of account and interest-rate. Checkingaccount: all the attributes of account, overdraftamount. Department of Information Technology, Sanjivani COE Kopargaon

45. Department of Information Technology, Sanjivani COE Kopargaon Generalization There are similarities between the customer entity set and the employee entity set in the sense that they have several attributes in common. This commonality can be expressed by generalization, which is a containment relationship that exists between a higher- level entity set and one or more lower- level entity sets. Person is the higher- level entity set and customer and employee are lower-level entity sets. Higher- and lower- level entity sets also may be designated by the terms superclass and subclass, respectively. The person entity set is the superclass of the customer and employee subclasses. Generalization is a simple inversion of specialization

46. Department of Information Technology, Sanjivani COE Kopargaon Constraint A higher- level entity set with attributes and relationships that apply to all of its lower- level entity sets Lower- level entity sets with distinctive features that apply only within a particular lower- level entity set Constraint Condition defined- userdefined Disjoint- Overlapping Total - Partial

47. Department of Information Technology, Sanjivani COE Kopargaon Constraint Condition- defined : Lower- level entity sets, membership is evaluated on the basis of whether or not an entity satisfies an explicit condition or predicate. Account has the attribute account- type All account entities are evaluated on the defining account- type attribute. Only those entities that satisfy the condition account- type = “savings account” are allowed to belong to the lower- level entity set person. All entities that satisfy the condition account- type = “checking account” are included in checking account. Since all the lower- level entities are evaluated on the basis of the same attribute this type of generalization is said to be attribute-defined. User- defined: Lower- level entity sets are not constrained by a membership condition; rather, the database user assigns entities to a given entity set

48. Department of Information Technology, Sanjivani COE Kopargaon Constraint Constraint relates to whether or not entities may belong to more than one lower-level entity set within a single generalization. Disjoint: A disjointness constraint requires that an entity belong to no more than one lower-level entity set. Example: an account entity can satisfy only one condition for the account- type attribute either a savings account or a checking account, but cannot be both. Overlapping: The same entity may belong to more than one lower-level entity set within a single generalization. Example: Consider the employee work team example, and assume that certain managers participate in more than one work team. A given employee may therefore appear in more than one of the team entity sets that are lower- level entity sets of employee. Example: Generalization applied to entity sets customer and employee leads to a higher- level entity set person. The generalization is overlapping if an employee can also be a customer.

49. Department of Information Technology, Sanjivani COE Kopargaon Completeness constraint Completeness constraint on a generalization or specialization: specifies whether or not an entity in the higher- level entity set must belong to at least one of the lower- level entity sets within the generalization/specialization. Total generalization or specialization: Each higher- level entity must belong to a lower- level entity set Shown by using a double line to connect the box representing the higher-level entity set to the triangle symbol The account generalization is total: All account entities must be either a savings account or a checking account. Partial generalization or specialization: Some higher- level entities may not belong to any lower- level entity set. Partial generalization is the default.