1 / 18

Database Design--Topics

Learn about the steps involved in identifying and fixing relationships in database design, including identifying information elements, entities, primary keys, and non-key attributes. Address performance issues and design application objects, reports, and stored procedures.

critchey
Download Presentation

Database Design--Topics

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--Topics • DB Design Steps • Identify Relationships • M:M Relationships

  2. Database Design Steps • Identify “Information Elements” • Identify Entities • Identify Primary Keys • Identify Relationships • Fix Relationship Problems • Identify Non-Key Attributes • Fix Attribute Problems • Address Performance Issues • Build Application Objects (stored procedures, views) • Design Reports • List is not strictly sequential • Some steps performed recursively • Some steps require returning to previous steps

  3. Relationships • "A meaningful association between (or among) entities" • What in the world does this mean? • Relationships indicate how entities interact from the organization's perspective • Relationships will end up defining paths through the database along which data will be retrieved • The paths usually mirror real world associations between entities

  4. Relationships (cont.) • While entities are nouns relationships are verbs • Buys, teaches, sells, owns, … • Is a • Has • Relationship verb describes how two entities interact with each other • If two entities do not interact (from the organization’s official viewpoint) then there is no relationship between them • Professor ?? Football_Play • ‘Direction’ of verb is not very important Important special cases

  5. Relationships Exercise Connect all Related EntitiesFrom our List and Name the Relationships(do not apply cardinalities)

  6. Cardinality • Understanding “Cardinality” is one of the most fundamentally important concepts in DB design • Cardinality indicates how many occurrences of an entity must or may be allowed in the relationship with any one occurrence in the other entity • Cardinality goes in each direction • One student may/must take ? Classes • One class must/may be taken by ? Students

  7. Cardinality (cont.) • The cardinality at each end of a relationship specifies two elements • A maximum number of related records • One (at most one) • Many (an unconstrained number more than one) • A minimum number of related records • Mandatory (at least one related record is required) • Optional (no related record is required though one or many may exist)

  8. Cardinality Notation • Mandatory One • One professor must have exactly one phone number • Mandatory Many • A customer must have at least one purchase to be a customer but may have many • Optional One • One professor may have as few as zero reserved parking spaces but may have only one at most • Optional Many • One student may take as few as zero classes but may take more than one class

  9. Cardinality Notation (cont.) • Interpret these cardinalities

  10. Cardinality Notation (cont.) • Relationship cardinality is governed by the number of related occurrences you could have • If a student could have two majors then relationship is ‘Many’ on the Major side • May a car or house have more than one owner? • May an Employee be assigned to more than one job title at a time? • Will you record a Supplier if you do not currently carry any of their products? • Will you enter an Employee without assigning them to a position?

  11. Cardinality Notation (cont.) • The graphical layout of a relationship is purely arbitrary

  12. Organization Policy and Cardinality • Business policies (or regulations) may affect cardinality • Identify legitimate business policies that support each of the different cardinality combinations reflected here

  13. Cardinality Exercise Apply Cardinalities to EachRelationship Identified in the Prior Step

  14. Foreign Keys • Relationships are established when the Primary Key attribute(s) of one entity is/are found in another entity • These attributes are called Foreign Keys in the other entity Foreign Keys

  15. Many-to-Many Relationships • Many-to-Many (M:N) relationships must be decomposed into a new entity and two relationships • Carefully examine the cardinality of the two new relationships

  16. M:M Relationship Exercise Decompose M:M Relationships From the Previous Exercise

  17. Foreign Key Exercise Place Appropriate Foreign Keys In All Child EntitiesFound in the Previous Exercise

  18. Other Problems Exercise Fix Other Problems Found in the Previous Exercise • Ternary or n-ary relationships • Attributes on relationships (probably none yet) • Unary M:M relationships

More Related