• Sql

    Normalization

    https://vertabelo.com/blog/how-to-remember-database-normal-forms/ Normal Forms: A Recap The key, the whole key, and nothing but the key, so help me Codd. The key stands for 1NF: all tables must have a (primary) key because all records in the table must be unique.The whole key stands for 2NF: no functional dependencies on part of the key are allowed.Nothing but the key (attribute) stands for 3NF: no functional dependencies between non-key attributes are allowed.Nothing but the key stands for BCNF: only functional dependencies out of candidate keys are allowed. “2NF/3NF: Every non-key attribute isdependent on the key, the whole key, and nothing but the key—so help me Codd.” Example: The db from… Database diagram…

    Comments Off on Normalization
  • azure,  mysql,  Sql,  Zabbix

    Zabbix on VM remote MySql (Na market)

    Lets continue and install Zabbix on a VM and MySql on another: BasicsSubscriptionFree TrialResource groupzabbix-rgVirtual machine namevm-zabbixRegionWest USAvailability optionsNo infrastructure redundancy requiredImageUbuntu Server 18.04 LTS – Gen1SizeStandard DS1 v2 (1 vcpu, 3.5 GiB memory)Authentication typePasswordUsernameespentestPublic inbound portsSSHAzure SpotNoDisksOS disk typeStandard HDDUse managed disksYesUse ephemeral OS diskNoNetworkingVirtual network(new) zabbix-rg-vnetSubnet(new) default (10.0.0.0/24)Public IP(new) vm-zabbix-ipAccelerated networkingOffPlace this virtual machine behind an existing load balancing solution?NoManagementBoot diagnosticsOnOS guest diagnosticsOffAzure Security CenterBasic (free)System assigned managed identityOffAuto-shutdownOffBackupDisabledPatch installationOS-orchestrated patching: patches will be installed by OSAdvancedExtensionsNoneCloud initNoProximity placement groupNoneTagsEnvironmentTest (Auto-shutdown schedule)EnvironmentTest (Availability set)EnvironmentTest (Disk)EnvironmentTest (Network interface)EnvironmentTest (Network security group)EnvironmentTest (Public IP address)EnvironmentTest (Recovery Services vault)EnvironmentTest (SSH key)EnvironmentTest (Storage account)EnvironmentTest (Virtual machine)EnvironmentTest (Virtual machine extension)EnvironmentTest (Virtual network) Ok…

    Comments Off on Zabbix on VM remote MySql (Na market)
  • Sql

    OD20761C – Querying Data with Transact-SQL

    Database diagram used for some learning (Bikestore) The rest is here: https://github.com/spawnmarvel/t-sql https://github.com/spawnmarvel/t-sql In MSSM, on every object there it a property options, if you do some changes, you can press the script before the change is applied to get the SQL used. Run just portions of the query, mark the section and execute: docs microsoft sql server https://docs.microsoft.com/nb-no/sql/?view=sql-server-ver15 CASE expression https://github.com/spawnmarvel/t-sql/blob/master/module3.sql Joins https://github.com/spawnmarvel/t-sql/blob/master/module4_multiple_tables.sql INNER JOIN (Get all that matches on both tables) SELF JOIN, related to get the manger Change schema: Right-click on the specific table name and choose Design optionRight-click on the window->propertiesAnd we are there (after the change, save solution and disconnect-connect. Connected again: View all functions…

    Comments Off on OD20761C – Querying Data with Transact-SQL
  • Sql

    SQL Server Data Manipulation

    Version used here SQL Server Management Studio 15.0.18206.04 The following illustrates the BikeStores database diagram: 5 Load Sample Database After that data is loaded, you can make your own diagram, navigate like this: Go to Sql Server Management Studio >Object Explorer >Databases >Choose and expand your Database.Under your database right click on “Database Diagrams” and select “New Database Diagram”.It will a open a new window. Choose tables to include in ER-Diagram (to select multiple tables press “ctrl” or “shift” button and select tables).Click add.Wait for it to complete. Done!! BikeStores Sample Database – create objects.sql – this file is for creating database objects including schemas and tables.BikeStores Sample Database –…

    Comments Off on SQL Server Data Manipulation
  • Sql

    MS SQL Server 2016 Express with SSMS

    Download MS SQL Server 2016 Express: https://www.microsoft.com/en-us/download/confirmation.aspx?id=56840 Install also the SSMS tool from the install, or download it from MS. https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15 SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure, from SQL Server to Azure SQL Database. SSMS provides tools to configure, monitor, and administer instances of SQL Server and databases. Use SSMS to deploy, monitor, and upgrade the data-tier components used by your applications, and build queries and scripts. Use SSMS to query, design, and manage your databases and data warehouses, wherever they are – on your local computer, or in the cloud. SSMS is free! Connect: .\SQLEXPRESS or the below Create a autoincrement,…

    Comments Off on MS SQL Server 2016 Express with SSMS
  • linux,  Python,  Sql

    MariaDb linux Python ORM

    https://linuxize.com/post/how-to-install-mariadb-on-ubuntu-18-04/ MariaDB is an open source, multi-threaded relational database management system, backward compatible replacement for MySQL. It is maintained and developed by the MariaDB Foundation including some of the original developers of the MySQL. https://linuxize.com/post/how-to-create-a-sudo-user-on-ubuntu/ sudo apt update sudo apt install mariadb-server sudo mysql -V sudo systemctl status mariadb Run the mysql_secure_installation command to improve the security of the MariaDB installation: sudo mysql_secure_installation The script will prompt you to set up the root user password, remove the anonymous user, restrict root user access to the local machine and remove the test database. At the end the script will reload the privilege tables ensuring that all changes take effect immediately. All…

    Comments Off on MariaDb linux Python ORM
  • Sql

    8 Ways to Fine-tune your SQL Queries (for production databases)

    ‘Sanitization’ is the removal of malicious data from user input, such as form submissions or maybe more simply… The cleaning of user input to avoid code-conflicts (duplicate ids for instance), security issues (xss codes etc), or other issues that might arise from non-standardized input & human error/deviance. Define Business Requirements before Beginning Write very specific requirements and confirm them with stakeholders. The performance of the production database is too critical to have unclear or ambiguous requirements. Make sure the requirements are as specific as possible, and confirm the requirements with all stakeholders before running the query. Define SELECT Fields instead of SELECT * Inefficient: SELECT *FROM Customers Efficient: SELECT FirstName,…

    Comments Off on 8 Ways to Fine-tune your SQL Queries (for production databases)
  • Python,  Sql

    PostgreSql jsonb

    https://www.postgresql.org/docs/9.4/datatype-json.html JSON data types are for storing JSON (JavaScript Object Notation) data, as specified in RFC 7159. Such data can also be stored as text, but the JSON data types have the advantage of enforcing that each stored value is valid according to the JSON rules. There are also assorted JSON-specific functions and operators available for data stored in these data types; see Section 9.15. There are two JSON data types: json and jsonb. They accept almost identical sets of values as input. The major practical difference is one of efficiency. The json data type stores an exact copy of the input text, which processing functions must reparse on each…

    Comments Off on PostgreSql jsonb
  • Django,  Github,  Sql

    Django database API migrations and models 101

    Django API Once you’ve created your data models, Django automatically gives you a database-abstraction API that lets you create, retrieve, update and delete objects. This document explains how to use this API. Refer to the data model reference for full details of all the various model lookup options. https://docs.djangoproject.com/en/2.0/topics/db/queries/ So our testmodel is a simple Note where we store text from the user. This is our base for e-lo on web. We feed e-lo some text and select a of several NLP functions that will be implemented later (Some of them are implemented, but must interfaced with Django, now they are interfaced with class cmd.Cmd([completekey[, stdin[, stdout]]]).

    Comments Off on Django database API migrations and models 101
  • Grafana,  Sql

    Grafana with Postgresql and MS SQL (2020)

    Grafana https://grafana.com/ The open platform for beautifulanalytics and monitoring.The leading open source software for time series analytics I used:4.6.0-beta2, the reason for that was to test Postgresql data source function that just came out for about 6-12 months ago. https://grafana.com/grafana/download/4.6.0-beta2?platform=windows Getting started http://docs.grafana.org/guides/getting_started/ Start Grafana: Navigate to http://localhost:3000 Grafana Dashboard: In PostgreSql, make a table with the fields you want, insert some data: Grafana data source Add Db, type, table, port, user, password, etc: Grafana Graph Make a panel, and edit it: Connect and make the query Use timeseries: All the updates in the tags table will be reflected in the graph: #Updated post 26.09.2020 SQL SERVER in windows Installing…

    Comments Off on Grafana with Postgresql and MS SQL (2020)