Brainscape's Knowledge GenomeTM

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


See full index

Relational Databases > Chap 4 > Flashcards

Chap 4 Flashcards

1
Q

Determinant

A

The attribute on the left side of the arrow in a functional dependency

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

Functional Dependency

A

A constraint between 2 attributes in which the value of 1 is determined by the value of another.

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

Transitive Dependency

A

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

OrderID->CustomerID->CustName
A functional dependency between the primary key and one or more nonkey attributes that are dependent on the primary key via another nonkey attribute.

OrderID->CustomerID->CustName

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

Recursive Foreign Key

A

A foreign key in a relation that references the primary key values of the same relation

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

Normalization

A

The process of decomposing relations with anomalies into smaller well-structured relations.

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

Composite Key

A

Primary key that consists of more than one attribute

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 requires that certain rules regarding relationships between attributes or foreign dependencies are satisfied.

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

Partial Functional Dependency

A

A functional dependency in which one or more nonkey 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
9
Q

Enterprise Key

A

A primary key whose value is unique across all relations.

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

Surrogate Primary key

A

A serial number or other system-assigned primary key for a relation

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

Transitive Dependency

A

A functional dependency between primary key and one or more non key attributes that are dependent upon the primary key via another nonkey attribute.

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

3NF

A

A relation that is in 2NF and has no transitive dependencies

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

2NF

A

A relation 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
14
Q

1NF

A

A relation that has a primary key and in which there are no repeating groups

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

Anomaly

A

An error or inconstancy that may result when a user attempts to update a table that contains redundant data.

Three types: Insertion, Deletion, Modification

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

Well Structured Relation

A

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

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

Normal Form vs. Normalization

A

Normalization is the process of decomposing relations with anomalies to Normal Form.

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

Candidate Key vs. Primary Key

A

Candidate key is an attribute or combination of attributes that uniquely identifies a row in a relation. It can be the primary key but does not have to be.

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

Partial Dependency vs. Transitive Dependency

A

A partial dependency is where A->B but something can be removed from A and yet A->B still.

In a transitive dependency, A->B, B->C therefore A->C
A partial dependency is where A->B but something can be removed from A and yet A->B still.

In a transitive dependency, A->B, B->C therefore A->C

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

Composite Key vs. Recursive Foreign Key

A

A composite key is a primary key that has more than 1 attribute where as a recursive foreign key is where the primary key also exists as a foreign key in the same relation.

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

Determinant vs. Candidate Key

A

Determinants is the attribute on the left side of the arrow of a functional dependency.

Candidate key is an attribute or combination of attributes that uniquely identify a row in a relation.

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

Enterprise vs. Surrogate Key

A

Surrogate keys have no business meaning and are solely used to identify a record in the table.

Enterprise keys are a primary key who’s unique across the entire database.

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

Differences between conceptual and logical data model

A

Conceptual data modeling is done in the planning and analysis phase. Analysts draw a diagram to outline scope of data involved. Then produces a data model that identifies all data, every attribute and relationship defined.

Logical Data Model describes data as detailed as possible. Attributes, PK, FKs specified. Normalization is done.

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

Two properties each candidate key must satisfy

A
  1. Unique identification: must uniquely identify row; implies each nonkey attribute is functionally dependent
  2. Nonredundant: no attribute in key can be deleted without destroying property of unique identification
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

Three anomalies that can arise

A

Insertion, Deletion and Modification

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

Purpose of Normalization

A
  1. Minimize redundancy thereby avoiding anomalies and conserving space
  2. Simplify enforcement of referential integrity constraints
  3. Make easier to maintain data (insert, update, delete)
  4. Provide better design and strong basis for future growth.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

Benefits of enforcing integrity constraints as part of database design and implementation process

A

Better design
Maintains consistency
No orphaned records

Databases act as central repositories and may serve several applications so those rules must be enforced across all applications. Any changes must be made to all places.

28
Q

How are relationships between entities represented in relational data model?

A

Associations between tables are defined through use of foreign keys

29
Q

How do you represent a M:N ternary relationship in a relational data model?

A

Map an associative entity that links 3 regular entity types by creating a new associative relation

30
Q

What is the relationship between the primary key of a relation and the functional dependencies among all attributes within that relation?

A

The primary key is the determinant among all fully functional dependent attributes in a relation.

31
Q

What can be done with primary keys to eliminate key ripple effects a database evolves?

A

An enterprise key can be created that would be unique across the entire database eliminating those types of issues

32
Q

Three conditions that suggest a surrogate key should be created as a primary key.

A
  1. When there is a composite key
  2. When natural key is inefficient (too long, not suitable, etc.)
  3. Natural key may not be unique over time….may be recycled
33
Q

Horizontal vs. Vertical Partitioning

A

These are forms of denormalization involving creating more tables by partitioning a relation into multiple physical tables.

Horizontal: Places different rows into different tables based on common column values

Vertical: Distributes columns into separate tables repeating the primary key

34
Q

Pros of Partitioning

A
  1. Efficiency – records used together grouped together
  2. Local Optimization – better performance
  3. Security – Users only access data they have access to
  4. Recovery and Uptime – Faster backups
  5. Load Balancing – partitions on different disks – less contention
35
Q

Cons of Partitioning

A
  1. Inconsistent Speed (across partitions)
  2. Complexity
  3. Duplicate data
36
Q

Physical File vs. Tablespace

A

