MSIT IT525 Class 2

1. MSIT IT525 Class 2 Jon Walter McKeeby, DSc Diana Dee

2. Book • Coronel, Carlos; Morris, Steven and Peter Rob. 2013. Database Systems: Design, Implementation, and Management.  Tenth Edition. Cengage Learning. Boston, MA. • ISBN-10: 1-133-52679-7. ISBN-13: 978-1-133-52679-7.

3. ER Modeling • Entities • Attributes • Relationships • Review of Checklist/Style Guide • Top-Down Database Approach • Bottom-Up Database Approach

4. Entities/Attributes/Primary Key • What is an entity? • Person, place, thing, concept or event in which data is tracked. • What is an entity instance? • A row within a table. • What is an attribute? • A property of an entity type that is of interest. • What is a composite attribute? • Two or more attributes that have meaning as a whole. • Example: Name = FirstName + MiddleName + LastName; Address = Street + City + State + PostalCode • What is a simple (atomic) attribute? • The smallest possible characteristic of an entity. • What is an identifier? • An attribute (or combination of attributes) whose value distinguishes instances or uniquely defines an instance of an entity type or a row in a table (e.g. CustomerNo) • An identifier is called a “candidate key”, one of which is chosen as the “primary key” • What is a composite identifier? • An identifier that includes two or more attributes to uniquely define an a row e.g. a two-column primary key in an associative entity

5. Relationships: Participation & Cardinality • What is connectivity? The classification of the relationship between entities such as 1:1, 1:M, M:N. • What is a relationship? An association between entities such as 1:1, 1:M, M:N. • What is participation? Whether or not the entity is required or not within the relationship. Also referred to as minimum cardinality. • Mandatory shown by perpendicular line which means 1 • Optional shown by oval which means 0 • What is cardinality? A constraint on the number of entities participating in a relationship. • Types: 1-M; M-N; 1-1 • What is an identifying relationship?The primary key from the one table is part of the primary key in the second table. This is shown by a solid relationship line. • What is a weak entity?Part of the PK is dependent on the PK of another table. This is shown by a solid relationship line as well as the PK containing FK to another table.

6. Business Rules: How To Determine Relationship Cardinality • List the Entity Type Pair. • To determine the cardinality of a relationship, you make two statements about the relationship, each starting with a singular entity, and examine the (one-word) multiplicity of each. • The two statements look at the relationship from each “side”. < A | an| ONE > <entity type 1> <some verb> < one | many ><entity type 2> < A | an| ONE > <entity type 2> <some verb> < one | many ><entity type 1> • List the relationship. For M:N list the associative entity. Order – Customer An (one) order is placed by only onecustomer. (one) A (one) customer may place many orders. (many) Customer 1:M Order Order – Product An (one) order contains manyproducts. (many) A (one) product may be listed on many orders. (many) Order M:N Product AE: OrderLine

7. Data Integrity • Primary Key: The selected candidate key use to uniquely identify a row. • Entity Integrity: Primary Key • Each table has column(s) with unique values • Ensures entities are traceable • Foreign Key: An attribute or group of attributes in one table whose values must match the primary key in another table. • Referential Integrity: Foreign Key • Values of a column in one table match values from a source table • Ensures valid references among tables

8. Relationships of Tables

9. Relationships • What are the relationship types of an ERD? • Recursive/Self/Unary Relationship • A relationship found within a single entity type. • One to One Relationship • One entity instance is associated with only one instance of the related entity. • One to Many Relationship. • One entity instance is associated with many instances of the related entity. • Many to Many Relationship • Associations among two or more entities in which one occurrence of an entity is associated with many occurrences of a related entity and one occurrence of the related entity is associated with many occurrences of the first entity. • Ternary/M-Way Relationship • Association or relationship between three entities.

10. Recursive/Self/Unary • Self identifying relationships are associations among members of the same set. • One entity instance is associated with one or many instances within the same entity. • Example: • An employee has a supervisor who is also an employee.

