database midterm Flashcards
(86 cards)
1
Q
Data
A
Raw Info
2
Q
Field
A
Group of info with meaning
3
Q
Record
A
One or more fields connected
4
Q
File
A
Collection of related records
5
Q
Data Redundancy
A
Same data is stored unnecessarily at different places
6
Q
Data Independence
A
Data access is unaffected by changes in the physical data storage
7
Q
Database Management System (DBMS)
A
A collection of programs that manages the database
8
Q
Main Components of a Database System
A
Hardware, Software, Data, People, Procedures
9
Q
Metadata
A
Data about data
10
Q
Common Problems of Spreadsheets
A
- Lengthy Development Times
- Difficulty getting quick answers
- Complex system admin
- Lack of security and data sharing
11
Q
Importance of Data Modelling
A
Creates a sound data structure that real-world operations can depend on
12
Q
Business Rule
A
A brief and precise description of a policy or procedure within an organization
13
Q
Features of the Relational Data Model
A
- Used with Query Language and RDBMS
- Lower data redundancy
14
Q
Entity Relational Data Model (ERDM)
A
A simple, graphic database structure model for people to interpret
15
Q
Three Types of Relationships
A
- 1 to 1; 1:1
- 1 to Many; 1:M
- Many to Many: M:N
16
Q
Table
A
A 2-D structure composed of intersecting rows and columns
17
Q
Big Data Phenomenon
A
A movement to find new and better ways to manage large amounts of data while providing high performance and scalability at a reasonable cost
18
Q
3 V’s
A
Volume: amount of data sored
Velocity: speeds at which data grows and processed to get info
Variety: data can be collected in different data format
19
Q
Sparse Data
A
Data with nulls or empty files
20
Q
Logical Independence
A
Changing the internal model without affecting the model
21
Q
Physical Independence
A
Physical model can be changed without affecting the internal model
22
Q
Entity Integrity
A
Each row in a table has its own unique identity primary key
23
Q
Referential Integrity
A
Every reference to an entity instance by another entity instance is valid
24
Q
Requirements to be Union-Compatible
A
Both relations must have the same amount of attributes and the corresponding columns must share the same domain
25
Natural Join
Link tables by selecting only rows with common values in their common attribute
26
Equijoin
Links tables on the basis of an equality condition that compares specified columns of each table
27
Outer Join
Matched pairs are retained. Any unmatched values in the other table would be left null
28
Avoid Homonyms and Synonyms
Homonyms use the same name as the attributes
| Synonyms indicate the sue of a different name to describe the same attribute
29
Two Conditions Met to be Classified as a Weak Entity
- Entity must show the existence dependence
| - Entity must intake the primary key of its parent entity
30
Cardinality
Minimal and maximum number of an entity. Useful for setting limits.
31
Derived Attributes
An attribute calculated from existing values
32
Existence Dependence
An entity can only exist when associated with another entity
33
Relationship Degree
Specifies the number of different and unique entities associated with a relationship in a database
34
Binary Relationship
Two entity association
35
Unary Relationship
Associated with its own single entity
36
Ternary Relationship
Three entity association
37
Composite Attribute
Attributes that can be subdivided to provide additional information and new attributes
38
Simple Attributes
Attributes that can not be subdivided
39
Weak Relationship
Foreign key is not a primary key
40
Strong Relationship
Related entity has a primary key component of parent key
41
Primary Key
An important key that uniquely identifies a row
| a table can have only one primary key
42
Candidate Key
Multiple keys that can act as primary key
| a table can have multiple candidate keys
43
Alternate Key
Candidate keys other than a primary key
44
Super Key
Candidate keys added to the primary key
45
Composite Key
A key that consists of more than one attribute to uniquely identify rows in a table
46
Foreign Key
A column of a table which points to the primary key of another table. Act as a cross reference between tables
47
Recursive Relationship
A relationship between two entities that are similar entity sets of occurrences
48
Normalization
Process of assigning attributes to entities to reduce data anomalies by eliminating data redundancies
49
1NF
Table must contain no multivalued attributes
50
2NF
Contain no partial dependencies
51
3NF
Contain no transitive dependencies
52
Partial Dependency
Attribute that is part of the primary key that can determine another attribute
53
Three Data Anomalies
Data structures that can Update, Insert, or Delete
54
Transitive Dependency
Two non-key attributes are dependencies and one of the keys is partially dependent to the primary key
55
Surrogate Key
Is an identifier that uniquely identifies an entity in a database that does not thrive on obtaining data. Used to connect different tables together
56
Denormalization
Combines tables together to improve the performance of data at a cost of more data redundancy
57
Flags
Special code to indicate the absence of value
58
Relvar
Variable that holds a relation
59
Inner Join
Only returns matched records from the tables that are being joined
60
Theta Join
Links tables using an inequality comparison operator
61
Select
Unary operator that yields a horizontal subset of a table
62
Project
Unary operator that yields a vertical subset of a table
63
Union
Combines all rows from two tables, excluding duplicate rows, must be union-compatible
64
Intersect
Yields only the rows that appear in both tables, must be union-compatible
65
Difference
Yields all rows in one table that are not found in the other table, must be union-compatible
66
Product
Yields all possible pairs of rows from two tables
67
Divide
Uses one double-column table as the dividend and one single-column table as the divisor.
Output is a single column that contains all values from the second column of the dividend that is associated with every row in the divisor.
68
Data Dictionary
Description of all tables in the database created by the user and designer
69
System Catalog
System data dictionary that describes all objects within the database
70
Index Key
Index's reference point that leads to data location identified by the key
71
Unique Index
Index key can have only one pointer value associated with it
72
NoSQL
| Advantages and Disadvantages
Advantages
- High scalability, availability
- Low-cost hardware
- Supports Big Data
- Improves storage efficiency
Disadvantages
- Complex programming
- No relationship support
- No transaction integrity support
73
Object-Oriented Model
| Advantages and Disadvantages
Advantages
- Semantic content is added
- Visual representation
- Inheritance promotes data integrity
Disadvantages
- Slow development of standards
- Complex navigational system
- Learning curve is steep
74
Entity Relationship Model
| Advantages and Disadvantages
Advantages
- Visual modeling yields conceptual simplicity
- Visual representation makes it an effective communication tool
- Integrated with the dominant relational model
Disadvantages
- Limited constraint representation
- Limited relationship representation
- No data manipulation language
- Loss of info content occurs with attributes are removed from entities
75
Relational Model
| Advantages and Disadvantages
Advantages
- Structural independence is promoted using independent tables
- Tabular view improves the simplicity
- Ad hoc query capability
- Isolates the end-user from physical-level details
Disadvantages
- Requires substantial hardware and system software
- May promote information problems
76
Network Model
| Advantages and Disadvantages
Advantages
- Conceptual simplicity
- Handles more relationship types
- Data access is flexible
- Includes data definition language and data manipulation language
Disadvantages
- System complexity limits efficiency
- Navigational system yields complex implementation
- Structural changes require changes in all application programs
77
Hierarchical Model
Advantages
- Promotes data sharing
- Parent/child relationship promotes conceptual simplicity
- Database security
- Efficient with 1:M relationships
Disadvantages
- Requires knowledge of physical data storage characteristics
- Navigational system requires knowledge of the hierarchical path
- No data definition
- Changes in the structure requires changes in all application programs
78
Extended Relational Data Model (ERDM)
Supports OO features, extensible data types based on classes, and inheritance
79
Extensible Markup Language (XML)
Manages unstructured data for efficient and effective exchange of data
80
Unified Modeling Language (UML)
Describes sets of diagrams and symbols to graphically model a system
81
Class
Collection of similar objects with shared structure and behaviour organized in a class hierarchy
82
Class Hierarchy
Resembles an upside-down tree in which each class has only one parent
83
Inheritance
Object inherits methods and attributes of classes above it
84
4NF
No independent multivalued dependencies
85
Delete Anomaly
When you delete an entity which can unintentionally delete other entities which you don't want
86
Business Rule
Serve communication tool between the users and designers
