Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

The Entity-Relationship (ER) Model, Exercises of Database Management Systems (DBMS)

This is an example of a many-to-one constraints, that is, many students can be advised by one professor, but each student can only have (at most) one advisor.

Typology: Exercises

2021/2022

Uploaded on 09/27/2022

damyen
damyen 🇺🇸

4.4

(27)

274 documents

1 / 24

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
The Entity-Relationship (ER)
Model
Week 1 - 2
Professor Jessica Lin
pf3
pf4
pf5
pf8
pf9
pfa
pfd
pfe
pff
pf12
pf13
pf14
pf15
pf16
pf17
pf18

Partial preview of the text

Download The Entity-Relationship (ER) Model and more Exercises Database Management Systems (DBMS) in PDF only on Docsity!

The Entity-Relationship (ER)

Model

Week 1 - 2

Professor Jessica Lin

The E-R Model

Entities and Entity Sets

  • An entity is an object that exists and is distinguishable from other objects. For instance, Michelle Lee with S.S.N. 890-12-3456 is an entity, as she can be uniquely identified as one particular person in the universe.
  • An entity may be concrete (a person or a book, for example) or abstract (like a holiday or a disease).
  • An entity set is a set of entities of the same type (e.g., all persons having an account at a bank).
  • Entity sets need not be disjoint. For example, the entity set Student (all students in a university) and the entity set professor (all professors in a university) may have members in common. (i.e a computer science professor might take a class in anthropology). 4
  • An entity is represented by a set of attributes. (e.g. name , SSN , Phone-Num for customer entity.)
  • The domain of the attribute is the set of permitted values (e.g. the telephone number must be ten positive integers).
  • Formally, an attribute is a function which maps an entity set into a domain.
  • Every entity is described by a set of (attribute, data value) pairs. There is one pair for each attribute of the entity set.
  • e.g. a particular student entity is described by the set {(name, Lee), (SSN, 890-123-456), (street, Blaine), (city, Riverside)}.

Entities and Entity Sets Continued

We can express the overall logical structure of a database graphically with an E-R diagram. Its components are:

  • rectangles representing entity sets.
  • ellipses representing attributes.
  • diamonds representing relationship sets.
  • lines linking attributes to entity sets and entity sets to relationship sets.

E-R diagrams Continued

Students name

S.S.N

City Street Professor name P.I.D advised by since The since attribute in this example is called a descriptive attribute , since it describes the mapping from A to B

We can also use arrows to indicate key constraints (often simply referred to as constraints )

Key Constraints

Students name

S.S.N

City Street Professor name P.I.D advised by since Suppose the university has the following rule: A student is allowed to be advised by at most one professor. However, a professor is allowed to advise more than one student. This is an example of a many-to-one constraints , that is, many students can be advised by one professor, but each student can only have (at most) one advisor. We can represent this with an arrow as shown below.

Key Constraints: Examples

  • One-to-one : An entity in A is associated with at most one entity in B, and an entity in B is associated with at most one entity in A. A man may be married to at most one woman, and a woman may be married to at most one man (both men and women can be unmarried) Is Married to Men name Women name Is Married to since This diagram is not a part of the ER model! It is just an intuitive picture to explain a concept 10

Key Constraints: Examples

  • One-to-many : An entity in A is associated with any number in B. An entity in B is associated with at most one entity in A. A woman may be the mother of many (or no) children. A person may have at most one mother. Women's Club name Low I.Q. Club name Is Mother of Born on Is mother of Note that this example is not saying that Moe does not have a mother, since we know as a biological fact that everyone has a mother. It is simply the case that Moe s mom is not a member of the Women s club. 11

Key Constraints: Examples

  • Many-to-many : Entities in A and B are associated with any number from each other. Girls name Boys name Is Classmate of Since Is Classmate of

Key Constraints

  • The arrow positioning is simple once you get it straight in your mind, so do some examples. Think of the arrow head as pointing to the entity that one refers to.
  • Some people use the term Mapping Cardinalities or “ Multiplicity ” to refer to key constraints.

Participation Constraints Cont.

Is Married to Men name Women name Is Married to since Participation Constraints are indicated by bold lines in ER diagrams. We can use bold lines (to indicate participation constraints), and arrow lines (to indicate key constraints) independently of each other to create an expressive language of possibilities.

More on Relations

  • Entities sets can be related to themselves. Students name Study Partner Course # Students name Study Partner Course # We can annotate the roles played by the entities in this case. Suppose that we want to pair a mature student with a novice student... Mature Novice When entities are related to themselves, it is almost always a good idea to indicate their roles.

Entity versus Attribute

Sometimes we have to decide whether a property of the world we want to model should be an attribute of an entity, or an entity set which is related to the attribute by a relationship set. A major advantage of the latter approach is that we can easily model the fact that a person can have multiple phones, or that a phone might be shared by several students. (attributes can not be set-valued) Student Name SID Phone Student Name SID Phone # Prefix Number Can be reached at Expires

Entity versus Attribute Cont.

A classic example of a feature that is best modeled as a an entity set which is related to the attribute by a relationship set is an address. Student Name SID Address Student Name SID Address Num^ Street Has Address City Student Name SID Num Street City Very bad choice for most applications. It would make it difficult to pretty print mailing labels, it would make it difficult to test validity of the data, it would make it difficult/impossible to do queries such as how many students live in Fairfax? A better choice, but it only allows a student to have one address. Many students have a two or more address (i.e. a different address during the summer months) This method cannot handle this. The best choice for this problem