Database Systems General Knowledge

  • A primary key is a special column in a table that uniquely identifies each row (i.e. record)
  • Primary key cannot be null
  • In a student table, student admission ID could be the primary key (Last name for example may not be the primary key as it may not be unique)
  • It is a column in one table that refers to a primary key in another table
  • Let’s say we have student and marks tables. The primary key in student table is student_id. Student_id is a foreign key field in marks table. This helps us to find the student relevant to a given row of marks
  • A schema defines the structure of a relational table
  • It contains a name for the schema, field names and their respective data types
  • It is a model where data is organized into a collection of tables
  • Foreign key and primary key relationships are used to connect these tables together
  • All databases use SQL to talk to them
  • All of them maintain data in tables (has a well defined schema)
  • Each table can have a primary key, and zero or more foreign keys
  • SQLite is severless database whereas MySQL (and all other databases) are client-server databases
  • In other words, for SQLite, you don’t need to setup a database server, but for MySQL, you need to have a database server running
  • For SQLite, database is simply a file and it reside in the same location where you have your script, for example, the python code
  • If you want to have a database that can be accessed remotely, you need to go with a client-server database like MySQL, Postgresql, Oracle, etc.
  • SQLite is not suitable when many members are working on the same project and reading/writing to the database
MySQL has a client-server architecture. The client (your code or Mysql Admin interface) and database server can be in the same machine or in different machines
For SQLite, there is no database server. Both your program and the database file reside in the same machine.
  • Both are relational databases
  • Both understands SQL queries
  • Both store data in tables
  • Usage of them through a program (like Python) is very similar
  • MySQL
  • Postgresql
  • Oracle
  • MS SQL Server
  • The above mentioned databases are relational databases — they store data in tables
  • But in a NoSQL database like MongoDB, we store documents — they are usually stored in JSON format, but they don’t need to have a predefined schema like in relational databases




#probability #statistics #ML #DL #coding #security #building #digest

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Docker image commands- Part-01

Internshala buildathon — imagination & creation

How to use Thinking Sphinx in RoR

My Internship at Lakshya Solutions India

Create Gym management with Laravel 5.7

Polkadot Backend API Creation

ARTH Team task 1- Python Menu integrating AWS, Docker, LVM and Hadoop with Voice Control

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store


#probability #statistics #ML #DL #coding #security #building #digest

More from Medium

Notes on Apache Log4j2 issue, fix, lessons learned and next steps

NetSuite Map/Reduce Scripts in Retail Practices

Decision Points on Controlled Content Mapped for Ten of the S1000D Issues: From 1.9 to 5.0

Messaging System of Enterprises: Complex Network of Data Transfer