Brainscape's Knowledge GenomeTM

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


See full index

Database Design > Quiz 1 > Flashcards

Quiz 1 Flashcards

1
Q

What makes a well-structured relation?

A

A relation that contains minimal redundancy and allows users to insert,
modify, and delete the rows in a table without errors or inconsistencies

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

Insertion anomalies

A

An insertion anomaly is the inability to add data to the database due to the absence of other data. Are experienced when we attempt to store a value for one attribute
but cannot because the value of another attribute is unknown

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

Deletion anomalies

A

A deletion anomaly occurs when you delete a record that may contain attributes that shouldn’t be deleted.

Are experienced when a value for one attribute we wish to keep is unexpectedly removed when a value for another attribute is deleted

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

Modification anomalies

A

A modification anomaly is an unexpected side effect from trying to insert, update, or delete a row.

Are experienced when changes to multiple instances of an entity (rows of a table) are needed to effect an update to a single value of an attribute

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

Normalization

A

The process of successively reducing relations with anomalies to produce smaller, well-structured relations.

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

What are the objectives of normalization?

A

To eliminate certain kinds of data redundancy and to avoid certain anomalies

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

Normal Form

A

A state of a relation that can be determined by applying simple rules regarding functional dependencies (or relationships between attributes). Are used to eliminate or reduce redundancy in database tables

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

A violation of NF is when…

A

two (or more) different entities are combined into a single entity

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

What are the two constraints required for 1NF

A
  1. There are no repeating groups in the relation. In other words, each
    row/column intersection can contain one and only one value, rather
    than a set of values.
  2. A primary key has been defined, which uniquely identifies each row in the relation.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

A table with repeating groups is converted to a relation in 1NF by

A

Extending the data in each column to fill the cells that are empty because of the repeating groups structures

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

Functional Dependency

A

A particular relationship between two attributes or two sets of attributes: The value of one attribute (the determinant) determines the value of other attributes

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

Determinant

A

Any attribute that you can use to determine the values assigned to other attribute(s) in the same row.
The attribute on the left-hand side of the arrow in a functional dependency

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

A primary key is always a ________, while a __________ may or may not be a primary key

A

Determinant

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

Give an example of a functional dependency

A

SSN -> NAME, ADDRESS, BIRTHDATE. A person’s name, address and birthdate are functionally dependent on that person’s social security number

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

True/False: Data in a relation prove that a functional dependency exists

A

FALSE. Only knowledge of the problem domain is a reliable method for identifying
a functional dependency

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

Identify the functional dependencies:
EMPLOYEE3 (EMP_ID, NAME, DEPT, SALARY, COURSE_ID, COURSE_NAME, DATE_ COMPLETED)

A

EMP_ID -> NAME, DEPT, SALARY
COURSE_ID ->COURSE_NAME
EMPID, COURSE_ID -> DATE_COMPLETED

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

Partial functional dependency

A

A functional dependency in which one or more nonkey (non-prime) attributes are functionally dependent on part (but not all) of the primary key

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

We check partial dependency if …

A

we have a composite primary key.

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

Partial dependencies create issues because….

A

Partial dependencies cannot be tolerated because a table that contains such dependencies is still subject to data redundancies and, therefore, to various anomalies

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

The requirements for 2NF are:

A
  1. The table is in 1NF
  2. It includes no partial dependencies; that is, no attribute is dependent on only a portion of the primary key

2NF a relation that is in 1NF in which every nonkey attribute is fully functionally dependent on the primary key

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

Convert this table from 1 NF to 2 NF
EMPLOYEE3 (EMP_ID, NAME, DEPT, SALARY, COURSE_ID, COURSE_NAME,
DATE_COMPLETED)

A

Write each key component on a separate line, and then write the original key on the last line.

Each component will become the key in a new table:
EMP_ID
COURSE_ID
EMP_ID, COURSE_ID

Write the dependent attributes after each new key.
We get three new tables:
EMPLOYEE (EMP_ID, NAME, DEPT, SALARY)
COURSE (COURSE_ID, COURSE_NAME)
EMPCOURSE (EMP_ID, COURSE_ID, DATE_COMPLETED)

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

A relation that is in 1NF will be in 2NF if any one of the following conditions applies

A

The primary key consists of only one attribute

No nonkey attributes exist in the relation (thus all of the attributes in the
relation are components of the primary key)

Every nonkey attribute is functionally dependent on the full set of primary key attributes

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

Transitive Dependency

A

A functional dependency between two (or more) nonkey (or non-prime) attributes. A functional dependency is said to be transitive if it is indirectly formed by two functional dependencies. When an indirect relationship causes functional dependency it is called Transitive Dependency.

