Brainscape's Knowledge GenomeTM

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


See full index

Computer Science (Year 2) > Database Theory and Design > Flashcards

Database Theory and Design Flashcards

You may prefer our related Brainscape-certified flashcards:
Biology 101 flashcards
1
Q

Define a Database (DB)

A

An organized collection of related data

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

Define a Database Management System (DBMS)

A

Software that manages and controls access to the database

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

Define Database application

A

Program that interacts with the database at some point in its execution

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

Define a Database System

A

A collection of programs that interact with the database

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

What are two ways of storing and managing data?

A
  1. File-based System
  2. Database System
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Define a File-based System

A

A collection of application programs that perform services for the end-users. Each program defines and manages its own data.

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

Define a Database System

A

A shared collection of logically related data and its description, designed to meet the information needs of an organization

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

What are the advantages of a File-based approach?

A

User Control
- End user has total control of the stored data
- Can modify application or data at will

Applications and data file can be optimised for particular tasks

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

What are the disadvantages of a File-based approach?

A
  • Data duplication
  • Data dependence
  • Incompatible file formats
  • Proliferation of application programs (APPS)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What is Data Duplication

A

If a record is duplicated in each of the 3 files, a change to the record requires a change to all 3 files.

Integrity of student records may be compromised if the files are not all updated - which file holds the correct information.

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

What is Data Dependence

A

The way data is stored affects how it can be used, making the data and the program tightly connected

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

What are imcompatible file formats

A

If application programs are separatly implemented to data files, then the data files may be incompatible with the application program

Meaning the data files would have to be translated to fit the application program

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

What is the Proliferation of application prgorams?

A

If you wanted more information out of data files than initially anticipated

You would have to add more application programs to handle new queries

Leading to the proliferation of files and application programs that each official has to handle

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

How does a database approach address the limitation of an application program?

A

The data is stored in a DB, which is then accessed by DBMS.

When officials need info from the DB they write queries in SQL to communicate with the DBMS which in turn gives them what they need

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

What is a DBMS

A

Software that interacts with users’ application programs and the DB

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

Define a Database Management System (DBMS)

A

Software system that enables the user to define, create, maintain and control access to the database

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

What does an End-user do?

A

Run applications to perform specific database operations

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

What does an Application Developer do?

A

Use a programming language to provide the required functionality for the end users

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

What does a Database Designer do?

A

Identify the data, relationships between data, constraints on the data

Map the logical design into a set of tables & integrity constraints, select straoge structures & access methods

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

What does a Database Administrator (DBA) do?

A

Responsible for implementation and monitoring of the database

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

What are the 13 advantages of a DBMS?

A
  • Control of data redundancy
  • Data Consistency
  • Sharing of data
  • Improved data integrity
  • Improved Security
  • Enforcement of Standards
  • Economy of Scale
  • Balance of conflicting requirements
  • Improved data accessibility and responsiveness
  • Increased Productivity
  • Improved maintenance through data independence
  • Increased concurrency
  • Backup and Recovery services
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What is Control of Data Redundancy?

A

Since all data is now stored in a single database, there are no unnecessary multiple copies of data

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

What is Data Consistency?

A

Only one copy of each data item

(changes only have to be made to 1 item, therefore if there was multiple and they werent all changed data wouldnt be consistent)

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

What is Sharing of data?

A

Since data is common to the organization, it’s easy for all authorized users to have access to the same data

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

What is improved data integrity?

A

Organizational data is soted in one place, it’s easy to enforce consistency rules that shouldn’t be violated

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

What is improved security?

A

Database administrator can easily put in place security and access measures by simply enfrocing these using the DBMS

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

What is Enforcement of Standards?

A

Since all data is in the same database, its easy to enforce data standards such as:
- Data formats
- Naming conventions
- Documentation standards
- Update procedures
- Access rules

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

What is Economy of Scale?

A

Combining an entire organization’s data into one database and creating a set of applications that wokr on this one source results in saving costs

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

What is the Balancing of conflicting requirements?

A

Since the DBA has an organizational-wide view of all operations, they can optimize the design and operational use of the database to provide the best use of resources as a whole as opposed to satisfying one department at the expense of another

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

What is improved data accessibility and responsiveness?

A

Common database provides easy data access to all authorized people within orgranization

Also, the DBMS provides SQL to enable end-users to easily construct ad hoc queries without detailed programming knowledge

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

What is increased productivity?

A

The DBMS provides all low level file handling routines that are typically written in application programs
They also typically provide SQL enabling users to develop queries themselves
Altogether, this reduces programming effort

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

What is improved maintenance through data independence?

A

A DBMS separates data management from application programs
- so if changes are made to underyling data structure, the DBMS doesnt have to change application programs.

The DBMS will provide the necessary mapping between application programa and data stored on the database

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

What is increased concurrency?

A

Many DBMSs allow users to access the database simultanoeusly without any conflict

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

What are Backup and recovery services?

A

DBMS have facilities to enable data recovery following a failure

They can slo automatically backup the data stored in the database

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

What are the disadvantages of a DBMS?

