Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

Database Management Systems > Exam Two > Flashcards

Exam Two Flashcards

1
Q

data are organized in the form of tables/relations, rows, columns

A

data structure

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

powerful SQL operations for retrieving and modifying data stored in the relation

A

data manipulation

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

mechanisms for implementing business rules that maintain integrity of manipulated data

A

data integrity

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

named, 2D table of data, rows (records), columns (fields)

A

relation

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

to qualify a table as a relation what six things must it have?

A

unique table name
every attribute value must be atomic (not multivalued or composite)
every row must be unique
attributes/columns must have unique names
order of columns must be irrelevant
order of rows must be irrelevant

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

used as indexes to speed up response to user queries

A

key fields

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

unique identifiers of relations, guarantees all rows are unique

A

primary keys

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

identifiers that enable a dependent relation (on many side of relationship) to refer to its parent relation (on the one side)

A

foreign keys

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

first step of relational model

A

mapping regular entities to relations

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

what to do with composite attributes in relational model?

A

use only in their simple component attribute form

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

what to do with multivalued attributes in relational model?

A

become separate relation with foreign key taken from the superior entity

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

second step of relational model

A

mapping weak entities

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

what to do with weak entities in relational model?

A

becomes separate relation with foreign key taken from the superior entity

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

what is the primary key composed of for a weak entity?

A

partial identifier of weak entity and primary key of identifying relation

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

third step of relational model

A

mapping binary relationships

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

what to do with a one to many relationship in relational model?

A

primary key on one side becomes foreign key on the many side

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

what to do with a many to many relationship in relational model?

A

create a new relation with the primary keys of the two entities as its primary key, composite primary key

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

what to do with a one or one relationship in relational model?

A

primary key on mandatory side becomes foreign key on optional side

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

fourth step of relational model

A

mapping associative entities

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

what to do with associative entity if identifier is not assigned in relational model?

A

default primary key is composed of primary keys of two entities

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

what to do with associative entity if identifier is assigned in relational model?

A

make primary keys into foreign keys

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

fifth step of relational model

A

mapping unary relationships

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

what to do with unary relationships that are one to many in relational model?

A

recursive foreign key in the same relation, make new field and make it the foreign key

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

what to do with unary relationships that are many to many in relational model?

A

make two relations, one for associative relation where primary key has two attributes both from primary key

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
sixth step of relational model
mapping ternary relationships
26
what to do for ternary relationships in relational model?
one relation for each entity and one for associative, associative entity relation has foreign keys to each entity in the relationship
27
seventh step of relational model
mapping supertype/subtype relationship
28
what to do for supertype/subtype relationship in relational model?
one relation for supertype and one for each subtype, primary key of supertype becomes primary key of subtype discrimination with a different name
29
how to treat partial and overlapping for supertype/subtype relationship in relational model?
partial- can be unknown overlapping- treat as composite attribute and take them seperately
30
allowable values for an attribute (includes data types and restrictions on values), all of the values that appear in a column of a relation must be from the same domain
domain constraints
31
no primary key attributes may be null, all primary key values must contain data values that must be unique, no two rows the same
entity integrity
32
rules that maintain consistency between the rows of two related tables, foreign key value on many side must match a primary key values on one side
referential integrity
33
what are the three parts of referential integrity?
restrict, cascade, set-to-null
34
don't allow delete of parent side if related rows exist in dependent side
restrict
35
automatically delete dependent side rows that correspond with the parent side rows to be deleted
cascade
36
set foreign key in the dependent side to null if deleting from parent side (not allowed for weak entities)
set-to-null
37
process of decomposing relations with anomalies to produce smaller, well-structured relations, deciding which attributes should be grouped together in a relation so that all anomalies are removed
data normalization
38
primarily a tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data
purpose of data normalization
39
contain minimal data redundancy and allow users to insert, delete, and update rows without causing data inconsistencies, avoid anomalies
well-structured relations
40
adding new rows forces a user to create duplicate data, redundant
insertion anomaly
41
deleting rows may cause a loss of data that would be needed for other rows
deletion anomaly
42
changing data in a row forces changes to other rows because of duplication
modification anomaly
43
value of one attribute (the determinant) determines the value of another attribute, constraint between two attributes or two sets of attributes
functional dependency
44
unique identifiers, one will become the primary key
candidate key
45
first normal form
no multivalued attributes
46
second normal form
no partial dependencies
47
third normal form
no transitive dependencies
48
short text statements part of a graphical representation with each relation in a rectangle with attributes
structure of DB in schema
49
set of values that may be assigned to an attribute
domain
50
domain name, meaning, data type, size/length, allowable values/range
what is included in domain definition
51
value that may be assigned to an attribute when no other value applies or when the applicable value is unknown
null
52
errors or inconsistencies in a table when a use attempts to update data
anomalies
53
what are the types of anomalies?
insertion, deletion, modification
54
minimize data redundancy, simply enforcement of preferential integrity constraints, make it easier to maintain data, provide better design that is an improved representation of the real world and a stronger basis for future growth
goals of normalization
55
state of a relation that requires that certain rules regarding relationships between attributes (or functional dependencies) are satisfied
normal form
56
attribute on left side of functional dependency
determinant
57
when a nonkey attribute is functionally dependent on part (but not all) of the primary keys
partial functional dependency
58
functional dependency between primary key and one or more nonkey attributes that are dependent on the primary key via another nonkey attribute
transitive dependency
59
multiple attributes that have different names but the same meaning
synonyms
60
alternative names used for an attribute
alias
61
attribute name that may have more than one meaning
homonym
62
synonyms, homonyms, transitive dependencies, supertype/subtype relationships
redundancy problems that are removed by merging relations
63
hierarchy of SQL
cluster (computer) database schema (folder) table
64
how to put composite key in SQL
PRIMARY KEY (PK1, PK2);
65
standard for relational database management systems, created 1974-1979 by IBM
structured query language (SQL)
66
DBMS that manages data as collection of tables, all relationships represented by common values in related tables
RDBMS
67
specify syntax/semantics for data definition and manipulation, definitely data structures and basic operations, enable portability of DB definition and application modules, specify minimal and complete standards, allow for later growth/enhancement to standard
original purpose of SQL standard
68
reduced training costs, productivity, application portability, application longevity, reduced dependence on a single vendor, cross-system communication
benefits of standardized relational language
69
set of schemas that constitute description of a DB whether or not a user created it
catalog
70
structure that contains descriptions of objects created by user (tables, views, constraints), collection of related objects associated with catalog
schemas
71
commands that define a DB, including creating, alternating, dropping tables, establishing constraints
data definition language (DDL)
72
commands that maintain and query a DB
data manipulation language (DML)
73
commands that control a DB, including admin privileges and committing data
data control language (DCL)
74
catalog, schema, DDL, DML, DCL
SQL environment
75
strings, binary, number, temporal, boolean
SQL data types
76
steps in table creation
identify data types identify columns that can/can't be null identify columns that must be unique identify primary key - foreign key mates determine default values identify constraints on columns (domains) create table and associated indexes
77
choice of indexes, file organizations for base table, file organizations for indexes, data clustering, statistics maintenance
control processing/store efficiency
78
single value returned from SQL query with aggregate function
scalar aggregate
79
multiple values returned from SQL query with aggregate function
vector aggregate
80
relational Operation that causes two or more tables with a common domain to be combined into a single table or view
join
81
an equi-join where one of the duplicate columns is eliminated in the result table
inner join
82
what is inner used for?
alternative to where close to match primary and foreign keys
83
components of relational model
data structure data manipulation data integrity

Database Management Systems (2 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions