Chapter 4: Advanced SQL

Question 1

Explain char vs varchar

Answer 1

char(m): has a fixed length

varchar(m): variable length that can increase or decrease as needed

Question 2

What is the purpose of integrity constraints?

Answer 2

By putting rules (constraints) on attributes/data you can ensure that said data contains the correct value.
-guard against accidental damage to the database by ensuring authorized changes to DB don’t result in a loss of data consistency

Question 3

What are some built in data types for SQL

Answer 3

date: (4-digit) year, month, and date
time: time of day in hours, minutes and seconds
timestamp: date plus time of day
interval: period of time
- date/time/timestamp minus another gives an interval
- intervals can be added to date/time/timestamp values

Question 4

How can a user define a data type in SQL?

Answer 4

CREATE TYPE

Question 5

How can a user define a domain in SQL?

Answer 5

CREATE DOMAIN

Question 6

types vs domains in SQL

Answer 6

they are similar

• domain: names, user-defined set of valid values
• type: attribute that specifies the type of data of any object

Question 7

What are domain constraints?

Answer 7

• most elementary form of integrity constraint

- tests values inserted in DB, and test queries to make sure comparisons are valid

Question 8

What is a BLOB?

Answer 8

• binary large object
• when a piece of data is very large and is stored as its binary representation (looking at bits)
• this is done with non-ascii data

Question 9

What is a CLOB?

Answer 9

• character large object
• large piece of data that is text, is looked at and stores through bytes
• done with ascii stores of text

Question 10

How are BLOBs and CLOBS stored in tables?

Answer 10

They are stored as pointers to the actual data

Question 11

What is the benefit of having constraints at the table/schema level?

Answer 11

Constraints specified at the table/schema level carry over to all other areas and people who use them
-you do not have to write the constraints anywhere else in the application program once it is defined at the schema level

Question 12

What are examples of constraints on a single relation?

Answer 12

• not null
• primary key
• unique
• check(P), where P is a predicate

Question 13

What does NOT NULL do?

Answer 13

sets an attribute with the constraint that it cannot be null

Question 14

What does PRIMARY KEY do?

Answer 14

sets an attribute as the primary key, also primary key’s cannot be null

Question 15

What does UNIQUE do?

Answer 15

sets a set of attributes to form a candidate key

-candidate key’s can be null

Question 16

What does CHECK(P) do?

Answer 16

allows for certain domains to be restricted to certain values

Question 17

What is referential integrity?

Answer 17

When the integrity constraints of one attribute in one table are passed along to another via an relation between two tables
-like how foreign key’s of a child table have the same integrity constraints as they do in the the parent table

Question 18

What do the three key clauses do in the CREATE TABLE statement?

Answer 18

• primary key clause: lists attributes that comprise the primary key
• unique key clause: lists attributes that comprise a candidate key
• foreign key clause: lists the attributes that comprise the foreign key and the name of the relation referenced by the foreign key

Question 19

What is an assertion?

Answer 19

• a predicate expressing a condition that we wish the database to always satisfy
• CREATE ASSERTION CHECK
• when assertion is made, system checks it for validity and tests it again on every update that may violate the assertion