11. 1:1 Relationship • One entity instance is associated with only one instance of the related entity. • Not a common relationship type. • Identifying Relationship – The primary key from the one table is part of the primary key in the second table. This is shown by a solid relationship line. • Example: • DB designer splits most frequently used patient attributes from patient address attributes as a way to group the attributes.

12. 1:M Relationship • Most common form of relationship between entity types. • An entity instance in one entity links to multiple rows in a different entity. • Example: • A customer has multiple orders, an order line has multiple order lines and a product can be contained on multiple order lines. OrderNumberwithin Order is unique and therefore it is not a weak entity. However, if the design was to have the OrderNo and CustomerNo as the key in Order, then Order would be a Weak Entity. OrderLine is an Associative Entity in that it borrows the OrderNumber and SKU as part of its Primary Key.

13. M:N Relationship • A row in one entity type “A” has a collection of rows in entity type “B” while one row entity type “B” has a collection of rows in entity type “A”. • SQL cannot support M to N efficiently. Therefore, M-Ns are converted to 1 to M and M to 1. • Associative Entity – An entity designed to transform an M:N relationship into two 1:M relationships. The composite entity’s primary key comprises at least the primary keys of the entities that it connects. Some systems show as having diagonal line within the entity rectangle. • Weak Entity – An entity that displays existence dependence and inherits the primary key from its parent entity. • Identifying Relationship – The primary key from the one table is part of the primary key in the second table. This is shown by a solid relationship line. • Example: A student enrolls in multiple majors while a major has multiple students.

14. M:N Relationship With Attributes • Relationships can have attributes. • Attribute depends on both entity types, not just one entity type. • Example: • Order of authors is important. • AuthOrder is part of the Writes relationship (combination of Author and Book). • AuthOrder is not part of the Author or Book entity types.

15. Ternary/M-Way Relationships • Relationships that involve more than 2 entity types. Tracks interaction of multiple entity types. • Associative Entity – An entity designed to transform an M:N relationship into two 1:M relationships. The composite entity’s primary key comprises at least the primary keys of the entities that it connects. • Weak Entity – An entity that displays existence dependence and inherits the primary key from its parent entity. • Identifying Relationship – The primary key from the one table is part of the primary key in the second table. This is shown by a solid relationship line. • Example:3 way relationship tracks who supplies a part on a specified project • The entity uses tracks part, supplier per project and all are required for an entry to be placed in Uses.

16. ER Notation Coronel, Morris & Rob (2013) Database Systems, 10th Edition

17. Data Modeling Checklist Coronel, Morris & Rob (2013)

18. Top-Down Database DesignProcedure/Methodology To design a database that will support a “form,” “user view,” or “report”. • Find the entity types. • List directly-related entity type pairs. • Write the business rules to determine cardinality. • Draw the ERD. • Designate primary keys (PKs) and foreign keys (FKs). • Identify other needed attributes. • Review ERD participation and constraints and update the ERD.

19. Top-Down Database Design1. Find the entity types

20. Top-Down Database Design1. Find the entity types From the previous slide, for our Sales process, we have entity types: Customer Order Product

21. Top-Down Database Design2. List directly-related entity type pairs [This may not be easy or obvious. It takes practice, and a careful reading of the business description.] Customer – Order Order – Product Note: Customer is not directly related to Product.

22. Top-Down Database Design3. Write the business rules; determine cardinality Customer – Order An (one) order is placed by only one customer. (one) A (one) customer may place many orders. (many) Customer 1:M Order Order – Product An (one) order may contain many products. (many) A (one) product may be in many orders. (many) Order M:N Product (AE: OrderLine)

23. Top-Down Database Design4. Draw the ERD5. Designate PKs and FKs Note: An FK is not promoted to be part of a PK, except in an associative entity (AE).

24. Top-Down Database Design6. Identify other needed attributes

