Brainscape's Knowledge GenomeTM

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


See full index

Database > Midterm Review > Flashcards

Midterm Review Flashcards

1
Q

What is a DBMS?

A

A database management system, which is a collection of programs that manages the database structure and controls access to data stored in the database.

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

What is the role of the DBMS?

A

It is the intermediary between the user and the database. It enables data to be shared among multiple applications or users, presents the end user with an integrated view of data, provides more efficient and effective data management, and improves sharing, security, integration, access, decision-making, productivity, etc.

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

What are 3 advantages of the DBMS?

A

Improved data sharing, improved data security, better data integration, minimized data inconsistency, improved data access, improved decision making, and increased end-user productivity.

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

What are 3 functionalities of the DBMS?

A

Data dictionary management, data storage management, data transformation and presentation, security management, multiuser access control, backup and recovery management, database access languages and application programming interfaces, and database communication interfaces.

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

How can you categorize the types of databases?

A

By number of users (single-user database/desktop, multiuser database/workgroup/enterprise), by location (centralized, distributed, cloud), by data type (general-purpose, discipline-specific), by usage and time sensitivity (operational, analytical (OLAP), and by data structure (unstructured, structured, semi-structured).

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

Name and explain 3 problems with file system data processing.

A

Lengthy development times, difficulty of getting quick answers, complex system administration, lack of security and limited data sharing, and extensive programming.

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

What is data redundancy?

A

Unnecessarily storing the same data at different places.

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

What are 3 results of data redundancy?

A

Poor data security, data inconsistency, data-entry errors, data integrity problems, and data anomalies.

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

What is a database system?

A

An organization of components that define and regulate the collection, storage, management, and use of data within a database environment.

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

What are the components of a database system?

A

Hardware, software, people, procedures, and data.

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

What are 3 disadvantages of database systems?

A

Increased costs, management complexity, maintaining currency, vendor dependence, and frequent upgrade/replacement cycles.

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

What is a data model?

A

A representation of data structures and their characteristics, relations, constraints, transformations, and other constructs with the purpose of supporting a specific problem domain.

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

What is the importance of a data model?

A

It facilitates communication/interaction among the designer, the applications programmer, and the end user, it gives various views of the database, organizes data for various users, and provides an abstraction for the creation of a good database.

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

What are the building blocks of data models?

A

Entity, attribute, relationship and constraint.

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

What is an entity?

A

A person, place, thing, or event about which data will be collected and stored.

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

What is an attribute?

A

A characteristic of an entity.

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

What is a relationships?

A

An association among entities.

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

What is a constraint?

A

A restriction placed on data.

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

What is a business rule?

A

A brief, precise, and unambiguous description of a policy, procedure, or principle.

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

How do we use business rules to identify building blocks of data models?

A

Nouns translate into entities, and verbs translate into relationships among entities.

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

What added capabilities does the Object-Oriented Data Model (OODM) provide to compared Entity Relationship (ER) models?

A

It is capable of the concept of inheritance, which allows objects within the class hierarchy to inherit attributes and methods from their parent classes.

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

What are the similarities and differences of ER and OODM?

A

Both the ER and OODM models include the concept of attributes, which describe the properties of objects/entities. They also both represent entities, which are collections of objects sharing similar attributes and behaviors, and finally, both allow for the representation of relationships between entities.

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

What is big data?

A

A movement that aims to find new and better ways to manage large amounts of web- and sensor-generated data and derive business insight from it.

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

What are the 3Vs?

A

Volume refers to the amounts of data being stored, velocity refers to the speed in data growth as well as the need to process this data quickly to generate information and insight, and variety data is collected in multiple different data formats.

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

What are the advantages of the hierarchical model?

A

It is simple and efficient.

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

What are the advantages of the network model?

A

It is flexible and has navigational capabilties.

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

What are the advantages of the relational data model?

A

It is simple, enforces data integrity, and has query optimization.

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

What are the advantages of the entity-relationship (ER) model?

A

It has conceptual clarity, allows for communication, and has better data analysis.

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

What are the advantages of the object-oriented data model (OODM)?

A

It allows for data encapsulation, complex relationships, and inheritance.

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

What are the advantages of the enhanced entity-relationship (EER) model?

A

It allows for enhanced modeling, complex relationships, and semantic clarity.

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

Briefly compare the similarities and differences of External Views and Conceptual Model abstraction levels.

A

They include the same level of details, while the conceptual model represents a global view of the entire database by the entire organization, and the external view is the end users’ view of the data environment.

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

What are the advantages of external views?

A

It is easier to identify specific data requirements for each business unit’s operations, it is easier to check the model to ensure that it supports all processes as defined by their external models, it helps to ensure security constraints in the database design by focusing only a subset of data, and it makes application program development much simpler.

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

Define ‘table’ in a relational model.

A

A table is perceived as a two-dimensional structure composed of rows and columns.

34
Q

What are the characteristics of a table?

A

Each table row (tuple) represents a single entity occurrence within the entity set. Each table column represents an attribute, and each column has a distinct name. Each intersection of a row and column represents a single data value. All values in a column must conform to the same data format. Each column has a specific range of values known as the attribute domain. The order of the rows and columns is immaterial to the DBMS. Each table must have an attribute or combination of attributes that uniquely identifies each row.

35
Q

What is a superkey?

A

An attribute or combination of attributes that uniquely identifies each row in a table.

36
Q

What is a candidate key?

A

A minimal (irreducible) superkey; a superkey that does not contain a subset of attributes that is itself a superkey.

37
Q

What is a primary key?

A

A candidate key selected to uniquely identify all other attribute values in any given row; cannot contain null enties.

38
Q

What is a foreign key?

A

An attribute or combination of attributes in one table whose values must either match the primary key in another table or be null.

39
Q

What is a secondary key?

A

An attribute or combination of attributes used strictly for data retrieval purposes.

40
Q

What is a composite key?

A

A key that is composed of more than one attribute.

41
Q

What is the requirement for the entity integrity rule?

A

All primary key entries are unique, and no part of a primary key may be null.

42
Q

What is the requirement for the referential integrity rule?

A

A foreign key may have either a null entry, as long as it is not a part of its table’s primary key, or an entry that matches the primary key value in a table to which it is related (every non-null foreign key value must reference an existing primary key value).

43
Q

Name three integrity rules.

A

The entity integrity rules, the referential integrity rule, the NOT NULL constraint, and the UNIQUE constraint.

44
Q

What is the entity integrity rule?

A

All primary key entries are unique, and no part of a primary key may be null.

45
Q

What is referential integrity?

A

A foreign key may have either a null entry, as long as it is not part of its table’s primary key, or an entry that matches the primary key value in a table to which it is related (every non-null foreign key value must reference an existing primary key value).

46
Q

What is the NOT NULL constraint?

A

Placed on a column to ensure that every row in the table has a value for that column.

47
Q

What is the UNIQUE constraint?

A

Restriction placed on a column to ensure that no duplicate values exist for that column.

48
Q

Compare the SELECT and PROJECT relational operators.

A

Both have the property of closure, both are unary because they one use one table as input, SELECT will not limit the attributes returned so all attributes of the table will be included in the result, and PROJECT will not be limited in the rows returned, so all rows of the specified attributes will be included in the result.

49
Q

Name the relational set operators that require union compatible inputs.

A

UNION, INTERSECT, and DIFFERENCE.

50
Q

What is the UNION operator and what are its requirements and results?

A

It combines two sets of tuples into a single set, eliminating duplicate tuples. Both relations involved must have the same number of attributes, and the corresponding attributes must have compatible data types. The result is a new relation containing all unique tuples from both input relations.

51
Q

What is the INTERSECT operator and what are its requirements and results?

A

It retrieves the common tuples that exist in both sets, eliminating duplicates. Like UNION, both input relations must have the same number attributes with compatible data types. The result is a new relation containing only the tuples that appear in both input relations.

52
Q

What is the EXCEPT operator and what are its requirements and results?

A

It retrieves tuples that are in the first set but not in the second set, eliminating duplicates. Similar to UNION and INTERSECT, the input relations must have the same number of attributes with compatible data types. The result is a new relation containing tuples that are present in the first input relation but not in the second.

53
Q

What are the steps of a natural join?

A

PRODUCT of tables are created, SELECT performed on the output, then PROJECT is performed.

54
Q

What are the differences/similarities between inner join and outer join?

A

It is not the opposite of an inner join, it can be thought of an outer join as an “inner join plus”.

55
Q

How do you implement a many-to-many (M:N) relationship in the relational environment?

A

M:N relationships can be implemented by created a new entity in 1:M relationships with the original entities. You can add a composite bridge table that has primary keys as foreign keys of both related tables as part of its primary key.

56
Q

What are Codd’s relationship data rules? (1)

A

1.) Information: All information in a relational database must be logically represented as column values in rows within tables.
2.) Guaranteed Access: Every value in a table is guaranteed to be accessible through a combination of table name, primary key value, and column name.
3.) Systematic Treatment of Nulls: Nulls must be presented and treated in a systematic way, independent of data type.
4.) Dynamic Online Catalogue: The metadata must be stored and managed i tables within the database; such data must be available to authorized users using the standard database relational language.
5.) Comprehensive Data: The relationship database can support many languages, however, it must support one well-defined declarative language as well as data definition, view definition, data manipulation, integrity constraints, authorization, and transaction management.

