Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
44 Cards in this Set
- Front
- Back
steps for transforming a data model into a database design
|
1. create a table for each entity
2. create relationships by placing foreign keys 3. specify logic for enforcing minimum cardinality |
|
candidate (Alternative) keys
|
are alternative identifiers of unique rows in the table, indicated by AK
|
|
Null status
|
indicates whether or not he value of the column can be null
*null means that eh column value can be null but not alway null |
|
data types
|
are DBMS dependent
CHAR(n) VARCHAR DATE TIME MONEY INTEGER DECIMAL |
|
default vaule
|
the value supplied by the DBMS when a new row is created
*must be documented as part of the design |
|
data constraints
|
are limitations on data values
|
|
domain constraint
|
limit column values to a particular set of values
|
|
range constraint
|
limit values to a particular interval of values
|
|
intrarelation constraint
|
limits a column's values in comparison with other columns in the same table.
example: EMPLOYEE.ReviewDate be a least 3 months after EMPLOYEE.HireDate |
|
interelation constraint
|
limits a column's values in comparison with other columns in other tables
|
|
BCNF
|
remove redundancies and anomalies
|
|
4NF
|
remove multivalued dependencies
|
|
1:1 strong entity relationships
|
the Rule: place the key on one entity in the other entity as a foreign key
|
|
define foreign key
|
as either unique or as AK
|
|
1:N strong entity relationships
|
the Rule: place the primary key of the table on the one side of the relationship into the table on the many side of the relationship as the foreign key
*"place the key of the parent(one side) in the child (many)" foreign key doesn't have to be unique since a parent can have many children |
|
N:M strong entity relationships
|
there is no place for the foreign key in either table = create an intersection table
|
|
intersection table
|
stores data about the corresponding rows from each entity
* the table consists only of the primary keys of each table which form a composite primary key each's table's primary key becomes a foreigh key linking back to that table |
|
four uses for ID-Dependent entities
|
represining N:M relationships
association relatinships multivalued attributes archetype/instance relationships |
|
association relationships
|
has one or more attrubutes specific to the relationship no the entitles
*holds the relationships between 2 or more stong entities as in an N:M relatinship *contains the primary keys of the 2 or more entities as a compsoite primary key and foreign keys *has one or more columns of attributes specific to the associations of the other 2 or more entities |
|
mulivalued attributes
|
replace teh mulitvalued entity with a table and replace each of its attributes with a column
|
|
surrogate key
|
is a DBMS-supplied identifier of each row of a table, are unique withing the table and never change, assigned when row is created and destroyed when row is deleted.
*are the best possible primary keys of every table |
|
steps for creating tables
|
-specify primary keys
-specify candidate keys -specify properties for each colums (null, data, default dat constraints) -verify normalization |
|
create relationships by placing foreign keys
|
-relationships between strong entities
-identifing relationships with ID-dependent entities -relationships betweena strong entity and a weak but non-id-dependent entity -mixed relationships -relationships between supertype/subtype entities -recursive relationships |
|
specify logic for enforcing minimum cardinality
|
-m-o relationships
-0-m relationships -m-m relationships |
|
AK1.1
AK1.2 |
AK1.1= because it is the first alternate key and the first column of that key
AK1.2 = it's the second column of the first alternate key |
|
cascading update
|
occurs when a change to the parent's primary key is applied to the child's foreign key
*surrogate keys never change and there is not need for cascading updates when using them |
|
cascading delete
|
occurs when associated child rows are deleted along with the deletion of a parent row
*weak entities generally do cascade deletes |
|
trigger
|
is a stored program that is executed by the DBMS whenever a specified event occures on a specified tableor view
*used to enforce specific minimum cardinality enforcement actions not otherwise programmed into the DBMS |
|
DML
|
statements which are used for querying ,inserting, updating and deleting data
|
|
DDL
|
statements which are used for creating tables, relationships, and other database structures
|
|
create table
|
statement is used for creating relations (tables)
describes 3 parts: column name data type column optional constraints *note (see pp) |
|
varchar vs. char
|
char will use the same amount of space for each character reguardless if it needs to
varchar = uses just the number of that is inputed |
|
constraints
|
5 types:
primary key unique null/not null foreign key check default |
|
DML
|
statements which are used for querying ,inserting, updating and deleting data
|
|
DDL
|
statements which are used for creating tables, relationships, and other database structures
|
|
create table
|
statement is used for creating relations (tables)
describes 3 parts: column name data type column optional constraints *note (see pp) |
|
varchar vs. char
|
char will use the same amount of space for each character reguardless if it needs to
varchar = uses just the number of that is inputed |
|
constraints
|
5 types:
primary key unique null/not null foreign key check default |
|
reserved word
|
not allow to use in sql
*description = put in brackets |
|
Alter statement
|
changes table structure, properties, or constraints after it has been created
|
|
sql view
|
is a virtual (temporary) table that is constructed from other tables or views - doesn't have data of its won - obtains it from other tables
SQL CREATE VIEW statement. how you block personal information so only certain people can see it |
|
trigger
|
is a stored porgram (action) tha tis executed by the DBMS whenever a specified event occurs on a specified table or view
3 types: BEFORE INSTEAD OF AFTER |
|
triggers are
|
specific with a table
|
|
stored procedure
|
is a program that is stored within the database and is compoiled when used
can recieve input parameters and return reulst procedures can be called from scripting languages, standard lanuages and sql command prompt |