Section 08 - REVIEW

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