Designing a Database

1
Designing a Database

• Unleashing the Power ofRelational Database Design

2
Understanding Databases

• There are three basic kinds of databases called
  (1) flat file, (2) relational, and (3)
  object-oriented.
• A flat file database keeps all of the records in
  a single file, in which the data elements are
  separated by a break character such as a comma or
  a tab.
• The terms comma-delimited or tab-delimited refer
  to data stored in this manner.

3
Comma Delimited Data
4
What Is a Data Table?

• Flat file databases typically contain just one
  data table, which is a database structure that
  organizes the data into rows and columns.
• Each row contains one record, and the columns
  contain the data fields that the record
  comprises.

5
Visualizing a Data Table
6
What Is a Relational Database?

• A relational database is a data structure that
  comprises multiple tables containing primary key
  columns through which the records in one table
  can relate (i.e., key) to the data in another
  table.
• A data tables primary key is a column in which
  every entry is unique.
• A foreign key is a data field that relates the
  record to the table in which that same column
  occurs as a primary key.

7
VisualizingRelationalData
8
What Is an Object-Oriented Database?

• The third and final kind of database is called
  object-oriented, in which programmers writing
  code in object-oriented languages can create
  complex data structures in which one data type
  can build upon, or inherit, properties from
  another.
• In this course, we deal with relational databases.

9
Database Design Principles

• Each table should have a column containing a
  unique row ID number to serve as the tables
  primary key.
• A table should store data for a single type of
  entity.
• A table should avoid columns that are allowed to
  contain null values. If you must have empty
  values, use an empty string.

10
Database Design Principles(continued)

• A table should not have repeating values.
• A field should have the same meaning in each row
  of a table.
• Multiple instances of an entity should not be
  represented as multiple columns.

11
Three-Tier Web Application Model