57
Q

What are Codd’s relationship data rules? (2)

A

6.) View Updating: Any view that is theoretically updatable must be updatable through the system.
7.) High-Level Insert: The database must support set-level inserts, updates, and deletes.
8.) Physical Data Independence: Application programs and ad hoc facilities are logically unaffected when physical access methods or storage structures are changed.
9.) Logical Data Independence: Application programs and ad hoc facilities are logically unaffected when changes are made to the table structures that preserve the original table values.
10.) Integrity Independence: All relational integrity constraints must be definable in the relational language and stored in the system catalog, not at the application level.

58
Q

What are Codd’s relationship data rules? (3)

A

11.) Distribution Independence: The end users and application programs are unaware of and unaffected by the data location (distributed vs. local databases).
12.) Nonsubversion: If the system supports low-level access to the data, users must not be allowed to bypass the integrity rules of the database.
13.) Rule Zero: All preceding rules are based on the notion that to be considered relational, a database must use its relational facilities exclusively for management.

59
Q

What is the difference between a database and a table?

A

A table, a logical structure that represents an entity set, is only one of the components of a database.

60
Q

What does it mean to say that a database displays both entity integrity and referential integrity.

A

Entity integrity describes a condition in which all tuples within a table are uniquely identified by their primary key. The unique value requirement prohibits a null primary key value, because nulls are not unique. Referential integrity describes a condition in which a foreign key value has a match in the corresponding table or in which the foreign key value is null. The null foreign key “value” makes it possible not to have a corresponding value, but the matching requirement on values that are not null makes it impossible to have an invalid value.

