1 / 34

MSIT IT525 Class 2

MSIT IT525 Class 2. Jon Walter McKeeby, DSc Diana Dee. 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. ER Modeling.

Download Presentation

MSIT IT525 Class 2

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

More Related