Ask Question, Ask an Expert


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”


FROM CardCharge CC, Card C, Person P

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


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”


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

Database worksheetuse the attached database to complete the

Database Worksheet Use the attached database to complete the following: Create a simple excel spreadsheet with data to update the database. >>> This can hold any type of data to be used in this database In MS Access 1. C ...

List the different types of locks used by dbms systems and

List the different types of locks used by DBMS systems and explain how they differ from each other. Additionally, suggest the suitable scenario for each of them. 400 to 500 words, at least 4 references with one website r ...

Assessment item 1taskthe fermoy house databasethe owners of

Assessment Item 1: Task The Fermoy House database The owners of Fermoy House, a Bed and Breakfast guest house in the Blue Mountains of NSW, have approached you to build them a database to help them run their business. Fe ...

Project guidelinesoverviewthe final project for this course

Project Guidelines Overview The final project for this course is the creation of a database proposal and implementation plan report that will address the needs of a given scenario. The purpose of this assessment is to as ...

1 what is the difference between database and data

1. What is the difference between database and data warehouse? 2. What is OLAP? 3. What is a data cube? How do DSS take advantage of the structure?

Database individual assignmentdesign a database entity and

Database: Individual Assignment Design a database (entity and relationship tables in Access) for a small college department library. You should have the entities and relationships you need to support: Purchase books Sell ...

The following assignment is based on the database

The following assignment is based on the database environment designed in the Week Three Individual Assignment. Your database project must meet the following requirements: • Create a database using professional principle ...

Database design and development assignmentintroduction -

Database Design and Development Assignment Introduction - The sample solution of assignment 1 will be released on the course website approximately two (2) weeks after the assignment 1 due date.  You must use that sample ...

Application creating and populating a databasesuppose you

Application: Creating and Populating a Database Suppose you have a small retail store and decide to create a database to track sales. You have information about recent sales that you want to use for testing the database. ...

Part aexercise 1 data scienceread the article at

Part A Exercise 1: Data Science Read the article at and answer the following: What is Data Science? According to IBM estimation, what is the percent of the data ...

  • 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

A cola-dispensing machine is set to dispense 9 ounces of

A cola-dispensing machine is set to dispense 9 ounces of cola per cup, with a standard deviation of 1.0 ounce. The manuf

What is marketingbullwhat is marketing think back to your

What is Marketing? • "What is marketing"? Think back to your impressions before you started this class versus how you

Question -your client david smith runs a small it

QUESTION - Your client, David Smith runs a small IT consulting business specialising in computer software and techno

Inspection of a random sample of 22 aircraft showed that 15

Inspection of a random sample of 22 aircraft showed that 15 needed repairs to fix a wiring problem that might compromise

Effective hrmquestionhow can an effective hrm system help

Effective HRM Question How can an effective HRM system help facilitate the achievement of an organization's strate