Brainscape's Knowledge GenomeTM

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


See full index

Intro to Relational DB - (CS1011) > Unit 2 - Design > Flashcards

Unit 2 - Design Flashcards

1
Q

Alternate Key

A

A candidate key that has not been selected as the primary key or part of the composite key.

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

Analytical Database

A

A database that is used primarily to generate reports based on data that doesn’t frequently change. It is optimized for fast querying.

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

Anomalies

A

Inconsistencies that may occur when there are redundancies or dependencies between non-key attributes.

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

Associative Entities

A

AKA junction or bridge entity
An entity that exists to convert a many-to-many relationship between two other entities into two one-to-many relationships in order to normalize the database.

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

Associative Entity

A

An entity that exists in order to normalize a many-to-many relationship between two other entities.

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

Attribute

A

A characteristic or property of an entity that is stored in the database. Each column in a database table represents an attribute.

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

Boyce-Codd Normal Form

A

The Boyce-Codd normal form, also known as BCNF, is viewed as 3.5NF. BCNF requires that the database design first fulfills the requirements of the third normal form (3NF) but also has every determinant in a table as a candidate key.

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

Business Process

A

A set of activities or tasks that are performed in a coordinated manner to achieve a specific business goal.

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

Business Use Case

A

A detailed description of how a system will be used to achieve a specific business goal in a certain scenario.

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

Candidate Key

A

A set of one or more attributes that can uniquely identify each entry in a table.

must have 2 properties:
Uniqueness: Each combination of values in the candidate key must be unique across all the rows in the table. No two rows can have the same values for the candidate key.

Irreducibility (Minimality): No proper subset of the candidate key should have the uniqueness property.
In other words, if any attribute is removed from the candidate key, it should no longer be able to uniquely identify each row.

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

Cardinality

A

The description of the numerical relationships between two tables.

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

Cartesian Product

A

An over-counting of data in a query that computes aggregates or counts when the query includes data from multiple tables that are not directly connected.

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

Chasm Trap

A

A condition in which two one-to-many joins converge on a single table.

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

Chen Notation

A

A visual representation technique for entity-relationship modeling that uses rectangles to represent entities, diamonds for relationships, and lines to represent cardinality and participation constraints.

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

Composite Key

A

A key that consists of two or more foreign keys, which produces a unique id for the record. Useful, but not mandatory in an associative identity. A new primary key for the entity would be a better choice if it will be referenced from other tables.

EX: entities movie and actor would be a many to many relationship. add an associate entity “role” which would have the movieID and actorID foreign keys as the composite key

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

Connection Trap

A

A situation where a complex relationship pattern can lead to ambiguous or incorrect query results and challenges in maintaining data integrity.

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

Data Warehouse

A

A centralized data repository collected from various sources, optimized for analysis, reporting, and decision making. A type of analytical database that is denormalized.

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

Denormalization

A

A process that intentionally duplicates data or reintroduces redundancy to increase query performance, simplify data retrieval, and optimize certain operations.

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

Dimension Table

A

A table that supports a fact table by storing additional details about its data.

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

Direct Relationship

A

A relationship that does not require an associative table—in other words, a one-to-one or one-to-many relationship.

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

Fact Table

A

A table that contains quantitative data such as sales figures or other measurable metrics.

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

Fan Trap

A

The fan trap occurs when you have two one-to-many joins that follow a parent-child form. You will probably get incorrect results if you try to aggregate both measures simultaneously.

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

Fifth Normal Form (5NF)

A

The fifth normal form (5NF) is mostly conceptual. A relation between tables is in 5NF if it is in the fourth normal form (4NF) and contains no join dependencies.

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

First Normal Form (1NF)

A

A relational database system’s first normal form (1NF) defines the fundamental rules for normalizing a single table to have unique column names and no duplicate rows.
(Ensure each table contains only atomic (indivisible) values. Create separate
tables for repeating groups or multivalued attributes.)

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

Fourth Normal Form (4NF)

A

A database design that satisfies all of the properties of the third normal form (3NF) and Boyce-Codd normal form (BCNF), and additionally should not have any multivalued dependencies.

26
Q

Functional Dependency

A

A situation in which the primary key determines the value of each attribute that is not a primary key.

27
Q

Lookup Table

A

A type of reference table that exists to provide a list of allowable values to populate an attribute in another table.
EX: a dropdown for state abbreviations

28
Q

Many-to-Many (M:N)

A

A relationship in which many rows in one table are related to many rows in a second table. This relationship type isn’t one that can be implemented in a relational model.

29
Q

Normal Forms

A

The series of stages or steps that the normalization process works through.

30
Q

Normalization

A

