Database Flashcards
what to do if you have an entity that has many to many relationships with 2 different entities?
You would need linked tables to represent the connection of 2 different entities.
For example, if you have an entity CAR, another that is USER, and PICTURE. One CAR could have many PICTURES, but one USER could also have many PICTURES. You could be tempted to put in the PICTURE table carId and userID as columns. But that would mean that for every entity connection you would have to add a column. That is not good. It is better to keep every entity with not so many columns.
So the reasonable solution would be to have one table USER_PICTURE with userID and pictureID. And, apart from that, we would have another entity CAR_PICTURE table, with carID and pictureID.
What is the difference between text and “character varying” in PostgresSQL?
“character varying” has length. text do not. If you don’t specify length for “character varying”, it would behave as “text”.
The good thing about this is that if you try to save a row with more characters, the DB will throw an error.
What is the best type for a “year” field (like 2005, 2014, etc)?
That would be smallInt
How would you write a query with a where comparing strings?
SELECT “MakeId”, “MakeName”
FROM public.”Makes” WHERE “MakeName” like ‘For%’;
=========
using “like”, single quotes (‘ ‘)
How would you write a query checking a number that is between 2 numbers?
With the keyword BETWEEN
SELECT “MakeId”, “MakeName”
FROM public.”Makes” WHERE “MakeId” between 2 and 4
How do you check if a field is null?
with the IS NULL operator
SELECT * FROM public.”customDB” WHERE “columnPepe” IS NULL
Which is the best type for better precision for a number with decimals in Postgres?
It is DECIMAL/NUMERIC (numeric and decimal are the same for Postgres, check this). https://stackoverflow.com/questions/33730538/difference-between-decimal-and-numeric-datatype-in-psql
What is Normalization? Name the first 3 levels
Database normalization is the process of structuring a relational database in order to reduce data redundancy and improve data integrity.
Level 1
Datos indivisibles (atomic data): si es un string con la dirección es divisible, se puede dividir en calle, piso, departamento, etc
No repetición de datos por tabla
No repetir datos por fila
Level 2
Cumple la primera y para determinar una columna necesito todas las PK
Por ejemplo si las PK son DNI y ID de proyecto, esto lo cumple para cantidad de horas trabajadas de un empleado.
Ahora, si tuvieras el nombre de empleado, puedo determinarlo solamente con el DNI así q no lo cumple
Level 3
Ejemplo de algo q no cumple con la 3 es una BD de un hospital con tabla pacientes, q almacene el nombre del doctor, ese nombre debería estar en la entidad doctor
What is an index in a DB? PROS and CONS?
The DB build tree to get to the record faster.
Types of indexes in Postgres
- “non-clustered”: Postgres does not have clustered indexes. If you run create index it will create a (non-clustered) B-Tree index
- Functional Indexes: are helpful in a PostgreSQL database when the query retrieves data based on the result of a function. For example “… WHERE anuall_salary / 12 > 1000”, in this case the index could be “anuall_salary / 12”
- more types: https://www.postgresqltutorial.com/postgresql-indexes/postgresql-index-types/#:~:text=PostgreSQL%20has%20several%20index%20types,with%20different%20kinds%20of%20queries.
PROS: make the Select faster
CONS: slow down INSERT, UPDATE and DELETE.
How to write SARGABLE (Searchable) queries?
- Avoid using functions or calculations in indexed columns in the WHERE clause
- Use direct comparison if possible, instead of wrapping the column in a function
- if we need to use a function in a column, create a function-based index
Como mejorar la performance de una query SQL?
Mejorar el rendimiento de una consulta SQL es fundamental para garantizar que tu base de datos funcione de manera eficiente. Aquí tienes algunas estrategias para optimizar el rendimiento de tus consultas SQL:
- Índices eficientes:
a. Asegúrate de que las columnas utilizadas con frecuencia en cláusulas WHERE, JOIN y ORDER BY tengan índices.
b. Utiliza índices compuestos si es necesario.
c. Mantén los índices actualizados. - Escritura eficiente:
a. Minimiza las instrucciones de escritura (INSERT, UPDATE, DELETE) en consultas críticas.
b. Agrupa las instrucciones de escritura siempre que sea posible para reducir el bloqueo de tablas. - Optimización de consultas:
Usa Function Indexes cuando es necesario
Utiliza JOINs en lugar de subconsultas siempre que sea posible, ya que los JOINs suelen ser más eficientes. - Apropiada normalización y desnormalización:
Normaliza tu base de datos para evitar redundancia de datos.
Puedes desnormalizar datos selectos si las consultas frecuentes requieren un alto número de JOINs. - Partitioning:
Divide tablas grandes en particiones para dividir los datos en segmentos más manejables. - Buffering y caching:
Usa almacenamiento en caché de resultados frecuentes para reducir el tiempo de procesamiento. - Índices full-text:
Si necesitas buscar texto completo, considera la implementación de índices full-text. - Análisis de rendimiento:
Utiliza herramientas de análisis de rendimiento como EXPLAIN en MySQL o Query Execution Plan en SQL Server para identificar problemas de rendimiento en tus consultas. - Control de recursos:
Limita el número de conexiones concurrentes y las consultas en ejecución para evitar la sobrecarga del servidor de base de datos. - Hardware adecuado:
Asegúrate de que el hardware del servidor de base de datos esté dimensionado adecuadamente para satisfacer la carga de trabajo.
Optimización de almacenamiento:
Utiliza tipos de datos eficientes y evita el almacenamiento excesivo de datos no utilizados.
Actualizaciones y mantenimiento:
Mantén tu base de datos actualizada con las últimas correcciones y optimizaciones.
