Brainscape's Knowledge GenomeTM

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


See full index

Data Management Concepts > Chapter 4 > Flashcards

Chapter 4 Flashcards

1
Q

What is data structure?

A

Tables (relations), rows, columns

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

What is data manipulation?

A

Powerful SQL operations for retrieving and modifying data.

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

What is data integrity?

A

Mechanisms for implementing business rules that maintain integrity of manipulated data

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

What is a name, two-dimensional table of data?

A

A relation

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

What consists of rows (records) and columns (attribute or field)?

A

A table

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

What are the requirements for a table to qualify as a relation?

A
  • Must have a unique name
  • Every attribute value must be atomic (not multivalued, not composite)
  • Every row must be unique (can’t have two rows with exactly the same values for all their fields)
  • Attributes (columns) in tables must have unique names.
  • The order of the columns must be irrelevent.
  • The order of the rows must be irrelevent.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What must all relations be in?

A

1st Normal form

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

How do relations correspond with the E-R model?

A
  • Relations (tables) correspond with entity types and with many-to-many relationship types.
  • Rows correspond with entity instances and with many-to-many relationship instances
  • Columns correspond with attributes.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Are relation (in relational database) and relationship (in E-R model) the same?

A

No, they are not the same.

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

What are special fields that serve two main purposes (Primary and Foreign)?

A

Keys

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

What two main purposes do keys serve?

A
  • Primary Key
  • Foreign Key
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What is a primary key?

A

Unique identifiers of the relation.

Example: Employee numbers, SSN etc.

This guarentees that all row are unique

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

What are Foreign keys?

A

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

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

How are the fields in keys defined?

A

Simple (a single field) or composite (more than one field)

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

How are keys usually used?

A

As indexes to speed up the response to user queries.

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

Keys (Visual example)

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

What are domain constraints?

A

Allowable values for an attribute

Example: setting restrictions for what can be input for an attribute.Visual below

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

What does entity integrity mean?

A
  • That no primary key attribute may be null. All primary key fields MUST contain data values.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What is referential integrity mean?

A

It refers to rules that maintain consistency between the rows of two related tables.

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

What are rules that state that any foreign key value (on the relation of the many side) MUST match a primary key value in the relation of the one side. (Or the foreign key can be null)?

A

Referential Integrity

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

What is an example of Referential Integrity?

A

Delete Rules

Restrict - don’t allow delete of “parent” side if related rows exist in “dependent” side

Cascade - automatically delete “dependent” side rows that correspon with the “parent” side row to be deleted

Set-to-Null - set the foreign key in the dependent side to null if deleting from the parent side -> not allowed for weak entities

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

Referential Integrity Constraints visualized

A
23
Q

When transforming an ER diagram into a relation, how are simple attributes handled?

A

E-R attributes map directly onto the relation.

24
Q

When transforming an ER diagram into a relation, how are composite attributes handled?

A

Use only their simple, component attributes

25
Q

When transforming an ER diagram into a relation, how are multivalued attributes handled?

A

Becomes a seperate relation with a foreign key taken from the superior entity

26
Q

How are weak entities mapped from an ER diagram into relations?

A
  • They become a seperate relation with a foreign key taken from the superior entity
  • Primary key composed of:
  • partial identifier of weak entity
  • primary key of identifying relation (strong entity)
27
Q

How is a One-to-Many Binary relationship mapped when transforming ER diagrams into relations?

A

Primary key on the one side becomes a foreign key on the many side

28
Q

How is a Many-to-Many Binary relationship mapped when transforming ER diagrams into relations?

A

Create a new relation with the primary keys of the two entities as its primary key

29
Q

How is a one-to-one Binary relationship mapped when transforming ER diagrams into relations?

A

Primary key on mandatory side becomes a foreign key on optional side

30
Q

How are associative entities mapped when transforming ERDs into relations?

A

If the Identifier is not assigned

Default primary key for the association relation is composed of the primary keys of the two entities (and in M:N relationship)

If the Identifer is assigned

It is natural and familiar to end-users

Default identifier may not be unique

31
Q

Mapping an associative entity with identifer example

A
32
Q

How is a Unary relationship mapped when transforming ERD into relations?

A

One-to-Many = Recursive foreign key in the same relation

Many-to-Many = Two relations:

  • One for the entity type
  • One for an associative relation in which the primary key has two attributes, both taken from the primary key of the entity
33
Q

How are ternary (and n-ary) relationships mapped when transforming ERDs into relations?

A
  • One relation for each entity and one for the associative entity
  • The associative entity has foreign keys to each entity in the relationship
34
Q

How are supertype/subtype relationships mapped when transforming ERDs into relations?

A
  • One relation for supertype and for each subtype
  • Supertype attributes (including identifier and subtype discriminator) go into supertype relation
  • Subtype attributes go into each subtype; primary key of supertype relation also become primary key of subtype relation
  • 1:1 relationship established between supertype and each subtype, with supertype as primary table
35
Q

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

A

Data normalization

36
Q

What is the process of decomposing relations with anomalies to produce smaller, well-structured relations?

A

Data normalization

37
Q

What defines a well-structured relation?

A

A relation that contains minimal data redundancy and allows users to insert, delete, and update rows without causing data inconsistencies.

38
Q

What is the goal of a well-structured relation?

A

To avoid anomalies

39
Q

What are the different types of anomalies to be avoided?

A

Insertion Anomaly

Deletion Anomaly

Modification Anomaly

40
Q

What type of anomaly occurs when adding new rows forces users to create duplicate data?

A

Insertion Anomaly

41
Q

What type of anomaly occurs when deleting rows may cause a loss of data that would be needed for other future rows?

A

Deletion Anomaly

42
Q

What type of anomaly occurs when changing data in a row forces changes to other rows because of duplication?

A

Modification Anomaly

43
Q

Example table with anomalies

A
44
Q

Steps of normalization visualized

A
45
Q

What is it called when the value of one attribute (the determinant) determines the value of another attribute?

A

Functional dependency

46
Q

What is a candidate key?

A

A unique identifier. One of the candidate keys will become the primary key.

For example: perhaps there is both credit card number and SS# in a table, in this case both are candidate keys

47
Q

What are non-key fields functionally dependent on?

A

Every candidate key

48
Q

What is First Normal Form?

A
  • No multivalued attributes
  • Every attribute value is atomic
  • All relations are in 1st Normal Form

Example of what is and isn’t in 1st Normal Form

49
Q

Further Anomalies Example

A
50
Q

What is second normal form?

A
  • 1NF PLUS every non-key attribute is fully funtionally dependent on the ENTIRE primar key.
  • Every non-key attribute must be defined by the entire key, not by only part of the key
  • No partial functional dependencies
51
Q

What is third normal form?

A

2NF PLUS no transitive dependencies (functional dependencies on non-primary-key attributes)

*It’s called transitive, because the primary key is a determinant for another attribute, which in turn is a determinant for a third.

52
Q

What is the solution to transition transitive dependencies to make something third normal form?

A

Non-key determinant with transitive dependencies go into a new table; non-key determinant becomes primary key in the new table and stays as foreign key in the old table.

53
Q

What is it called when you combine entities from multiple ER models into common relations?

A

View integration

54
Q

What are the issues to watch out for when merging entities from different ER models?

A

Synonyms - two or more attributes with different names but same meaning

Homonyms - attributes with same name but different meanings

Transitive dependencies - even if relations are in 3NF prior to merging, they may not be after merging

Supertype/Subtype relationships - may be hidden prior to merging

Data Management Concepts (12 decks)

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