Understanding Logical Data Independence and Indexes in Databases

Logical data independence allows users to view data without being affected by changes in the underlying structure. Views in databases act as virtual tables, providing a way to simplify data access. Indexes, on the other hand, optimize query performance by efficiently retrieving data from large datasets. By leveraging these concepts, database users can work with data more effectively and improve query efficiency.

• Data Independence
• Views
• Indexes
• Databases
• Query Optimization

gasp_76 Follow

Uploaded on Oct 03, 2024 | 0 Views

Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

Presentation Transcript

1. Logical Data Independence

2. Key Idea: Logical Data Independence

3. What are Views? A view is just a query with a name We can use the view just like a real table Why can we do this? Because we know that every query returns a relation: We say that the language is algebraically closed

4. View example View example A view is a relation defined by a query Purchase(customer, pid, store) Product(pid, pname, price) StorePrice(store, price) CREATE VIEW StorePrice AS SELECTx.store, y.price FROM Purchase x, Product y WHERE x.pid= y.pid This is like a new table StorePrice (store,price )

5. Customer(cid, name, city) Purchase(customer, product, store) Product(pname, price) StorePrice(store, price) How to Use a View? How to Use a View? A "high end" store is a store that sold some product over 1000. For each customer, find all the high end stores that they visit. Return a set of (customername, high-end-store) pairs. SELECT DISTINCT z.name, u.store FROM Customer z, Purchase u, StorePrice WHERE z.cid = u.customer AND u.store = v.store AND v.price > 1000

6. Key Idea: Indexes Key Idea: Indexes Databases are especially, but not exclusively, effective at Needle in Haystack problems: Extracting small results from big datasets Your query will always* finish, regardless of dataset size. Indexes are easily built and automatically used when appropriate by the optimizer. CREATE INDEX seq_idx ON sequence(seq); SELECT seq FROM sequence WHERE seq = GATTACGATATTA ;