If P -> Q and Q -> R is true, then P-> R is a transitive dependency.

To achieve 3NF, eliminate the Transitive Dependency.

24
Q

Example of a transitive dependency

A

Course (CourseID, Cname, InstructorID, Iname)
Neither Instructor ID nor Iname is a key attribute - that is, neither
attribute is at least part of a key.
However, Iname is functionally dependent on InstructorID
InstructorID -> Iname

25
Q

True/ False: A transitive Dependency still yields data anomalies

A

True

26
Q

A table is in 3NF if it is

A

in 2NF and It contains no transitive dependencies

27
Q

A transitive dependency can be resolved by

A

decomposing into more tables

28
Q

ER Model stands for

A

entity-relationship

29
Q

True/False a table in 3NF guarantees all anomalies have been removed

A

False

30
Q

A table is in BCNF if

A

it is 3NF and For every non-trivial functional dependency (FD) in the table, the determinant is a super key of the table.

31
Q

A functional dependency is trivial if

A

the dependent is a subset of the determinant

32
Q

If a functional dependency X->Y holds true where Y is not a subset of X, this is an example of a

A

non trivial Functional dependency.

33
Q

A super key is

A

a set of one or more attributes (columns), which can uniquely identify a row in a table.

34
Q

A candidate key

A

Candidate key is also a unique key (like primary keys) to identify a record uniquely in a table but a table can have multiple candidate keys.

35
Q

Steps to convert to BCNF

A

Pull out the undesirable FDs from the target relation as separate relation(s).

Retain the determinant of the pulled-out relation schema as an attribute(s) in the leftover target relation schema to facilitate reconstruction of the original target relation schema

36
Q

BCNF stands for

A

Boyce-Codd Normal Form

37
Q

A table is in 4NF if

A

If it is in BCNF and does not have multi-valued dependency

38
Q

Conditions for a table to have a multi-valued dependency

A

Minimum of three columns.

Two multi-valued attributes, e.g., Y and Z, that are independent of each other in R

39
Q

X ⇉Y | Z
X

A

X ⇉Y, means that for a given value of X, the same set of Y values occurs for each value of Z

40
Q

Steps to decompose to 4NF

A

Replace the target relation schema (R) by the projections (R1 and R2) that contain the determinant and dependent present in each of the two MVDs.

41
Q

What is a Lossless join decomposition

A

a decomposition of a relation R into relations R1, R2 such that if we perform a natural join of relation R1 and R2, it will return the original relation R.

42
Q

A lossless join decomposition can test

A

potential multivalued dependency

43
Q

Steps to test a multivalued dependency

A

List all potential/possible multivalued dependencies and test them. Ex. Name →→ Skill | Music, Music →→ Skill | Name, Skill →→ Name | Music
Then do natural joins to see if additional rows occur

44
Q

If additional rows are created while doing a natural join to test multivalued dependency then

A

the test fails, we have multi-value dependency

45
Q

A table is in 5NF

A

if it is in 4NF and it does not have a join dependency

46
Q

Multi-valued dependency definition

A

occurs when two attributes in a table are independent of each other but, both depend on a third attribute.
A multivalued dependency consists of at least two attributes that are dependent on a third attribute that’s why it always requires at least three attributes.

47
Q

5NF is also called

A

Project Join Normal Form

48
Q

A join dependency in a relation schema R pertains to conditions where

A

the natural join of a proper subset of its projections results in the strict reconstruction of R

49
Q

A relation schema, R does not have a join dependency if

A

It cannot be reconstructed by a natural join of any proper subset of its projections

50
Q

True/False If a table can be decomposed into smaller tables without losing information or having additional information, the table violates the 5th normal form.

A

True

51
Q

We resolve a join dependency by

A

decomposing the table into smaller tables

52
Q

True/False A table that has no undesirable functional dependencies is in 4NF

A

FALSE

53
Q

Given table R (X, Y, Z) with Y and Z mapping to multi-valued attributes, if we cannot do lossless-join decomposition with binary projections R1 (X, Y) and R2 (X, Z), then Y and Z are not independent.

A

TRUE

54
Q

Primary Key Definition

A

Primary Key is a set of attributes (or attribute) which uniquely identify the tuples in relation or table.

The primary key is a minimal super key, so there is one and only one primary key in any relationship.

A primary key is always a candidate key.

55
Q

Composite Key

A

A primary key that consists of more than one attribute

56
Q

Foreign key

A

An attribute in a relation that serves as the primary key of another relation in the same database

Database Design (2 decks)

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