1 / 109

Section 08 - REVIEW

Section 08 - REVIEW. E-R Diagrams. The Entity-Relationship Approach Represents reality using well-defined graphics and rules Basic building blocks are “things” (entities) and relationships. Member. M. Adopts. 1. Animal. E-R Diagrams. Advantages Theoretical foundation (Set Theory)

Download Presentation

Section 08 - REVIEW

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. Section 08 - REVIEW

  2. E-R Diagrams • The Entity-Relationship Approach • Represents reality using well-defined graphics and rules • Basic building blocks are “things” (entities) and relationships Member M Adopts 1 Animal

  3. E-R Diagrams • Advantages • Theoretical foundation (Set Theory) • Good for communication • Build E-R Model, then translate to any type of RDBMS • Disadvantages • Different (yet another new thing to learn) • Must translate to the relational model

  4. E-R Diagrams • Entity-Relationship Model: Basic Concepts • Entity • Thing, Object, Concept of interest to the enterprise • Each occurrence can be uniquely identified

  5. E-R Diagrams • Entity-Relationship Model: Basic Concepts • Attribute • Property of an entity • Column

  6. E-R Diagrams • Entity-Relationship Model: Basic Concepts • Relationship • Association between two (or more) entities

  7. E-R Diagrams • Entity-Relationship Model: Basic Concepts • Entity Identifier • Attribute(s) whose value uniquely identifies an entity • Primary Key

  8. E-R Diagrams • What is an Entity? • Physical entity types • Person • Building • Machine • Book • Usually Singular

  9. E-R Diagrams • What is an Entity? • Conceptual entity types • Contract • Account • Order • Course

  10. E-R Diagrams • What is an Entity? • Event entity types • Transaction • Shipment • Reservation • Phone Call • Seminar Offering

  11. E-R Diagrams • Entity-Relationship Model: Diagrams • Example: • Soft Rectangle represents entities • Noun • Singular • Connecting Line represents relationships • Verb Member Adopts Animal

  12. E-R Diagrams • Relationships have Characteristics • A relationship has Cardinality (Degree) One-to-One One-to-Many Many-to-Many

  13. E-R Diagrams • Each entity’s participation is Mandatory or Optional • Cardinality & Optionality are based on business rules Mandatory Optional

  14. E-R Diagrams • Mandatory • Every instance of the entity MUST participate in the relationship • Example: • Every animal is cared for by at least one employee

  15. E-R Diagrams • Optional • An instance of the entity CAN participate in the relationship • Example: • Some employees do not take care of animals

  16. E-R Diagrams • Determining Optionality & Cardinality • Optionality & Cardinality • Specify lower and upper bounds of each entity’s participation in the relationship • Use one of the following templates

  17. E-R Diagrams • Template 1 One ________(can/must) ________(one and only one/one or more) __________ • Template 2 One ________________a minimum of (0/1) and a maximum of (1/many) __________

  18. E-R Diagrams • Use either template • Read each relationship twice • Left to Right • Right to Left

  19. E-R Diagrams • Guidelines to Develop an E-R Diagram • Identify the Major Entities • Identify the Attributes for each entity • Determine the Unique Identifier(s) • Identify the Relationships • Assign Cardinality • Determine Optionality • Resolve M:N Relationships

  20. E-R Diagrams • Mapping the E-R Diagram to the Relational Database • Each entity becomes a Table • Each attribute becomes a Column • Unique Identifier becomes the PK • Each 1:M becomes a FK on the Many Side

  21. E-R Diagrams • Practice 01 • A company has ten departments • A company has five divisions • A company has one hundred employees • Each employee must work for one department • Each division has two departments

  22. E-R Diagrams • Practice 02 • A company has twenty employees • Each employee works for a department • There are two departments in the company

  23. E-R Diagrams • Practice 03 • A company has three divisions • A company has one manager per division • Each manager is in charge of one committee

  24. E-R Diagrams • Practice 04 • A company has a sales department with fifteen salespersons • Each salesperson works for the sales department • Each salesperson is supervised by one manager • The managers may not have an employee to supervise

  25. E-R Diagrams • Practice 05 • A piece of equipment is built with ten parts • The parts come from suppliers • All parts are held in inventory until needed to build a piece of equipment

  26. E-R Diagrams • Practice 06 • There are two hundred students • Each student must attend an orientation • An orientation is held at the beginning of each semester • Students attend the orientation in their first or second semester

  27. E-R Diagrams • Practice 07 • There are forty rooms in a dorm • Each room in the dorm holds two students • There are five dorms on campus • Each dorm has four floors

  28. E-R Diagrams • Practice 08 • Each faculty member advises fifty students • Each student has an advisor • There are faculty that do not advise students

  29. E-R Diagrams • Practice 09 • Students enroll in courses • Courses are taught each semester • Students receive a final grade for each course • Each course has a maximum number of students enrolled • Each course has a minimum number of students enrolled

  30. E-R Diagrams • Practice 10 • Basketball players sign contracts • Contracts are good for one to three years • Some players play in a game • Not all players may play in a game • Some players may be injured • Some injuries require a hospital visit • Hospitals take care of patients • Some hospital patients are basketball players

  31. End 10-21-05

  32. Normalization • Normalization using Codd’s Rules • Codd and contemporaries developed rules for “Normal Forms” • 1NF • 2NF • 3NF • Normal levels to do in database design • Boyce/Codd NF – 3.5NF • 4NF • 5NF

  33. Normalization

  34. Normalization 1NF

  35. Normalization 2NF

  36. Normalization 2NF & 3NF CLASS STUDENT

  37. Normalization • Rules for 1NF, 2NF, & 3NF • 1NF • Break out repeating groups into a separate entity • 2NF • Break out attributes that are dependent on part of the primary key into a separate entity • Called Partial Dependency • 3NF • Break out attributes that are wholly dependent on another key (not PK) into a separate entity • Called Transitive Dependency

  38. Normalization • Normalization Cont’d • A relation is in 3NF if all the attributes are functionally dependent • On the Key • On the Whole Key, and • On Nothing but the Key • (So Help Me Codd)

  39. Functional Dependency & Normalization • How to Normalize Data using Functional Dependencies • Definition of Functional Dependency • Given a relation R, attribute Y of R is functionally dependent on attribute X of R, if and only if each X value in R has associated with it precisely one Y-value in R (at any one time)

  40. Functional Dependency Functional Dependency & Normalization • Y of R is Dependent on X of R • X functionally determines Y X Y

  41. Functional Dependency Functional Dependency & Normalization • Example 01

  42. Functional Dependency Functional Dependency & Normalization • Example 01 • Normalization begins with the arrangement of information into tables with rows and columns such that repeating groups of information have been eliminated, that is, the "cells" have data with atomic values. In addition, normalized tables should have some data field(s) which is unique for all rows. • In this case, because SMITH has two identical subscriptions, we need to invent a new field, namely SUBSCRIPTION NUMBER, in order to insure uniqueness, i.e. no duplicate rows

  43. Functional Dependency Functional Dependency & Normalization • Example 01 • 1NF – Resulting Table

  44. Functional Dependency Functional Dependency & Normalization • Example 01 • Functional Dependency A central concept of the normalization process is the functional dependency. Simply put, a functional dependency exists between two data fields when for each distinct value of one field, there is only one possible value for the other field.  

  45. Functional Dependency Functional Dependency & Normalization • Example 01 • For example, if we assume that SUBSCRIBER NUMBER is a uniquely assigned number for each subscriber, then there is a functional dependency between SUBSCRIBER NUMBER and NAME. We could say that SUBSCRIBER NUMBER functionally determines NAME or, conversely,that NAME is functionally dependent upon SUBSCRIBER NUMBER.

  46. Functional Dependency Functional Dependency & Normalization • Example 01 • This functional dependency and others are shown below, using a convenient notation, i.e. " A-->B.“ • SUBSCRIBER NUMBER-->NAME • MAGAZINE CODE-->MAGAZINE • SUBSCRIPTION NUMBER-->SUBSCRIBER NUMBER, NAME, MAGAZINE CODE,MAGAZINE, START DATE, END DATE  

  47. Functional Dependency Functional Dependency & Normalization • Example 01 • It is critical to this process to fully understand the underlying assumptions about the information that is being normalized. Successful normalization is, for all practical purposes, impossible without understanding the meaning and usage of information.

  48. Functional Dependency Functional Dependency & Normalization • Example 01 • In our example, our assumptions are:  • (1) SUBSCRIBER NUMBER is uniquely assigned to each subscriber. • (2) MAGAZINE CODE is a convenient unique code for each magazine name. • (3) SUBSCRIPTION NUMBER is uniquely assigned to each subscription and therefore functionally determines all fields.

  49. Functional Dependency Functional Dependency & Normalization • Example 01 • 2NF • Information which is in Second Normal Form has the quality that some field (or fields) functionally determines all of the others. This field(s) is called a primary key. Building Second Normal Form tables is simply the mechanical process of making tables out of the functional dependencies and noting which field(s) is the primary key. The following notation shows our new tables in Second Normal Form.

  50. Functional Dependency Functional Dependency & Normalization • Example 01 • 2NF Subscriber Table Magazine Table Subscription Table

More Related