Chap 007
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Chap 007 as PDF for free.
More details
- Words: 3,665
- Pages: 41
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
C H A P T E R
7
Irwin/McGraw-Hill
DATA MODELING AND ANALYSIS
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Chapter Seven
Whitten Bentley Dittman
Data Modeling and Analysis
• Define systems modeling and differentiate between logical and physical system models. • Define data modeling and explain its benefits. • Recognize and understand the basic concepts and constructs of a data model. • Read and interpret an entity relationship data model. • Explain when data models are constructed during a project and where the models are stored. • Discover entities and relationships. • Construct an entity-relationship context diagram. • Discover or invent keys for entities and construct a key-based diagram. • Construct a fully attributed entity relationship diagram and describe all data structures and attributes to the repository or encyclopedia. • Normalize a logical data model to remove impurities that can make a database unstable, inflexible, and nonscalable. • Describe a useful tool for mapping data requirements to business operating locations.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Chapter Map
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
System Models
A model is a representation of reality. Logical models show what a system is or does. They are implementation independent; that is, they depict the system independent of any technical implementation. Physical models show not only what a system is or does, but also how the system is physically and technically implemented.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling
Data modeling is a technique for organizing and documenting a system’s data. Data modeling is sometimes called database modeling because a data model is eventually implemented as a database. It is sometimes called information modeling. The actual model is frequently called an entity relationship diagram (ERD) because it depicts data in terms of the entities and relationships described by the data.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Sample Entity Relationship Diagram (ERD)
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Entity
An entity is a class of persons, places, objects, events, or concepts about which we need to capture and store data. Name of Entity
Persons: agency, contractor, customer, department, division, employee, instructor, student, supplier.
Places: sales region, building, room, branch office, campus.
Objects: book, machine, part, product, raw material, software license, software package, tool, vehicle model, vehicle.
Events: application, award, cancellation, class, flight, invoice, order, registration, renewal, requisition, reservation, sale, trip.
Concepts: account, block of time, bond, course, fund, qualification, stock.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Entity
An entity instance is a single occurrence of an entity. Example: instances of the entity STUDENT may include
Betty Arnold
John Taylor
Lisa Simmons
Bill Macy
Heather Leath
Tim Wrench
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Attributes An attribute is a descriptive property or characteristic of an entity. Synonyms include element, property, and field. A compound attribute is one that actually consists of other attributes
Irwin/McGraw-Hill
STUDENT Na me .L a s t N a m e .F irs t N a m e .M id d le In itia l A d d re s s .S tr e e t A d d r e s s .C ity .S ta te o r P ro v in c e .C o u n try .P o s ta l C o d e Phone Num ber .A r e a C o d e .E x c h a n g e N u m b e r .N u m b e r W ith in E x c h a n g e D a te o f B irth G ender Race M a jo r G r a d e P o in t A v e ra g e
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Domains
The data type for an attribute defines what type of data can be stored in that attribute. The domain of an attribute defines what values an attribute can legitimately take on. The default value for an attribute is the value that will be recorded if not specified by the user.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Identification A key is an attribute, or a group of attributes, that assumes a unique value for each entity instance. A group of attributes that uniquely identifies an instance of an entity is called a concatenated key. A candidate key is a “candidate to become the primary key” of instances of an entity. A primary key is that candidate key that will most commonly be used to uniquely identify a single entity instance. Any candidate key that is not selected to become the primary key is called an alternate key. A subsetting criteria is an attribute (or concatenated attribute) whose finite values divide all entity instances into useful subsets.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Identification Keys & Subsetting Criteria
Irwin/McGraw-Hill
STUDENT S tu d e n t N u m b e r (P rim a ry K e y ) S o c ia l S e c u r ity N u m b e r (A lte r n a te K e y ) Nam e .L a s t N a m e .F irs t N a m e .M id d le In itia l A d d re s s .S tr e e t A d d re s s .C ity .S ta te o r P ro v in c e .C o u n try .P o s ta l C o d e Phone Num ber .A re a C o d e .E x c h a n g e N u m b e r .N u m b e r W ith in E x c h a n g e D a te o f B irth G e n d e r (S u b s e ttin g C r ite r ia 1 ) R a c e (S u b s e ttin g C rite r ia 2 ) M a jo r (S u b s e ttin g C rite ria 3 ) G ra d e P o in t A v e r a g e
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Relationships
A relationship is a natural business association that exists between one or more entities. The relationship may represent an event that links the entities or merely a logical affinity that exists between the entities.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Cardinality
Cardinality defines the minimum and maximum number of occurrences of one entity that may be related to a single occurrence of the other entity. Because all relationships are bidirectional, cardinality must be defined in both directions for every relationship.
Irwin/McGraw-Hill
bidirectional
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Degree
The degree of a relationship is the number of entities that participate in the relationship.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Degree
A recursive relationship is a relationship that exists between different instances of the same entity
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Degree
Relationships may exist between more than two entities and are called N-ary relationships. The example ERD depicts a ternary relationship.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Degree An associative entity is an entity that inherits its primary key from more than one other entity (called parents). Each part of that concatenated key points to one and only one instance of each of the connecting entities.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Foreign Keys A foreign key is a primary key of one entity that is contributed to (duplicated in) another entity to identify instances of a relationship.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Foreign Keys Nonidentifying relationships are those in which each of the participating entities has its own independent primary key, In other words, none of the primary key attributes is shared. Identifying relationships are those in which the parent entity contributes its primary key to become part of the primary key of the child entity.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Foreign Keys
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Foreign Keys A nonspecific relationship (or many-to-many relationship) is one in which many instances of one entity are associated with many instances of another entity.
Nonspecific relationships must be resolved. Most nonspecific relationships can be resolved by introducing an associative entity.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Resolving Nonspecific Relationships (continued)
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Resolving Nonspecific Relationships (continued)
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Generalization
Generalization is a technique wherein the attributes that are common to several types of an entity are grouped into their own entity, called a supertype. An entity supertype is an entity whose instances store attributes that are common to one or more entity subtypes. An entity subtype is an entity whose instances inherit some common attributes from an entity supertype and then add other attributes that are unique to an instance of the subtype.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Generalization Hierarchy
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Entity Discovery for SoundStage
Irwin/McGraw-Hill
Entity Name
Business Definition
agreement
A contract whereby a member agrees to purchase a certain number of products within a certain time. After fulfilling that agreement, the member becomes eligible for bonus credits that are redeemable for free or discounted products.
member
An active member of one or more clubs. Note: A target system objective is to re-enroll inactive members as opposed to deleting them.
member order
An order generated for a member as part of a monthly promotion, or an order initiated by a member. Note: The current system only supports orders generated from promotions; however, customer initiated orders have been given a high priority as an added option in the proposed system.
transaction
A business event to which the Member Services System must respond.
product
An inventoried product available for promotion and sale to members. Note: System improvement objectives include (1) compatibility with new bar code system being developed for the warehouse, and (2) adaptability to a rapidly changing mix of products.
promotion
A monthly or quarterly event whereby special product offerings are made available to members.
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
The Context Data Model
MEMBER ORDER
is a
sells
responds to
MEMBER
places PRODUCT
has conducted
TRANSACTION
generates
binds
features
Irwin/McGraw-Hill
PROMOTION
AGREEMENT
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
The Key-based Data Model MEMBER ORDER Primary Key Order-Number [PK1]
sells
is a
responds to
MEMBER ORDERED PRODUCT Primary Key Order-Number [PK1] [FK] Product-Number [PK2] [FK]
places
MEMBER Primary Key Member-Number [PK1]
has conducted
sold as
PRODUCT Primary Key Product-Number [PK1]
generates
Irwin/McGraw-Hill
TRANSACTION Primary Key Transaction-Reference-Number [PK1]
AGREEMENT Primary Key Agreement-Number [PK1]
is featured as
TITLE PROMOTION Primary Key Product-Number [PK1] [FK] Promotion-Number [PK2] [FK]
binds
features
PROMOTION Primary Key Promotion-Number [PK1]
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
The Key-based Data Model With Generalization is a
MEMBER ORDER Primary Key Order-Number [PK1]
sells
MEMBER ORDERED PRODUCT Primary Key Order-Number [PK1] [FK] Product-Number [PK2] [FK]
responds to
MEMBER Primary Key Member-Number [PK1]
places
has conducted
binds
sold as
TRANSACTION Primary Key Transaction-Reference-Number [PK1]
AGREEMENT Primary Key Agreement-Number [PK1] PRODUCT Primary Key Product-Number [PK1]
generates is a MERCHANDISE Primary Key Product-Number [PK1] [FK]
is a
AUDIO TITLE Primary Key Product-Number [PK1] [FK]
Irwin/McGraw-Hill
is a TITLE Primary Key Product-Number [PK1] [FK]
is a VIDEO TITLE Primary Key Product-Number [PK1] [FK]
PROMOTION Primary Key Promotion-Number [PK1]
is featured as
PROMOTION TITLE Primary Key Promotion-Number [PK1] [FK] Product-Number [PK2] [FK]
is a
features
GAME TITLE Primary Key Product-Number [PK1] [FK]
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
The Fully-Attributed Data Model
sells
MEMBER ORDERED PRODUCT Primary Key Order-Number [PK1] [FK] Product-Number [PK2] [FK]
sold as
MEMBER ORDER Primary Key Order-Number [PK1] Non-Key Attributes Order-Creation-Date Order-Fill-Date Member-Name Member-Address Shipping-Address-Name Shipping-Street-Address Shipping-City Shipping-State Shipping-Zip Shipping-Instructions Order-Sub-Total Order-Sales-Tax Order-Shipping-Method Order-Shipping-&-Handling-Cost Order-Status Order-Prepaid-Amount Order-Prepayment-Method Promotion-Number [FK] Member-Number [FK] Member-Number-1 . Member-Number [FK] Ordered-Product-Number Quantity-Shipped Quantity-Backordered Ordered-Product-Description Ordered-Product-Title Purchased-Unit-Price Extended-Price
is a
responds to
places
MEMBER Primary Key Member-Number [PK1] Non-Key Attributes Member-Name Member-Status Member-Street-Address Member-Post-Office-Box Member-City Member-State Member-Zip-Code Member-Daytime-Phone-Number Member-Date-of-Last-Order Member-Balance-Due Member-Credit-Card-Type Member-Credit-Card-Number Member-Credit-Card-Expire-Date Member-Bonus-Balance-Available Audio-Category-Preference Audio-Media-Preference Date-Enrolled Expiration-Date Game-Category-Preference Game-Media-Preference Number-of-Credits-Earned Video-Category-Preference Video-Media-Preference Agreement-Number [FK] Privacy-Code Email-Address
PRODUCT Primary Key Product-Number [PK1] Non-Key Attributes "Universal-Product-Code (Alternate Key)" Quantity-in-Stock Product-Type Suggested-Retail-Price Default-Unit-Price Current-Special-Unit-Price Current-Month-Units-Sold Current-Year-Units-Sold Total-Lifetime-Units-Sold is a MERCHANDISE Primary Key Product-Number [PK1] [FK] Non-Key Attributes Merchandise-Name Merchandise-Description Merchandise-Type Unit-of-Measure
is a AUDIO TITLE Primary Key Product-Number [PK1] [FK] Non-Key Attributes Artist Audio-Category Audio-Sub-Category Number-of-Units-in-Package Audio-Media-Code Content-Advisory-Code
Irwin/McGraw-Hill
has conducted
TRANSACTION Primary Key Transaction-Reference-Number [PK1] Non-Key Attributes Transaction-Date Transaction-Type Transaction-Description Transaction-Amount Member-Number [FK] Order-Number [FK]
AGREEMENT Primary Key Agreement-Number [PK1] Non-Key Attributes Agreement-Expire-Date Agreement-Active-Date Fulfillment-Period Required-Number-of-Credits
binds
is a TITLE Primary Key Product-Number [PK1] [FK] Non-Key Attributes Title-of-Work Title-Cover Catalog-Description Copyright-Date Entertainment-Category Credit-Value
is a VIDEO TITLE Primary Key Product-Number [PK1] [FK] Non-Key Attributes Producer Director Video-Category Video-Sub-Category Closed-Captioned Language Running-Time Video-Media-Type Video-Encoding Screen-Aspect MPA-Rating-Code
generates
is featured as TITLE PROMOTION Primary Key Promotion-Number [PK1] [FK] Product-Number [PK2] [FK]
PROMOTION Primary Key Promotion-Number [PK1] Non-Key Attributes Promotion-Release-Date Promotion-Status Promotion-Type Product-Number Title-of-Work
features
is a GAME TITLE Primary Key Product-Number [PK1] [FK] Non-Key Attributes Manufacturer Game-Category Game-Sub-Category Game-Platform Game-Media-Type Number-of-Players Parent-Advisory-Code
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Analysis & Normalization
Data analysis is a process that prepares a data model for implementation as a simple, nonredundant, flexible, and adaptable database. The specific technique is called normalization. Normalization is a data analysis technique that organizes data attributes such that they are grouped to form nonredundant, stable, flexible, and adaptive entities.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Normalization: 1NF, 2NF, 3NF • An entity is in first normal form (1NF) if there are no attributes that can have more than one value for a single instance of the entity. Any attributes that can have multiple values actually describe a separate entity, possibly an entity and relationship. • An entity is in second normal form (2NF) if it is already in 1NF and if the values of all nonprimary key attributes are dependent on the full primary key—not just part of it. Any nonkey attributes that are dependent on only part of the primary key should be moved to any entity where that partial key is actually the full key. This may require creating a new entity and relationship on the model. • An entity is in third normal form (3NF) if it is already in 2NF and if the values of its nonprimary key attributes are not dependent on any other non-primary key attributes. Any nonkey attributes that are dependent on other nonkey attributes must be moved or deleted. Again, new entities and relationships may have to be added to the data model.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
First Normal Form Example
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
First Normal Form Example
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Second Normal Form Example
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Second Normal Form Example
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Third Normal Form Example
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Third Normal Form Example
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
SoundStage 3NF Data Model MEMBER ORDERED PRODUCT Primary Key Order-Number [PK1] [FK] Product-Number [PK2] [FK] Non-Key Attributes Quantity-Ordered Quantity-Shipped Quantity-Backordered Purchase-Unit-Price Credits-Earned
sells
MEMBER ORDER Primary Key Order-Number [PK1] Non-Key Attributes Order-Creation-Date Order-Fill-Date Shipping-Address-Name Shipping-Street-Address Shipping-City Shipping-State Shipping-Zip Shipping-Instructions Order-Sub-Total Order-Sales-Tax Order-Shipping-Method Order-Shipping-&-Handling-Cost Order-Status Order-Prepaid-Amount Order-Prepayment-Method Promotion-Number [FK] Member-Number [FK] Member-Number-1 . Member-Number [FK]
sold as
PRODUCT Primary Key Product-Number [PK1] Non-Key Attributes "Universal-Product-Code (Alternate Key)" Quantity-in-Stock Product-Type Suggested-Retail-Price Default-Unit-Price Current-Special-Unit-Price Current-Month-Units-Sold Current-Year-Units-Sold Total-Lifetime-Units-Sold is a MERCHANDISE Primary Key Product-Number [PK1] [FK] Non-Key Attributes Merchandise-Name Merchandise-Description Merchandise-Type Unit-of-Measure
is a AUDIO TITLE Primary Key Product-Number [PK1] [FK] Non-Key Attributes Artist Audio-Category Audio-Sub-Category Number-of-Units-in-Package Audio-Media-Code Content-Advisory-Code
Irwin/McGraw-Hill
is a
responds to
places
MEMBER Primary Key Member-Number [PK1] Non-Key Attributes Member-Name Member-Status Member-Street-Address Member-Post-Office-Box Member-City Member-State Member-Zip-Code Member-Daytime-Phone-Number Member-Date-of-Last-Order Member-Balance-Due Member-Credit-Card-Type Member-Credit-Card-Number Member-Credit-Card-Expire-Date Member-Bonus-Balance-Available Audio-Category-Preference Audio-Media-Preference Date-Enrolled Expiration-Date Game-Category-Preference Game-Media-Preference Number-of-Credits-Earned Video-Category-Preference Video-Media-Preference Agreement-Number [FK] Privacy-Code Email-Address
is a
binds
TITLE Primary Key Product-Number [PK1] [FK] Non-Key Attributes Title-of-Work Title-Cover Catalog-Description Copyright-Date Entertainment-Category Credit-Value
is a VIDEO TITLE Primary Key Product-Number [PK1] [FK] Non-Key Attributes Producer Director Video-Category Video-Sub-Category Closed-Captioned Language Running-Time Video-Media-Type Video-Encoding Screen-Aspect MPA-Rating-Code
generates
has conducted
TRANSACTION Primary Key Transaction-Reference-Number [PK1] Non-Key Attributes Transaction-Date Transaction-Type Transaction-Description Transaction-Amount Member-Number [FK] Order-Number [FK]
AGREEMENT Primary Key Agreement-Number [PK1] Non-Key Attributes Agreement-Expire-Date Agreement-Active-Date Fulfillment-Period Required-Number-of-Credits
is featured as
is a GAME TITLE Primary Key Product-Number [PK1] [FK] Non-Key Attributes Manufacturer Game-Category Game-Sub-Category Game-Platform Game-Media-Type Number-of-Players Parent-Advisory-Code
TITLE PROMOTION Primary Key Product-Number [PK1] [FK] Promotion-Number [PK2] [FK]
features
PROMOTION Primary Key Promotion-Number [PK1] Non-Key Attributes Promotion-Release-Date Promotion-Status Promotion-Type
3NF Member Services (Entity Relation Subject Area) SA/2001 Tue May 02, 2000 10:41 Comment Sandra Shepherd
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Customer .Customer Number .Customer Name .Customer Address .Customer Credit Rating .Customer Balance Due Order .Order Number .Order Date .Order Amount Ordered Product .Quantity Ordered .Ordered Item Unit Price Product .Product Number .Product Name .Product Description .Product Unit of Measure .Product Current Unit Price .Product Quantity on Hand
Irwin/McGraw-Hill
INDV R RU RU X R INDV SRD SRD SRD INDV SUD SUD ALL R R R R R X
R R R
ALL R R R ALL R R ALL CRUD CRUD CRUD CRUD CRUD
SS R R
CRUD CRUD CRUD
SS R
CRUD CRUD ALL R R RU R R
ALL R R R R RU
ALL CRUD CRUD CRUD R R ALL CRUD CRUD CRUD ALL CRUD CRUD ALL R R R R R R
ALL R R R RU RU R R R R R
SS CRUD CRUD CRUD R R SS CRUD CRUD CRUD SS CRUD CRUD ALL R R R R R R
INDV = individual
ALL = ALL
SS = subset
X = no access
S = submit
C = create
R = read
U = update
SS R R R
SS R R R SS
ALL R R R R R RU
SS CRUD CRUD CRUD R R SS CRUD CRUD CRUD SS CRUD CRUD ALL R R R R R R
. Warehose
San Diego
. Sales
San Francisco
. Warehouse
. Sales
Boston
. A/R
. Sales
. Warehouse
. Advertsing
. Marketing
Kansas City
Customers
Entity . Attribute
Location
Data-to-Location-CRUD Matrix
SS R R R
SS R R R SS
ALL R R R R R RU
D = delete
Copyright © 2000 The McGraw-Hill Companies. All Rights res
Whitten Bentley Dittman
C H A P T E R
7
Irwin/McGraw-Hill
DATA MODELING AND ANALYSIS
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Chapter Seven
Whitten Bentley Dittman
Data Modeling and Analysis
• Define systems modeling and differentiate between logical and physical system models. • Define data modeling and explain its benefits. • Recognize and understand the basic concepts and constructs of a data model. • Read and interpret an entity relationship data model. • Explain when data models are constructed during a project and where the models are stored. • Discover entities and relationships. • Construct an entity-relationship context diagram. • Discover or invent keys for entities and construct a key-based diagram. • Construct a fully attributed entity relationship diagram and describe all data structures and attributes to the repository or encyclopedia. • Normalize a logical data model to remove impurities that can make a database unstable, inflexible, and nonscalable. • Describe a useful tool for mapping data requirements to business operating locations.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Chapter Map
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
System Models
A model is a representation of reality. Logical models show what a system is or does. They are implementation independent; that is, they depict the system independent of any technical implementation. Physical models show not only what a system is or does, but also how the system is physically and technically implemented.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling
Data modeling is a technique for organizing and documenting a system’s data. Data modeling is sometimes called database modeling because a data model is eventually implemented as a database. It is sometimes called information modeling. The actual model is frequently called an entity relationship diagram (ERD) because it depicts data in terms of the entities and relationships described by the data.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Sample Entity Relationship Diagram (ERD)
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Entity
An entity is a class of persons, places, objects, events, or concepts about which we need to capture and store data. Name of Entity
Persons: agency, contractor, customer, department, division, employee, instructor, student, supplier.
Places: sales region, building, room, branch office, campus.
Objects: book, machine, part, product, raw material, software license, software package, tool, vehicle model, vehicle.
Events: application, award, cancellation, class, flight, invoice, order, registration, renewal, requisition, reservation, sale, trip.
Concepts: account, block of time, bond, course, fund, qualification, stock.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Entity
An entity instance is a single occurrence of an entity. Example: instances of the entity STUDENT may include
Betty Arnold
John Taylor
Lisa Simmons
Bill Macy
Heather Leath
Tim Wrench
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Attributes An attribute is a descriptive property or characteristic of an entity. Synonyms include element, property, and field. A compound attribute is one that actually consists of other attributes
Irwin/McGraw-Hill
STUDENT Na me .L a s t N a m e .F irs t N a m e .M id d le In itia l A d d re s s .S tr e e t A d d r e s s .C ity .S ta te o r P ro v in c e .C o u n try .P o s ta l C o d e Phone Num ber .A r e a C o d e .E x c h a n g e N u m b e r .N u m b e r W ith in E x c h a n g e D a te o f B irth G ender Race M a jo r G r a d e P o in t A v e ra g e
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Domains
The data type for an attribute defines what type of data can be stored in that attribute. The domain of an attribute defines what values an attribute can legitimately take on. The default value for an attribute is the value that will be recorded if not specified by the user.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Identification A key is an attribute, or a group of attributes, that assumes a unique value for each entity instance. A group of attributes that uniquely identifies an instance of an entity is called a concatenated key. A candidate key is a “candidate to become the primary key” of instances of an entity. A primary key is that candidate key that will most commonly be used to uniquely identify a single entity instance. Any candidate key that is not selected to become the primary key is called an alternate key. A subsetting criteria is an attribute (or concatenated attribute) whose finite values divide all entity instances into useful subsets.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Identification Keys & Subsetting Criteria
Irwin/McGraw-Hill
STUDENT S tu d e n t N u m b e r (P rim a ry K e y ) S o c ia l S e c u r ity N u m b e r (A lte r n a te K e y ) Nam e .L a s t N a m e .F irs t N a m e .M id d le In itia l A d d re s s .S tr e e t A d d re s s .C ity .S ta te o r P ro v in c e .C o u n try .P o s ta l C o d e Phone Num ber .A re a C o d e .E x c h a n g e N u m b e r .N u m b e r W ith in E x c h a n g e D a te o f B irth G e n d e r (S u b s e ttin g C r ite r ia 1 ) R a c e (S u b s e ttin g C rite r ia 2 ) M a jo r (S u b s e ttin g C rite ria 3 ) G ra d e P o in t A v e r a g e
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Relationships
A relationship is a natural business association that exists between one or more entities. The relationship may represent an event that links the entities or merely a logical affinity that exists between the entities.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Cardinality
Cardinality defines the minimum and maximum number of occurrences of one entity that may be related to a single occurrence of the other entity. Because all relationships are bidirectional, cardinality must be defined in both directions for every relationship.
Irwin/McGraw-Hill
bidirectional
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Degree
The degree of a relationship is the number of entities that participate in the relationship.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Degree
A recursive relationship is a relationship that exists between different instances of the same entity
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Degree
Relationships may exist between more than two entities and are called N-ary relationships. The example ERD depicts a ternary relationship.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Degree An associative entity is an entity that inherits its primary key from more than one other entity (called parents). Each part of that concatenated key points to one and only one instance of each of the connecting entities.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Foreign Keys A foreign key is a primary key of one entity that is contributed to (duplicated in) another entity to identify instances of a relationship.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Foreign Keys Nonidentifying relationships are those in which each of the participating entities has its own independent primary key, In other words, none of the primary key attributes is shared. Identifying relationships are those in which the parent entity contributes its primary key to become part of the primary key of the child entity.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Foreign Keys
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Foreign Keys A nonspecific relationship (or many-to-many relationship) is one in which many instances of one entity are associated with many instances of another entity.
Nonspecific relationships must be resolved. Most nonspecific relationships can be resolved by introducing an associative entity.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Resolving Nonspecific Relationships (continued)
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Resolving Nonspecific Relationships (continued)
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Modeling Concepts: Generalization
Generalization is a technique wherein the attributes that are common to several types of an entity are grouped into their own entity, called a supertype. An entity supertype is an entity whose instances store attributes that are common to one or more entity subtypes. An entity subtype is an entity whose instances inherit some common attributes from an entity supertype and then add other attributes that are unique to an instance of the subtype.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Generalization Hierarchy
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Entity Discovery for SoundStage
Irwin/McGraw-Hill
Entity Name
Business Definition
agreement
A contract whereby a member agrees to purchase a certain number of products within a certain time. After fulfilling that agreement, the member becomes eligible for bonus credits that are redeemable for free or discounted products.
member
An active member of one or more clubs. Note: A target system objective is to re-enroll inactive members as opposed to deleting them.
member order
An order generated for a member as part of a monthly promotion, or an order initiated by a member. Note: The current system only supports orders generated from promotions; however, customer initiated orders have been given a high priority as an added option in the proposed system.
transaction
A business event to which the Member Services System must respond.
product
An inventoried product available for promotion and sale to members. Note: System improvement objectives include (1) compatibility with new bar code system being developed for the warehouse, and (2) adaptability to a rapidly changing mix of products.
promotion
A monthly or quarterly event whereby special product offerings are made available to members.
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
The Context Data Model
MEMBER ORDER
is a
sells
responds to
MEMBER
places PRODUCT
has conducted
TRANSACTION
generates
binds
features
Irwin/McGraw-Hill
PROMOTION
AGREEMENT
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
The Key-based Data Model MEMBER ORDER Primary Key Order-Number [PK1]
sells
is a
responds to
MEMBER ORDERED PRODUCT Primary Key Order-Number [PK1] [FK] Product-Number [PK2] [FK]
places
MEMBER Primary Key Member-Number [PK1]
has conducted
sold as
PRODUCT Primary Key Product-Number [PK1]
generates
Irwin/McGraw-Hill
TRANSACTION Primary Key Transaction-Reference-Number [PK1]
AGREEMENT Primary Key Agreement-Number [PK1]
is featured as
TITLE PROMOTION Primary Key Product-Number [PK1] [FK] Promotion-Number [PK2] [FK]
binds
features
PROMOTION Primary Key Promotion-Number [PK1]
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
The Key-based Data Model With Generalization is a
MEMBER ORDER Primary Key Order-Number [PK1]
sells
MEMBER ORDERED PRODUCT Primary Key Order-Number [PK1] [FK] Product-Number [PK2] [FK]
responds to
MEMBER Primary Key Member-Number [PK1]
places
has conducted
binds
sold as
TRANSACTION Primary Key Transaction-Reference-Number [PK1]
AGREEMENT Primary Key Agreement-Number [PK1] PRODUCT Primary Key Product-Number [PK1]
generates is a MERCHANDISE Primary Key Product-Number [PK1] [FK]
is a
AUDIO TITLE Primary Key Product-Number [PK1] [FK]
Irwin/McGraw-Hill
is a TITLE Primary Key Product-Number [PK1] [FK]
is a VIDEO TITLE Primary Key Product-Number [PK1] [FK]
PROMOTION Primary Key Promotion-Number [PK1]
is featured as
PROMOTION TITLE Primary Key Promotion-Number [PK1] [FK] Product-Number [PK2] [FK]
is a
features
GAME TITLE Primary Key Product-Number [PK1] [FK]
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
The Fully-Attributed Data Model
sells
MEMBER ORDERED PRODUCT Primary Key Order-Number [PK1] [FK] Product-Number [PK2] [FK]
sold as
MEMBER ORDER Primary Key Order-Number [PK1] Non-Key Attributes Order-Creation-Date Order-Fill-Date Member-Name Member-Address Shipping-Address-Name Shipping-Street-Address Shipping-City Shipping-State Shipping-Zip Shipping-Instructions Order-Sub-Total Order-Sales-Tax Order-Shipping-Method Order-Shipping-&-Handling-Cost Order-Status Order-Prepaid-Amount Order-Prepayment-Method Promotion-Number [FK] Member-Number [FK] Member-Number-1 . Member-Number [FK] Ordered-Product-Number Quantity-Shipped Quantity-Backordered Ordered-Product-Description Ordered-Product-Title Purchased-Unit-Price Extended-Price
is a
responds to
places
MEMBER Primary Key Member-Number [PK1] Non-Key Attributes Member-Name Member-Status Member-Street-Address Member-Post-Office-Box Member-City Member-State Member-Zip-Code Member-Daytime-Phone-Number Member-Date-of-Last-Order Member-Balance-Due Member-Credit-Card-Type Member-Credit-Card-Number Member-Credit-Card-Expire-Date Member-Bonus-Balance-Available Audio-Category-Preference Audio-Media-Preference Date-Enrolled Expiration-Date Game-Category-Preference Game-Media-Preference Number-of-Credits-Earned Video-Category-Preference Video-Media-Preference Agreement-Number [FK] Privacy-Code Email-Address
PRODUCT Primary Key Product-Number [PK1] Non-Key Attributes "Universal-Product-Code (Alternate Key)" Quantity-in-Stock Product-Type Suggested-Retail-Price Default-Unit-Price Current-Special-Unit-Price Current-Month-Units-Sold Current-Year-Units-Sold Total-Lifetime-Units-Sold is a MERCHANDISE Primary Key Product-Number [PK1] [FK] Non-Key Attributes Merchandise-Name Merchandise-Description Merchandise-Type Unit-of-Measure
is a AUDIO TITLE Primary Key Product-Number [PK1] [FK] Non-Key Attributes Artist Audio-Category Audio-Sub-Category Number-of-Units-in-Package Audio-Media-Code Content-Advisory-Code
Irwin/McGraw-Hill
has conducted
TRANSACTION Primary Key Transaction-Reference-Number [PK1] Non-Key Attributes Transaction-Date Transaction-Type Transaction-Description Transaction-Amount Member-Number [FK] Order-Number [FK]
AGREEMENT Primary Key Agreement-Number [PK1] Non-Key Attributes Agreement-Expire-Date Agreement-Active-Date Fulfillment-Period Required-Number-of-Credits
binds
is a TITLE Primary Key Product-Number [PK1] [FK] Non-Key Attributes Title-of-Work Title-Cover Catalog-Description Copyright-Date Entertainment-Category Credit-Value
is a VIDEO TITLE Primary Key Product-Number [PK1] [FK] Non-Key Attributes Producer Director Video-Category Video-Sub-Category Closed-Captioned Language Running-Time Video-Media-Type Video-Encoding Screen-Aspect MPA-Rating-Code
generates
is featured as TITLE PROMOTION Primary Key Promotion-Number [PK1] [FK] Product-Number [PK2] [FK]
PROMOTION Primary Key Promotion-Number [PK1] Non-Key Attributes Promotion-Release-Date Promotion-Status Promotion-Type Product-Number Title-of-Work
features
is a GAME TITLE Primary Key Product-Number [PK1] [FK] Non-Key Attributes Manufacturer Game-Category Game-Sub-Category Game-Platform Game-Media-Type Number-of-Players Parent-Advisory-Code
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Data Analysis & Normalization
Data analysis is a process that prepares a data model for implementation as a simple, nonredundant, flexible, and adaptable database. The specific technique is called normalization. Normalization is a data analysis technique that organizes data attributes such that they are grouped to form nonredundant, stable, flexible, and adaptive entities.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Normalization: 1NF, 2NF, 3NF • An entity is in first normal form (1NF) if there are no attributes that can have more than one value for a single instance of the entity. Any attributes that can have multiple values actually describe a separate entity, possibly an entity and relationship. • An entity is in second normal form (2NF) if it is already in 1NF and if the values of all nonprimary key attributes are dependent on the full primary key—not just part of it. Any nonkey attributes that are dependent on only part of the primary key should be moved to any entity where that partial key is actually the full key. This may require creating a new entity and relationship on the model. • An entity is in third normal form (3NF) if it is already in 2NF and if the values of its nonprimary key attributes are not dependent on any other non-primary key attributes. Any nonkey attributes that are dependent on other nonkey attributes must be moved or deleted. Again, new entities and relationships may have to be added to the data model.
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
First Normal Form Example
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
First Normal Form Example
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Second Normal Form Example
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Second Normal Form Example
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Third Normal Form Example
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Third Normal Form Example
Irwin/McGraw-Hill
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
SoundStage 3NF Data Model MEMBER ORDERED PRODUCT Primary Key Order-Number [PK1] [FK] Product-Number [PK2] [FK] Non-Key Attributes Quantity-Ordered Quantity-Shipped Quantity-Backordered Purchase-Unit-Price Credits-Earned
sells
MEMBER ORDER Primary Key Order-Number [PK1] Non-Key Attributes Order-Creation-Date Order-Fill-Date Shipping-Address-Name Shipping-Street-Address Shipping-City Shipping-State Shipping-Zip Shipping-Instructions Order-Sub-Total Order-Sales-Tax Order-Shipping-Method Order-Shipping-&-Handling-Cost Order-Status Order-Prepaid-Amount Order-Prepayment-Method Promotion-Number [FK] Member-Number [FK] Member-Number-1 . Member-Number [FK]
sold as
PRODUCT Primary Key Product-Number [PK1] Non-Key Attributes "Universal-Product-Code (Alternate Key)" Quantity-in-Stock Product-Type Suggested-Retail-Price Default-Unit-Price Current-Special-Unit-Price Current-Month-Units-Sold Current-Year-Units-Sold Total-Lifetime-Units-Sold is a MERCHANDISE Primary Key Product-Number [PK1] [FK] Non-Key Attributes Merchandise-Name Merchandise-Description Merchandise-Type Unit-of-Measure
is a AUDIO TITLE Primary Key Product-Number [PK1] [FK] Non-Key Attributes Artist Audio-Category Audio-Sub-Category Number-of-Units-in-Package Audio-Media-Code Content-Advisory-Code
Irwin/McGraw-Hill
is a
responds to
places
MEMBER Primary Key Member-Number [PK1] Non-Key Attributes Member-Name Member-Status Member-Street-Address Member-Post-Office-Box Member-City Member-State Member-Zip-Code Member-Daytime-Phone-Number Member-Date-of-Last-Order Member-Balance-Due Member-Credit-Card-Type Member-Credit-Card-Number Member-Credit-Card-Expire-Date Member-Bonus-Balance-Available Audio-Category-Preference Audio-Media-Preference Date-Enrolled Expiration-Date Game-Category-Preference Game-Media-Preference Number-of-Credits-Earned Video-Category-Preference Video-Media-Preference Agreement-Number [FK] Privacy-Code Email-Address
is a
binds
TITLE Primary Key Product-Number [PK1] [FK] Non-Key Attributes Title-of-Work Title-Cover Catalog-Description Copyright-Date Entertainment-Category Credit-Value
is a VIDEO TITLE Primary Key Product-Number [PK1] [FK] Non-Key Attributes Producer Director Video-Category Video-Sub-Category Closed-Captioned Language Running-Time Video-Media-Type Video-Encoding Screen-Aspect MPA-Rating-Code
generates
has conducted
TRANSACTION Primary Key Transaction-Reference-Number [PK1] Non-Key Attributes Transaction-Date Transaction-Type Transaction-Description Transaction-Amount Member-Number [FK] Order-Number [FK]
AGREEMENT Primary Key Agreement-Number [PK1] Non-Key Attributes Agreement-Expire-Date Agreement-Active-Date Fulfillment-Period Required-Number-of-Credits
is featured as
is a GAME TITLE Primary Key Product-Number [PK1] [FK] Non-Key Attributes Manufacturer Game-Category Game-Sub-Category Game-Platform Game-Media-Type Number-of-Players Parent-Advisory-Code
TITLE PROMOTION Primary Key Product-Number [PK1] [FK] Promotion-Number [PK2] [FK]
features
PROMOTION Primary Key Promotion-Number [PK1] Non-Key Attributes Promotion-Release-Date Promotion-Status Promotion-Type
3NF Member Services (Entity Relation Subject Area) SA/2001 Tue May 02, 2000 10:41 Comment Sandra Shepherd
Copyright © 2000 The McGraw-Hill Companies. All Rights res
SYSTEMS ANALYSIS AND DESIGN METHODS 5th Edition
Whitten Bentley Dittman
Customer .Customer Number .Customer Name .Customer Address .Customer Credit Rating .Customer Balance Due Order .Order Number .Order Date .Order Amount Ordered Product .Quantity Ordered .Ordered Item Unit Price Product .Product Number .Product Name .Product Description .Product Unit of Measure .Product Current Unit Price .Product Quantity on Hand
Irwin/McGraw-Hill
INDV R RU RU X R INDV SRD SRD SRD INDV SUD SUD ALL R R R R R X
R R R
ALL R R R ALL R R ALL CRUD CRUD CRUD CRUD CRUD
SS R R
CRUD CRUD CRUD
SS R
CRUD CRUD ALL R R RU R R
ALL R R R R RU
ALL CRUD CRUD CRUD R R ALL CRUD CRUD CRUD ALL CRUD CRUD ALL R R R R R R
ALL R R R RU RU R R R R R
SS CRUD CRUD CRUD R R SS CRUD CRUD CRUD SS CRUD CRUD ALL R R R R R R
INDV = individual
ALL = ALL
SS = subset
X = no access
S = submit
C = create
R = read
U = update
SS R R R
SS R R R SS
ALL R R R R R RU
SS CRUD CRUD CRUD R R SS CRUD CRUD CRUD SS CRUD CRUD ALL R R R R R R
. Warehose
San Diego
. Sales
San Francisco
. Warehouse
. Sales
Boston
. A/R
. Sales
. Warehouse
. Advertsing
. Marketing
Kansas City
Customers
Entity . Attribute
Location
Data-to-Location-CRUD Matrix
SS R R R
SS R R R SS
ALL R R R R R RU
D = delete
Copyright © 2000 The McGraw-Hill Companies. All Rights res
Related Documents
Chap 007
June 2020 14
Chap 007
November 2019 18
Chap 007
May 2020 21
Chap 007
May 2020 17
Chap 007
May 2020 11
007
November 2019 50More Documents from ""
Chapter 3b
May 2020 20
Data Representation
June 2020 22
Introduction To Assembly Language (with New Lessons)
June 2020 22
Chap 013
June 2020 25
Process Modeling: C H A P T E R
June 2020 1