Physical File: Named partition of secondary memory (ie, hard disk) allocated to store physical records

Tablespace: Logical storage unit in which data from 1 or more tables, views, etc. can be stored. Can have several tablespaces consisting of logical units (extents) which contain contiguous data blocks.

37
Q

Normalization vs. Denormalization

A

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

Denormalization transforms normalized relations into non-normalized relations.
 If there are two entities with a 1:1 relationship may combine them
 If there are M:M relationships with nonkey attributes
 When a relation references data that doesn’t have any other relationships

38
Q

Range Control vs. Null Control

A

These are structures to enforce data integrity

Range Control: limits set of permissible values a field can have. (Better to implement in DBMS than in application).

Null Value Control: integrity control that prohibits null value

39
Q

Secondary Key vs. Primary Key

A

A field or combination of fields that can be indexed for faster storage retrieval

40
Q

Major Inputs into Physical Database Design?

A

 Normalized relations (including estimates of range of the number of rows in tables)
 Definitions of each attribute
 Descriptions of where and when data used in various ways (entered, retrieved, deleted, updated)
 Expectations or requirements for response time, data security, backup, recovery, retention and integrity
 Descriptions of the DBMS used to implement the database.

41
Q

What are the key decisions in physical database design?

A

*Affects integrity and performance

 Choose data type for each attribute: maximizes integrity and minimizes storage space
 Setting DBMS file organization so similarly structured records arranged in secondary memory for quick storage, retrieval and update
 Selecting structures (including indexes and overall database architecture)
 Prepare to handle queries that will optimize performance

42
Q

What decisions have to be made to develop a field specification?

A

Field: the smallest unit of application data recognized by system software

 Type of data used to represent values of this field
 Data integrity controls built into the database
 Mechansms the DBMS uses to handle missing values

43
Q

What are the objectives of selecting a data type for a field?

A

 Represent all possible values
 Improve data integrity
 Supports all data manipulations
 Minimizes storage space

44
Q

Why reserve more space for numeric field than any of initial stored values?

A

If DBMS uses numeric field’s data type for results on any mathematics a small data type won’t work

45
Q

Why are field values sometimes coded?

A

Can be done with fields that have limited number of possible values because it requires less space. (Lookup or translation table)

46
Q

What options are available for controlling data integrity at the field level?

A

 Default value
 Range Control
 Null Value Control
 Referential Integrity

47
Q

3 ways to handle missing field values

A
  1. Substitute with an estimate
  2. Track missing data to generate reports (can use a trigger)
  3. Perform sensitivity testing so missing data are ignored
48
Q

Why normalized relations may not comprise an efficient physical implementation structure?

A

May not yield efficient data processing.

Fully normalized tables create large numbers of tables so processing performance can be severely impacted.

49
Q

Seven Important Criteria for Selecting the Best File Organization

A
  1. Fast data retrieval
  2. High throughput for processing data and maintenance transactions
  3. Efficient use of storage space
  4. Protect from failures or data loss
  5. Minimize need for reorganization
  6. Accommodate growth
  7. Security from unauthorized use
50
Q

What are benefits of hash index table?

A

Retrieval on PK very fast

Detecting, updating and adding records is very easy

51
Q

Purpose of clustering of data in a file

A

Useful for improving performance of join operatrions

52
Q

9 rules of thumb for choosing indexes

A
  1. Most useful on larger tables
  2. Index primary key for each table
  3. Index search field (fields frequently in WHERE clause)
  4. Fields in ORDER and GROUP BY commands
  5. When there are >100 but not <30
  6. If field has long values, consider creating compressed version
  7. Use surrogate if key determines location
  8. May have limit on number of indexes of bytes allowed
  9. Be careful of fields with null values
53
Q

Why are there significant performance gains in query performance achieved with parallel processing?

A

Because table can be so large, parallel processing can break a part a query into modules so each can be processed in parallel.

54
Q

What problems might arise from vertical portioning? What general conditions influence when to partition vertically?

A

Some disadvantages are inconsistent access speed, complexity and wasted space and update time. It can be used when database files are distributed across multiple computers.

55
Q

Why should you not create an index for every column?

A

Indexes create a lot of overhead

56
Q

With Sequential File Organization, is it possible to permit sequential scanning of data based on several sorted orders?

A

It is possible but it involves scanning the entire file so its very slow.

57
Q

File Organization

A

A technique for physically arranging the records of a file on secondary storage devices.

58
Q

Sequential File Organization

A

Storage of records in a file sequence according to its primary key.

59
Q

Indexed File Organization

A

Storage of records either sequentially or nonsequentially with an index that allows software to locate individual records.

Index->Table

60
Q

Hashed File Organization

A

Storage System where address for each record is determined using some algorithm. Algorithm converts a primary key value into a relative record number or relative file address.

61
Q

Secondary Key

A

An index that allows each entry to point to One or more fields for which more than one record may have the same combination of values. Also called a nonunique key.

62
Q

Data Type

A

Detailed coding scheme recognized by system software for representing organizational data.

63
Q

Join Index

A

An index on columns from 2 or more tables that come from the same domain of values

64
Q

Pointer

A

A field of data indicating a target address that can be used to locate a related field or record of data.

65
Q

Clustering Index

A

DBMS allows adjacent secondary memory space to contain rows from several tables. Can be joined together so they share the same data blocks. Reduces the time to access related records compared to normal allocation of different files to different areas of a disk.

Relational Databases (1 decks)

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