61
Q

What are the requirements that two relations must satisfy in order to be considered union-compatible?

A

In order for two relations to be union-compatible, they must have the same number of attributes (columns) and corresponding attributes (columns) must have the same domain.

62
Q

Which relational algebra operators can be applied to a pair of tables that are not union-compatible?

A

The PRODUCT, JOIN, and DIVIDE operators can be applied to a pair of tables that are not union-compatible. DIVIDE does not place specific requirements on the tables to be operated on, however, those requirements do not include union-compatibility. SELECT/RESTRICT and PROJECT are performed on individual tables, not pairs of tables.

63
Q

Why is the data dictionary sometimes called the database designer’s database?

A

Just as the database stores data that is of interest to the users, the data dictionary stores data that is of interest to the database designer.

64
Q

How do you translate the statement, “The file contains two hundred records, each record containing nine fields.” to relational database terminology?

A

“The table/entity set contains two hundred rows/tuples/entities, each of these rows contain nine attributes.”

65
Q

Compare composite and simple attributes and give an example for each.

A

Composite attributes are attributes that can be subdivided to yield additional attributes, for example an address can be a stress, city, state, and zip code. Simple attributes are attributes that cannot be subdivided, like age.

66
Q

Compare single-valued and simple attributes and give an example for each.

A

