Entities and Relationships

1. Entities and Relationships Database Principles

2. Database Principles What is an Entity? • Something of interest, capable of independent existence • A “stand alone” thing or a concept • A type of thing such as Automobile and not “my car”. • Something with properties. • EMPLOYEE is an entity with property SALARY • SALARY is not an entity because if I say SALARY you say, “Whose?” In other words, a SALARY only makes sense if we are talking a some EMPLOYEE so it is not “capable of independent existence”. • In sentences, entities show up often as nouns; unfortunately, so do properties. • Check out Wikipedia and what it says about entities.

3. Database Principles Exercise: • Working in pairs come up with a list of (Entity,Property) pairs. Both people in the pair need to agree. • Don’t throw the pairs away, you’ll need them later.

4. Database Principles What are Properties: • The fact that an entity has a property often represents a specific business rule. • A business or organization is run by rules. It is a well-known secret among database designers that the process of designing a database is that of capturing, in an unambiguous way, the rules of a business or organization. • The above is a business rule captured in the Entity-Relationship language by representing Salary as a property of the entity, EMPLOYEE. Every employee earns exactly one salary

5. Database Principles Exercise: • Go back to your (Entity,Property) pairs and express the related business rules. • What “business” is each pair a part of?

6. Database Principles Exercise: • A database designer is in a unique position to understand how a business is run. Discuss. • How could a database designer beneficially use this power? • How could a database designer detrimentally use this power?

7. Database Principles How do we picture an Entity? EntityName List of properties

8. Database Principles Was it always this way? No • Chen’s early notation, called a “Bubble Diagram”. . . . Property 2 EntityName Property 1

9. Database Principles Exercise: • Discuss the pros and cons of each approach to drawing an entity.

10. Database Principles Notational Confusion: • Chen called EMPLOYEE an entity type and Andrew Pletch an entity. • Other people said, if there is no confusion let’s drop the “type” and call EMPLOYEE an entity and Andrew Pletch an entity instance (or just an instance). • So Chen’s original approach should have been called the • We will use the terms entity and (entity) instance. • In some sense, an entity represents the set of its instances. • Entities are usually identified in the singular Entity-Type Relationship-Type Approach

11. Database Principles EMPLOYEE EmployeeID FName LName DOB Salary This is an ENTITY This is a group of entity instances Just to make it clear: • Exercise: Why call it EMPLOYEE and not EMPLOYEES?

12. Database Principles Exercise: • Work in small groups. • In the following paragraph identify things you think are entities and things you think are properties of entities. You can also consider things that are implicitly (not explicitly) referred to in the paragraph. The College at New Paltz is one of the campuses of the State University of New York. It is a liberal arts college with programs in arts, fine arts, business, science and engineering. Its faculty and students come from all over the state, the United States and the world.

13. Database Principles What’s in a Name? • A rose, by any other name … • It turns out, in database at least, a name is not enough. • Both these sets of instances can legitimately be called instances an entity called CAR but they are NOT instances of the same thing, are they? CAR What are this entity’s instances? { My car, My son’s car, My wife’s car, Your car, . . . } { VW Passat, Honda Accord, Toyota Corolla, Ford Escort, . . . }

14. Database Principles Exercise: • How can the following been seen as different types of Entities? • BOOK, PRODUCT, COURSE • Give me an example of something else. • Keep these examples around for a couple of slides

15. Database Principles CAR CAR underscore key attributes { My car, My son’s car, My wife’s car, Your car, . . . } { VW Passat, Honda Accord, Toyota Corolla, Ford Escort, . . . } Attributes: • Some properties of entities are called attributes. • Among the attributes of an entity there are some that identify the entity. These are called key attributes. • No two different instances of an entity have the same values for their key attributes. Make Model VIN

16. Database Principles The Key to Power; the Power of the Key • Keys are also called unique identifiers. • What is an identifier? What is a unique identifier? • In the Earthsea Trilogy by Ursula Le Guin, a story about a mage called Ged, people or things would be “under your control” if you knew their true name. • In database you “control/know” an entity if you know its key attributes. If you don’t, you don’t!! • This is the first hint that in Entity-Relationship we are going to insist that we know what we are talking about. • It turns out that as a language, Entity-Relationship is well-defined. • What does well-defined mean?

17. Database Principles Exercise: • Returning to our previous example: • What different keys are possible for BOOK, PRODUCT and COURSE?

18. Database Principles Keys vs Entities • There is a one-to-one correspondence between the distinct values of a key attribute and the instances of the corresponding entity. • Keys are surrogates for Entities – we can store key values where we can’t store entity instances. • Exercise: Identify the keys for the following entities BOOK CAR MEDICAL PROCEDURE PROFESSOR PROCESS APARTMENT COURSE {ISBN},{Author,Title} {VIN},{Make,Model} {Name},{Procedure Number} {Name}, {SSN} {ProcessID} {BuildingName,ApartmentNum} {Name},{Crs#},{Crs#.Sec#,Sem}

19. Database Principles Context: • Notice that things exist in a “context” and that unless the context is clear, assignment of keys is not easy. • For example, the context for identifying the key to Apartment to be the (BuildingName, ApartmentNum) pair is that we are only talking about apartments in a single city and not across the nation. • If you broaden the context, then something needs to be added to the key that helps distinguish between instances in different contexts. • Nationally, the new key to Apartment might be (City, BuildingName, ApartmentNum).

20. Database Principles A Final Word on Keys: • Things with the same key are the same thing! • Exercise: Find examples of things with the same key but that on the surface appear to be different things. Explain why they are really the same thing.

21. Database Principles A final word on attributes: • Although it is not a hard-and-fast rule, in this course we will insist that attributes be single-valued. In other words, not records and not lists. EMPLOYEE EmployeeID FName LName Address{ Street City, State, Zip} List_of_Jobs key attribute good examples of attributes bad examples of attributes NOTE: Any attribute name thatis expressed in the plural is probably a list so badly designed.

22. Database Principles CAMPUS PROGRAM FACULTY STUDENT Name Location CampusType Num_Faculty Num_Students Name Telephone Num_Depts Num_Faculty Num_Students SSN FName LName Office_Num Telephone StudentID FName LName Address Telephone Exercise: • Let’s revisit the exercise we worked on a while ago. • Suppose these are our entities: Add some suitable attributes and underscore the key attributes. The College at New Paltz is one of the campuses of the State University of New York. It is a liberal arts college with programs in arts, fine arts, business, science and engineering. Its faculty and students come from all over the state, the United States and the world.