25. Top-Down Database Design7. Review participation & Constraints • Participation is whether or not the entity is required or not within the relationship. Also referred to as minimum cardinality. • Mandatory shown by perpendicular line which means 1; Optional shown by oval which means 0 • Constraints (Required fields will be in bold; required FK will change participation cardinality to be one and only one). • Customer: Require LastName, FirstName, StreetAddressLine1, City, State, ZipCode, HomePhone • Phone must be of valid format, State must be valid abbreviation • Order: Require CusotmerId, OrderDate (Changes Order to Customer to require Customer) • Order requires at least one OrderLine • Subtotal = Quantity * Unit Price from OrderLine • Tax = StateTaxRate * SubTotal; when applies • Shipping Selected from Table • Total = Subtotal + Tax + Shipping • OrderLine: Require Quantity, UnitPrice (Changes OrderLine to Product to require Product) • Product: Require Description, UnitPrice • UnitPrice > 0

26. Bottom-Up Database DesignProcedure / Methodology To design a database that will support a “form,” “user view,” or “report.” • Find all the attributes on the form. • Establish the dependencies (determinants). • Group attributes that have a common determinant into an entity type; name it. • Find directly-related entity type pairs. • Determine cardinalities for each pair. • Draw the ERD. • Review ERD participation and constraints and update the ERD.

27. Bottom-Up Database Example Here is part of a customer order form:

28. Bottom-Up Database Example1. Find All the Attributes Attributes: CustomerID Subtotal CustomerName Tax Address Shipping OrderNumber Total OrderDate Qty SKU Description UnitPrice

29. Bottom-Up Database Example2. Establish the Dependencies • What does Functional Dependency mean? The value of one attribute is determined by the value of another attribute. Example: CustomerName is functionally dependent on CustomerID CustomerID determines (is a determinantof) CustomerName

30. Bottom-Up Database Example2. Establish the Dependencies AttributeDepends On (Determinant) CustomerID [nothing // is itself a determinant] CustomerNameCustomerID Address CustomerID OrderNumber [nothing] OrderDateOrderNumber SKU [nothing] Description SKU Subtotal OrderNumber Tax OrderNumber Shipping OrderNumber Total OrderNumber Qty OrderNumber, SKU UnitPriceOrderNumber, SKU at for ordered product UnitPrice SKU at product level

31. Bottom-Up Database Example3. Group Attributes into Entity Types • Attributes with the same determinant(s) are grouped together with their determinant(s), and entity types are named: Customer (CustomerID, CustomerName, Address) Order (OrderNumber, OrderDate, Subtotal, Tax, Shipping, Total) Product (SKU, Description) OrderLine (SKU, OrderNumber, Qty, UnitPrice)

32. Bottom-Up Database Example4-5. Directly-Related Pairs and Cardinalities Directly-related pairs and cardinalities are the same as was determined in the top-down example: Customer 1:M Order Order M:N Product A many-to-many relationship has a linking or bridging table, formally called an “associative entity.” Order 1:MOrderLineM:1 Product

33. Bottom-Up Database Example6. Draw the ERD

34. Bottom-UP Database Design7. Review participation & Constraints • Participation is whether or not the entity is required or not within the relationship. Also referred to as minimum cardinality. • Mandatory shown by perpendicular line which means 1; Optional shown by oval which means 0 • Constraints (Required fields will be in bold; required FK will change participation cardinality to be one and only one). • Customer: Require LastName, FirstName, StreetAddressLine1, City, State, ZipCode, HomePhone • Phone must be of valid format, State must be valid abbreviation • Order: Require CusotmerId, OrderDate (Changes Order to Customer to require Customer) • Order requires at least one OrderLine • Subtotal = Quantity * Unit Price from OrderLine • Tax = StateTaxRate * SubTotal; when applies • Shipping Selected from Table • Total = Subtotal + Tax + Shipping • OrderLine: Require Quantity, UnitPrice (Changes OrderLine to Product to require Product) • Product: Require Description, UnitPrice • UnitPrice > 0