1 / 61

Spring 2013 CS 103 Computer Science – Business Problems Lecture 14: Entity-Relationship Model

Spring 2013 CS 103 Computer Science – Business Problems Lecture 14: Entity-Relationship Model. Instructor: Zhe He Department of Computer Science New Jersey Institute of Technology. Entities. An entity is anything that can be identified by a fixed number of its characteristics (attributes)

temira
Download Presentation

Spring 2013 CS 103 Computer Science – Business Problems Lecture 14: Entity-Relationship Model

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Spring 2013 CS 103Computer Science –Business ProblemsLecture 14: Entity-Relationship Model Instructor: Zhe He Department of Computer Science New Jersey Institute of Technology

  2. Entities • An entity is anything that can be identified by a fixed number of its characteristics (attributes) • The attributes have names and values • The values are the data that is stored in the table • In relational databases, an attribute is a “column of a table”

  3. Entities The attribute’s name is the tag used in the Identity role The attribute values are the content enclosed in the tags An entity is a group of attributes collected together by the tag used in the Affinity role The entity is that object that is being described by all the tags

  4. Entities In addition to having a name, attributes also have a data type (such as number, text, image) The data type defines the form of the information that can be stored in a field By specifying the data type, database software prevents us from accidentally storing bad information in a table

  5. Entities and Table Entity defines a table...the name of the entity is the name of the table Each column is one of the possible attributes The values in the columns are the attributes’ values, and the rows are the entity instances A specific set of values for the attributes of an entity is an entity instance Any table containing specific rows is said to be a table instance

  6. Entities In addition to having a name, attributes also have a data type (such as number, text, image) The data type defines the form of the information that can be stored in a field By specifying the data type, database software prevents us from accidentally storing bad information in a table

  7. Properties of Entities • A relational database table can be empty • It is a table with no rows • An entity is anything defined by a specific set of attributes • A table exists with a name and column headings • Once entity instances have been specified, there will be rows • Among the instances of any table is the “empty instance”

  8. Instances Are Unordered Each distinct table is a different table instance Two table instances will have a different set of rows Tables with the same rows (but reordered) are the same table instance The order of the rows doesn’t matter in databases

  9. Instances Are Unordered • The attributes (columns) are also considered to be unordered • The attributes or column heading have a name, they are not tracked by position • Column information stays in columns • They cannot switch to being a row • Row information stays in rows • They cannot switch to being a column

  10. Uniqueness There are few limits on what an entity can be Things that can be identified from each other qualify as entities Entities can be distinguished by their attributes they are unique No two rows in a database table can be the same Unique instances is what is intended when a database is setup

  11. Keys • What attributes distinguish the rows in a database table? • Single attributes might be sufficient • Multiple attributes might be required to ensure uniqueness • Any set of attributes for which all entities are different is called a candidate key • Database tables usually have several candidate keys

  12. Keys One of the candidate keys is the primary key The primary key is the one that the database system will use to decide uniqueness Candidate keys qualify only if they distinguish among all entities forever If no combination of attributes qualifies as a candidate key, then a unique ID must be assigned to each entity

  13. Atomic Data • Databases treat the information as atomic • The information cannot be decomposed into smaller parts • The “only atomic data” rule is usually relaxed for certain types of data: • Dates, time, and currency • A date value 01/01/1970 must be treated as a single unity • The format of the data attribute, say dd/mm/yyyy, allows the program to understand how the field decomposes

  14. Database Schemes Tags are a cumbersome way to define a table Database systems specify a table as a database scheme ordatabase schema The scheme is a collection of table definitions that gives the name of the table, lists the attributes, their data types, and identifies the primary key

  15. Database Schemes Each database system has specific requirements for how a scheme is presented There are no universal rules

  16. An example of a table name manf Winterbrew Pete’s Bud Lite Anheuser-Busch Beers Attributes (column headers) Tuples (rows)

  17. Schemas Relation schema = relation name and attribute list. Optionally: types of attributes. Example: Beers(name, manf) or Beers(name: string, manf: string) Database = collection of relations. Database schema = set of all relation schemas in the database. 17

  18. Why Relations? Very simple model. Often matches how we think about data. Abstract model that underlies SQL, the most important database language today. 18

  19. From E/R Diagrams to Relations Entity set -> relation. Attributes -> attributes. Key -> Primary Key Relationships -> relations whose attributes are only: The keys of the connected entity sets. Attributes of the relationship itself. 19

  20. Entity Set -> Relation Relation: Beers(name, manf) name manf Beers 20

  21. Entity Set - Example Emp Job EmpNo Name Hire Date Comm Salary Emp EmpNoNumber(4) Name Varchar2(10) Job Varchar2(9) Sal Number(7,2) Comm Number(7,2) HireDate Date Primary Key 21

  22. Relationships A relationship connects two or more entity sets. It is represented by a diamond, with lines to each of the entity sets involved. 22

  23. Relationship -> Relation Likes husband Favorite Buddies Likes(drinker, beer) Favorite(drinker, beer) wife Buddies(name1, name2) Married Married(husband, wife) name name addr manf Drinkers Beers 23

  24. Example Must be the same At becomes part of Logins name name Logins At Hosts location billTo Hosts(hostName, location) Logins(loginName, hostName, billTo) At(loginName, hostName, hostName2) 24

  25. Example name addr name manf Bars Beers Sells Bars sell some beers. license Drinkers like some beers. Frequents Likes Note: license = beer, full, none Drinkers frequent some bars. Drinkers name addr 25

  26. Example For the relationship Sells, we might have a relationship set like: Bar Beer Joe’s Bar Bud Joe’s Bar Miller Sue’s Bar Bud Sue’s Bar Pete’s Ale Sue’s Bar Bud Lite 26

  27. Multi-way Relationships Sometimes, we need a relationship that connects more than two entity sets. Suppose that drinkers will only drink certain beers at certain bars. Our three binary relationships Likes, Sells, and Frequents do not allow us to make this distinction. But a 3-way relationship would. 27

  28. Example name addr name manf Bars Beers license Preferences Drinkers name addr 28

  29. A Typical Relationship Set Bar Drinker Beer Joe’s Bar Ann Miller Sue’s Bar Ann Bud Sue’s Bar Ann Pete’s Ale Joe’s Bar Bob Bud Joe’s Bar Bob Miller Joe’s Bar Cal Miller Sue’s Bar Cal Bud Lite 29

  30. Many-Many Relationships Focus: binary relationships, such as Sells between Bars and Beers. In a many-many relationship, an entity of either set can be connected to many entities of the other set. E.g., a bar sells many beers; a beer is sold by many bars. 30

  31. In Pictures: many-many 31

  32. Many-One Relationships Some binary relationships are many -one from one entity set to another. Each entity of the first set is connected to at most one entity of the second set. But an entity of the second set can be connected to zero, one, or many entities of the first set. 32

  33. In Pictures: many-one 33

  34. Example Favorite, from Drinkers to Beers is many-one. A drinker has at most one favorite beer. But a beer can be the favorite of any number of drinkers, including zero. 34

  35. One-One Relationships In a one-one relationship, each entity of either entity set is related to at most one entity of the other set. Example: Relationship Best-seller between entity sets Manfs (manufacturer) and Beers. A beer cannot be made by more than one manufacturer, and no manufacturer can have more than one best-seller (assume no ties). 35

  36. In Pictures: one-one 36

  37. Representing “Multiplicity” Show a many-one relationship by an arrow entering the “one” side. Show a one-one relationship by arrows entering both entity sets. Rounded arrow = “exactly one,” i.e., each entity of the first set is related to exactly one entity of the target set. 37

  38. Example Likes Drinkers Beers Favorite 38

  39. Example Consider Best-seller between Manfs and Beers. Some beers are not the best-seller of any manufacturer, so a rounded arrow to Manfs would be inappropriate. But a beer manufacturer has to have a best-seller. 39

  40. In the E/R Diagram Best- seller Manfs Beers 40

  41. Attributes on Relationships Sometimes it is useful to attach an attribute to a relationship. Think of this attribute as a property of tuples in the relationship set. 41

  42. Example Sells Bars Beers price Price is a function of both the bar and the beer, not of one alone. 42

  43. Keys A key is a set of attributes for one entity set such that no two entities in this set agree on all the attributes of the key. It is allowed for two entities to agree on some, but not all, of the key attributes. We must designate a key for every entity set. 43

  44. Keys in E/R Diagrams Underline the key attribute(s). 44

  45. Example: name is Key for Beers Beers name manf 45

  46. Example: a Multi-attribute Key dept number hours room Courses • Note that hours and room could also serve as a • key, but we must select only one key. 46

  47. Weak Entity Sets Occasionally, entities of an entity set need “help” to identify them uniquely. Entity set E is said to be weak if in order to identify entities of E uniquely, we need to follow one or more many-one relationships from E and include the key of the related entities from the connected entity sets. 47

  48. Example name is almost a key for football players, but there might be two with the same name. number is certainly not a key, since players on two teams could have the same number. But number, together with the team name related to the player by Plays-on should be unique. 48

  49. In E/R Diagrams name number name Plays- on Players Teams • Double diamond for supporting many-one relationship. • Double rectangle for the weak entity set. 49

  50. Weak Entity-Set Rules A weak entity set has one or more many-one relationships to other (supporting) entity sets. Not every many-one relationship from a weak entity set need be supporting. The key for a weak entity set is its own underlined attributes and the keys for the supporting entity sets. E.g., (player) number and (team) name is a key for Players in the previous example. 50

More Related