Brainscape's Knowledge GenomeTM

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


See full index

Database > Fundamentals > Flashcards

Fundamentals Flashcards

1
Q

Database

A

Structured set of data that is accessible in many ways

A collection of tables and their relationships

(Data + rules on data)

(can run multiple queries without affecting the data structure)

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

Data Anomalies

A

Data management problems resulting from poor data structure

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

Insert Anomaly

A

Error prohibiting insertion

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

Update Anomaly

A

Error from updates OR when multiple updates are required

Risk of missing some items

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

Delete Anomaly

A

Deletes other info

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

Solving poor database structure

A

Separate data into different tables BUT keep relationships the same

Adding a redundant column removes anomalies (only in relational database)

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

E. F Codd

A

A relational Model of Data for Large Shared Data Banks

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

Donald Chamberlin & Raymond BOyce

A

SEQUEL /SQL (Structured English Query Language)

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

Peter Chen

A

The entity-relationship model-toward a unified view of data

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

Larry Ellison

A

Oracle

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

Jim Gray

A

The Transaction Concept Virtues and Limitations

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

Database Management System

A

A software package which allows to define, store, use and maintain a database

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

Database System

A

Database Management System (DBMS) + Database

Hardware + Software (OS, DBMS, Application programs and utility) +
People +
Procedures +
Data

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

Problems with file approach to data management

A
  • Redundant data => waste of storage space
  • Inconsistent data
  • Strong dependency between applications and data
  • Concurrent actions lead to inconsistent state of data
  • Difficult to integrate various applications
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Types of Data

A

Raw & Metadata

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

Metadata

A

Data descriptions

Stored in data dictionary in DBMS

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

Database vs File Approach

A

DBMS manages both and metadata in catalog

Application-data independence

View (data subset) can be defined

Concurrency control to support multiple users

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

File Approach

A

File only contains data; metadata stored in applications

Application - structural data dependence (need to code to retrieve new information)

No views possible (unless duplication)

No multi user support

Lengthy development times

Complex system administration

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

Data model

A

A clear description of the data concepts, their relationships and various data constraints that together make up the content of the database

Should provide a formal and perfect mapping of the real world

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

Conceptual Data Model

A

High - level data concepts, close to how the business user perceives the data

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

Logical Data Model

A

Concepts that may be understood by business users but are not too far removed from physical data organisation

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

Physical Data Model

A

Low- level concepts that describe the datas physical storage details

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

Database schema

A

Conceptual organisation of the entire database as view by the database administrator

Specified during database design

Not expected to change frequently

Stored in the catalog

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

Database state

A

The data in the database at a particular moment (set of instances)

Changes on ongoing basis

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

The three - schema architecture

A

External Schema

Conceptual Schema

Internal Schema

Changes in one layer should have minimal impact on the others

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

External Schema

A

Describes the part of the database that a particular user group is interested in and hides the rest of the database from that user group

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

Conceptual Schema

A

Specifies data concepts, characteristics, relationships, integrity rules and behaviour

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

Internal Schema

A

Specifies how the data are stored/ organised physically (e.g. indexes, access paths)

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

Benefits Three-Schema Architecture

A
  1. Efficiency (size)
  2. Maintainability (ease of updating, reduces redundancy)
  3. Performance (importance of data)
  4. Security (sensitive information, authorisation)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

Data Dictionary / Catalog

A

Heart of DBMS

System database with metadata

Contains definitions for external schema, conceptual schema and physical schema

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

Database Designer

A

Designs conceptual schema

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

Database administrator

A

Designs external and physical shcema

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

Application Developer

A

Develops database applications in a programming language

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

Business user

A

Makes use of data stored in database via applications (makes queries on the database)

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

DBMS vendors

A

Companies selling DBMS i.e. Oracle, Microsoft

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

Data Definition Language (DDL)

A

Language used by the database administrator (DBA) to define the database’s logical, internal and external schemas

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

