Cost Management Systems

1
AGENDA

• Today-
• Relational Data Models
• Transforming the ER diagram
• In-class Problem 3
• Data Normalization
• Some examples

2
EXAMPLE

• Pipefitters Supply Company accepts orders that
  includes the customers name and a list of
  inventory items that that the customer wishes to
  purchase. This inventory list includes the
  quantity of each inventory item and the price .
• A sale order can have only one customer each and
  can each have many inventory items and each
  inventory item can appear on many sales orders.

3
Relational DATA Model

• Three components
• Data structure.
• Data organized as tables
• Data manipulation.
• Operations to manipulate data
• Data integrity
• Facilities to specify business rules that
  preserves integrity when data is manipulated

4
Relational Definitions

• Relation
• Named two dimensional table of data
• Every relation has a unique name.
• Every attribute (column) value is single-valued.
• Every row is unique.
• Attributes in tables have unique names.
• The order of the columns is irrelevant.
• The order of the rows is irrelevant.

5
Relational Keys and Structures

• Primary Key-unique identifier
• Composite Key- consists of more than one
  attribute
• Foreign Key-serves as a primary key in some other
  table
• One-to-Many Relationship
• Many-to-Many Relationship

6
Pine Valley Furniture Company
7
Schema for four relations
8
Instance of a relational schema
9
Integrity Constraints

• Domain Constraints
• Allowable values for an attribute.
• Entity Integrity
• No primary key attribute may be null.
• Referential Integrity
• Foreign key must match another relations primary
  key or be null
• Operational Constraints
• Business rules.

10
Referential integrity constraints
11
Transforming E-R Diagrams into Relations

• Map Regular Entities to Relations
• Composite attributes Use only their simple,
  component attributes
• Multi-valued Attribute - Becomes a separate
  relation
• 2. Map Weak Entities
• Becomes a separate relation with a foreign key
  taken from the identifying relation

12
Mapping a composite attribute
CUSTOMER entity type with composite attribute
13
Example of mapping a weak entity
Weak entity DEPENDENT
14
Relations resulting from weak entity
15
Transforming E-R Diagrams Into Relations

• What about multi-valued attributes?
• 3. Map Binary Relationships
• One-to-Many - Primary key on the one side becomes
  a foreign key on the many side.
• Many-to-Many - Create a new relation with the
  primary keys of the two entities as its primary
  key.
• One-to-One - Primary key on the mandatory side
  becomes a foreign key on the optional side .

16
Example of mapping a 1M relationship
Relationship between customers and orders
17
M
Mapping the relationship
18
Example of mapping an MN relationship
Requests relationship (MN)
19
Three resulting relations
20
Mapping a binary 11 relationship
Binary 11 relationship
21
Resulting relations
22
Transforming E-R Diagrams Into Relations

• 4. Map Associative Entities
• Default primary key for the association relation
  is the primary keys of the two entities.

23
Mapping an associative entity with an identifier
Associative entity (SHIPMENT)
24
Three relations
25
Transforming E-R Diagrams Into Relations

• 5. Map Unary Relationships
• One-to-Many - Recursive foreign key in the same
  relation.
• Many-to-Many - Bill-of-materials Two relations
• One for the entity type.
• One for an associative relation in which the
  primary key has two attributes, both taken from
  the primary key of the entity.
• 6. Map Ternary (and n-ary) Relationships
• One relation for each entity and one for the
  associative entity.

26
Mapping a ternary relationship
Ternary relationship with associative entity
27
Mapping the ternary relationship
28
Supertype/Subtype Relationships
29
Mapping Supertype/Subtype relationships
to relations
30
Data Normalization

• Primarily a tool to validate and improve a
  logical design so that it satisfies certain
  constraints that avoid unnecessary duplication of
  data.
• The process of decomposing relations to produce
  smaller, well-structured relations.

31
Steps in Normalization
32
Normalization

• Normalization is accomplished in stages
• Apply rules regarding functional dependencies
  (the relationships between attributes)
• First Normal Form
• No multi-valued attributes (No rows containing
  two or more values for a SINGLE attribute)

33
Second Normal Form

• 1NF and no partial functional dependencies. Every
  non-key attribute depends FULLY on the entire
  primary key only
• Exampleno entries such as
• First Last Stud Course Grade
• Bill Jones 100 math101 A
• Bill Jones 100 math201 A
• Note that here the primary key is Student
  Course and Last depends only on the Student not
  the Course (only part of the primary key).

34
Third Normal Form

• 2NF and no transitive dependencies (functional
  dependency between non-key attributes.)

35
Removing a transitive dependency
Decomposing the SALES relation
36
Relations in 3NF