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
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?
What are some commercial relational databases?
- 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