A
  • Complex: require those using them to fully understand them
  • Size: Large piece of software that requires alot of memory to run
  • Cost of DBMS: Cost varies depending on if its single user or a big
    organization. Maintenance and operations costs also need to be
    factored.
  • Cost of conversion: Data conversion costs as well as training and
    recruiting costs
  • Performance: Written for general use, so some applications aren’t
    as fast
  • Greater impact of failure: Centralization increases system vulnerability. Failure may result in everyone being affected in an organization
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

What are the 3 levels in Three-level Architecture?

A

External Level
- The way users percieve data

Conceptual Level
- Provide both the mapping and desired independence

Internal Level
- The way DBMS and the OS perceive the data

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

What are the two objectives of Three-level Architecture?

A
  • Data Abstraction
  • Data Independence
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

What is Data Abstraction?

A

Hide storage details and present the usres with a conceptual view of the database

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

What is Data Independence?

A

The capacity that upper levels are unaffected by changes to lower level

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

What is Logical Data Independence?

A

Change conceptual schema without having to change external schemas and their application programs

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

What is Physical Data Independence?

A

Change internal schema without having to change conceptual schema

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

What is a Database schema?

A

The description of the structure of the database

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

What is a Database state?

A

The content of a DB at a moment in time

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

What is a Data model?

A

A set of concepts to describe
- Data and relationships among data
- Data constraints

45
Q

What are the 3 categories of Data model?

A

Conceptual data model
Logical data model
Physical data model

46
Q

What is a Conceptual data model?

A

Identifies the high-level data structure

(Independent of DBMS, application prgorams, physical considerations)

47
Q

What is a Logical data model?

A

Describes the data in terms of data structures

Independent of a particular DBMS product and storage technology

48
Q

What are examples of Logical data models?

A

Hierarchical

Network

Relational

49
Q

What is a Hierarchical data model?

A

Data is organized in a tree-like structure

Each node has 1 parent

50
Q

What is a Network data model?

A

Data is organized as a graph

One node can have more than one parent node

51
Q

What is a Relational data model

A

Data is stored in tables. Each table, called a relation, consists of rows and columns, much like a spreadsheet

52
Q

What is a Physical data model?

A

Describe how data is stored in the computer, representing record structures, record openings, and access apths

Highly dependent on target DBMS

53
Q

What is Motivation in Databases?

A

One of the most difficult aspect of database design is that designers, programmers and end-users tend to view data in different ways

Need a model for communication that is non-technical and free of ambiguities

54
Q

What are the 3 main notations used for an ER Model?

A
  1. Chen Notation
  2. Crow’s feet Notation
  3. UML Notation
55
Q

What does an ER Model consist of?

A
  • Entity
  • Relationship
  • Attribute
  • Constraint
56
Q

Define an Entity.

A

A group of objects with the same properties

57
Q

Define a Relationship.

A

Meaningful associations among two or more entities

58
Q

Define a Degree of Relationship.

A

The number of participating entity types in a relationship

59
Q

What are 4 Degree of Relationship types?

A
  • Degree one: recursive
  • Degree two: binary
  • Degree three: ternary
  • Degree four: quaternary
60
Q

What is a Multiple Relationship?

A

Two entities are associated through more than one relationship

61
Q

Define an Attribute.

A

A property of an entity or a relationship

62
Q

Define a Candidate Key

A

The minimal number of attribute(s) whose value(s) uniquely identify each entity occurrence
- A candidate key cannot contain a null

63
Q

Define a Primary Key

A

The candidate key that is selected to uniquely identify each occurrence of an entity type

64
Q

What are the principles of choosing a Primary key?

A
  • Attribute length
  • Minimal number of attributes required
  • Future certainty of uniqueness
65
Q

Do relationships have candidate keys?

A

NO

66
Q

What is a Weak Entity Type?

A

Existence-dependent on some other entity
- Each entity occurrence cannot be uniquely identified using only the attributes assoicated with that entity type

67
Q

Define Multiplicity

A

The number of possible occurrences of an entity type that may relate to a single occurrence of an associated entity type through a particular relationship

68
Q

What are the 3 relationship types?

A
  1. One-to-one (1:1)
  2. One-to-many(1 : *)
  3. Many-to-many (* : *)
69
Q

What is a one-to-one relationship?

A

Each record in one table is linked to exactly one record in another table.

70
Q

What is a one-to-many relationship?

A

A record in one table can be linked to multiple records in another table.

71
Q

What is a many-to-many relationship?

A

Multiple records in one table can be linked to multiple records in another table.

72
Q

What is Multiplicity for complex relationships?

A

It’s a way of showing the β€œhow many” in a relationship between entities

73
Q

What is Cardinality?

A

The maximum values for the multiplicity ranges on either side of the relationship

74
Q

What is Participation?

A

The minimum values for the multiplicity ranges on either side of the relationship

75
Q

What is the difference between Optional and Mandatory Participation?

A

Optional (if 0)
Mandatory (if 1 or more)

76
Q

What is a fan trap?

A

A type of database design flaw that occurs in an entity-relationship (ER) model when two one-to-many relationships connect in a way that causes incorrect data interpretation.

77
Q

Define Specialisation

A

