Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

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
Q

sixth step of relational model

A

mapping ternary relationships

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

what to do for ternary relationships in relational model?

A

one relation for each entity and one for associative, associative entity relation has foreign keys to each entity in the relationship

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

seventh step of relational model

A

mapping supertype/subtype relationship

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

what to do for supertype/subtype relationship in relational model?

A

one relation for supertype and one for each subtype, primary key of supertype becomes primary key of subtype discrimination with a different name

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

how to treat partial and overlapping for supertype/subtype relationship in relational model?

A

partial- can be unknown
overlapping- treat as composite attribute and take them seperately

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

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

A

domain constraints

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

no primary key attributes may be null, all primary key values must contain data values that must be unique, no two rows the same

A

entity integrity

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

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

A

referential integrity

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

what are the three parts of referential integrity?

A

restrict, cascade, set-to-null

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

don’t allow delete of parent side if related rows exist in dependent side

A

restrict

35
Q

automatically delete dependent side rows that correspond with the parent side rows to be deleted

A

cascade

36
Q

set foreign key in the dependent side to null if deleting from parent side (not allowed for weak entities)

A

set-to-null

37
Q

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

A

data normalization

38
Q

primarily a tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data

A

purpose of data normalization

39
Q

contain minimal data redundancy and allow users to insert, delete, and update rows without causing data inconsistencies, avoid anomalies

A

well-structured relations

40
Q

adding new rows forces a user to create duplicate data, redundant

A

insertion anomaly

41
Q

deleting rows may cause a loss of data that would be needed for other rows

A

deletion anomaly

42
Q

changing data in a row forces changes to other rows because of duplication

A

modification anomaly

43
Q

value of one attribute (the determinant) determines the value of another attribute, constraint between two attributes or two sets of attributes

A

functional dependency

44
Q

unique identifiers, one will become the primary key

A

candidate key

45
Q

first normal form

A

no multivalued attributes

46
Q

second normal form

A

no partial dependencies

47
Q

third normal form

A

no transitive dependencies

48
Q

short text statements part of a graphical representation with each relation in a rectangle with attributes

A

structure of DB in schema

49
Q

set of values that may be assigned to an attribute

A

domain

50
Q

domain name, meaning, data type, size/length, allowable values/range

A

what is included in domain definition

51
Q

value that may be assigned to an attribute when no other value applies or when the applicable value is unknown

A

null

52
Q

errors or inconsistencies in a table when a use attempts to update data

A

anomalies

53
Q

what are the types of anomalies?

A

insertion, deletion, modification

54
Q

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

A

goals of normalization

55
Q

state of a relation that requires that certain rules regarding relationships between attributes (or functional dependencies) are satisfied

A

normal form

56
Q

attribute on left side of functional dependency

A

determinant

57
Q

when a nonkey attribute is functionally dependent on part (but not all) of the primary keys

A

partial functional dependency

58
Q

functional dependency between primary key and one or more nonkey attributes that are dependent on the primary key via another nonkey attribute

A

transitive dependency

59
Q

multiple attributes that have different names but the same meaning

A

synonyms

60
Q

alternative names used for an attribute

A

alias

61
Q

attribute name that may have more than one meaning

A

homonym

62
Q

synonyms, homonyms, transitive dependencies, supertype/subtype relationships

A

redundancy problems that are removed by merging relations

63
Q

hierarchy of SQL

A

cluster (computer)
database
schema (folder)
table

64
Q

how to put composite key in SQL

A

PRIMARY KEY (PK1, PK2);

65
Q

standard for relational database management systems, created 1974-1979 by IBM

A

structured query language (SQL)

66
Q

DBMS that manages data as collection of tables, all relationships represented by common values in related tables

A

RDBMS

67
Q

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

A

original purpose of SQL standard

68
Q

reduced training costs, productivity, application portability, application longevity, reduced dependence on a single vendor, cross-system communication

A

benefits of standardized relational language

69
Q

set of schemas that constitute description of a DB whether or not a user created it

A

catalog

70
Q

structure that contains descriptions of objects created by user (tables, views, constraints), collection of related objects associated with catalog

A

schemas

71
Q

commands that define a DB, including creating, alternating, dropping tables, establishing constraints

A

data definition language (DDL)

72
Q

commands that maintain and query a DB

A

data manipulation language (DML)

73
Q

commands that control a DB, including admin privileges and committing data

A

data control language (DCL)

74
Q

catalog, schema, DDL, DML, DCL

A

SQL environment

75
Q

strings, binary, number, temporal, boolean

A

SQL data types

76
Q

steps in table creation

A

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
Q

choice of indexes, file organizations for base table, file organizations for indexes, data clustering, statistics maintenance

A

control processing/store efficiency

78
Q

single value returned from SQL query with aggregate function

A

scalar aggregate

79
Q

multiple values returned from SQL query with aggregate function

A

vector aggregate

80
Q

relational Operation that causes two or more tables with a common domain to be combined into a single table or view

A

join

81
Q

an equi-join where one of the duplicate columns is eliminated in the result table

A

inner join

82
Q

what is inner used for?

A

alternative to where close to match primary and foreign keys

83
Q

components of relational model

A

data structure
data manipulation
data integrity

Database Management Systems (2 decks)

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