1 / 27

Database Design

Database Design . Lessons 2 & 3 Database Models, Entities, Relationships. Database Design . Conceptual Analysis What Logical Design How Physical Build Data Information. An Entity. Something of significance to business about which data must be known

lok
Download Presentation

Database Design

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related