The process of maximizing the differences between members of an entity by identifying their distinguishing characteristics

78
Q

Define Generalisation

A

The process of minimizing the differences between entities by identifying their common characteristics

79
Q

What are the participation constraints?

A
  • Optional
  • Mandatory
80
Q

What are the disjoint constraints?

A
  • And (nondisjoint)
  • Or (disjoint)
81
Q

Define Aggregation

A

An abstraction through which relationships are treated as higher-level entities

82
Q

Define a Relation

A

A two-dimensional table that has specific characteristics

83
Q

What are 4 terminologies for describing a table?

A

Attribute - A column that defines a specific piece of information about each record
(row). Each attribute represents a data field

Tuple - A row that represents a single record or entry. Each tuple contains specific
data for all the attributes (columns) in the table.

Degree - number of attributes (columns) it has

Cardinality - number of tuples (rows) it contains

84
Q

What is a Candidate Key?

A

The minimal set of attributes whose values uniquely identify each tuple

85
Q

What is a Foreign Key?

A

An attribute, or set of attributes, within one relation that matches the primary key of another relation

86
Q

List the key properties of Relations

A

A relation has a name that is distinct from all other relation names in the database
Each cell of relation contains exactly one atomic (single) value
Each attribute has a distinct name
The values of an attribute are all from the same domain
Each tuple is distinct - there are no duplicate tuples
Order of attributes and tuples has no significance

87
Q

Define Relation Schema

A

A named relation defined by the relation name (table name) and attributes

88
Q

Define Relation Instance/State

A

A set of tuples from a relation schema

89
Q

Define Entity Intregrity

A

Uniqueness : No two tuples can have identical values for candidate keys
Not-Null: No attribute of a candidate key can be a NULL
- Null: A value of an attribute is currently unknown or not applicable for this tuple

90
Q

What is Referential Intregrity

A

If a foreign key exists in a relation
- Either the foreign key value matches a primary ke y value in its home relation
- Or the foreign key is NULL

91
Q

Define a Domain

A

Set of allowable values for one or more attributes

92
Q

What are Domain Constraints?

A

Enables RDBMS to carry out checks on data errors and to determine the range of operations that can be carried out on the domain

93
Q

Define a View

A

A subset of base relations

94
Q

Define a Base Relation

A

A named relation corresponding to an entity in the conceptual schema, whose tuples are phyiscally stored in the database

95
Q

What are Features of Views?

A

Virtual/derived relation. It does not necessarily exist in the database, but can be produced upon request.

Dynamic. The changes made to the base relation(s) that affect the view are immediately reflected in the view.

Generated by applying appropriate relational operations (e.g., 𝜎, πœ‹, x, β‹ˆ, ….)

96
Q

What are Strong Entity Relation Schemas?

A

Create a relation that includes all the simple attributes of that entity, with primary key specified
- For composite attributes, include only the constituent simple attributes

97
Q

Explain a One-to-Many Binary Relationship

A

One record in a table is related to many records in another table
- The many side (child entity) receive the foreign key

98
Q

Explain a One-to-One Binary Relationship with mandatory participation on both sides

A

Combine the two entities to form one relation
The primary key of any one of the two relations is chosen to be the priamry key, whilst the other becomes the alternate key

99
Q

Explain a One-to-One Binary Relationship with mandatory participation on one side

A

The entity with mandatory participation receives the foreign key

100
Q

Explain a One-to-One Binary Relationship with optional participation on both sides

A

Foreign key in either side is fine

101
Q

Explain a Many-to-Many Binary Relationship

A

Create a new relation as an associative entity
Primary key: both of these primary keys of its associate entities, may add other attributes

101
Q

Explain a Recursive Relationship

A

Same rules as binary relationships
- (1:1), (1:) recursive relationships are implemented by foreign keys
- (:*) recursive relationships are implemented by creating a new relation

102
Q

Explain a Complex Relationship

A

Create a new relation, include its own attributes
Then post a copy of the primary keys of the participating entities into the new relation to act as foreign keys
Primary key: Any foreign keys represent the β€œmany”-side form the primary keys, add other attributes

103
Q

In a Superclass/Subclass Relationship what can you do?

A
  1. Only keep the subclasses
  2. Only keep the superclass
  3. Keep both
104
Q

Explain a Weak Entity 1:1 relationship

A

In a weak entity relationship, a 1:1 relationship means that each instance of the weak entity is associated with exactly one instance of the strong entity and vice versa.

Key Points:
Weak Entity: Cannot exist without a strong entity; it relies on it for its identification.
Strong Entity: Has its own primary key.

105
Q

Explain a Weak Entity 1:* relationship

A

In a weak entity relationship, a 1:* relationship means that each instance of the weak entity is associated with one instance of the strong entity, but one instance of the strong entity can be associated with multiple instances of the weak entity.

Key Points:
Weak Entity: Cannot exist independently; relies on the strong entity for identification.
Strong Entity: Has its own primary key.

106
Q

Define a Multi-valued attribute

A

An attribute that can have more than one value. For example, a person can have multiple phone numbers.

107
Q
A

Computer Science (Year 2) (4 decks)

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