Database

database schema

https://docs.google.com/spreadsheets/d/1dx3oQNYuATBNntJo5ISX7skG8VwoBQoax6h3lnEO57w/edit#gid=0

Rails supports 6 types of associations:

  • belongs_to
  • has_one
  • has_many
  • has_many :through
  • has_one :through
  • has_and_belongs_to_many

database association

belongs_to

has_many

reviews belongs_to user

user has_many review

=====================================

SQLite:

A very powerful, embedded relational database management system.

pros:

  • file based: stores the entire database as a single file, extremely portable
  • doesn't need a server: really fast
  • great for developing and testing: fast and working with one single file is relatively simple

cons:

  • no user management: it doesn't have a higher-level of concurrency
  • Only allow write operation sequentially and will lock the entire database file during writing.

SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. However, client/server database engines (such as PostgreSQL, MySQL, or Oracle) usually support a higher level of concurrency and allow multiple processes to be writing to the same database at the same time.

when to use:

single-user local applications; application that read/write files to disk directly

when no to use:

multi-user applications: multiple clients need to access the same database.

applications that need to do write operation frequently because sqlite only allows one single write operation at a time

Why is SQLite preferred over any other database management system?

Posted by

Sharad Jaiswal

SQLite is recommended to be used because of following advantages:

  • SQLite supports almost all Operating Systems.
  • It is free of cost and flexible.
  • It is very compact less than 400KiB.
  • It requires no setup or administration as it comes with zero-configuration.
  • No separate server processor system is required to operate.
  • An SQLite database can be stored in a single cross-platform disk file.
  • It is self-contained that means no external dependencies.
  • It provides easy access to API as it is written in ANSI-C.

18) Explain what is view in SQLite?

In SQLite, a view is actually a composition of a table in the form of pre-defined SQLite Query. A view can consist of all rows of a table or selected rows from one or more tables.

19) Explain what are SQLite Indexes?

SQLite indexes are special lookup tables that the database search engine use to speed up data retrieval. In simple words, it is a pointer to data in a table.

20) When Indexes should be avoided?

Indexes should be avoided when

  • Tables are small
  • Tables that changes frequently
  • Columns that are frequently manipulated or having a high number of NULL values

MySQL:

The most popular and commonly used RDBMS.

It doesn't implement the full SQL standard, but it still offers users many functionalities.

pros:

  • easy to use: easy install, has GUI
  • can handle lots of data
  • Because it doesn't implement the full SQL standard, it works very efficiently

cons

  • Because it doesn't implement the full SQL standard, it has some functional limitations.

  • Since MySQL does not [try to] implement the full SQL standard, this tool is not completely SQL compliant.

PostgreSQL:

The most advanced, SQL-compliant and open-source objective-RDBMS.

pros:

  • data integrity: it can offer better reliability, data integrity and ACID compliance.
  • more acid compliant and sql compliant

ACID: (Atomicity, Consistency, Isolation, Durability)

cons:

  • performance: If the applications do simple read operation frequently, MySQL can do a better job.
  • complicated to use

when to use:

  • if design a complex database scheme, PostgreSQL offers more functionalities and still ACID compliant.

when not to use:

  • if read-heavy applications
  • not easy to setup and learn to use

reference:

https://www.digitalocean.com/community/tutorials/sqlite-vs-mysql-vs-postgresql-a-comparison-of-relational-database-management-systems

https://www.2ndquadrant.com/en/postgresql/postgresql-vs-mysql/

https://www.upwork.com/hiring/data/postgresql-vs-mysql/

Distinguish between SQLite and SQL?

SQLite:

  • It is a well built, embedded relational database management system that is mostly used in mobile devices for data storage.
  • It is file based and does not support stored procedures

SQL:

  • It is a structured query language
  • It is server based and supports stored procedures

results matching ""

    No results matching ""