Database Design

1. Database Design Lessons 2 & 3Database Models, Entities, Relationships Data modeling attempts to capture the needs of the business users so that the resulting database is one that everyone can use easily. Data modeling attempts to capture the needs of the business users so that the resulting database is one that everyone can use easily.

2. Database Design Conceptual Analysis What Logical Design How Physical Build Data Information Talk about difference in Conceptual model Vs Physical Conceptual Physical blueprints house sketch of outfit the real thing Recipe for a cake cake itself Steps to conceptual/Logical Gather requirements identity entities Model entities identify information about entities Determine attributes for each entity Identify relationships Physical Model Convert entities to tables Convert attributes to fields/columns Define relationships PK & FK Conceptual model: A data model, usually represented by an entity-relationship diagram Physical model: A design for an object (a car, a house, a database, etc.) which includes implementation details such as size, volume, weight, etc.) Talk about difference in Conceptual model Vs Physical Conceptual Physical blueprints house sketch of outfit the real thing Recipe for a cake cake itself Steps to conceptual/Logical Gather requirements identity entities Model entities identify information about entities Determine attributes for each entity Identify relationships Physical Model Convert entities to tables Convert attributes to fields/columns Define relationships PK & FK Conceptual model: A data model, usually represented by an entity-relationship diagram Physical model: A design for an object (a car, a house, a database, etc.) which includes implementation details such as size, volume, weight, etc.)

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 1.2.5 Entities can be: Instances are: Employees Marge Hohly, Jack Wilson, Wes Nance, Bill Farmer Students Jane Doe, Mary Smith, Tom Jones Orders Big Mac, Asia Chicken salad, frees, coke Customers Shopping Mall Cerritos Mall, Stonewood Center, Town Center, Del Amo Center Movie Star Brad Pitt, Elizabeth Taylor, Crystal Gayle Shoe Sandels, tennis shoe, loafers, flip flops Tangibles: 1.2.5 Entities can be: Instances are: Employees Marge Hohly, Jack Wilson, Wes Nance, Bill Farmer Students Jane Doe, Mary Smith, Tom Jones Orders Big Mac, Asia Chicken salad, frees, coke Customers Shopping Mall Cerritos Mall, Stonewood Center, Town Center, Del Amo Center Movie Star Brad Pitt, Elizabeth Taylor, Crystal Gayle Shoe Sandels, tennis shoe, loafers, flip flops Tangibles:

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 1.2.5 Movies Stars ____________________________ Basic Instincts Sharon Stone The Proposal Sandra Bullock Julie & Julia Meryl Streep Inglourious Basterds Brad Pitt1.2.5 Movies Stars ____________________________ Basic Instincts Sharon Stone The Proposal Sandra Bullock Julie & Julia Meryl Streep Inglourious Basterds Brad Pitt

5. Examples Entity = Product New York = instance Director can be either an entity or instance � context is important 1.2 Dream House example: Entities Instances Door Front (location) or French (type) Window bedroom (location) or Bay (type) Tree Maple Paint Color Blue Room Living City New York Product Coke Cola1.2 Dream House example: Entities Instances Door Front (location) or French (type) Window bedroom (location) or Bay (type) Tree Maple Paint Color Blue Room Living City New York Product Coke Cola

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 Examples: Stylist: name (mandatory) Specialty (optional) Salary (mandatory) Customer: name (mandatory) phone number (mandatory) Service: code or name (mandatory) description (mandatory) price (mandatory) Mandatory or Optional nature is determined by business rules Examples: Stylist: name (mandatory) Specialty (optional) Salary (mandatory) Customer: name (mandatory) phone number (mandatory) Service: code or name (mandatory) description (mandatory) price (mandatory) Mandatory or Optional nature is determined by business rules

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 # Entity UID Song Title Event venue Customer First_name, Last_name Student IDEntity UID Song Title Event venue Customer First_name, Last_name Student ID

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 2.3.5 The choice of attributes is related to the information that a business cares to track (business requirement). Example: shoe size important to shoe store but not to grocery store. Name - character type Salary � numeric type photograph � image type 2.3.5 The choice of attributes is related to the information that a business cares to track (business requirement). Example: shoe size important to shoe store but not to grocery store. Name - character type Salary � numeric type photograph � image type

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� Data should be �implementation-free� should not depend on type of database, computer or programming language Establish Business Rules � Business rules define the restrictions imposed on the data by the business Business Rules have their purpose to make sure the business operates correctly. Example: when selling liquor or cigarettes � need to check age of purchaser Data should be �implementation-free� should not depend on type of database, computer or programming language Establish Business Rules � Business rules define the restrictions imposed on the data by the business Business Rules have their purpose to make sure the business operates correctly. Example: when selling liquor or cigarettes � need to check age of purchaser

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) Contains no derived data For example: hours work and pay rate thus would not store pay amount as it can be derived GPA would not be stored as it can also be calculated from course grades and units taken. For each row in entity 1 1:1 (one and Only one row in 2nd entity) & vise versa (single line) 1:M one row in entity 1 to many rows in entity 2 (crows foot) M:M � need to resolve with an intersection entityContains no derived data For example: hours work and pay rate thus would not store pay amount as it can be derived GPA would not be stored as it can also be calculated from course grades and units taken. For each row in entity 1 1:1 (one and Only one row in 2nd entity) & vise versa (single line) 1:M one row in entity 1 to many rows in entity 2 (crows foot) M:M � need to resolve with an intersection entity

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 Questions to ask: Must an employee have a job? Can an employee have more than one job? Must a job be done by an employee? Must or May an employee be assigned to a department? Example: President is not assigned to a department TEACHERS/COURSES/STUDENTS Teachers may teach courses (part-time teachers may have the term off) Are all courses taught by teachers? Are online classes taught by teachers? Students attend classes. Must students take a course to be considered a �student�? Can there be a course with no students?Questions to ask: Must an employee have a job? Can an employee have more than one job? Must a job be done by an employee? Must or May an employee be assigned to a department? Example: President is not assigned to a department TEACHERS/COURSES/STUDENTS Teachers may teach courses (part-time teachers may have the term off) Are all courses taught by teachers? Are online classes taught by teachers? Students attend classes. Must students take a course to be considered a �student�? Can there be a course with no students?

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 Each DEPARMENT may have one or more EMPLOYEEs Each EMPLOYEE must be assigned with one and only one DEPARTMENTEach DEPARMENT may have one or more EMPLOYEEs Each EMPLOYEE must be assigned with one and only one DEPARTMENT

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 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

24. Conventions Not a strict requirement (can reverse)

25. Matrix Diagram 3.4.4

26. ERD

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