Entities and Relationships

1. Entities and Relationships Database Principles

2. What is an Entity? • Something of interest, capable of independent existence • A 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. Database Principles

3. 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. Database Principles

4. 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 Database Principles

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

6. 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”? Database Principles

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

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

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

10. 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 Database Principles

11. 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? Database Principles

12. 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 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. Database Principles

13. 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 instance 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, . . . } Database Principles

14. 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 Database Principles

15. 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 Database Principles

16. 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? Database Principles

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

18. 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 keys where we can’t store entities. • 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} Database Principles

19. 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. Database Principles

20. 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 Database Principles

21. 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. Database Principles