Samples of File Organization in DBMS: Relations in Files

[momentum]

There is no sample file given in DBMS file organization chapter in my book.

1. Storing each relation in separate file

2. Storing many relation in one file.

Can you please provide a sample of these files. How they look ?

 

[jedishrfu]

The nearest thing I can think of is a csv file

Each line represents a row

And each comma separated field represents a column

You would use some dB utility program to load a table in the database.

The next thing might be a sequence sql statements to create the table and insert rows into the table.

And the last would a db utility that would save and restore your dB in case of a system crash.

 

[momentum]

The nearest thing I can think of is a csv file

Each line represents a row

And each comma separated field represents a column

okay ...thanks ... But this did not answer to my query. Could you please elaborate on these two parts below.

(We are not talking about SQL and database inserts. We are talking about maintaining relations in files)

1. Storing each relation in a separate file - Does it mean CSV file would contain only one row in file?

2. Storing many relations in one file. - what they mean by storing many relations in one file ? how?

I'm stuck right at this spot.

 

[PeterDonis]

There is no sample file given in DBMS file organization chapter in my book.

Is your book about a specific DBMS? Or just general DBMS principles? Any sample file would be specific to a particular DBMS; there is no general format that applies to all of them.

 

[momentum]

Is your book about a specific DBMS? Or just general DBMS principles? Any sample file would be specific to a particular DBMS; there is no general format that applies to all of them.

its general DBMS principle.
Could you please reply to my above query ?

 

[PeterDonis]

its general DBMS principle.

Then I don't see how the book can give you any sample file or information about how relations are stored in files, since that is going to be specific to each particular DBMS.

Could you please reply to my above query ?

I don't see how there is any response possible beyond what I've already stated. If your book refers to any particular DBMS, you could look in the manual for that DBMS to see how it stores relations in files.

 

[momentum]

Then I don't see how the book can give you any sample file or information about how relations are stored in files, since that is going to be specific to each particular DBMS.
I don't see how there is any response possible beyond what I've already stated. If your book refers to any particular DBMS, you could look in the manual for that DBMS to see how it stores relations in files.

I'm talking about this: https://prnt.sc/l5uoze
I don't want to know details of how they store...But does Storing each relation in a separate file mean ..one record in one file ?

 

[PeterDonis]

does Storing each relation in a separate file mean ..one record in one file ?

I don't know. That would seem to be what it is saying, but since no specific DBMS is referenced, I don't see how you could know for sure.

Is this for a class? Have you asked the professor or teaching assistant? Or have you tried looking on the Internet for the manuals for particular database systems to see how they store relations? There is a lot of info on the major DBMSs online.

 

[anorlunda]

Storing each relation in a separate file

That could mean storing the metadata, not the actual data. That statement is not enough information to explain what they are talking about.

 

[jedishrfu]

Okay I see now. They are talking about database implementations. This varies from database engine to database engine.

Small ones like H2 database utilize the file system to store parts of their database. As an example, blob fields in a row would be stored in a separate file for each row and each blob field. This allows the searchable fields of a table table if not too large to fit into memory while the blob data is stored elsewhere. It’s for speed mostly.

In contrast some other dbms would use one file for everything ie all schemas, all tables are stored in one random access master file whose design is specific to that vendor. It’s something they have optimized to work well across multiple OS platoforms and is more independent of the OS file system.

One limitation of the approach is the file size limit like windows has a 2GB file size. They way they store everything is not normally shown to users as it unnecessary to running your queries.

There’s also the notion of logical database and physical database. Logical ones express your design in a straightforward way whereas the physical one deals with how that is actually mapped to the file system. One table might be spread across multiple files or the keys might be in one file and referenced data in several files.

Think of how zip files are organized or tar files or docker images. All use their custom schemes.

 

[jedishrfu]

To be clear the physical database design is still above the layer of how they actually implement things which is vendor specific.