Fundamentals Flashcards
(103 cards)
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)
2
Q
Data Anomalies
A
Data management problems resulting from poor data structure
3
Q
Insert Anomaly
A
Error prohibiting insertion
4
Q
Update Anomaly
A
Error from updates OR when multiple updates are required
Risk of missing some items
5
Q
Delete Anomaly
A
Deletes other info
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)
7
Q
E. F Codd
A
A relational Model of Data for Large Shared Data Banks
8
Q
Donald Chamberlin & Raymond BOyce
A
SEQUEL /SQL (Structured English Query Language)
9
Q
Peter Chen
A
The entity-relationship model-toward a unified view of data
10
Q
Larry Ellison
A
Oracle
11
Q
Jim Gray
A
The Transaction Concept Virtues and Limitations
12
Q
Database Management System
A
A software package which allows to define, store, use and maintain a database
13
Q
Database System
A
Database Management System (DBMS) + Database
Hardware + Software (OS, DBMS, Application programs and utility) +
People +
Procedures +
Data
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
15
Q
Types of Data
A
Raw & Metadata
16
Q
Metadata
A
Data descriptions
Stored in data dictionary in DBMS
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
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
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
20
Q
Conceptual Data Model
A
High - level data concepts, close to how the business user perceives the data
21
Q
Logical Data Model
A
Concepts that may be understood by business users but are not too far removed from physical data organisation
22
Q
Physical Data Model
A
Low- level concepts that describe the datas physical storage details
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
24
Q
Database state
A
The data in the database at a particular moment (set of instances)
Changes on ongoing basis
25
The three - schema architecture
External Schema
Conceptual Schema
Internal Schema
Changes in one layer should have minimal impact on the others
26
External Schema
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
27
Conceptual Schema
Specifies data concepts, characteristics, relationships, integrity rules and behaviour
28
Internal Schema
Specifies how the data are stored/ organised physically (e.g. indexes, access paths)
29
Benefits Three-Schema Architecture
1. Efficiency (size)
2. Maintainability (ease of updating, reduces redundancy)
3. Performance (importance of data)
4. Security (sensitive information, authorisation)
30
Data Dictionary / Catalog
Heart of DBMS
System database with metadata
Contains definitions for external schema, conceptual schema and physical schema
31
Database Designer
Designs conceptual schema
32
Database administrator
Designs external and physical shcema
33
Application Developer
Develops database applications in a programming language
34
Business user
Makes use of data stored in database via applications (makes queries on the database)
35
DBMS vendors
Companies selling DBMS i.e. Oracle, Microsoft
36
Data Definition Language (DDL)
Language used by the database administrator (DBA) to define the database's logical, internal and external schemas
37
Data Manipulation Language (DML)
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
38
Relational Database Systems (RDBMS)
Stores data in tables so it can be used in relation to other stored databases
SQL is both a DML & DBA in RDBMS
39
Data and functional independance
Changes in data definitions have minimal to no impact on applications
40
Physical (computer) Data Independance
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
41
Logical (human) Data Independance
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
42
Function
Interface (signature) : name of the function and its arguments
43
Method
Implementation: specifies how the function should be executed
44
Functional Independance
Implementation (method) can change without impact on software applications
45
Data Redundancy
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
Data Integrity Rules
Enforce correctness of data
Embedded in applications (in file approach)
Specified as part of conceptual schema and stored in catalog (database approach)
47
Syntactical Rules (syntax)
Specify how data is represented and stored i.e.
Customer ID should be numeric
Birthdate should be DD/MM/YYY
48
Semantical Rules
Focuses on the meaning of the data i.e.
Account balance should be >0
Customer ID should be unique
49
When are integrity rules enforced?
Whenever anything is updated (database)
When applications are accessing files (file based approach)
50
Concurrency Control
Concurrent execution of database programs allows for good performance
51
DBMS ACID properties
Ensures database transaction are processed in a reliable way (must be done together)
ATOMIC
CONSISTENT
ISOLATED
DURABLE
52
Data Security
Authority -> read access vs write access
Accessibility -> whole database or certain parts
Logins and passwords assigned to users (rules stored in catalog)
53
Backups Abilities
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
Performance Utilities
Distributing data storage
Tuning indices (faster queries)
Tuning queries (improve application performance)
Optimizing buffer management
55
Buffer manager
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
Data Management Importance
Data management should be
1. Scalable
2. Accessible
3. Accurate
4. Secure
5. Consistent
6. Permanent
57
Purpose of a database
Provides structure to datasets
58
Table
Describes a formalized repeating list of data
Consists of rows and columns
59
Unique Key
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
Synthetic Key / Surrogate Key
DBMS generated primary key
61
Primary Key
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
Foreign Key
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
Junction or Linking Table
Maps two or more tables together by referencing the primary keys of each data table
Used to establish many-to-many relationships
64
Transactions
A unit of work
Happens in full or not at all
65
Acid: Atomic
Execute a transaction ->all or nothing property
| If transaction fails, changes reverts back to original state
66
Acid: Consistency
Transaction must take the database from one consistent state to another
67
Acid : Isolation
Data and the transaction isolated when transaction occurs and until it is completed
68
Acid : Durability
Robust transaction
69
SQL
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
CRUD
Create
Read
Update
Delete
71
Knowledge
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
Data Management
A process that focuses on data collection, storage and retrieval (CRUD)
73
Advantages of DBMS
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
Query
A question or task asked by a end user of a database in the form of SQL code
75
Ad-hoc Query
A spur of he moment question
76
Data inconsistency
A condition in which different version of the same data yield different results
77
Database design
The process that yields the description of the database structure
78
DBMS Function
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
8. Database communication interfaces
79
Entity
A place, person, thing, concept or event for which data can be stored
80
Attribute
Characteristic of an entity (aka column -> file approach)
81
Relationship
Association among entities
82
Constraint
A restriction placed on the data
83
Database subschema
Portion of the database "seen" by the application programs
84
Data Manipulation Language
Defines the environment in which data can be managed and is used to work with the data in the database
85
Data definition Language
Enables the database administrator to define the schema components
86
Tuple
A table row in a relation (aka row in file approach)
87
Why Databases
Data is pervasive
Data is ubiquitous
Databases allow storing and sharing of data in an easy and secure way
88
Islands of Information
Data in scattered data locations
89
Data Integrity
A condition in which the data in the database complies with all entity and referential integrity constraints
90
Relational Data Model
Allows designer to foccs on logical representation of the data and its relationships rather than on physical storage details
91
Composite Key
Key that is composed of more than one attribute
92
Key Attribute
An attribute that is part of a key
93
Candidate Key
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
Entity integrity
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
Null
Absence of any data or value
96
Secondary key
A key used strictly for data retrieval purposes
97
Flags
Special codes implemented by designers to trigger a required response
98
Superkey
An attribute or attributes that uniquely identify each entity in a table
can uniquely identity each attribute in the row
99
Select / Restrict
Unary operator
| Yields values for all rows found in the table that satisfy a given condition
horizontal
100
Project
All values for selected attributes in the order which they are requested (vertical)
101
Intersect
Yields rows that appear in both tables
Table must be union compatible i.e. numeric and numeric
102
Difference
Yields all rows in one table that are not found in the other table (substracts one table from another)
103
Natural join
Links tables by selecting only the rows with common values in their common attributes
