Explain char vs varchar
char(m): has a fixed length
varchar(m): variable length that can increase or decrease as needed
What is the purpose of integrity constraints?
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
What are some built in data types for SQL
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
How can a user define a data type in SQL?
CREATE TYPE
How can a user define a domain in SQL?
CREATE DOMAIN
types vs domains in SQL
they are similar
- domain: names, user-defined set of valid values
- type: attribute that specifies the type of data of any object
What are domain constraints?
- most elementary form of integrity constraint
- tests values inserted in DB, and test queries to make sure comparisons are valid
What is a BLOB?
- 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
What is a CLOB?
- character large object
- large piece of data that is text, is looked at and stores through bytes
- done with ascii stores of text
How are BLOBs and CLOBS stored in tables?
They are stored as pointers to the actual data
What is the benefit of having constraints at the table/schema level?
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
What are examples of constraints on a single relation?
- not null
- primary key
- unique
- check(P), where P is a predicate
What does NOT NULL do?
sets an attribute with the constraint that it cannot be null
What does PRIMARY KEY do?
sets an attribute as the primary key, also primary key’s cannot be null
What does UNIQUE do?
sets a set of attributes to form a candidate key
-candidate key’s can be null
What does CHECK(P) do?
allows for certain domains to be restricted to certain values
What is referential integrity?
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
What do the three key clauses do in the CREATE TABLE statement?
- 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
What is an assertion?
- 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
