Data Management

Front 1

steps for transforming a data model into a database design

Back 1

1. create a table for each entity
2. create relationships by placing foreign keys
3. specify logic for enforcing minimum cardinality

Front 2

candidate (Alternative) keys

Back 2

are alternative identifiers of unique rows in the table, indicated by AK

Front 3

Null status

Back 3

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

Front 4

data types

Back 4

are DBMS dependent
CHAR(n)
VARCHAR
DATE
TIME
MONEY
INTEGER
DECIMAL

Front 5

default vaule

Back 5

the value supplied by the DBMS when a new row is created
*must be documented as part of the design

Front 6

data constraints

Back 6

are limitations on data values

Front 7

domain constraint

Back 7

limit column values to a particular set of values

Front 8

range constraint

Back 8

limit values to a particular interval of values

Front 9

intrarelation constraint

Back 9

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

Front 10

interelation constraint

Back 10

limits a column's values in comparison with other columns in other tables

Front 11

BCNF

Back 11

remove redundancies and anomalies

Front 12

4NF

Back 12

remove multivalued dependencies

Front 13

1:1 strong entity relationships

Back 13

the Rule: place the key on one entity in the other entity as a foreign key

Front 14

define foreign key

Back 14

as either unique or as AK

Front 15

1:N strong entity relationships

Back 15

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

Front 16

N:M strong entity relationships

Back 16

there is no place for the foreign key in either table = create an intersection table

Front 17

intersection table

Back 17

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

Front 18

four uses for ID-Dependent entities

Back 18

represining N:M relationships
association relatinships
multivalued attributes
archetype/instance relationships

Front 19

association relationships

Back 19

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

Front 20

mulivalued attributes

Back 20

replace teh mulitvalued entity with a table and replace each of its attributes with a column

Front 21

surrogate key

Back 21

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

Front 22

steps for creating tables

Back 22

-specify primary keys
-specify candidate keys
-specify properties for each colums (null, data, default dat constraints)
-verify normalization

Front 23

create relationships by placing foreign keys

Back 23

-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

Front 24

specify logic for enforcing minimum cardinality

Back 24

-m-o relationships
-0-m relationships
-m-m relationships

Front 25

AK1.1
AK1.2

Back 25

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

Front 26

cascading update

Back 26

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

Front 27

cascading delete

Back 27

occurs when associated child rows are deleted along with the deletion of a parent row
*weak entities generally do cascade deletes

Front 28

trigger

Back 28

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

Front 29

DML

Back 29

statements which are used for querying ,inserting, updating and deleting data

Front 30

DDL

Back 30

statements which are used for creating tables, relationships, and other database structures

Front 31

create table

Back 31

statement is used for creating relations (tables)
describes 3 parts:
column name
data type
column optional constraints
*note (see pp)

Front 32

varchar vs. char

Back 32

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

Front 33

constraints

Back 33

5 types:
primary key
unique
null/not null
foreign key
check
default

Front 34

DML

Back 34

statements which are used for querying ,inserting, updating and deleting data

Front 35

DDL

Back 35

statements which are used for creating tables, relationships, and other database structures

Front 36

create table

Back 36

statement is used for creating relations (tables)
describes 3 parts:
column name
data type
column optional constraints
*note (see pp)

Front 37

varchar vs. char

Back 37

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

Front 38

constraints

Back 38

5 types:
primary key
unique
null/not null
foreign key
check
default

Front 39

reserved word

Back 39

not allow to use in sql
*description = put in brackets

Front 40

Alter statement

Back 40

changes table structure, properties, or constraints after it has been created

Front 41

sql view

Back 41

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

Front 42

trigger

Back 42

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

Front 43

triggers are

Back 43

specific with a table

Front 44

stored procedure

Back 44

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