Data Manipulation Language (DML)

A

Language used to retrieve, insert, delete and modify data

DML statements can be embedded in a general-purpose programming language or entered interactively through a front-end querying tool

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

Relational Database Systems (RDBMS)

A

Stores data in tables so it can be used in relation to other stored databases

SQL is both a DML & DBA in RDBMS

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

Data and functional independance

A

Changes in data definitions have minimal to no impact on applications

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

Physical (computer) Data Independance

A

Neither applications nor external or conceptual schema must be changed when changes are made to data storage specifications i.e. different storage locations

DBMS provides interfaces between the conceptual and physical data models

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

Logical (human) Data Independance

A

Software applications are minimally impacted by changes in the conceptual schema

i.e. adding new data concepts, characteristics, relationships

DBMS provides interfaces between conceptual and external schema

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

Function

A

Interface (signature) : name of the function and its arguments

43
Q

Method

A

Implementation: specifies how the function should be executed

44
Q

Functional Independance

A

Implementation (method) can change without impact on software applications

45
Q

Data Redundancy

A

When the same data is stored unnecessarily at different places
May be desired -> increase performance in distributed environments

DBMS manages redundancy (updates all consistently = less error prone; no user intervention)

  • > synchronization
  • > consistency
46
Q

Data Integrity Rules

A

Enforce correctness of data

Embedded in applications (in file approach)

Specified as part of conceptual schema and stored in catalog (database approach)

47
Q

Syntactical Rules (syntax)

A

Specify how data is represented and stored i.e.

Customer ID should be numeric

Birthdate should be DD/MM/YYY

48
Q

Semantical Rules

A

Focuses on the meaning of the data i.e.

Account balance should be >0
Customer ID should be unique

49
Q

When are integrity rules enforced?

A

Whenever anything is updated (database)

When applications are accessing files (file based approach)

50
Q

Concurrency Control

A

Concurrent execution of database programs allows for good performance

51
Q

DBMS ACID properties

A

Ensures database transaction are processed in a reliable way (must be done together)

ATOMIC

CONSISTENT

ISOLATED

DURABLE

52
Q

Data Security

A

Authority -> read access vs write access

Accessibility -> whole database or certain parts

Logins and passwords assigned to users (rules stored in catalog)

53
Q

Backups Abilities

A

In case of loss of data due to errors (hardware/network/ bugs)

Full or incremental backups

Allows restoration of data after loss or damage

54
Q

Performance Utilities

A

Distributing data storage

Tuning indices (faster queries)

Tuning queries (improve application performance)

Optimizing buffer management

55
Q

Buffer manager

A

Software layer that is responsible for brining pages from physical disk to main memory as needed

Divides main memory into a collection of pages known as buffer pool

56
Q

Data Management Importance

A

Data management should be

  1. Scalable
  2. Accessible
  3. Accurate
  4. Secure
  5. Consistent
  6. Permanent
57
Q

Purpose of a database

A

Provides structure to datasets

58
Q

Table

A

Describes a formalized repeating list of data

Consists of rows and columns

59
Q

Unique Key

A

Allows the identification of a particular row in a table

A table can have more than one unique key

Can accept only one NULL value for a column

60
Q

Synthetic Key / Surrogate Key

A

DBMS generated primary key

61
Q

Primary Key

A

A column of table which uniquely identifies each role in a table

Only one is allowed in a table

No duplicate or NULL values allowed

Allows connection of different tables in a database

62
Q

Foreign Key

A

A column or group of columns in a relational database table that provides a link between data in two tables

It is a column(s) that references a column (most often a primary key) of another table

63
Q

Junction or Linking Table

A

Maps two or more tables together by referencing the primary keys of each data table

Used to establish many-to-many relationships

64
Q

Transactions

A

A unit of work

Happens in full or not at all

65
Q

Acid: Atomic

A

