Database Design

1. Database Design Lessons 2 & 3Database Models, Entities, Relationships

2. Database Design • Conceptual • Analysis • What • Logical • Design • How • Physical • Build • Data • Information

3. An Entity • Something of significance to business about which data must be known • A name for the things that you can list • A single name of noun • Entities have Instances • Occurrences of entities • Rows

4. Entities vs. Instance • Entities can be: • Tangible, like Person or Product • Nontangible, like skill level • An event, like concert, graduation, wedding • Instance examples: • Animal entity instances like, Dalmatian, Siamese cat, cow, tiger • Car entity instances like, sedan, station wagon, SUV, convertible

5. Examples • Entity = Product • New York = instance • Director can be either an entity or instance – context is important

6. Attributes • Entities have Attributes • Single-value property, detail of an entity • Piece of information that describes, qualifies, quantifies, classifies and/or specifies an entity • Property of an entity • Attributes have a data type

7. Attributes • Describe an entity • Attribute vs. attribute value • color vs. blue • animal type vs. dog • Can have one and only one value at a given point in time • One or more attributes must be defined as a unique identifier (UID)

8. Unique Identifier (UID) • Used to distinguish one instance of an entity from another • Example: Student ID as a UID for student entity • part number as a UID for product entity • Social security number (UID) for employee • Denote with a #

9. Attributes • Must be a single-values at any point in time • Should be stored in one and only one entity • Values have data type • Example: entity CAR may have attributes “model” & “color” (values of “beetle”, “green”) • An attribute may change over time

10. Attribute • Volatile may change with time, like age • should look for non-volatile attributes like birth date rather than age • Mandatory vs. Optional • email address mandatory for EMPLOYEE if modeling email application • email address optional for CUSTOMER is modeling an online catalog

11. Entity relationship diagram (ERD) • Visual way to display business requirements • Tool used in design stage • Used to react to, validate, and correct data in database • Entities should be “implemetation-free”

12. Relationship • Represents something significant to a business • Expresses how entities are mutually related • Always exist between entities • Always have two perspectives • Is named at both ends • Between two entities (or one entity and itself) • Have an optionality • Have a degree or cardinality

13. Conventions • Entities appear as all capital letters and singular • Relationships are italicized • Entities are placed in soft boxes (rounded corners) • Examples: • EMPLOYEE hold JOBs • JOBs are held by EMPLOYEEs • PRODUCTs are classified by a PRODUCT TYPE • PRODUCT TYPE classifies a PRODUCT

14. Optionality of relationships • A relationship adds a link between entities • Relationships come from business rules • Is it a mandatory or optional relationship

15. Optionality of relationships • Are either Mandatory or Optional • Mandatory value is a REQUIRED field • Use MUST to describe • Denoted with an * and a solid line • Optional value may be supplier or not • Use MAY to describe • Denoted with a ° and a dashed line • Example: • Each DEPARTMENT must have one or more EMPLOYEEs • Each DEPARTMENT may have one or more EMPLOYEEs

16. Identifying Relationships • Cardinality or Degree of relationship • Describes how many? • Use ‘one and only one’ or ‘one or more’ • use crow foot to denote ‘one or more’ in ERD • Examples: • Each DEPARTMENT may have one or more EMPLOYEEs • Each EMPLOYEE must be assigned to one and only one DEPARTMENT • See ERD on next slide

17. ERD • Entities use soft boxes DEPARTMENT # ID o name o location EMPLOYEE #ID * first name * last name o telephone number o salary * job hire assigned to

18. Examples: • Each SEAT may be sold to one or more PASSENGERs • this example accounts for overbooking • Each PASSENGER may purchase one and only one SEAT

19. Entity naming • Name must be unique • Create a description of the entity (be explicit) • Be aware of homonyms • Market = 16 to 25 years • Market = Europe, Asia etc. • Avoid reserved words • Remove the relationship name from the entity name

20. ERD conventions - summary • Entities go in soft boxes • Entity names are singular and written in all capital letters • Attributes go under Entity • # is a UID (unique identifier – Key) • * mandatory attribute • o optional attribute

21. ERD conventions - summary • Relationships are lines (optionality) • solid are mandatory • dashed are optional • Lines terminations express cardinality • “single toe” denotes “one and only one” • “crow’s foot” denotes “one or more”

22. Example • Each HAIRSTYLIST may work on one or more CLIENTs • Each CLIENT must be assigned to one and only one HAIRSTYLIST • See next slide to ERD

23. ERD diagram • List entity and attributes • HAIRSTYLIST • # id • * first name • *last name • * address • * phone number • * social-security number • * salary • CLIENT • # client number • * first nameo last nameo phone number work on assigned to

24. Conventions • Not a strict requirement (can reverse) this crow is flying east this crow is flying south

25. Matrix Diagram • 3.4

26. ERD COUNTRY TRAVELER visit visited by the location of located in have seen seen by LANDMARK

27. Previous ERD • Note ERD included optionality and cardinality • Note there are several M:M relationship. This is a valid relationship, but discussed in later chapters