w3d2 Flashcards
iso restrictions on identifiers
must start with a letter
no longer than 128 characters
cannot contain spaces
every data type is
either pre defined, constructed or user defined
the name of a predefined or preconstructed data type is
a reserved word specified by that part of ISO/IEC 9075 that specifies the data type
the name of a user defined data type
is provided in it’s definition
predefined data type
datatype specified by ISO/IEC 9075 AND is provided by the SQL implementation
- predefined even when the user is allowed to provide certain parameters
- is atomic(not composed of values of other data types)
Each table has
a name that is unique within the schema
a structure that is the same for each of its records
each attribute needs as a minimum
a unique name within the record
a data type ( which could be the name of a domain )
Integrity constraints
define valid states of sql data by constraining values in base tables
constaint can cause DBMS to
reject any atempt to insert, delete, or update against the restraint
domain
set of legal values, (a sophisticated data type
when not otherwise specified
the domain is the set of all legal values, that are based on the data type of the attribute
CHECK
further specifies the legal values of a domain
domain constraint
constraint specified for a domain
- applied to all columns that are based on that domain and to all values that are cast to that domain
CONDITION ONLY CHECKED UPON
an insert or update of the record within the constraint
- not checked when records referred to in the check of the domain are updated or deleted
The domain name can and should be used
in place of a data type by one or more data definitions
Domains important for
help us recognize if we are dealing with the same type of attribute or not
- a list of domains provides us with an idea about what types of data we have in our database
By using good domain names it’s clearer to know what your talking about it what you’re talking about
entity integrity
deal with the relation of records to eachother in a table
primary key
one set of attributes that uniquely defines a record
Referential integrity
links a set of attributes in this record to a record key in another table
referential integrity CASCADE
will delete the constrained row if the referenced row in the foreign key is deleted
will update the foreign key value if the referenced value is updated
referential integrity SET NULL
will set each of the attributes in the foreign key to NULL if the referenced value is changed or it is deleted(provided the foreign key’s value is not defined as not null)
referential integrity SET DEFAULT
Will set each of the attributes in the foreign key to it’s default if the referenced value is changed or it’s record is deleted (provided the foreign key attributes were defined with defaults)
referential integrity NO ACTION
will not allow the updating or deleting of the referenced value (default if other not specified)
ASSERTion
named constraint that may relate to the content of individual rows of a table, to the entire contents of a table, or to a state required to exist among a number of tables
- an integrity constraint that is not directly linked to a table
- similar to a CHECK but involves multiple tables
- differs from a DOMAIN in that it is not a data type
- can be automatically checked every time an INPUT, UPDATE or DELETE might violate it
