RelationalDBDesign RelationalDBDesign

Relational Constructs   «Prev  Next»
Lesson 7 Column domains
Objective Describe column domains.

Columns represent Entity Attributes

Whenever you create a table to represent an entity, you create columns to represent that entity's attributes.
In the preceding exercise, you created a table to record each item in the orders placed by Stories on CD customers.
The Line Item table, illustrated below, recorded the order number, the item ordered, the quantity, the item's normal selling price,
any applicable discount, the subtotal for the line or "LineCost", and the shipping status of the item.

Line Item
OrderNo CDNo Quantity SellingPrice Discount LineCost Shipped

LINE ITEM table records the 1) Order number, 2) CDNo, 3) Quantity, 4) Selling Price, 5) Discount, 6) LineCost, and 7) whether the item was shipped
You can store the contents of these fields as plain text. However, doing so prevents the RDBMS from recognizing which attributes should be described by numbers (which, as you will see in the next lesson, can be used in calculations) and which attributes should be described by dates. All RDBMSs based on the Structured Query Language (SQL) provide a standard selection of data domains (also referred to as data types [1]) that characterize a column's contents.

Available domains

The data domains available in an SQL-based RDBMS include the following:
  1. CHAR: A fixed-length text string of up to 256 characters
  2. VARCHAR: A variable-length text string of up to 256 characters
  3. INT: An integer (whole number); the available size depends on the operating system, though a range of -32,768 to 32,767 (no decimals) is common.
  4. DECIMAL: Numbers with decimal values allowed; the database designer can define the number of allowable places to the left and right of the decimal point.
  5. DATE: A calendar date, usually of the form MM/DD/YY (two-digit month, two-digit day, two-digit year)
  6. TIME: A time (default format varies)
  7. DATETIME: Combined date and time
  8. BOOLEAN: Logical true or false, usually represented 1 or 0

Be sure to read your database management system's manual carefully some RDBMSs have domains that you can use to store larger integer values. In Microsoft Access, for example, the long domain can store integer values from
- 2,147,483,648 to 2,147,483,647. The next lesson describes column domains that support calculations.

[1]data type: The category of data stored in a database. Each field in a database must have a data type assigned to it, such as Integer, Text, or Date/Time.