Database Models

1
E-Commerce Databases

• Database models

2
Relational Databases

• Entities (e.g. Customer, Products ...)
• Relationships (e.g. a Customer orders many
  Products)
• Entity Relationship Diagram (ERD)
• Normal Forms (to exclude duplicity of data and
  resulting update problems)

3
Entity Type / Table

• Tables
• Attributes
• Records

4
Primary Keys

• Primary Keys
• Relationships

Primary Key cus_id
5
Relationships
Product
Customer
confirms sales
nm
Relationships become Tables
Product
sales
sold
Customer
confirms
1n
n1
6
Relationships
Customer Table
Sales Table
Product Table
7
Entity Relationship Diagram
Product
sales
sold
Customer
confirms
1n
n1

• Illustrating entity types and their relationships
• Input for the database design team which tables
  to create

8
Redundancy Elimination

• First Normal Form
• First Normal Form eliminates repeating groups by
  putting each into a separate table and connecting
  them with a one-to-many relationship.
• Second Normal Form
• Second Normal Form eliminates functional
  dependencies on a partial key by putting the
  fields in a separate table from those that are
  dependent on the whole key.
• Third Normal Form
• Third Normal Form eliminates functional
  dependencies on non-key fields by putting them in
  a separate table. At this stage, all non-key
  fields are dependent on the key, the whole key
  and nothing but the key.
• -gt Data are normalised !

9
1Nf to 2NF Example

• Separate into different entities/tables
• Check that no attributes are dependent on
  key-subset (e.g. warehouse-address being
  dependent on key warehouse)

10
2NF to 3NF Example

• Identify non-key fields which would be keys of a
  separate table
• Separate out these tables

11
4NF / 5NF
Capability
employee_id, skill_id, lang_id
Competence
Skill
Employee
1n
n1
1n
Language
Speaking
12
Exercise 1 Master Example 1

• Design the Entity Realtionship Diagram for a
  typical customer and product sales situation
  including stock control
• Identify the primary keys, the relationships, and
  the attributes required within each table
• Check against the 3NF
• Do this work in about 1 hour

13
Exercise 2 Master Example 2

• Design the Entity Realtionship Diagram for a
  typical e-newspaper ordering site with customers,
  newspapers to select, newspapers to renew and to
  administer sales and payments
• Identify the primary keys, the relationships, and
  the attributes required within each table
• Check against the 3NF
• Do this work in about 1 hour

14
References

• F. Codd, "A Relational Model of Data for Large
  Shared Data Banks", Comm. ACM 13 (6), June 1970,
  pp. 377-387. The original paper introducing the
  relational data model.
• F. Codd, "Normalized Data Base Structure A Brief
  Tutorial", ACM SIGFIDET Workshop on Data
  Description, Access, and Control, Nov. 11-12,
  1971, San Diego, California, E.F. Codd and A.L.
  Dean (eds.). An early tutorial on the relational
  model and normalization.
• E.F. Codd, "Further Normalization of the Data
  Base Relational Model", R. Rustin (ed.), Data
  Base Systems (Courant Computer Science Symposia
  6), Prentice-Hall, 1972. Also IBM Research Report
  RJ909. The first formal treatment of second and
  third normal forms.
• C.J. Date, An Introduction to Database Systems
  (third edition), Addison-Wesley, 1981. An
  excellent introduction to database systems, with
  emphasis on the relational.
• R. Fagin, "Multivalued Dependencies and a New
  Normal Form for Relational Databases", ACM
  Transactions on Database Systems 2 (3), Sept.
  1977. Also IBM Research Report RJ1812. The
  introduction of fourth normal form.
• R. Fagin, "Normal Forms and Relational Database
  Operators", ACM SIGMOD International Conference
  on Management of Data, May 31-June 1, 1979,
  Boston, Mass. Also IBM Research Report RJ2471,
  Feb. 1979. The introduction of fifth normal form.
  
• W. Kent, "A Primer of Normal Forms", IBM
  Technical Report TR02.600, Dec. 1973. An early,
  formal tutorial on first, second, and third
  normal forms.
• T.-W. Ling, F.W. Tompa, and T. Kameda, "An
  Improved Third Normal Form for Relational
  Databases", ACM Transactions on Database Systems,
  6(2), June 1981, 329-346. One of the first
  treatments of inter-relational dependencies.