Execute a transaction ->all or nothing property

If transaction fails, changes reverts back to original state

66
Q

Acid: Consistency

A

Transaction must take the database from one consistent state to another

67
Q

Acid : Isolation

A

Data and the transaction isolated when transaction occurs and until it is completed

68
Q

Acid : Durability

A

Robust transaction

69
Q

SQL

A

A declarative query language, not a procedural or imperative language

Describe what you want, no need to specify algorithms to retrieve data

DO CRUD

70
Q

CRUD

A

Create
Read
Update
Delete

71
Q

Knowledge

A

The body of information and facts about a specific subject.

Implies understanding and insight of the information as it applies to an environment (given context)

72
Q

Data Management

A

A process that focuses on data collection, storage and retrieval (CRUD)

73
Q

Advantages of DBMS

A

Improved data sharing

Improved data security (better enforcement of data security & privacy)

Better data intergration

Minimized data inconsistency

Improved data access

Improved decision making

Increased end-user productivity

74
Q

Query

A

A question or task asked by a end user of a database in the form of SQL code

75
Q

Ad-hoc Query

A

A spur of he moment question

76
Q

Data inconsistency

A

A condition in which different version of the same data yield different results

77
Q

Database design

A

The process that yields the description of the database structure

78
Q

DBMS Function

A
  1. Data dictionary management
  2. Data storage management
  3. Data transformation and presentation
  4. Multiuser access control
  5. Backup and recovery management
  6. Data Integrity management

7, Database access languages and application programming interfaces

  1. Database communication interfaces
79
Q

Entity

A

A place, person, thing, concept or event for which data can be stored

80
Q

Attribute

A

Characteristic of an entity (aka column -> file approach)

81
Q

Relationship

A

Association among entities

82
Q

Constraint

A

A restriction placed on the data

83
Q

Database subschema

A

Portion of the database “seen” by the application programs

84
Q

Data Manipulation Language

A

Defines the environment in which data can be managed and is used to work with the data in the database

85
Q

Data definition Language

A

Enables the database administrator to define the schema components

86
Q

Tuple

A

A table row in a relation (aka row in file approach)

87
Q

Why Databases

A

Data is pervasive
Data is ubiquitous

Databases allow storing and sharing of data in an easy and secure way

88
Q

Islands of Information

A

Data in scattered data locations

89
Q

Data Integrity

A

A condition in which the data in the database complies with all entity and referential integrity constraints

90
Q

Relational Data Model

A

Allows designer to foccs on logical representation of the data and its relationships rather than on physical storage details

91
Q

Composite Key

A

Key that is composed of more than one attribute

92
Q

Key Attribute

A

An attribute that is part of a key

93
Q

Candidate Key

A

A minimal superkey, a key that does not contain a subset of attributes

A Candidate Key can be any column or a combination of columns that can qualify as unique key in database.

Full functional dependency

a primary key chose can be a candidate key

94
Q

Entity integrity

A

Condition in which each row in the table has its own unique identity

Primary key must contain unique values
No key attribute in the primary key can contain null

95
Q

Null

A

Absence of any data or value

96
Q

Secondary key

A

A key used strictly for data retrieval purposes

97
Q

Flags

A

Special codes implemented by designers to trigger a required response

98
Q

Superkey

A

An attribute or attributes that uniquely identify each entity in a table

can uniquely identity each attribute in the row

99
Q

Select / Restrict

A

Unary operator

Yields values for all rows found in the table that satisfy a given condition
horizontal

100
Q

Project

A

All values for selected attributes in the order which they are requested (vertical)

101
Q

Intersect

A

Yields rows that appear in both tables

Table must be union compatible i.e. numeric and numeric

102
Q

Difference

A

Yields all rows in one table that are not found in the other table (substracts one table from another)

103
Q

Natural join

A

Links tables by selecting only the rows with common values in their common attributes

Database (3 decks)

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