Ask Question, Ask an Expert

+1-415-315-9853

info@mywordsolution.com

Ask DBMS Expert


Home >> DBMS

problem 1: In the following, assume the latency/transfer-rate model of disk performance, where we estimate disk access times by allowing blocks that are consecutive on disk to be fetched with a single seek time and rotational latency cost (as shown in class). Also, we use the term RID (Record ID) to refer to an 8-byte "logical pointer" that can be used to locate a record (tuple) in a table.

You are given the following very simple schema for a credit card payment database. In this schema, people can make payments to merchants (stores) with their credit cards. All the payment records are stored in the CardCharge table with a unique identifier and a timestamp. (We are only concerned with the time of the charge, not the time the merchant receives the money from the credit card company or the time the customer pays his credit card bill, which both are much later.) The database stores for every person a name, city, state, and SSN. A customer may have several credit cards, for which we store the customer’s SSN, the time the credit card was issued,, and the expiration time. Each merchant has a name, city, state and a unique identifier. The details of the schema are shown below:

Person(ssn, pname, pcity, pstate)

Card(ccn, ssn, issuetime, expiration) // ssn references Person

Merchant(mid, mname, mcity, mstate)

CardCharge(chargeid, ccn, mid, ctime, camount) // ccn reference Card and mid references Merchant

Assume there are 50 million customers, 200 million cards, 1 million merchants and 10 billion charge records. Each tuple is of size 200 bytes, and each ID requires 16-bytes. Consider the following queries:

SELECT chargeid

FROM CardCharge CC, Card C, Person P

WHERE CC.ccn = C.ccn and C.ssn = P.ssn and P.pname = “Cathy Crowbar”

SELECT P.ssn

FROM CardCharge CC, Card C, Person P

WHERE CC.ccn = C.ccn and C.ssn = P.ssn and P.pcity = “Chicago”

SELECT P.ssn

FROM CardCharge CC, Card C, Merchant M, Person P

WHERE CC.mid = M.mid and CC.ccn = C.ccn and C.ssn = P.ssn and CC.camount > 1000 and M.mcity = “Elko” and P.pcity = “New York City”

SELECT C.ssn

FROM CardCharge CC, Card C, Merchant M, Person P

WHERE CC.ccn = C.ccn and CC.mid = M.mid and C.ssn = P.ssn and M.mcity = P.pcity

a) For each query, describe in one sentence what it does. (That is, what task does it perform?)

In the following, to describe how a query is executed, draw a query plan tree and state what algorithms should be used for the various selections and joins. Provide estimates of the running times, assuming these are dominated by disk accesses.

b) Assume that there are no indexes on any of the relations, and that all relations are unclustered (not sorted in any way). Describe how a database system would best execute all four queries in this case, given that 2GB of main memory are available for query processing, and assuming a hard disk with 10 ms for seek time plus rotational latency (i.e., a random access requires 10 ms to find the right position on disk) and a maximum transfer rate of 60 MB/s.

Assume that 2% of all customers live in Chicago and 5% live in New York City, that there are only 5 customers named “Cathy Crowbar”, that there are 200 Merchants Elko, and that 1% of all charges are for more than $1000. Also, if nothing is stated, assume independence (e.g.., customers in Chicago have on average the same number of cards and same spending patterns as the average customer, and if 1% of all people live in Cleveland and 20% of all charges were done in 2011, then 0.2% of all charges were made during 2011 by people living in Cleveland.)

c) Consider a sparse clustered B+-tree index on chargeid in the CardCharge table, and a dense unclustered B+-tree index on mname in the Merchant table, where mname has a (fixed) size of 16 bytes. For each index, what is the height and the size of the tree? How long does it take to fetch a record with a particular key value value using these indexes?

d) Suppose that for each query, you could create up to two index structures to make the query faster. What index structures would you create, and how would this change the evaluation plans and running times? (In other words, redo (b) for each query using your best choice of indexes for that query.)

problem 2:

a) Consider a hard disk with 6000 RPM and 3 single-sided platters. Each surface has 400,000 tracks and 2000 sectors per track. (For simplicity, we assume that the number of sectors per track does not vary between the outer and inner area of the disk.) Each sector has 1024 bytes. What is the capacity of the disk? What is the maximum rate at which data can be read from disk, assuming that we can only read data from one surface at a time? What is the average rotational latency?

b) Suppose we have another disk, different from the one in part (a), with average seek time 4 ms, average rotational latency 6 ms, and maximum transfer rate 60 MB/s. How long does it take to read a file of size 8 KB? How about a file of 80 KB? How about a file of 8 MB? Use both the block model (4KB per block) and the latency/transfer-rate model, and compare.

c) Suppose you have a file of size 81 GB that must be sorted, and you have only 1 GB of main memory to do the sort (plus unlimited disk space). Estimate the running time of the I/O-efficient merge sort algorithm from the class on this data, using the hard disk from part (b). Use the latency/transfer-rate model of disk performance, and ignore CPU performance. Assume that in the merge phase, all sorted runs from the initial phase are merged together in a single merge pass.

d) Suppose you use two (instead of one) merge phases in the scenario in (c). What would be the degree of the merges, and how would this change the running time of the sort?

DBMS, Programming

  • Category:- DBMS
  • Reference No.:- M96027

Have any Question? 


Related Questions in DBMS

Query 1list all movies played in landmark or music box

Query 1 List all movies played in Landmark or Music Box. Output only titles and eliminate duplicates. Query 2 List all stars born after 1960. Order them by their birthdate in ascending order. Output their first names, la ...

Advanced relational database final programming project in

Advanced Relational Database Final Programming Project In this project, you will design a secure database with several tables using good relational database design techniques. Project Objectives: After completing this Pr ...

Suppose that you are the database developer for a local

Suppose that you are the database developer for a local college. The Chief Information Officer (CIO) has asked you to provide a summary of normalizing database tables that the IT staff will use in the upcoming training s ...

Replacement china incproject descriptionyou work as an

Replacement China, Inc. Project Description: You work as an associate database manager at Replacement China, Inc. This firm specializes in finding difficult-to-replace, no-longer manufactured china, crystal, silver, and ...

Benefits of data protection controlsbased on what you have

Benefits of Data Protection Controls Based on what you have learned about data protection in an online environment, prepare a 5-slide presentation to a department head or the CEO of a company to convince the audience tha ...

Enterprisebim projectgoal - to enable students to

Enterprise/BIM Project Goal - To enable students to experience the process of building a non-trivial database-backed web application. Tasks 1. Choose any area that interests you - this does not necessarily have to be abo ...

1 what is the most popular database model2 how are primary

1. What is the most popular database model? 2. How are primary and foreign keys different? 3. What are the five important software components of a database management system? 4 What are the four major types of data-minin ...

Virtualization fundamentalsunit 3 research projectwrite an

Virtualization Fundamentals Unit 3 Research Project Write an overview of *any two* of the following topics. Each overview should be between one half and one full page long. You should use a minimum of two sources for eac ...

Listed the five important software components of a dbms the

Listed the five important software components of a DBMS: the DBMS engine, the data definition, data manipulation, application generation, and data administration subsystems. Which of those are most and least important to ...

A task descriptionoverviewyour task is to create and test a

A. Task Description Overview Your task is to create and test a database in MySQL using PhpMyAdmin. You are provided with a scenario and supporting documents, describing the requirements that Southern Cross University may ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

Section onea in an atwood machine suppose two objects of

SECTION ONE (a) In an Atwood Machine, suppose two objects of unequal mass are hung vertically over a frictionless

Part 1you work in hr for a company that operates a factory

Part 1: You work in HR for a company that operates a factory manufacturing fiberglass. There are several hundred empl

Details on advanced accounting paperthis paper is intended

DETAILS ON ADVANCED ACCOUNTING PAPER This paper is intended for students to apply the theoretical knowledge around ac

Create a provider database and related reports and queries

Create a provider database and related reports and queries to capture contact information for potential PC component pro

Describe what you learned about the impact of economic

Describe what you learned about the impact of economic, social, and demographic trends affecting the US labor environmen