Understanding Characteristics of Relations and SQL Data Types
1) List and briefly explain the Characteristics of Relations:
- 1. Ordering of Tuples in a Relation: A relation is defined as a set of tuples. Mathematically, elements of a set have no order among them; hence, tuples in a relation do not have any particular order. Similarly, when tuples are represented on a storage device, they must be organized in some fashion, and it may be advantageous, from a performance standpoint, to organize them in a way that depends upon their content.
- 2. Ordering of Values within a Tuple: The order of attributes and their values is not that important as long as the correspondence between attributes and values is maintained. A tuple can be considered as a set of (Ai, vi) pairs, where each pair gives the value of the mapping from an attribute Ai to a value vi from dom(Ai). The ordering of attributes is not important because the attribute name appears with its value.
- 3. Values and NULLs in the Tuples: Each value in a tuple is an atomic value; that is, it is not divisible into components. An important concept is NULL values, which are used to represent the values of attributes that may be unknown or may not apply to a tuple. NULL values have several meanings, such as value unknown, value exists but is not available, or attribute does not apply to this tuple.
- 4. Interpretation (Meaning) of a Relation: Each tuple in the relation can then be interpreted as a fact or a particular instance of the assertion. Each relation can be viewed as a predicate, and each tuple in that relation can be viewed as an assertion for which that predicate is satisfied (i.e., has value true) for the combination of values in it.
2) Explain Attribute Data Types and Domains in SQL:
- Basic Data Types: The basic data types available for attributes include numeric, character string, bit string, Boolean, date, and time.
- Numeric Data Types:
- Integer numbers of various sizes like INTEGER or INT, and SMALLINT.
- Floating-point (real) numbers of various precision like FLOAT or REAL, and DOUBLE PRECISION.
- Formatted numbers using DECIMAL(i, j) — or DEC(i, j) or NUMERIC(i, j) — where i, the precision, is the total number of decimal digits and j, the scale, is the number of digits after the decimal point.
- Character String Data Types:
- Fixed length — CHAR(n) or CHARACTER(n), where n is the number of characters.
- Varying length — VARCHAR(n) or CHAR VARYING(n) or CHARACTER VARYING(n), where n is the maximum number of characters.
- Bit String Data Types:
- Fixed length n — BIT(n) — or varying length — BIT VARYING(n), where n is the maximum number of bits.
- Another variable-length bit string data type called BINARY LARGE OBJECT or BLOB is also available to specify columns that have large binary values, such as images.
- Boolean Data Types:
- Values of TRUE or FALSE.
- In SQL, because of the presence of NULL values, a three-valued logic is used, so a third possible value for a Boolean data type is UNKNOWN.
- Date Data Types:
- Date has ten positions, and its components are YEAR, MONTH, and DAY in the form YYYY-MM-DD.
- The TIME data type has at least eight positions, with the components HOUR, MINUTE, and SECOND in the form HH:MM:SS.
- Timestamp Data Type: Includes the DATE and TIME fields, plus a minimum of six positions for decimal fractions of seconds and an optional WITH TIME ZONE qualifier.
- Interval: Another data type related to DATE, TIME, and TIMESTAMP is the INTERVAL data type. This specifies an interval — a relative value that can be used to increment or decrement an absolute value of a date, time, or timestamp.
Step 2: For each weak entity type W in the ER schema with owner entity type E, create a relation R, and include all simple attributes (or simple components of composite attributes) of W as attributes. In addition, include as foreign key attributes of R the primary key attribute(s) of the relation(s) that correspond to the owner entity type(s).
Step 4: For each regular binary 1:N relationship type R, identify the relation (N) relation S. The primary key of T as a foreign key of S. Simple attributes of R map to attributes of S.
Step 4: For each regular binary 1:N relationship type R, identify the relation (N) relation S. The primary key of T as a foreign key of S. Simple attributes of R map to attributes of S.
Step 6: For each multi-valued attribute A, create a new relation R. This relation will include an attribute corresponding to A, plus the primary key K of the parent relation (entity type or relationship type) as a foreign key in R. The primary key of R is the combination of A and K.
