6.1. How do the relations (tables) in SQL differ from the relations defined form

Question

6.1. How do the relations (tables) in SQL differ from the relations defined formally
in Chapter 3? Discuss the other differences in terminology. Why does
SQL allow duplicate tuples in a table or in a query result?

SQL allows a table (relation) to have two or more tuples that are identical in all their attribute values. Hence, in general, an SQL table is not a set of tuples, because a set does not allow two identical members, rather, it is a multiset of tuples. Some SQL relations are constrained to be sets because a key constraint has been declared or because of DISTINCT option has been used in SELECT statement. On contrary relation defined formally says that arelation is set of tuples that is, same values are not allowed for any tuple Correspondence between ER and Relational Model can help in understanding other differences in terminology. Relational Model ER Model Entity type Entity relation 1:1 or 1:N relationship type Foreign keytor relationship type) MN relationship type Relationship relation and two foreign keys n-ary relationship type Relationship relation and n foreign keys Simple Attributes Attribute Composite attributes Set of simple component attribute Multivalued attributes Relation and foreign keys Domain Value set Key attributes Primary or secondary key SQL allows duplicate tuples for following reasons: Duplicate elimination is a expensive operation. 1. 2. User may want to see duplicate tuples in the result of query 3. When an aggregate function is applied to tuples, in most cases user don't want to remove duplicates.

Explanation / Answer

Ques How do the relations (tables) in SQL differ from the relations defined formally?
Ans
Relations never contain duplicate tuples.
- It follows because a body is defined to be a set of tuples. And sets don’t contain duplicate elements.
Now, SQL tables are allowed to contain duplicate rows and therefore aren’t relations.
Note these points:
Relation to mean a relation without duplicate tuples, by definition and not an SQL table.
Relational operations always produce a result without duplicate tuples.

Terminology
- Normalization
- Denormalization

Q Why does SQL allow duplicate tuples in a table or in a query result?
Ans
SQL is not relational
SQL DBMSs are not relational DBMSs.

Duplicate rows are a part of the SQL model for the reason:
- SQL language doesn't implement the relational algebra.
- SQL uses a multiset-based algebra.
- The results of queries and other operations in relational algebra are relations that always have distinct tuples,
but SQL DBMSs don't deal only with relations.

Given this fundamental "feature" of the SQL language, SQL database engines need to have mechanisms for processing and storing duplicate rows.

Example:-
If you're never going to query a single value from the table, you don't create a primary key in order to avoid having to wait for an insert into the key.

Also, SQL returns data according to WHERE filter in a query. If table has duplicates and condition is true, the rows are selected. You can add DISTINCT to avoid duplicates.

Related Questions

Navigate

• Browse (All)
• Browse 6
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.