The Relational Data Model and Relational Database Constraints

Questions: 1. Why are tuples in a relation not ordered? 2. Why are duplicate tuples not allowed in a relation? 3. Why do we designate one of the candidate keys of a relation to be the primary key? 4. List the data types that are allowed for SQL attributes.5. Describe the four clauses in the syntax of a simple SQL retrieval query. Show what type of constructs can be specified in each of the clauses. Which are required and which are optional? Answers: 1. A relation is a logical representation of the database. According to the definition, the order of attributes in a relation is important to maintain the corresponds between the between the values to respective attributes. Ordering the tuples in a relation, it will need extra commands and more functionalities. As, ordering of tuple is not necessary and has no impact on physical storage, it is not maintained to save computation resources. (Elmasri, 2008) 2. A relation is a set of ordered tuples, by definition. On the other hand, according to the definition of set, it does not contain duplicate value. Thus, a collection of tuples will be called relation only when there is no duplicates. So, there is no duplicate tuple in a relation. Primary key is used in relations to maintain this property. (Elmasri, 2008) 3. Candidate key can uniquely identify a tuple from a relation. Any candidate key can do the same task. On the other hand primary key is a candidate key that is designated as the same for a relation. There are other use of primary key, for example, in referencing to the foreign key, indexing to support searching etc. If there is two or more designated primary key, then referencing, indexing etc. will be problematic, more computational resources will be consumed to maintain properties of primary key for both. So, it is not used in practice. (Elmasri, 2008) 4. The data types available for SQL attributes are, numeric, bit string, character string, Boolean, Date and Time. The list is, Data Types Numeric INT SMALLINT FLOAT REAL DOUBLE PRECISION DECIMAL(i,j) Bit String BIT(n) BIT VARYING(n) Character String CHAR(n) VARCHAR(n) CLOB() Boolean Date Time 5. The Four clause in syntax of a simple SQL query are, SELECT is a required clause. This is used for selecting the attributes of a relation. FROM is a required clause. This is used for selecting the tuples of a relation. WHERE is an optional clause. It is used to retrieve some tuples that satisfies the condition with the WHERE clause in the SQL query. ORDER BY is an optional clause. It is used to order the results from the SQL query in ascending or descending order. References Elmasri, R. (2008). Fundamentals of Database Systems (6th ed.). Pearson.