Normalization is the process of organizing and structuring data in a relational database efficiently, which involves dividing a database into multiple tables and applying a set of rules.

31
Q

One-to-Many (1:N)

A

A relationship in which one row in a database table relates to many rows in a second table.

32
Q

One-to-One (1:1)

A

The one-to-one (1:1) relationship type is quite rare in database design, as this defines that a single row in one table is related to just one row in another table and vice versa.

33
Q

Partial Dependency

A

A condition in which an attribute in a table depends on only a part of the primary key and not the whole key.

34
Q

Reference Table

A

A table that contains a series of key-value entries where the key attribute is the primary key and the value attribute is what the key represents. Used when there is no need to store the history of the reference data

35
Q

Relationship Strength

A

The degree of association or dependency between entities in a relationship.

36
Q

Repeating Group

A

A condition where an attribute contains multiple entries for a single record.

37
Q

Second Normal Form (2NF)

A

All of the data and non-prime attributes are functionally dependent on each candidate key. Create separate tables for partial dependencies if necessary

38
Q

Snowflake Schema

A

A schema in which some of the dimension tables in a star schema have their own subordinate dimension tables.

39
Q

Star Schema

A

A schema in which a central fact table serves as the primary focus with support from dimension tables.

40
Q

Strong Entity

A

An entity that can exist independently of the other entities with which it has relationships.

41
Q

Strong Relationship

A

A relationship between two entities that are existentially independent of one another. (Between 2 strong entities)

42
Q

Third Normal Form (3NF)

A

In a 3NF-compliant table, no non-primary key attribute is transitively dependent on the primary key. It preserves lossless database transitions and removes functional dependencies.

43
Q

Transactional Database

A

A database that is used primarily to accept and store new transactions. It is optimized for fast data input.

44
Q

Weak Entity

A

An entity that cannot exist independently of one or more other entities with which it has relationships.

45
Q

Weak Relationship

A

A relationship involving two entities whose existence is dependent on that of the related entity. (Between a strong and weak entity)

46
Q

Chen notation - Entity

A

rectangle
(Tables in the database)

47
Q

Chen notation - strong (entity/relationship)

A

solid, single-line border
(does not rely on any
other entity for its existence. )

48
Q

Chen notation - weak(entity/relationship) (identifying) entity

A

solid, double-line border
(depends on another entity that is considered
its owner)

49
Q

Chen notation - associative entity

A

a diamond inside a rectangle
(weak entity that represents many-to-many relationships
expressed by another entity)

50
Q

Chen notation - Relationship

A

Diamond
(the interconnection between two entities)

51
Q

Chen notation - Participations

A

lines that link the entities to the relationship

52
Q

Direct relationships

A

Direct relationships are relationships that do not require an associative table (one-to-one or one-to-many relationships.)

53
Q

Chen notation - primary key

A

Underlined within its oval

54
Q

Chen notation - attributes

A

oval
(columns)

55
Q

Chen notation - cardinality

A

1:1 (one-to-one) - The “one” side of the relationship typically involves the primary key in that table, and
the “many” side is the foreign key.

1:N (one-to-many)
M:N (many-to-many)

56
Q

What should you do with 1 to many relationships?

A

it should generally be changed into two one-to-many relationships with a linking table (also called a bridge, associative, or composite table) in between them

57
Q

What is used to link 2 tables together in when you need to change a many to many to two one to many relationships

A

linking table AKA bridge, associative, or composite table, which will have multiple occurrences of the foreign key value

58
Q

Partial key

A

A weak entity’s unique identifier made up of a combination of its attributes and its parent entity’s attributes

59
Q

key-value entries

A

two columns where the first is the key (auto generated unique value or an abbreviation) and the second is the value

60
Q

Non-prime attribute

A

attributes that are not the table’s primary key

61
Q

transitive dependency

A

when one non-prime attribute depends on another nonprime attribute. (it must “transit” through another attribute)

EX: column A determines column B, and column B
determines column C. You would have a transitive functional dependency because column A then
determines column C. Column C should be removed and placed in a separate table to resolve this issue.
—
User (UserId, UserFirstName, UserLastName, Email, Phone, Address, City, State, Zip)

This is a transitive functional
dependency because the Zip is dependent on the UserID, and the City and State are dependent on the Zip. To
convert this table to 3NF, you would need to move this data into a separate table like this:
User (UserId, UserFirstName, UserLastName, Email, Phone, Address, Zip)
ZipCode (Zip, City, State)

User table would link to the Zip in the ZipCode table, so if you had to add an address in any other table later on, you
could use the same ZipCode table as a lookup table. (does not work if a country’s zip can have multiple names)

62
Q
A

Intro to Relational DB - (CS1011) (5 decks)

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