Logical Data Independence and Indexes in Databases
Logical Data Independence
Key Idea: Logical Data Independence
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”
V
i
e
w
e
x
a
m
p
l
e
A view is a relation defined by a query
•
Purchase(customer, pid, store)
•
Product(pid,
pname
, price)
•
StorePrice(store, price)
H
o
w
t
o
U
s
e
a
V
i
e
w
?
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
Customer(
cid
, name, city)
StorePrice(store, price)
Purchase(customer, product, store)
Product(
pname
, price)
K
e
y
I
d
e
a
:
I
n
d
e
x
e
s
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
’
;
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.
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.
E N D
Presentation Transcript
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
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 )
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
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 ;
