Relational Constructs   «Prev  Next»

Lesson 7 Column domains
Objective Describe column domains in the context of Entities

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

Column Domains used in Data Modeling

Question: How is the concept of column domains used in data modeling?
In data modeling, a column domain is a set of possible values that can be assigned to a particular column in a database table. The concept of column domains is used to ensure data consistency and accuracy by defining the range of valid values for each column. Column domains help to ensure that data entered into a database is consistent and accurate by enforcing constraints on the values that can be assigned to a column. For example, a column domain for a column that stores dates might specify that only dates between a certain range are valid, or that the date format must be in a particular format such as "YYYY-MM-DD".
By using column domains, data modeling can ensure that data stored in a database is consistent and accurate, which can help to prevent errors and improve data quality. Column domains also help to make it easier to update and modify the database schema, since changes to the domain can be made in a single location and applied to all columns that use the domain. In addition to enforcing constraints on values, column domains can also be used to specify default values, specify data types, and specify other attributes that are associated with a particular column. Overall, the use of column domains in data modeling is an important tool for ensuring data accuracy and consistency, and can help to improve the overall quality of data stored in a database.

Use Data Types for Fields

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.