Question

In: Electrical Engineering

Database System Question An automobile part trading company would like to store the following attributes for...

Database System Question

An automobile part trading company would like to store the following attributes for each
part in a database table:

PartNo: 10 bytes
Name: 30 bytes
UintMeasure: 5 bytes
UnitCost: 4 bytes
UnitPrice: 4 byes
QtyOnHand: 4 bytes
QtyOnOrder: 4bytes
PreferredSupplier: 30 bytes

Consider a disk with block size of 512 bytes and block pointer of 6 bytes long. Each record has a unique value of PartNo.
There are altogether 50,000 parts in the company.

Compute the following:

i) The number of file blocks required for storing the part records.

ii) The percentage of disk space saving achieved by implementing PartNo as primary index from implementing it as secondary index.

iii) The percentage of improvement on number of block accesses required for searching a product record based on PartNo’s value achieved by implementing PartNo as primary index from implementing it as secondary index.

Solutions

Expert Solution

Answer:

1)

Number of file blocks required for storing the part records.

Record length R = (10 + 30 + 5 + 4 + 4 + 4 + 4 + 30)

= 91bytes

Here number of file blocks b assuming an unspanned organization.

Blocking factor bfr = floor(512/91) = 5.626 records/block

No. of blocks required for one file =ceiling(50000/5.626) = 8887

2)

Disk space saving achieved by implementing PartNo as primary

index from implementing it as secondary index.

Index record size Ri = (10 + 6) = 16bytes

Index blocking factor bfri=floor(512/16) = 32

No. of 1st level index entries r1= No of file blocks b = 8887entries

No. of 1stlevel index blocks b1 = ceiling(8887/32) = 228blocks

No. of 2nd level index entries r2= No of 1st level blocks b1= 228entries

No. of 2nd level index blocks b2= ceiling(228/32)= 8blocks

No. of 3rd level index entries r3= No. of 2nd level index blocks b2= 8entries

No. of 3rd level index blocks b3 = ceiling(8/32) = 1

Here 3rd level is the top index level. Because it has only one block

Hence, the index has x =3levels.

Total number of blocks for the index bi= 228 + 8 + 1= 237blocks

If file is not ordered by the PartNo and we construct a secondary index on

PartNo - We will Repeat the previous exercise for the secondary index and we will compare with the primary index.

No. of 1st level index entries r1= No. of file records r = 50000

No. of 1st level index blocks b1 = ceiling(50000/32)= 1563blocks

To calculate the number of levels:

No. of 2nd level index entries r2= No. of first-level index blocks b1= 1563 entries

No. of 2nd level index blocks b2 = ceiling(1563/32)= 49blocks

No. of 3rd level index entries r3= No. of 2nd level index blocks b2= 49 entries

No. of 3rd level index blocks b3 = ceiling(49/32) = 2

Total number of blocks for the index bi = 1563 + 49 + 2 = 1614 blocks

The % of disk space saving achieved by implementing PartNo as primary

index from implementing it as secondary index = (237/1614 )*100 = 14.68 %


Related Solutions

Part 1Write a narrative that describes what information you will store in a database. Be as...
Part 1Write a narrative that describes what information you will store in a database. Be as descriptive as you can be. Part 2Create an E-R diagram that corresponds to yournarrative. If you have fewer than three entities in your E-R diagram, revise your narrative!Remember that foreign key values should not be in your entities.Make sure that you include maximum and minimum cardinalities for your relationships.You can use a tool to create the E-R diagram or draw it by hand and...
Create a database design with the following rules. List down all the entities and attributes and...
Create a database design with the following rules. List down all the entities and attributes and draw out the relationships. List all the business rules. 1. A person can have multiple accounts (Track individual accounts, Trusts, IRA, 401k, HSA, etc) a. Not all accounts must be through brokerage 2. Multiple brokerages (Fidelity, Vanguard, ect) a. Multiple accounts with different brokerages b. Multiple types of accounts allowed at each broker 3. Categorization of investments (Large Cap, mid cap, small cap, Bonds...
Design a database for an automobile company to provide to its dealers to assist them in...
Design a database for an automobile company to provide to its dealers to assist them in maintaining customer records and dealer inventory and to assist sales staff in ordering cars. Each vehicle is identified by a vehicle identification number (VIN). Each individual vehicle is a particular model of a particular brand offered by the company (e.g., the XF is a model of the car brand Jaguar of Tata Motors). Each model can be offered with a variety of options, but...
These are all part of one question: What is Equilibrium? What would Eq look like in...
These are all part of one question: What is Equilibrium? What would Eq look like in real life? Do buyers or sellers want to get to Eq?   Why do prices change? Do price have to change or can prices stay the same? Who sets the prices in the market?   Is trade Good or Bad for the US? When a country trades, are there going to be Winners and Losers? Can the US be the winner for all transactions? Why? How?
our company is creating the database for the sales system. These are the major components: Database...
our company is creating the database for the sales system. These are the major components: Database design User entry form design Report design Access and authentication Maintenance You are assigned as an IT manager. For this Discussion, gather information from the library and research other web resources. Write a plan describing your role in designing, developing, managing, and supporting the database.
select a store and choose a site location you would like to use for the store....
select a store and choose a site location you would like to use for the store. Describe the location using the following factors: A-Where is the location? For example, I might select a Smithtown shopping center on the corners of Route 25 and 111. B-How would you describe the population density for the location. C-Describe the quality of the location as you see it. D-Using the free zip code analysis found online, describe the demographics of the area including: average...
Pitt Fitness would like to analyze their options for an in-house database versus a cloud database-as-a-service. What are the benefits to moving their database to the cloud?
Pitt Fitness would like to analyze their options for an in-house database versus a cloud database-as-a-service. What are the benefits to moving their database to the cloud? 
Below are several table schemas representing online store delivery system, with attributes and relationships: You will...
Below are several table schemas representing online store delivery system, with attributes and relationships: You will use these to answer the SQL questions 1-7. (2 marks each). Shopper (shopperNo(pk), shopperfName, shopperlName, shopperContact, shopperCity, shopperCcard); Store (storeNo(pk), storeLocation, storeSize) Review (revCode(pk), revDate, revRating, shopperNo, storeNo(fk)) Purchase (purId(pk), purDate, purTime, purQty, purValue, shopperNo(fk), storeNo(fk))) Use SQL statements to answer the following questions: Use SQL statements to create the 2 tables: Purchase and Store with the correct relationship constraints. Add 2 rows: One...
As part of a research program for a new cholesterol​ drug, a pharmaceutical company would like...
As part of a research program for a new cholesterol​ drug, a pharmaceutical company would like to investigate the relationship between the ages and LDL​ (low-density lipoprotein) cholesterol of men. The accompanying data set shows the ages and LDL cholesterol levels of seven randomly selected men. Construct a​ 95% prediction interval to estimate the LDL cholesterol level of a 28​-year-old man. Age 24 37 27 31 42 32 42 Cholesterol 141 178 160 199 158 142 205 Determine the upper...
As part of a research program for a new cholesterol​ drug, a pharmaceutical company would like...
As part of a research program for a new cholesterol​ drug, a pharmaceutical company would like to investigate the relationship between the ages and LDL​ (low-density lipoprotein) cholesterol of men. The accompanying data set shows the ages and LDL cholesterol levels of seven randomly selected men. Construct a​ 95% prediction interval to estimate the LDL cholesterol level of a 25​-year-old man. Click the icon to view the data table. Age: 22 37 26 32 41 31 40 Cholesterol: 144 177...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT