1 / 57

Database Design

Database Design. Sections 6 & 7 First Normal Form (1NF), Second Normal Form (2NF), Unique Identifiers (UID), Third Normal Form (3NF), Arcs, Hierarchies and Recursive relationships. UID types. Artificial UID we need some # to have a key, can use autonumber Relationship is part of UID

lilli
Download Presentation

Database Design

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. Database Design Sections 6 & 7First Normal Form (1NF), Second Normal Form (2NF), Unique Identifiers (UID), Third Normal Form (3NF), Arcs, Hierarchies and Recursive relationships

  2. UID types • Artificial UID • we need some # to have a key, can use autonumber • Relationship is part of UID • use bar to indicate that key of parent is part of child key • makes a composite key • Composite key • 2 attributes function as a UID • Made of 2 unique attributes • The 2nd UID could be a key but function together with other key • Use primary key and secondary key – both must be mandatory and must be unique

  3. Unique Identifier (UID) • Value or combination of values that enables the user to find your record in a file/table • Simple UID is a single attribute DEPARTMENT # code * name

  4. Composite Unique Identifier • Composite UID is a combination of attributes CONCERT TICKET#date of performance #seat number

  5. Artificial UIDs • A UID that does not occur in the natural world but is created for identification purposes in a system • Examples: • student numbers • customer IDs • credit card numbers • social security numbers • passport numbers

  6. UID Artificial or Composite

  7. Example Composite UID • Composite UID is made up of two or more attributes together. • In bank example DD 6.1.8 (next slide) • The Bank number (UID in BANK entity) is part of the composite key for ACCOUNT, thus the use of the barred relationship

  8. UIDs from Barred Relationships

  9. PLAY LIST ITEM *comments for for have on EVENT #id *name *date *cost *description SONG #id *title *artist *duration Intersection Entity in M:M

  10. Intersection entity not barred

  11. Candidate and Secondary UID

  12. Normalization • To make sure you don’t store the same data twice in the model • Also, that you store the data in the correct place • We will cover the first 3 normal forms.

  13. Normalization • Your goal as a database designer is to "store information in one place and the best place." • Examine the first 3 Normal Forms (1NF, 2NF, & 3NF) • There are more normal forms but the first three enough for business

  14. First Normal Form (1NF) • First Normal Form requires that there be no multi-valued attributes and no repeating groups. • To check for 1NF, validate that each attribute has a single value for each instance of the entity. • In other words: “One value per Attribute”.

  15. Example 1NF • This has multiple values for the classroom. SCHOOL BUILDING #code *name *address *classroom

  16. Example 1NF • Telephone is a multvalued attribute that could be an Entity. Ie. Home, work, mobile, fax etc. • Not all Employees have multiple phones, so there would eliminate multiple NULL values. EMPLOYEE #id *name *address . . º telephone º salary

  17. 1NF violations

  18. 1NF violation solutions

  19. 1NF Review • Do a quick review of normalization and First Normal Form (all attributes must be single-valued). Explain that we will cover the second rule of normalization. • First Normal Form (1NF) • First Normal Form requires that there be no multivalued attributes and no repeating groups. To check for First Normal Form, validate that each attribute has a single value for each instance of the entity.

  20. 1NF Review • Classroom will have multiple values • This entity is not in First Normal Form SCHOOL BUILDING #code *name *address *classroom

  21. 1NF Review – School Building 1NF • Classroom is now its own entity. • All attributes have only one value • Both entities are in First Normal Form SCHOOL BUILDING #code *name *address CLASSROOM #number *floor *size

  22. Second Normal Form • Any non-UID attribute must be dependent on the entire UID • All attributes that are not part of the entity’s UID should be dependent on the whole UID. If the attribute changes does the UID also change? • This specifically applies to entities that have a UID that is composed of more than one attribute or a combination of attribute(s) and relationship(s).

  23. 2NF Example

  24. 2NF • Second Normal Form requires that any non-UID attribute be dependent on the entire UID. • This means that all attributes that are not part of the entity’s UID should be dependent on the whole UID. This specifically applies to entities that have a UID that is composed of more than one attribute or a combination of attribute(s) and relationship(s).

  25. 2NF example

  26. 2NF Example - violation

  27. TAPE #number *format *rating a copy of MOVIE #id *title *category recorded on TAPE #number *format MOVIE #id *title *category *rating a copy of recorded on Second normal form violation:

  28. Second Normal Form • Every object in an entity must be identified by a unique value. • In an ERD the symbol #id is commonly used for the unique identifier. • UID’s must be unique and NOT NULL. • In practice a number is typically assigned as a UID. • Some UID’s are composites being formed by barred relationships as in intersection entities for M:M relationships.

  29. 2NF - Example

  30. Third Normal Form • The rule of Third Normal Form states that no non-UID attribute can be dependent on another non-UID attribute.

  31. Third Normal Form No non-UID attribute can be dependent on another non-UID attribute. CITY #id *name *size *population *mayor *state *state flower Third Normal Form Violation

  32. Third Normal Form STATE #id *name *flower *song *motto CITY #id *name *size *population *mayor Third Normal Form in have

  33. Help you remember Normal Forms • This saying might help you remember the 3 normal forms • The truth the whole truth and nothing but the truth. • The truth (1NF) no multivalue attributes, and depend on key • The whole truth (2NF) – whole UID, attributes depend on the whole key • Nothing but the truth (3NF) – depends only on the key

  34. Arcs • Constraints two or more relationships on an entity. • Indicates that any instance of that entity can have only one valid relationship of the relationships in the arc at any one time. • Models an exclusive or across the relationships. An Arc is therefore also called an exclusive arc.

  35. EVENT #id *cost *name *date o description held at the venue for VENUE #id *address o comments PRIVATE HOME o accessibility feature? PUBLICSPACE *rental fee Subtype/Supertype Example

  36. Modeled as an ARC PARTNER #id *first name *last name EVENT PLANNER *expertise DJ *specialty MANAGER *authorized expense limit OTHER

  37. COMPANY #ID *name o contact name MEMBERSHIP *ID *start date *expiration date o termination CUSTOMER #ID *first name *last name ARC Example

  38. ARCS • Arcs are similar to supertypes/subtypes, and are often modeled as such. • Use supertypes/subtypes when you want to represent classifications, or types of things. • Use arcs to represent mutually exclusive relationships between entities. (A type of “either/or” situation)

  39. USER owner of LIST is referred to container of contained referring to LIST ITEM ARCS owned by is referred to referring to

  40. ARCS (previous screen) • An arc always belongs to one entity. • Arcs can include more than two relationships. • Not all relationships of an entity need to be included in an arc. • An entity may have several arcs. • An arc should always consist of relationships of the same optionality: • All relationships in an arc must be mandatory or all must be optional. • Relationships in an arc may be of different degree, although this is rare.

  41. A #id Hierarchy vs. Recursive Hierarchy Recursive

  42. Model Hierarchical Data M:1 relationships Company Division Department Team

  43. TEAM within made up of DEPARTMENT within made up of DIVISION within made up of COMPANY Model Hierarchical Data

  44. The UID of ROOM is the room id and the SUITE it is located within, the FLOOR it is on, and the BUILDING in which it is located. The UID of SUITE is the suite id, the FLOOR on which it is located, and the BUILDING in which it is located. The UID of FLOOR is the floor number and the BUILDING in which it is located. see notes Model Hierarchical Data ROOM #id located within the container of SUITE #number o tenant located on the container of FLOOR #number contained in the container of BUILDING #id *name

  45. A Recursive Relationship is a relationship between an entity and itself. Example – Read the recursive relationship in the following E-R Diagram. Each EMPLOYEE may be managed byone and only one EMPLOYEE Each EMPLOYEE may be the manager ofone or more EMPLOYEEs. managed by EMPLOYEE #badge number *first name *last name o manager_id o job o hire date o salary o commission the managed of Recursive Relationships

  46. Each COMPONENT may bea part ofone or more COMPONENTs. Each COMPONENT may be made up ofone or more COMPONENTs. Example – For the automobile manufacturing organization, consider all elementary parts, subassemblies assemblies and products as instances of an entity called COMPONENT. Then the previous complex E-R Model can be remodeled as a simple recursive relationship. Bill of Materials data as a many-to-many recursive relationship made up of COMPONENT #id Recursive Relationship exampleModel Bill of Materials a part of

  47. Example – Consider the recursive model of a Bill of Materials structure. This model will track information about which components are part of a fan. But if a washer is part of a fan, will it also track how many washers are part of a fan? a part of COMPONENT #id Many-to-Many examples part 1 made of of

  48. Resolve this M:M recursive relationship by adding the intersection entity ASSEMBLY RULE and two M:1 relationships back to the COMPONENT entity. ASSEMBLY RULE will have an attribute of quantity. ASSEMBLY RULE o quantity for for made up of a part of COMPONENT #identifier Resolve M:M recursive relationship part 2

  49. TEAM within within made up of ORGANIZATIONELEMENT #id *name DEPARTMENT within made up of DIVISION within made up of COMPANY Example – A business hierarchy can be drawn as a recursive relationship made up of

  50. Modeling Historical Data • Modeling data that changes over time can be a tricky subject • In this section we will cover factors that influence the decision to model historical data • create a new entity to track it • define a UID for such an entity

More Related