How do the relations (tables) in SQL differ from the relations defined formally in Chapter 3?
- in formal relational model: a relation is a set of tuples; vs in SQL: a relation is a multiset (AKA bag) of tuples because it allows duplicates
- SQL has concept of schema (AKA database): a grouping of tables that all belong to the same DB app
- SQL has concept of catalog: a collection of schemas
- in SQL: attributes are considered ordered in the sequence in which they are specified in the CREATE TABLE statement
- in SQL: boolean data type uses three-valued logic to accommodate for NULLs: TRUE FALSE UNKNOWN
- [from ch7] in SQL: can use CREATE ASSERTION to specify additional types of constraints that are outside the scope of the built-in relational model constraints (primary and unique keys, entity integrity, and referential integrity)
[How do the relations (tables) in SQL differ from the relations defined formally in Chapter 3?] Discuss other differences in terminology
Formal Relational Model || SQL relation | table attribute | column tuple | row N/A | schema N/A | catalog
SELECT (relational algebra) != SELECT (SQL)
[How do the relations (tables) in SQL differ from the relations defined formally in Chapter 3?] Why does SQL allow duplicate tuples in a table or in a query result?
in SQL a relation is a multiset (AKA bag) of tuples, whereas in formal relational model a relation is a set of tuples
SQL allows for duplicate tuples in the result because sometimes it isn’t practical for all results to be unique, and sometimes you want all results including the duplicates
SQL treats tables not as a set but rather as a multiset; duplicate tuples can appear more than once in a table and in the result of a query.
SQL does not automatically eliminate duplicate tuples in the results of queries for the following reasons:
* Duplicate elimination is an expensive operation. One way to implement it is to sort the tuples first and then eliminate duplicates.
* The user may want to see duplicate tuples in the result of a query.
* When an aggregate function is applied to tuples, in most cases we do not want to eliminate duplicates.
Explain the character-string data types and bit-string data types.
character-string:
- fixed length: CHAR(n), CHARACTER(n); n = length
- shorter strings are padded with spaces to the right
- varying length: VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n); n=max length
- very large text values: CHARACTER LARGE OBJECT, CLOB
- max length specified in kilobytes (K), megabytes (M), or gigabytes (G)
- specified btwn single quotes, ex: ‘abcdef’
- case sensitive
- ordered in alphabetic or lexicographic order
- default n=1
bit-string:
- fixed length: BIT(n); n=length
- varying length: BIT VARYING(n); n=max length
- very large binary values: BINARY LARGE OBJECT, BLOB
- max length specified in kilobits(K), megabits (B), or gigabits (G)
- specified btwn single quotes and preceeded by a B, ex: B’101001’
- default n=1
How does SQL allow implementation of the entity integrity and referential integrity constraints described in Chapter 3?
in SQL you can specify entity integrity constraints and referential integrity constraints during the CREATE TABLE statement or later with an ALTER TABLE statement
[entity integrity constraint: no PK value can be NULL]
- can specify NOT NULL constraint on attribute to not allow NULL values for that attribute
- can specify PRIMARY KEY on an attribute to specify that that attribute is the PK for the relation
- the NOT NULL constraint is implied for attributes specified with PRIMARY KEY
[referential integrity constraint: a tuple in one relation that refers to another relation must refer to a tuple that exists in the other relation]
- can specify FOREIGN KEY clause for referential integrity constraints
- can also specify actions for when a tuple in the referenced relation is deleted or updated (i.e. referential triggered actions)
- referential integrity constraints can be violated when tuples are inserted or deleted, or when a foreign key or primary key attribute value is updated
ex: CREATE TABLE Department (num INT NOT NULL, name VARCHAR(20) NOT NULL, PRIMARY KEY(num) );
CREATE TABLE User (email VARCHAR(80) NOT NULL, last_name VARCHAR(50) NOT NULL, first_name VARCHAR(50) NOT NULL, middle_name VARCHAR(50), department INT NOT NULL DEFAULT 99999, PRIMARY KEY(email), FOREIGN KEY(department) REFERENCES Department(num) ON DELETE SET DEFAULT ON UPDATE CASCADE );
[How does SQL allow implementation of the entity integrity and referential integrity constraints described in Chapter 3?] What about referential triggered actions?
a referential triggered action is the action you can specify when specifying a foreign key constraint for what action to take for an integrity violation
- referential integrity constraints can be violated when tuples are inserted or deleted, or when a foreign key or primary key attribute value is updated
options:
- RESTRICT: restrict the op; default
- SET NULL: set the value in the referencing relation to NULL if the attribute does not have the NOT NULL constraint
- SET DEFAULT: set the value in the referencing relation to the default value for that attribute (that was specified during CREATE TABLE or ALTER TABLE statement)
- CASCADE: if qualifier=ON DELETE: delete all referencing tuples; if qualifier=ON UPDATE: update the attribute value in the referencing tuples
- can have run-off effects if the referencing relation is referenced by other relations
qualifying actions:
- ON DELETE
- ON UPDATE
Give reasons why SQL does not automatically eliminate duplicate tuples in the results of queries.
SQL treats tables not as a set but rather as a multiset; duplicate tuples can appear more than once in a table and in the result of a query.
SQL does not automatically eliminate duplicate tuples in the results of queries for the following reasons:
* Duplicate elimination is an expensive operation. One way to implement it is to sort the tuples first and then eliminate duplicates.
* The user may want to see duplicate tuples in the result of a query.
* When an aggregate function is applied to tuples, in most cases we do not want to eliminate duplicates.
[Give reasons why SQL does not automatically eliminate duplicate tuples in the results of queries.] Which keyword is used to eliminate duplicate tuples from the result? Give an example.
DISTINCT
ex table: Player last_name, first_name, position, number Foles, Nick, QB, 9 Wentz, Carson, QB, 11 Sproles, Darren, RB, 43 Smallwood, Wendell, RB, 28 Agholar, Nelson, WR, 13 Jeffrey, Alshon, WR, 17 Ertz, Zach, TE, 86 Elliot, Jake, K, 4
SELECT COUNT(position) FROM Player; 8
SELECT COUNT(DISTINCT position) FROM Player; 5
