Welcome to Data Types. After watching this video, you will be able to: Explain what data types are Explain how data types are used in a database Identify some common data types Describe the advantages of using appropriate data types A database table represents a single entity and the columns in the table represent attributes of that entity. For example, a table named Book could contain columns for the title, published date, and the number of pages that they contain. The information entered into each column should always be of the same sort, or type, of data. In this example, the Title column should contain textual data, the Publish Date column should contain a date, and the Pages column should contain a number. We can use this idea to define the type of data, or data type, that a column can store. The data type that you assign to a column controls the data that the column can store. For example, a text column can contain alphanumeric data, but a date column can only contain dates in a valid date format, and a numeric column can only contain numbers. Different database management systems may use different names for similar data types, but they generally support a standard set. So let’s look at the commonly used data types in an RDBMS. Character string data types include fixed length data types and variable length. The length of a fixed length character string is often denoted in brackets after the type name, such as CHAR(10). This type uses the same amount of space in the database irrespective of the length of the actual data stored in it. For example, storing a city code such as NY in a CHAR(10) column will still take up 10 characters of space. Variable length character strings, often named VARCHAR, can specify a maximum length for the string. Numeric data types include integer types and decimal types. Integer data types only hold whole numbers, with no decimal parts. So 12 would be a valid entry, whereas 12.5 would not. Integer data types typically use 2 or 4 bytes of storage to hold at numbers from negative 2 million or 32 thousand to positive 2 million or 32 thousand. Smallints enable you to use less space for smaller numbers and bigints increase the size of the number that the data type can hold. Decimal data types can store whole numbers and decimal numbers. The sizes and precision of these data types vary between RDBMSs and have names such as numeric, decimal, dec, real, double, float, decfloat, etc. Date/time data can be categorised into dates, times, and timestamps. Dates consist of three-part values for the year, month, and day. And times also generally consist of a three part value for the hours, minutes, and seconds. A timestamp column is a combination of both and consists of seven parts: year, month, day, hour, minute, second, and microsecond. Other commonly used data types include: A Boolean which only holds 1 bit of information: a 0 or a 1. You can use these for true/false or yes/no type data. A binary string which holds a sequence of bytes that represent image, voice, or other media data. A large object, or LOB, which is generally a very large object such as a file. Often this type of data is stored outside of the main database table and a pointer to it is held in the table. The XML data type can store platform agnostic unstructured data in a hierarchical form. In addition to the various built-in data types covered in this video, many relational databases also allow you to create your own custom or “user defined” data types (UDTs) that are derived or extended from the built in types. As you might have guessed, you can avoid using specific data types altogether and define every column to hold character data. However, using the appropriate data type provides many advantages: When you define the data type that a column should hold, you avoid incorrect data being inserted into that column. For example, if you try to add an authors name into the Publish date column of the Books table, the insert will fail and you can take appropriate action. When date/time and numeric data is correctly typed you can accurately sort that data. For example, if you sort a character column containing numbers into ascending order, 102 may be listed before 12 or the query may fail. However if the column is defined as numeric, you can be sure that the numbers will be returned in numerical order as generally expected. Similarly you can accurately select ranges of data when it is correctly typed, for example, selecting all books published between 01-01-2001 and 31-12-2001. You can perform numeric calculations on typed data, for example calculating the total cost of an order. And you can take advantage of standard functions such as returning the average of column of ages or adding 2 days to an order date to estimate a shipping date. In this video, you learned that: Data types define the type of data that can be stored in a column. There are many different data types for all kinds of data. Using the correct data type for a column has many advantages.
