Flashcards
DBMS fundamentals: schemas, integrity, indexing and RAID
Cards in this deck (66)
- 1What is a Database?A very large, integrated collection of data
- 2What is a Database Management System?A software package designed to store and manage
databases - 3Approaches to management of dataDatabase approach
File system approach - 4Advantages of DBMS (vs. File System)o Data & Execution Abstraction
o Reliability
o Efficiency & Performance - 5Data AbstractionData Model:
collection of high-level data description
that hide low-level storage details
The Relational Model:
Every relation has a schema:
o Relation name
o Names of fields
o Types of fields - 6Database LanguagesData Definition Language (DDL):
schemas
Integrity Constraint
Data Manipulation Language (DML):
Query
insert, delete and update data
SQL - 7Levels of Data Abstraction in a DBMS1. Conceptual Schema
2. Physical Schema
3. External Schema (Views) - 8External Schema - ViewsAllows data access to be customized and
authorized at the user-level
Guided by end-user requirements
Views are computed as needed - 93-level Architectureview level: ITMajors
logical level: entire database schema
physical level: how these tables are stored - 10Execution Abstractiontransaction is a logical unit of work in
DBMSs
n deposit, withdraw, transfer money (banking transaction) - 11When an SQL-DBMS is Inappropriate?n the database has simple structure and/or its size
is small
Price to buy (DBMS & Hardware)
Can tolerate failures - 12Integrity Constraintscondition that must be true for any instance
of the database - 13Primary Key Constraintn No two distinct tuples can have same values in
all key fields - 14Foreign Key ConstraintsIf foreign key constraints are enforced,
referential integrity is achieved (reference primary key on parent table) - 15Referential IntegrityInsert might cause a violation, but Delete is ok
- 16Complex Integrity ConstraintsA condition similar to the one in the WHEREclause
of an SQL query
Three DDL constructs
Checks
Assertions
Triggers - 17Conceptual SchemaDescribes data in terms of data model
relational DBMS:
Describes all relations (tables) in database - 18Conceptual database design:the process of
designing a conceptual schema - 19Physical Schemao Describes how relations are stored on disk
- 20Physical database designthe process of
designing a physical database schema - 21External Schemao Allows data access to be customized and
authorized at the user-level.
Consists of a collection of views - 22Data IndependenceApplications insulated from how data is
structured and stored - 23Advantages of Views:1. Logical independence
2. For convenience and clarity when writing queries
Views can be used just like tables
3. For security
different access givi\en - 24o A DBMS implements views in two ways:ü Query Modification
ü View Materialization - 25Query Modificationpresents the view query in terms of a query on the
underlying base tables
Disadvantage:
n re-compute the view with every query - 26Materialized Viewsn Physically store the view (query) and its data
Advantage:
n Avoid re-computing the view with every query
But, materialized view maintenance is needed - 27View UpdateabilityIn general, a view is not updateable if:
an update on a view can be mapped to more than one
possible update on the base tables - 28ACID PropertiesAtomicity
Either all the operations associated with a transaction
happen or none of them happens
o Consistency
It satisfies the integrity constraints on the database at the
transaction's boundaries
o Isolation
The result of the execution of concurrent transactions is
the same as if transactions were executed serially
o Durability
The effects of completed transactions become permanent
surviving any subsequent failures - 29Modes of Constraints Enforcemento NOT DEFERRABLE or IMMEDIATE
n Evaluation is performed at input time
n By default constraints are created as NON DEFERRABLE
DEFERRED
n Constraints are not evaluated until commit time
DEFERRABLE
n It can be changed within a transaction to be DEFERRED using
SET CONSTRAINT - 30CHECK: attribute- vs. tuple-basedcheck can be used before create table to check whenever inserted and mod (tuple)
or after to check certian attributes dont violate constraint - 31AssertionsSimilar to CHECK but they are global constraints
Examples:
n # of IT stduents cannot exceed 1800 - 32TriggersA trigger consists of 3 parts:
1. Event(s),
2. Condition, and
3. Action - 33Storage Hierarchy1. Primary
n Random Access Memory (RAM)
2. Secondary
n Disks
3. Tertiary
n Tapes - 34Why not store everything in RAM?1. Expensive cost
2. Small capacity
3. Volatile - 35Disks and FilesDBMS stores information on hard disks
n READ: transfer data from disk to RAM
n WRITE: transfer data from RAM to disk - 36Disk Components 1Concentric rings called tracks
n Single-sided or double-sided platters
All tracks with same diameter = cylinder
Each track is divided into sectors - 37Disk Components 2A Block is multiple contiguous sectors
Only one head reads/writes at any one time
The arm assembly is moved in or out to position the
head on a desired track - 38Data Elementso Field: a database attribute (sequence of bytes)
o Record: sequence of fields
o Block: sequence of records
o File: sequence of blocks - 39Improving Access Time1. Block transfer
2. Cylinder-based Organization
3. Buffering and Prefetching
4. Multiple disks
5. Record Placement - 40To minimize access timedata is always
transferred from/to disks by blocks of bytes - 41Block header includes:Block ID
Role info such as data block, index block, etc.
Timestamp
Links to other blocks of the same table/file
Free-list - 42o UnspannedRecords are not allowed to cross block boundaries
- 43SpannedA record can span more than one block
- 44What is the cylinder-based organization? And what are its advantages?minimises the seek time and rotational delay of disk access by having data in a concentric circle on multiple platters, able to be read by different heads without additional seeks
- 45What is the difference between buffering and prefetching?Buffering attempts to reduce disk accesses by keeping as much of the already-accessed data in memory as possible (uses LRU LFU)
Prefetching speeds up data access by pre-loading data it knows will be needed at some point (it doesn’t know when the request will come). Requires extra main memory - 46Queuing Delaywhen multiple queries are executed at the same time, and
those queries access the same disk at the same time
Solution: Use multiple disks to support parallel access - 47Multiple Disks: Organizationso Data partitioning over several disks
Pros: increased access rate
Problem: if popular data is stored on same disk
request collisions (hot spots)
Cons: the cost
o Mirror disks: Disks hold identical copies
Pros: Doubles read rate
no request collisions
Does not slow down write requests
Cons: the storage of one - 48RAID Technologyn Redundant Arrays of Inexpensive (independent) Disks
o Acts as a single high-performance large disk - 49Data Striping:distributes data transparently over multiple
disks to make them appear as a single large, fast disk - 50Levels of RAID0: Non-Redundant
1: Mirrored
2: Memory Style ECC
3: Bit-Interleaved Parity
4: Block-Interleaved Parity
5: Block-Interleaved
Distribution-Parity - 51Fixed Length:- Storage space efficiency is low due to record internal fragmentation meaning a query will require more disk access.
- Search time efficiency is equally as fast for all access fields. - 52Variable Length:Storage space efficiency there is no fragmentation or wasted space, therefore it has good storage efficiency.
Search time is slower because it is necessary to read from the first field on wards in order to get to a specific field - 53Unordered (heap)Pros: Fast at inserting
Cons: Slow at Searching (n blocks) - 54Ordered (sorted)Pros: Fast at searching
Cons: Slow at inserting (have to shift n/2 blocks on average) - 55comparison I/Olook at table
- 56indexAn index is an auxiliary file that makes it more
efficient to search for a record in the data file - 57Index StructuresSingle-level Indexes
1. Primary Indexes
2. Clustering Indexes
3. Secondary Indexes
o Multi-level Indexes - 58Primary IndexesA primary index is nondense (sparse)
One index entry for each block in the data file
it occupies much smaller space than a data file, because:
1. There are fewer index entries than records
2. Each index entry is typically smaller than a data record
binary search o(logn) - 59To search for a recordBinary search in the index file
n Fetch (i.e., read) the block from the data file - 60Clustering Indexo Defined on an ordered data file
o The data file is ordered on a non-key field
o One entry for each distinct value of the field - 61Secondary Indexo Includes one index entry for each record in the data file
o An index entry points to a data block that contains the record
o It is a dense index - 62For&binary&search,And,&time&to&search forarecord&is&estimatedasCeiling (log2 b) *(s+rd+btt)
- 63f&the&blocks&are&scattered&over&the&disk,&a&seek&is&needed&for&each&block,&so&the&time to&search&n& blocks&isn*(s+rd+btt)
- 64. If&the&blocks&are&stored&consecutively,&the&time&to&read n&consecutive&blockss + rd + (n*btt)
- 65Wasted&space&per&blockB - (R*Bfr)
- 66—
Information
- Description
- Flashcards on Untitled, created by 101ldaniels . on 31/08/2016.
- Created by
- 101ldaniels .
- Views
- 1
- Rating