Introduction to Data Modeling: Entities, Attributes, and Relationships

1. Introduction to Data Modeling—Topics • Introduction to Data Modeling • Information elements • Introduction to Entities, Attributes, and Relationships • Basic notation • Chen • Alternative • More on attributes

2. What is Data Modeling? • Data modeling is a step in the process that begins with the planning phase of Information Engineering and ends with construction of the physical database EntitiesAttributesRelation-shipsRules InformationSystemsPlanning InformationElements PhysicalDatabase Data Modeling

3. What is Data Modeling (cont.) • Data Modeling is a process of requirements identification, documentation, and revision that results in a finished DB design • Process begins with gross identification of basic DB components • Design is refined according to rules for storage and retrieval efficiency • Finished DB design is converted to the physical DB • Some DB design tools make the conversion automatically

4. Information Elements • IS Design involves interviews with clients • Clients don’t understand our terminology or DB concepts (or they wouldn’t need us!) • We probably don’t understand much of theirs • Examine forms and reports • Interviews will result in a collection of "Information Elements" (my term) • Lists of items of concern to the client • Items that crop up in interviews • Items you recognize from your experience Exercise

5. Information Elements (cont.) • Task is to determine which part of a data model the different information elements fit • Entity • Attribute • Relationship • Business rule • System input or output • None of the above (irrelevant) • Our determinations generate the base data model • Further analysis modifies and extends the data model to its final form

6. Entities • "A person, place, object, thing, event, or concept about which the organization wishes to maintain data" • Examples from the university's database might be STUDENT, CLASS, and PROFESSOR • Each entity in the final data model will become a table in the physical database • It is important to distinguish between entities and attributes of an entity • Distinction may change with perspective • We will also create new entities as we refine our data model

7. Occurrences • "Occurrences" of an entity are individual instances of the entity • You are an occurrence of the STUDENT entity • I am an occurrence of the FACULTY entity • Occurrences correspond to records in the database • Take care not to confuse occurrences with entities • Some authors use the term “Entity Set” to imply that the Entity is a collection of occurrences

8. Defining Entities • It is amazingly important to explicitly define what is meant by each entity • What is contained in the following entities? • Customer − Order • Sale − Employee • Entity descriptions become part of the DB documentation (description property in SQL Server) • You cannot assume that developers using the DB will have the save vision for the meaning of an entity that you do

9. Defining Entities (cont.) • (One occurrence of this entity represents…) “A person or organization that has purchased products from the company or who has inquired about purchasing products” (Customer) • … “A person that has signed an employment agreement with the company including former employees. Excludes applicants, contractors, and contractor employees” (Employee) • Try very hard to avoid using the entity name as part of the definition.

10. Attributes • "A property or characteristic of an entity that is of interest to the organization" • E.g., what characteristics of a STUDENT are of interest to the University? • SSN, First Name, Last Name, Major, DOB, … • What characteristics are not of interest? • What about Professors and Classes? • What about your project? • Attributes become fields in a record in the physical database

11. Describing Attributes • Attributes must be described just like entities • What do these mean for a Product entity? • Price − Weight • Supplier − ProductID • “Street address excluding apartment number or P.O. Box where employee receives mail” • “Shipping weight of one unit of product including packaging in decimal fractions of a pound” • “Current retail price that will be charged to a customer purchasing this product” • Use Attribute description in SQL table definitions

12. Entities and Attributes • There can be ambiguity—depending on perspective—in determining what should be an entity and what should be an attribute • UCF may have an attribute of STUDENT that contains the high school each student graduated from • The State of Florida Dept. of Education may consider high schools to be an entity with its own attributes • Refinement of the database may require that some attributes be turned into new entities—watch for this as we continue in the course

13. Naming Entities and Attributes • Balance brevity with completeness • No Spaces • Order Detail → OrderDetail or Order_Detail • No SQL Reserved Words • Order → CustomerOrder • Date → OrderDate, HireDate, BirthDate • Time, Table, Insert, … • My preference is for “Pascal Case” • CustomerOrder • LastInventoryDate