How Virtual Memory Works?

  • Each programs has its on virtual address space and Map(Translator). Map maps to the physical address either in RAM or Disk.
  • When address is mapped in Disk, it is first loaded to RAM, also mapping table is updated and then data is loaded.
  • This Map is called Page Table and the entry in it is called Page Table Entry.
  • WHY WE CALL IT PAGE?
    • Well, if, for each virtual address an entry is used, the size of Page Table alone would be large. Also, note that for each program we have a Page Table.
    • So, each entry instead of handling each V. address, handles chunk of V. addresses(range) and maps that to chunk of P. addresses(range).
    • The point above resolves the problem of large memory usage.
    • We typically have 4kB of pages(1024 words).
    • Bigger the page size, moving from the disk would take more time. Lesser the page size, more memory is needed to store page table.
  • A V. address is divided in V. Page number and Page offset. V. Page number is mapped to P. page address and page offset just concatenates with it.Screenshot from 2016-06-22 22-13-50

DbMS: SQL Keys

  • Super Key
  • Candidate Key
  • Primary Key
  • Natural Key/Business Key/Domain Key
  • Surrogate Key
  • Unique Key
  • Foreign Key
  • Secondary Key
  • Simple Key

Super Key: Super key is the set of attributes in a table that can identify a row uniquely.

Candidate Key: Candidate key is minimal super key. If any of the attribute, in case of two or more attributes, is removed from the candidate key then uniqueness breaks. N0 candidate key can be NULL. A table can have a multiple candidate keys.

Primary Key: It is one of the candidate keys. Primary key cannot be NULL.

Natural Key:  Natural key is a key composed of columns that actually have a logical relationship to other columns within a table. For example, in case of database of a society, Name, Address is a natural key.

Surrogate Key: Surrogate means substitute, in case natural key is not a good choice, surrogate key is used. Usually column(s) are added for uniqueness of a tuple. For example an ID is provided to every row and this ID is used as primary key. Opposite to natural key, surrogate key does not have logical relationship to other values in a tuple.

Unique Key: (Confused) It is a candidate key. Used to prevent duplicate values in a column. It can be NULL. Multiple unique keys can exist. Unique values can be deleted.

Foreign Key: In the context of relational databases, a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table.In simpler words, the foreign key is defined in a second table, but it refers to the primary key in the first table. Foreign key enforces referential integrity:

  • Update to a table having a foreign can happen if the corresponding key exist in the other table.
  • Cascade Delete
  • Cascade Update

Secondary Key: Candidate key(s), not selected as primary key is/are secondary key.

Simple Key: Candidate key with a single attribute is a simple key.

DbMS: Joins

Joins are used to merge data based on predicate logic, of two tables that results in a temporary table;

Types:

  1. Outer Joins
    1. Left Outer Join
    2. Right Outer Join
    3. Full Outer Join
  2. Inner Join

Consider tables for examples:

Employee Location
EmpID EmpName
13 Jason
8 Alex
3 Ram
17 Babu
25 Johnson
EmpID EmpLoc
13 San Jose
8 Los Angeles
3 Pune, India
17 Chennai, India
39 Bangalore, India

Outer Join: Iterates over the all rows of both or one of the tables.

In left outer join, according to the query, it iterates over the key of left table provided in SQL query and matches the same in the right one. Tables will result in:

SQL:
select * from employee left outer join location on employee.empID = location.empID; OR
select * from employee left join location on employee.empID = location.empID;

Employee.EmpID Employee.EmpName Location.EmpID Location.EmpLoc
13 Jason 13 San Jose
8 Alex 8 Los Angeles
3 Ram 3 Pune, India
17 Babu 17 Chennai, India
25 Johnson NULL NULL

In right outer join, same happens for the right table:

SQL:
select * from employee right outer join location on employee.empID = location.empID; OR
select * from employee right join location on employee.empID = location.empID;

Employee.EmpID Employee.EmpName Location.EmpID Location.EmpLoc
13 Jason 13 San Jose
8 Alex 8 Los Angeles
3 Ram 3 Pune, India
17 Babu 17 Chennai, India
NULL NULL 39 Bangalore, India

Inner Join: Here if match happens only then join happens.

SQL:
select * from employee inner join location on employee.empID = location.empID; OR
select * from employee, location where employee.empID = location.empID;

Employee.EmpID Employee.EmpName Location.EmpID Location.EmpLoc
13 Jason 13 San Jose
8 Alex 8 Los Angeles
3 Ram 3 Pune, India
17 Babu 17 Chennai, India

 

Souirce: http://www.programmerinterview.com