DB Design: Notes for Class discussion

For class discussion:

Choosing data types:

  • Character types – for example:
    • strings and textual information
    • zip codes, social security numbers, phone numbers and similar data would be characters or text rather than numbers
    • URLs can be stored as text and later activated as hyperlinks in the application
    • filenames (e.g. “cat.jpg”) can be stored as text and then rendered as images in the webpages with appropriate programming in the application
  • Integer data types
  • Floating point data types
  • Dates
  • Boolean values

Evaluating further considerations regarding the data types for specific fields:

  • data validation and constraints on the values: making sure that the data are reasonable
  • ordering (the way the data would be sorted)
  • calculations that might be needed

Working with keys:

  • primary keys
  • concatenated keys (also called “compound keys”)
  • foreign keys
  • surrogate keys

Referential Integrity

  • insuring referential integrity
  • cascading updates and cascading deletes

Representing Relationships

We will discuss a number of case studies in class and review the concept and design using 1: many and many:many relationships. Further notes will be posted in the Class Notes for those dates.