Database Systems (资料库系统)
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
– Quickly search an index to locate a data entry with a key value k.
• Example of a data entry: <age, rid>
– Can use the data entry to find the data record.
• Example data record = data entry: <age, name, salary>
– (Alternative 2): <k, rid of data record with search key value k>
• Example data entry: <age, rid>
– Due 11/8 (next Tuesday)
• Practicum Assignment #1:
– Post on the course homepage on 11/7
• Next Week Reading: Chapter 9
3
Reflection
• How to design a database?
than reading them in random order
• Tapes: Can only read pages in sequence
– $0.3 per Gigabyte – Cheaper than disks; used for archival storage
• Other types of persistent storage devices:
• Tree-based indexing, Hash-based indexing
• Comparison on file organizations
– Question: which one is better/worse in performance?
• Indexes and Performance
match values in certain (“search key”) fields
Indexes
• An index on a file speeds up selections on the search key fields for the index.
– Any subset of the attributes of a table can be the search key for an index on the relation.
– Indexes: Data structures to organize records via trees or hashing.
• For example, create an index on employee age. • Like sorted files, speed up searches for a subset of records that 12
– Optical storage (CD-R, CD-RW, DVD-R, DVD-RW) – Flash memory
8
Definition
• A record is a tuple or a row in a relation table.
– Fixed-size records or variable-size records
• A file is a collection of records.
– Store one table per file, or multiple tables in the same file
• A page is a fixed length block of data for disk I/O.
– A file is consisted of pages.
– A data page also contains a collection of records.
– Typical page sizes are 4 and 8 KB.
9
File Organization
• Method of arranging a file of records on external storage.
– Questions:
• How to store table records on external storage device (e.g., a disk)? • How to speed up access to needed records on a disk?
– Heap file, Sorted file – Indexing data structures
space
• Which one is more difficult?
• The Actuated Workbench (MIT media lab)
2
Course Administration
• Assignment #2: due 11/2 • Assignment #3: post on the course homepage
– File and index layers organize records on files, and manage the indexing data structure.
11
Alternative File Organizations
• Many alternatives exist, each ideal for some situations, and not so good in others:
7
Types of External Storage Devices
• Magnetic Disks: Can retrieve random page at fixed cost
– ~$1 per Gigabyte – But reading several consecutive pages is much cheaper
– Sorted Files: Records are sorted. Best if records must be retrieved in some order, or only a `range’ of records is needed.
• Examples: employees are sorted by age. • Slow update in comparison to heap file.
– Conceptual design: ER Model – Logical design: Relational Model
• How to ask questions on a database?
– Relational Algebra & SQLs
• What’s next?
– How to get fast access to records?
– Record id (rid) is used to locate a record on a disk
• [page id, slot number]
– Indexes are data structures to efficiently search rids of given values
– Question: how to use indexing for better performance?
6
Data on External Storage
• External Storage: offer persistent data storage
– Unlike physical memory, data saved on a persistent storage is not lost when the system shutdowns or crashes.
– Search key does not have to be candidate key
• Example: employee age isindex file contains a collection of data entries (called k*).
Database Systems (資料庫系統)
October 31, 2019 Lecture #6
1
Physical & Digital Space Interaction
• Consider moving a pencil …
– Physical space movement mapping to digital space – Digital space movement mapping to physical
10
DB Storage and Indexing
• Layered Architecture
– Disk Space Manager allocates/de-allocates spaces on disks.
– Buffer manager moves pages between disks and main memory.
– Heap files: Records are unsorted. Suitable when typical access is a file scan retrieving all records without any order.
• Fast update (insertions / deletions)
Data records
Mary Paul
Data File (Large)
14
Alternatives for Data Entry k*
• Three alternatives for what to store in a data entry:
– (Alternative 1): Data record with key value k
• File organizations & indexes
• What’s further next?
– How to optimize query processing time?
4
Overview of Storage & Indexing
Chapter 8
5
Outline
• Types of external storage devices • File organizations
– (Alternative 3): <k, list of rids of data records with search key k>
• Example data entry: <age, rid_1, rid_2, …>
• Choice of alternative for data entries is independent of the indexing method.
Search key value: find employees with age = 25
(B+ Tree) (Hash)
Index Data Structure: Index entries + Indexing method
Data entries
(k=25, Paul’s rid)
Index File (Small for efficient search)
– Indexing method takes a search key and finds the data
entries matching the search key.
15
– Examples of indexing methods: B+ trees or hashing.
Alternatives for Data Entries (Contd.)
• Example of a data record: <name, age, salary>
– Can create multiple indexes on the same data records. 13 • Example indexes: age, salary, name
Indexing Example
• Example of a data entry: <age, rid>
– Can use the data entry to find the data record.
• Example data record = data entry: <age, name, salary>
– (Alternative 2): <k, rid of data record with search key value k>
• Example data entry: <age, rid>
– Due 11/8 (next Tuesday)
• Practicum Assignment #1:
– Post on the course homepage on 11/7
• Next Week Reading: Chapter 9
3
Reflection
• How to design a database?
than reading them in random order
• Tapes: Can only read pages in sequence
– $0.3 per Gigabyte – Cheaper than disks; used for archival storage
• Other types of persistent storage devices:
• Tree-based indexing, Hash-based indexing
• Comparison on file organizations
– Question: which one is better/worse in performance?
• Indexes and Performance
match values in certain (“search key”) fields
Indexes
• An index on a file speeds up selections on the search key fields for the index.
– Any subset of the attributes of a table can be the search key for an index on the relation.
– Indexes: Data structures to organize records via trees or hashing.
• For example, create an index on employee age. • Like sorted files, speed up searches for a subset of records that 12
– Optical storage (CD-R, CD-RW, DVD-R, DVD-RW) – Flash memory
8
Definition
• A record is a tuple or a row in a relation table.
– Fixed-size records or variable-size records
• A file is a collection of records.
– Store one table per file, or multiple tables in the same file
• A page is a fixed length block of data for disk I/O.
– A file is consisted of pages.
– A data page also contains a collection of records.
– Typical page sizes are 4 and 8 KB.
9
File Organization
• Method of arranging a file of records on external storage.
– Questions:
• How to store table records on external storage device (e.g., a disk)? • How to speed up access to needed records on a disk?
– Heap file, Sorted file – Indexing data structures
space
• Which one is more difficult?
• The Actuated Workbench (MIT media lab)
2
Course Administration
• Assignment #2: due 11/2 • Assignment #3: post on the course homepage
– File and index layers organize records on files, and manage the indexing data structure.
11
Alternative File Organizations
• Many alternatives exist, each ideal for some situations, and not so good in others:
7
Types of External Storage Devices
• Magnetic Disks: Can retrieve random page at fixed cost
– ~$1 per Gigabyte – But reading several consecutive pages is much cheaper
– Sorted Files: Records are sorted. Best if records must be retrieved in some order, or only a `range’ of records is needed.
• Examples: employees are sorted by age. • Slow update in comparison to heap file.
– Conceptual design: ER Model – Logical design: Relational Model
• How to ask questions on a database?
– Relational Algebra & SQLs
• What’s next?
– How to get fast access to records?
– Record id (rid) is used to locate a record on a disk
• [page id, slot number]
– Indexes are data structures to efficiently search rids of given values
– Question: how to use indexing for better performance?
6
Data on External Storage
• External Storage: offer persistent data storage
– Unlike physical memory, data saved on a persistent storage is not lost when the system shutdowns or crashes.
– Search key does not have to be candidate key
• Example: employee age isindex file contains a collection of data entries (called k*).
Database Systems (資料庫系統)
October 31, 2019 Lecture #6
1
Physical & Digital Space Interaction
• Consider moving a pencil …
– Physical space movement mapping to digital space – Digital space movement mapping to physical
10
DB Storage and Indexing
• Layered Architecture
– Disk Space Manager allocates/de-allocates spaces on disks.
– Buffer manager moves pages between disks and main memory.
– Heap files: Records are unsorted. Suitable when typical access is a file scan retrieving all records without any order.
• Fast update (insertions / deletions)
Data records
Mary Paul
Data File (Large)
14
Alternatives for Data Entry k*
• Three alternatives for what to store in a data entry:
– (Alternative 1): Data record with key value k
• File organizations & indexes
• What’s further next?
– How to optimize query processing time?
4
Overview of Storage & Indexing
Chapter 8
5
Outline
• Types of external storage devices • File organizations
– (Alternative 3): <k, list of rids of data records with search key k>
• Example data entry: <age, rid_1, rid_2, …>
• Choice of alternative for data entries is independent of the indexing method.
Search key value: find employees with age = 25
(B+ Tree) (Hash)
Index Data Structure: Index entries + Indexing method
Data entries
(k=25, Paul’s rid)
Index File (Small for efficient search)
– Indexing method takes a search key and finds the data
entries matching the search key.
15
– Examples of indexing methods: B+ trees or hashing.
Alternatives for Data Entries (Contd.)
• Example of a data record: <name, age, salary>
– Can create multiple indexes on the same data records. 13 • Example indexes: age, salary, name
Indexing Example