A simple attribute is an attribute that cannot be subdivided, like age. A single-valued attribute is an attribute that has only a single value, like a social security number.

67
Q

How would you implement multivalued attributes?

A

The better approach would be to develop a new entity composed of the original multivalued attribute’s component.

68
Q

What is a derived attribute?

A

An attribute whose value is calculated from other attributes and need not to be physically stored within the database.

69
Q

What are the advantages of a derived attribute?

A

Saves CPU processing cycles, saves data access time, data value is readily available, and can be used to keep track of historical data.

70
Q

What are the disadvantages of derived attributes?

A

Requires constant maintenance to ensure derived value is current, especially if any values used in the calculation change.

71
Q

How can be enforce the minimum and maximum number of entity occurrences expressed in a cardinality?

A

Cardinalities cannot be implemented directly through the database design that capability is provided by the application software or by triggers.

72
Q

Explain the differences between strong and weak relationships by giving an example.

A

Weak/non identifying relationship: Primary key of the related entity does not contain a primary key component of the parent entity. Strong/identifying relationships: Primary key of the related entity contains a primary key component of the parent entity.

73
Q

What are the conditions for a weak and strong (regular) entity?

A

The conditions of a weak entity are existence dependent, and has a primary key that is partially or totally derived from the parent entity in the relationship. A strong (regular) entity needs to be existence independence.

74
Q

Name and briefly explain 2 conflicting DB design goals.

A

Design standards, processing speed, and information requirements.

75
Q

Explain the inheritance rules in EER.

A

All entity subtypes inherit their primary key attribute from their supertype, entity subtypes inherit all relationships in which supertype entity participates, lower-level subtypes inherit all attributes and relationships from its upper-level supertypes (even if there is multiple levels of supertype and subtypes).

76
Q

What is total and partial completeness?

A

Partial completeness is when not every supertype occurrence is a member of a subtype. Total completeness is when every supertype occurrence must be a member of at least one subtype.

77
Q

What is the benefit of entity clustering?

A

Entity cluster is a “virtual” entity type used to represent multiple entities are relationships in ERD. It is formed by combining multiple interrelated entities into a single, abstract entity object. It is a temporary entity to simplify and enhance readability.

78
Q

Name 3 desirable characteristics of a primary key.

A

Non intelligent, no change over time, preferably single-attribute, preferably numeric, security-compliant.

79
Q

When should we use a surrogate key?

A

When a primary key doesn’t exist in the real world of the existing natural key might not be a suitable primary key. It is used to simplify the identification of entity instances, and is useful when there is no natural key, has no meaning in the user’s environment, and is helpful if the selected candidate key has embedded semantic contents or is too long.

80
Q

Explain a case where you need to use a composite primary key.

A

Identifiers of composite (bridge) entities, each primary key combination is allowed once in a M:N relationship. Identifiers of weak entities in a strong identifying relationship with the parent entity.

81
Q

What is a design trap?

A

Occurs when a relationship is improperly or incompletely identified, and is represented in a way that is not consistent with the real world.

82
Q

What is the most frequent design trap?

A

A fan trap, which occurs when one entity is in two 1:M relationships to other entities are produces an association among other entities not expressed in the model.

Database (1 decks)

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