Harddisk drive vs RAM - a comparison

Harddisk drive and Random access memory / Why RAM is considered as fastest when compared to hard disk? / Harddisk vs RAM / Computer performance and storage devices

Harddisk drive vs RAM - a comparison

	Hard Drive	RAM
Size	Large sizes Internal drives – Max 8 TB for desktops External drives – Max 8 TB	Small sizes Max 32 GB (as per the year 2015 for desktop computers)
Storage	Permanent (Non-volatile) Do not lose data due to power loss (Switched off).	Temporary (Volatile) Lose all data due to power loss (Switched off).
Time taken to read files	Vary for files. Depends on the size and location of a file.	Fixed for files. We are able to pick the data directly as per the size.
Units of storage	Bytes	Bytes
Requires power to retain content	No Hard drives retain contents as the writing surfaces are magnetic	Yes RAM cannot retain the content due to power loss is because of RAM is made up of circuits.
Made up of	Magnetic components	IC (Integrated Circuits) components
Storage capacity vs performance	No performance improvement. Increase in the size of the hard disk, say, from 500 GB to 1 TB would not show any performance improvements.	Performance changes. Increase in the size of RAM, say, from 4 GB to 8 GB will definitely show some improvements in performance.
How the files are stored?	Magnetically	Electronically
Flexibility in space	Flexible. We can delete unwanted files if we need more space (on the same disk)	Not flexible. If any program does not function due to less memory size, you need to upgrade your memory to make that program work.
Hardware dependency	Works on 32 bit and 64 bit computers	Upto 4 GB – supported by 32 bit computers Upto and above 4 GB – supported by 64 bit computers

SQL Exercise 5

SQL Exercises for Beginners / Simple SQL Exercises with Answers / SQL Exercises for simple Table creation and SELECT queries / Solved SQL exercises

Consider a relation REPAYMENT with the following schema;

REPAYMENT(BORROWER_ID, NAME, ADDRESS, LOANAMOUNT, REQUESTDATE, REPAYMENT_DATE, REPAYMENT_AMOUNT)

Assume that this table records the repayment of loans by the borrowers. A borrower may have multiple entries if he/she has paid multiple installments.

Write SQL statements (queries) to achieve the following;

Question (a)

Find all the records with information on repayments from the borrower with id equal to 42, and where the lent amount exceeds 1000.

Answer (a)

SELECT *

FROM Repayment

WHERE borrower_id=42 AND loanamount>1000;

Question (b)

Find the total amount repaid for every address in the repayment table.

Answer (b)

SELECT address, SUM(repayment_amount)

FROM Repayment

GROUP BY address;

Question (c)

Delete all information on the completed loans. (Note: you can find the status of the loan by summing the total repaid amount. If the total repaid amount is equal to the loan amount, then you would say that the loan is ended.)

Answer (c)

DELETE FROM Repayment A

WHERE loanamount=

(SELECT SUM(repayment_amount)

FROM Repayment B

WHERE B.borrower_id=A.borrower_id AND B.requestdate=A.requestdate);

Question (d)

Find all the borrower names who has unique address. (ie., you should not count the borrowers who are from the same address)

Answer (d)

SELECT name

FROM Repayment A

WHERE 1=

(SELECT COUNT(DISTINCT name)

FROM Repayment B

WHERE A.address=B.address);

Question (e)

Find the total number of repayments made by every borrower.

Answer (e)

SELECT borrower_id, count(*)

FROM repayment

GROUP BY borrower_id;

*******************

How to choose indexing or hashing technique?

How to choose indexing or hashing technique? / Points to consider in choosing an index for your application / Evaluation of different structures / When to go for indexing or hashing?

How to choose an indexing or hashing technique?

No one index is best for indexing in database. It may vary for different database applications. Hence, choosing a technique is purely based on the database application in question that is going to use the said index. These indexing techniques can be evaluated for the required application on the basis of the following points;

• Access type – it is the types of access that are supported by the index. Some applications may need to find the records with a particular search key value, or a range of values. You may choose the index based on your requirement.

• Access time – it is the time to find items by a certain type of index. The time to search and find the record in question may differ for different applications for different indexes. 
• For example, if your application requires finding a single record with the primary key value as the search key, then ordered index would be a best choice for you.

• Insertion time – the time consumed to insert a record in a data file and also the time required to update the index file. 
• For example, let us assume that you insert a new record into your table and the search key value of your record does not exist in the index table. Then the time to insert a record would be,

• The time to insert a record into your data file + the time to insert an appropriate index entry.

• Deletion time – Same as above. The time to delete a record entry from the data file as well as the index file (if necessary). Unlike insertion, it needs to consider the time to locate (search) the record that is to be deleted as an extra time.

• Space overhead – Additional space required to handle index. 
• For example, if you like to use the index to locate your records easily, you have to load the index file into your memory first. It occupies some amount of space based on the size of the index file. Also, in some cases we may need to load more than one index file based on the application requirement.