State & Signal

Engineering systems that survive reality.

Built and broken enough systems to know what holds. Writing about data platforms, distributed systems, and AI in production.

Start Reading About Me

Latest writing

The newest article is featured first, followed by recent posts.

MySQL Partitioning Example

/ by 

This post is about partitioning a MySQL table by year by using range partition type. This post does not deal with the theories on partition, if you would like to understand the complete partitioning methods and types visit the official MySQL documentation. In this post we are directly focus on the implementation steps, assuming that we have knew the basics of MySQL partitioning types and its usages.

[Solved] How to write a stored procedure in MySQL for insert?

/ by 

Problem:

When multiple client applications such as web applications, desktop applications and mobile applications written in different languages like php, python, java, objective c, etc. need to perform the same database operation (insert), the same operations done by different client applications and client applications are directly accessing the database table using same SQL statement.

Solutions:

We are implementing stored procedure; here the client applications will simply call the defined stored procedures to perform the database operation (insert). In this post let us see about the syntax of the stored procedure and will see an example of insert operation using stored procedure using MySQL.

[Solved] How to solve MySQL error code: 1062 duplicate entry?

/ by 

Error Message:



Error Code: 1062. Duplicate entry ‘%s’ for key %d


Example:


Error Code: 1062. Duplicate entry ‘1’ for key ‘PRIMARY’

Possible Reason:


Case 1: Duplicate value.


The data you are trying to insert is already present in the column primary key. The primary key column is unique and it will not accept the duplicate entry.

How to write SQL query?

/ by 

We have discussed about the conceptual data model, logical data model, normalization and denormalization techniques and we discussed about installing and running up with MySQL community server.  What will be next? Once we build the data model and decided the database server, the next process will be start developing the physical database. What is needed to develop physical database? We need a common language to communicate with both the developer and database server. Here, comes the structure query language. So in this post we are going to see about the SQL (Structure Query Language). After reading this post you will be able to answer the following questions:

How to install MySQL server on windows?

/ by 

Last week I got an assignment where I need to install MySQL server on client’s machine, after checking the system requirements, I came to know that, the machine contains the 64 bit windows operating system. So I thought to download MySQL community server for windows 64 bit operating system, but I found that the website provides only 32 bit installer for windows, and for 64 bit operating system, the website provides the zip file for download. My client needs to manage the MySQL products by himself; he was expecting software where he can manage all MySQL products at one place, so we need MySQL installer in that machine. So I decided to install MySQL server on windows machine using MySQL installer web community. In this post, let me go through the step by step guide to install MySQL server on windows machine.

Database denormalization techniques

/ by 

We have seen about the normalization and different levels of normalization in previous post. A normalized database design will store the information in more number smaller and separate tables. When you need to retrieve particular information from a normalized database you need to perform join operation, when you have more number of relationships among tables for particular information, you need to perform more join operation; it may further reduce the retrieval speed of the database. Here comes the denormalization, we are violating the normalization selectively in order to reduce the data retrieval time of a database. But at the same time over denormalization also leads to redundancy and inconsistent data, so it is data modeler’s response that, after denormalization the database should not become inconsistent.

What is database normalization?

/ by 

A few years back, when I was working as junior database administrator, one of seniors given me a conceptual data model sheet and asked me to read the model and prepare a logical model from the sheet. I studied ER diagram (Entity-Relationship diagram) during my engineering course, so I must have some basic knowledge, but the given conceptual model is little different from what I studied (only the symbols are different). So I was searching in internet with queries, "what is logical data model?" and "how to convert a conceptual data model to a logical data model?". I found some pages related to what I was searching and I understood the concept, after a week somehow I submitted my newly created incorrect logical data model to my senior (it is a simple user management application).

Conceptual data model

/ by 

Conceptual data model is a map of key concepts and rules binding these key concepts for a specific business, for a particular audience, that used for databases. Key concepts are both basic and critical to your business. Basic means this term is mentioned many times a day in your organisation in conversation among business people, such as CUSTOMER, EMPLOYEE, PRODUCT, etc.Critical means the business would be very different or non-existent without this key concept. For example, without the concept PRODUCT, your business may become non-existent.

Relationships and Cardinality

/ by 

In real world, entities have relationships with other entities.  Relationships define how two entities are associated with each other. The data modeling captures the nature of relationship between two entities. For example, the entity 'Customer' buys the 'Product' and the entity 'Vendor' sells the 'Product'. It captures the rules between these two entities. If two entities are Person and Telephone Number, the relationship may capture the rule "Each Person may have many Telephone Numbers" and "Each Telephone Number must belongs to one Person." 
Subscribe to: Posts (Atom)