0

Lets suppose that we have a database with three tables:

R(A',B,C): 100.000 records, r=100 bytes/record, bfr(R)=40 records/block, B(R)=2.500 blocks

S(B',D,E): 1.000 records, r=200 bytes/record, bfr(S)= 20 records/block, B(S)=50 blocks

T(C'F,G): 1.000 records, r=200 bytes/record, bfr(T)= 20 records/block, B(T)=50 blocks

R:Heap-organized

S:Sequential Organized

T:Sequential Organized

No indexes are available!

Question:

How can I calculate the cost for checking the consistency of the primary key and foreign key in table R for an INSERT command? Will I simply have to read the table 'till the end with

COST1 = B(R) Primary Key Consistency Constraint

COST2 = B(S)/2 Foreign Key Consistency Constraint

or is there any other way the system could check that itself?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
HelloIT
  • 172
  • 2
  • 22
  • 1
    What's your DBMS? – rory.ap Jan 23 '17 at 16:30
  • It's a general question on Database Management Theory for Academic purposes! – HelloIT Jan 23 '17 at 16:32
  • 1
    Calculating query costs depends on the DBMS.... – rory.ap Jan 23 '17 at 16:32
  • Ok let's suppose that my DBMS is PostgreSQL but I don't think this answer the question because it's a generalized question knowing that in: Heap Organized Tables: Insert Cost is 2(1 Read + 1 Write) Searching Cost is B or B/2(on primary key) and for Sequential Organized Tables: Insert Cost is logB+3 (1 Read+2 Writes) Searching Cost logB or logB+1 (for the overflow case) – HelloIT Jan 23 '17 at 16:41
  • 1
    @HelloIT And what about partition elimination, columnstore, indexed/materialized views, statistics, etc which can be used to eliminate reads based on simple conditions. First of all, it depends on RDBMS, index structures, fragmentation, index page fill factor, etc. Also keep in mind, that physical and logical reads are different and you have to take the query and other caches into account. – Pred Jan 23 '17 at 16:49
  • Well it's an old past paper from an examination test which given the data included in the question asks the following 1. Calculate the amount of the tables stored in the disk, in blocks 2. Calculate the cost for executing some Select Statements 3. Calculate the cost for executing two insert statements which pinpoints that insertion cost should include the consistency and validity of primary and foreign key. – HelloIT Jan 23 '17 at 16:54
  • In this case you may want to restructure/rewrite your question to include this info. Also, you may want to consider to ask it on [dba.se] or on [Math](http://math.stackexchange.com/) – Pred Jan 23 '17 at 16:56
  • Ok I will post the question on Database Administrators! – HelloIT Jan 23 '17 at 16:59

0 Answers0