Advanced Database Systems The Online Furniture Factory has added a new product,

Question

Advanced Database Systems

The Online Furniture Factory has added a new product, a Contemporary Patio Table. At the end of every day, they run a query to count sales on the new product to help manage inventory. We expect this product to sell greater than the average daily sales for all other products. The query must run quickly. They need you to do a performance review on the tables in the query and make table and index design recommendations. Here are the problem assumptions: On average, an order has 2 order lines. Assume PKs are the same as listed on Factory Data Model Diagram block read/write time = 1 ms

Explanation / Answer

create table Product
(
Product_ID int identity(1,1) primary key,
Product_Description nvarchar(512),
)

create table OrderLineALL
(
Order_ID bigint identity(1,1) primary key,
Order_Quantity int,
Prodcut_ID int
foreign key (Prodcut_ID) references Product(Prodcut_ID)
)

create table OrderLine
(
Order_ID bigint identity(1,1) primary key,
Order_Quantity int,
Prodcut_ID int
foreign key (Prodcut_ID) references Product(Prodcut_ID)
)

1.filter factors-> Product_description
2.add filter condition for OrderLine and OrderLineALL tables "where dbms_rowid.rowid_block_number(rowid) <= 25"
add filter condition for Product table "where "dbms_rowid.rowid_block_number(rowid) <= 30"
3. Binary tree file structure is advisable for storing millions of records and below are indexes
Create nonclustered index Product_Order_Index
on OrderLine(Product_ID) include(Order_Quantity)

Create nonclustered index Product_Order_Index
on OrderLine(Product_ID) include(Order_Quantity)

Create nonclustered index Product_Order_Index
on Product(Product_Description)

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.