CS 430 Database Theory

1. CS 430Database Theory Winter 2005 Lecture 3: A Fifty Minute Introduction to Data Modeling

2. What is Data Modeling? • Answer: Modeling the data that will be in our database • What data will be in our database? • The data needed to support our application • The data needed to meet the requirements for our application • Figure 3.1 from Text Book • Note that database design leads application design

3. Aside • When we build a data model we are modeling the data content of the “real world” • The real world is very complicated • But what’s a model, in this case: • “A simplified description of something complex”

4. How do we do modeling? • Use a method called “Entity/Relationship (ER) Modeling” • The most popular technique for data modeling • Also, sometimes called “Semantic Modeling” • That is, it tries to capture the semantics (meaning) of the data • In this case semantics refers particularly to rules about the data

5. Entities and Relationships • Entity • A distinguishable (type of) object • E.g. Person, Book • Has an identity (distinct from the data about it) • Relationship • An entity that serves to connect two or more other entities • Attribute or Property • A piece of information that describes an entity • E.g. Name, Title

6. Informal • All of these concepts are informal • In particular, as a design evolves, things change: • Relationships become Entities (and vice versa) • Attributes become Relationships

7. A little more complexity • Derived Attributes and Relationships • Ones that can be derived from other information • E.g. Age can be derived from Birth Date • Composite Attributes • Ones built up from simpler attributes • Single-Valued and Multi-Valued Attributes • What you think it means • Key • A collection of one or more attributes of an entity that uniquely identify that entity • E.g. Your SSN, the ISBN for a book

8. Yet More Complexity • Cardinality • Property of a relationship: 1 to 1, N to 1, 1 to N, M to N • Existence Dependency • Sometimes also called total participation • An entity can only exist if it participates in a specified relationship • Weak Entity • One that doesn’t have a key • Defined by their relationship to other entities (called an identifying relationship)

9. ER Diagrams • Figure 3.14 from Text Book • Shows diagrammatics

10. Company Database Example • Data Requirements (page 53, Text Book) • The company is organized into departments. Each department has a unique name, a unique number, and a particular employee who manages the department. We keep track of the start date when that employee began managing the department. A department may have several locations. • Etc., etc., etc. • Figure 3.2 from Text Book • ER Diagram for Company database • Figure 3.16 – Same thing as a UML Class Diagram

11. Assignment #1 • Work up a (rough) set of data requirements and a (rough) ER diagram for a database that would be used to run (some aspect of) a book store • The diagram can be hand drawn • Make sure your data requirements match your diagram • Keep it small (about size of Company database) • You will probably have to make some choices about what data to exclude • Try to keep it consistent, don’t worry about modeling everything • Goals: • Take a real world problem and think about the kinds of data that might be important for that problem. • Try your hand at a  little data modeling • Hidden Agenda: • I’m going to use this as a basis for you to implement a sample database application • Due: Monday, January 24