Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
68 Cards in this Set
- Front
- Back
DBMS components (4) |
1.) database 2.)DBA (database administrator) 3.) Application Programs 4.) Hardware ( storage, servers, etc) |
|
Why use DBMS? (7) |
1.) data sharing 2.) centralized control 3.) Redundancy control 4.) Data Integrity 5.) Data security 6.) Views 7.) Data independence (logical data, physical data) |
|
Schema |
The overall description of a database (at various granularities) |
|
Architecture |
A description of DBMS components and their interconnections |
|
ANSI/SPARC Architecture |
|
|
External level (ANSI/SPARC) |
describes user perspectives of the DB |
|
Conceptual Level (ANSI/SPARC) |
design of the field groupings, data relationships, etc. |
|
Internal Level (ANSI/SPARC) |
define record sizes, representatives, indices,etc. Hidden from application programs, users |
|
Interface between ANSI/SPARC levels (mappings) External Mapping |
(view to view) often easier to tweak an existing view. but efficiency is a concern |
|
Interface between ANSI/SPARC levels (mappings) External- Conceptual Mapping |
allows field renaming, arrangements for user benefits |
|
Interface between ANSI/SPARC levels (mappings) Conceptual- Internal Mapping |
Convert logical structure to physical representations Provides physical data independence |
|
Server Architecture (two tier) |
Client- presentation of data to user Server- fields requests from clients, runs the DBMS |
|
Server Architecture (multi tier) |
-more capabilities -distribution -less client computing needed |
|
What is SOA? |
Service Oriented Architectures software design technique Apps are built using pre written service mods Goal is to be flexible with the adoption of new business processes |
|
3 Parts of an SOA |
Advertising (service) Finding Using |
|
Distributed DBMS |
Each server has all or some, or none of the DB stored locally |
|
Storage Pyramid |
|
|
Hard drive Physical Characteristics side View |
|
|
Hard drive Physical Characteristics top view |
|
|
Sources of Read Write delay |
Seek Time Rotational delay Transfer Time |
|
Raid Disk Mirroring |
1 Write 2 copies Adv: can operate w/ failed drive (possibly read in parallel) Disadv: Cost (2 disks, capacity of one) |
|
Disk Striping |
distributing units of data across disks Adv. Performance Disadv. Inc probability of drive failure |
|
Parity Schemes (raid) |
Odd, Even |
|
Independent events for drive failure |
p(A ^ B) = p(A) + p(B) - p(A) * p(B) |
|
Hard drive failure review |
|
|
Indexing |
A file containing structured references to records of another file. |
|
Blocking Factor |
The # of whole records that can be stored in a block. bf - (block size/ record size) |
|
Internal Fragmentation |
Unallocable storage within an allocation unit. |
|
Fixed length Records: |
packed allocation (contiguous) vs. unpacked allocation |
|
Variable length Records |
similar to unpacked but you need ptrs in the directory w/ offsets |
|
Index |
A file containing structured references to records of another file |
|
Candidate key |
A key able to uniquely identify a record (i.e. Student ID, SSN) |
|
Primary Key |
The chosen Candidate key to be the records identifier |
|
Secondary key |
Any non-candidate key |
|
Sort Key |
The key used to sort the files records |
|
Ordered and Unordered Classification of indices |
Single (sorted file) Multi-level (B+ tree) |
|
Primary index: The indexed Field is a _______ |
Candidate Key |
|
Primary index: The index records are sorted on ___________ |
a candidate key |
|
Primary index: The file records are sorted on _____________ |
a candidate key |
|
Clustered Index: The indexed field is ___________ |
not a candidate key (therefore, secondary) |
|
Clustered Index: The index records are sorted on ____________ |
a secondary key |
|
Clustered Index: The file records are sorted on _______________ |
a secondary key
|
|
Secondary Index: the indexed field is _____________ |
any field |
|
Secondary Index: the index records are sorted on ____________ |
any field |
|
Secondary Index: the file records are not sorted on ____________ |
any field |
|
Dense Indices: |
Hold a record for every DB file record permits existence queries w/ index only |
|
Sparse Indices: |
indices have only a subset of the field values Therefore: smaller, faster to search |
|
Extendable Hashing |
|
|
B tree insert |
If leaf has capacity insert, otherwise, form new set for keys, promote median value to parent, create two nodes that are less than and greater than. Attach children on either side |
|
B Tree deletion |
|
|
For B-tree of order 2 how many keys? |
first level: 4
second level: 5 * 4 = 20 Third level: 5^2 * 4 = 100 |
|
For b tree of order 100, how many keys? |
first level: 200 second level: 201 * 200 third level: 201^2 * 200 |
|
B tree order determination |
|
|
B+ tree differences from B Tree |
Copies of some keys occupy internal nodes each key is stored in a leaf node leaf nodes are linked sequentially |
|
What are the Advantages of a B+ over a B Tree. Disadv? |
Adv: built in disk pointers for all key values, Supports exact match (10-20) DisAdv: Waste of storage capacity Insertion/ Deletions are a little more complex |
|
Definition: Null |
A null is a marker that indicates that a field does not have a value. |
|
Definition: Foreign Key |
A field in one file whose values are drawn from the primary key field of another file |
|
Which can be null? Primary or foreign? |
Foreign |
|
Foreign keys are defined when..... |
the DB is defined |
|
No PK can be ______ |
null |
|
Four Phases of DB design |
1: Requirements Analysis 2: Conceptual Design 3: Logical Design 4: Physical Design |
|
Definition: Entity |
An Entity is an instance of a general classification Ex. Each of you is an instance of class "human" |
|
Definition: Relationship
|
A relationship is an association between sets of entities
Ex. Instructors teach classes |
|
Definition: One to One Relationships |
In a 1 to 1 relationship between two entity sets A and B, an entity from A is associated with at most one entity from B, and an entity from B is associated with at most one entity from A. |
|
Definition: One to Many Relationship |
In a 1:N relationship, one entity from A is associated with none or more entities from B, but each from B is associated with at most one from A
|
|
Definition: Many to Many Relationship |
An M to N relationship is a pair of 1 to N relationships |
|
Ternary Relationship |
3 degree or 3 way. Ex. SPJ schema |
|
Four Data Models of Note |
Hierarchical Network Relational Object |