Database Systems General Knowledge

What is a primary key?

• 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)

What is a foreign key?

• 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

What is a schema?

• A schema defines the structure of a relational table
• It contains a name for the schema, field names and their respective data types

What does relational in relational databases mean?

• 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

What is common between all relational databases?

• 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

What is the difference between SQLite and MySQL?

• 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.

What are some similarities between SQLite and MySQL?

• Both are relational databases
• Both understands SQL queries
• Both store data in tables
• Usage of them through a program (like Python) is very similar

What are some open source relational databases?

• MySQL
• Postgresql

What are some commercial relational databases?

• Oracle
• MS SQL Server

What are NoSQL databases?

• 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