Categories
Sotfware & Developers & DevOps Tools & How-Tos

Normalization of SQL Database: Remove Redundancy

Normalization of database is an essential skill to know as a database designer and developer. I still remember being intimidated by what it would bring on– back when I took a SQL database course. It was scary hearing about it. Let me assure you: it is easier than you might imagine. What is important though, is that you understand the structure of a database quite well before beginning with this article. The result of a normalized database is an accessible retrieval of desired or suitable attributes by removing redundancy. However, take care as it can backfire if care is not taken. For this article, you will first be introduced to some key basic concepts that are fundamental to your understanding of normalization. One such concept is Functional Dependency.

DB normalization

Functional Dependency

Functional Dependency describes the relationship between different attributes and it is a property of schema– intension.. It’s a crucial concept to understand before proceeding with normalization. It has a one-to-one relationship between the attributes, and it holds for all instances.

“If A and B are attributes of relation R, B is functionally dependent on A (denoted A → B), if each value of A is associated with exactly one value of B.”

Chapter 14.

The attribute(s) on the left side are the determinants.

For two attributes– employee id and employee name– we may conclude that employee id functionally determines employee name, if the sample represents all instances of that data.

Transitive Dependency

Transitive Dependency relies on more than two attributes to existing. In other words, transitive dependency exists only if there are more than two attributes. Then, one attribute indirectly functionally determines another attribute– using a common attribute. The formal definition is:

A condition where A, B, and C are attributes of a relation such that dependency if A → B and B → C, then C is transitively dependent on A via B.

Full functional dependency – Excelling Paper.

In other words, if employee id functionally determines both branch number and branch address, it would be an issue. Why?

The short explanation is that branch address does not directly depend on the emp id in any way, so we cannot associate it as functionally dependent on emp id. Therefore, a better solution is to create a new relationship where branch id functionally determines branch address.

Introduction to SQL Normalization: Removing Redundancy

Normalization refers to a process for improving data integrity and minimizing redundancy. It provides us with a desired set of relations. The main goal is:

  • minimize redundancy
  • eliminate undesirable characteristics like Insertion, Update and Deletion Anomalies in SQL databases.

There are five forms of normalization– six to be exact.

However, we mostly normalize our data up to BCNF only.

Don’t worry if you don’t understand these terms yet; you will know everything by the end of this article.

First Normal Form: Remove Redundancy

First Normal Form refers to removing duplicate values. As such, the goal is to ensure that every intersection of row and column contains only one data entry.

One approach is to create a new relation for repeating groups(columns with multiple values for each instance); the other one is to fill in the missing information on non-repeating groups.

Second Normal Form: Remove Partial Dependency

For a relation to be in 2NF, it must have no partial dependencies. A partial dependency violates a condition of Functional Dependency– the minimal number of attributes necessary to maintain the relationship.

Copy the partially dependent attributes along with the primary key into a new relation.

Third Normal Form: Remove Transitive Dependency

The aim of 3NF is to remove transitive dependencies. A relation in Third Normal Form is also in 2NF and 1NF. This highlights the hierarchical structure of SQL Normalization. Moreover, a relation in 3NF has attributes that are not transitively dependent on each other.

Remove the transitively dependent attributes by copying of the primary key to a new relation.

Boyce Codd Normal Form

Boyce Codd Normal Form, or BCNF, is an improvement on Third Normal Form. Consequently, it adds to the condition of the Third Normal Form. Every determinant must be a candidate key for a table in BCNF.

Fourth Normal Form

The Fourth Normal Form consists of dealing with Multivalued Dependency. In other words, it exists when an A has a set of values for B and C.

Fifth Normal Form: Remove Join Dependency

A table is said to be in the Fifth Normal Form if it is without join dependency.

Example:

normalization of database table
Sample table to normalize

Attached above is a sample table of data that we will try to normalize.

For such examples, the key is to spend a few minutes reading and understanding the table and data requirements. It is key for any progress in real-life situations.

Remember: Functional Dependency is a one-to-one relationship that holds for all data instances with a few dependent attributes.

Using this example, I have broken down the normalization process in a SQL database in ten steps. I have made it so that it is easier for you to follow little steps rather than skimming over some details. Hopefully– as this is my aim–, you will be able to understand the way of going about for such a problem in SQL. Therefore, following these steps below will allow you to fundamentally proceed with solving SQL Normalization problems for your use-cases.

10 Steps For Normalization Problems in SQL Database

First Step: Find Functional Dependencies

Firstly, find the functional dependences. This is important information regarding such an example. After that, you can better understand the problem. As explained above, quite a few standard forms are based on violating a kind of Functional Dependency.

It’s essential to understand the data:

  • Functional Dependency 1: National Identity Number (NIN) determines the Employee Name.
  • FD 2: Moreover, NIN and Contract Number, combined, determine the number of hours.
  • FD 3: Furthermore, the Contract Number determines both the House Number and House Location.

How do you figure this information out? Try and see what remains the same for an attribute for a duplicate value. For instance, Contract Number C1024 repeats twice, while both the House Number and House Location remain the same.

  • Functional Dependency 4: Lastly, House Number determines House Location.

Second Step: Determine Primary Keys

After that, it’s the ideal time to find out the Primary Key(s) for this relation. An easy way to do this is to see if a single attribute functionally determines the remaining attributes. If not, then try different combinations that determine the remaining attributes. For example, NIN and Contract Number form a Composite Primary Key above.

functional dependency and first normal form normalization

Third Step: Check for Unnormalized Form and First Normal Form

Moreover, check if this relation is is Unnormalized Form or 1NF in the next step,

It’s quite easy to determine: each row and column intersection contains only one data instance.

Therefore, to conclude, this relation is already in the First Normal Form.

Note: Is it clear how to establish if a table or relation is already in 1NF? Will you be able to do it on your own following these key points?

Fourth Step: Is Second Normal Form Applicable?

Firstly, does Second Normal Form apply to this relation? Yes, because there is a composite Primary Key. Is the existing relation also in Second Normal Form? Well, no.

Let’s explore why. The way to do this is to look for partial dependencies, i.e. if a non-primary key is wholly dependent on a composite primary key or not.

Hence, in this example, it’s clear that FD 2 and 3 violate this. In FD 2, a partial primary key (NIN) determines a non-primary key (Employee Name).

Similarly, in FD 3, the partial primary key (Contract Number) determines two non-primary keys (House Number and House Location).

Therefore, we must normalize this table upto the 2NF.

how to create 2NF or Second Normal Form?

Sixth Step: Normalization of 1NF to 2NF

How do you get this relation or table in 2NF? To normalize the relation into Second Normal Form requires the creation of two new relations.

This means that the non-primary-key attributes are removed along with a copy of the part of the primary key that fully functionally determines them. The number of tables in our database have increased.

Seventh Step: Check if Third Normal Form Applicable

Great! You have made excellent progress already.

Now… Ask yourself: Is the relation in 3NF? Not quite.

Why? Transitive Dependency exists in this table. FD 4 is an example as House Location is transitively dependent on Contact Number.

3NF or Third Normal Form which is final form for this example of normalization

Eight Step: Normalization of 2NF to 3NF

To transform this relation into 3NF, create a new relation– so that no non-primary key is transitively dependent on the primary key.

Therefore, a new relation with House Number and House Location is created. As such, our number of tables have increased again.

Note: Can you see how the increasing number of tables might be a problem? The number of joins will increase. Therefore, the time taken for a query to run may be longer. This might not be a major issue for simple databases with limited data, but it is a massive one complex databases.

Ninth Step: Check if BCNF Applicable

Check: does Boyce-Codd Normal Form need to apply here? No, because no non-candidate key is a determinant.

Hence, this relation is already in BCNF. Great news, right? Yes! After that, there’s only more check or step to make.

Tenth Step: 4NF and 5NF

This relation is also in Fourth Normal Form as there are no non-trivial multivalued dependencies. Furthermore, there is no join dependency so it is also in Fifth Normal Form.

That’s all! You made it to the end! In conclusion, you have taken a problem and normalized it.

Conclusion:

In conclusion, the steps listed above will help you solve any real-life example. Similarly, this example helps demonstrate the technique or way of solving such a question effectively. Moreover, I hope you understood that a complex problem can also still be solved using this same architecture of steps. Don’t be fearful of such problems; stick to what you know– the basics. I also want to ensure that your learning process is enhanced by breaking it down into simpler terms. At the end, the crux still remains same.

Can you now answer the question: “What is Normalization?”. If the answer is yes, then well done– you made it! If not, don’t worry, re-read the post and check another reference material. It’s okay if you’re struggling. It’s a big ask to grasp suddenly. Only with practice and implementation can you get better.

I hope you liked this post. Please drop a like or comment below with your thoughts. Feel free to let me know below if something was unclear or if you want help with a matter in the chat box below. Check more of our articles if you are interested in topics like SQL Database Flavors or CRUD operations in Python.

Stay safe. Ciao!

Hits: 13

Categories
Cloud NiHao Cloud Documentation Sotfware & Developers & DevOps Storage

Top Chinese Cloud Storage Providers

The regulatory landscape for Chinese Cloud Storage and cloud computing providers

China’s economy is one of the biggest in the world and it has numerous enterprises operating all across different industries. Chinese Cloud storage providers allow these businesses to satisfy their application development, deployment and administration need with a service that provides them at an affordable price point – this is why China continues on as a hub for innovation! 😎

While the globe has a variety of recognized cloud computing and storage service providers, the Chinese market offers unique alternatives. Numerous Chinese enterprises and the Chinese subsidiaries of global cloud service providers also supply cutting-edge technologies.

Cloud infrastructure

China’s cloud infrastructure services continue to outperform the rest of the globe, owing to the government’s strategic investment in cloud computing.

Before looking into the ten most popular Chinese cloud storage services, it’s critical to understand the restrictions and licenses that all cloud-providing services in China must obtain.

Regulatory Framework for Cloud Computing and Chinese Cloud Storage providers

If you host a website in Mainland China, Chinese law mandates that you acquire and show an ICP (Internet Content Provider) License.

If you do not acquire this license, or if it is discovered that you are running a website in violation of the certification requirements, you risk being officially compelled to obtain one by China’s Ministry of Industry and Information Technology (MIIT) or your hosting provider. Furthermore, if you do not cooperate with their request, Government can take down your site without warning.

Before you can establish or host a website in China, you must first apply for an ICP license in the country. Now, in the cloud computing and information storage market the regulations are even more strict. For instance purely foreign companies are can not participate. As a direct consequence Companies like Google and dropbox are not offering their Cloud Storage products to the Chinese market and they are currently unaccessible in China.

How it applies to Chinese Cloud Storage

The Chinese Ministry of Industry and Information Technology administers the ICP registration and application processes. This is a Chinese government branch that oversees Chinese cloud storage providers’ initiatives (among others). After obtaining an ICP license, you must include the ICP China registration number or ICP License number in the footer of your website.

The Chinese Government will restrict Chinese websites based in China that do not have a valid ICP License. This will also apply if do not show their ICP number prominently on their website.

Requirements and Required Information

To qualify for ICP Filing, you must first register a corporate headquarters in China. This can be a subsidiary of your firm or a reputable sales partner to which the Chinese website is registered. Following that, you’ll need the following information:

  • Your Chinese company’s name, address, and telephone number
  • A Chinese verification document
  • Legal representative of the Chinese company’s name, mobile phone number, e-mail address, and sufficient proof of identification (does not necessarily have to be a Chinese citizen)
  • The person responsible for the website’s name, telephone number, cell phone number, e-mail address, and valid proof of identification (must be a Chinese citizen)
  • The website’s emergency contact’s cellphone number 
  • A photograph of the person responsible for the website. This while on a custom backdrop with the hosting company’s unique lettering (the hosting provider often gives this), IP address, ISP, and server location (usually provided by your hosting provider)
  • IP address, internet service provider, and server location (usually obtained from your hosting provider)
  • A list of the domains use to access the website
  • All domains must register with a Chinese domain registrar

Applying for ICP Filing

Generally, ICP Filing applications can be completed online. Based on the hosting provider. As a matter of fact you may be able to enter your information through a different interface. Additional restrictions or paperwork may apply depending on the hosting provider. Aside the province where your Chinese business is based might change the procedure. All submissions must be in Chinese.

The MIIT’s authorized provincial body will also need between five and twenty working days. This is to process the materials provided. If papers have any issues or mistakes, you will often be notified. This happens often within a few days after submission. After that, you must amend and resubmit the documents.

Chinese PIPL LAW

The PIPL is applicable to “personal information processing entities” (“PIPEs”). Which we define as “an organization or individual that selects independently the goals and methods of processing personal information”. (Section 73). The PIPL broadly defines “personal information”. It says is “various types of electronic or otherwise recorded information relating to an identified or identifiable natural person,”. This all excluding anonymized data, and “processing” as “the collection, storage, use, refining, transmission, provision, public disclosure, or deletion of personal information.” (4) (Article 4)

This Law has several resemblances to the EU’s General Data Protection Regulation (the “GDPR”). It include extraterritorial application, data transfer limits, compliance duties, and consequences for non-compliance. The PIPL poses certain issues for businesses doing business in China, even if their data processing operations take place outside of China. Finally the penalties for non-compliance might include monetary fines and placement on a government blacklist.

Key Features of PIPL Law

  • The PIPL sets guiding principles for personal information protection.
  • This law takes a risk-based approach, imposing stricter compliance requirements in certain high-risk circumstances.
  • The PIPL endows data subjects with legal rights. Foreign organisations are regulated under the PIPL.
  • The PIPL establishes sanctions for organisations who breach their duty to safeguard personal information (Article 66).
  • Foreign organisations also face repercussions under the PIPL for breaching the personal information rights of Chinese individuals or endangering China’s national security or public interest.
  • When foreign organisations transmit personal information beyond of China’s borders, they require specific details.
  • Certain businesses requires to submit to a government security assessment prior to transacting with foreign governments.
  • PIPEs located outside of China that handle personal data with the aim of doing business in China must create an organisation or designate a person to be accountable for personal information problems
  • Without the prior consent of the Chinese government, businesses and individuals may not transmit personal information maintained inside China to foreign judicial or law enforcement bodies.

Chinese Cloud Storage Providers

Consider the top most popular cloud service providers operating in China today. It may assist you in making the best decision for your company. These are China’s top cloud service providers.

Why this post about Chinese Cloud Storage?

We are a Cloud Storage provider as well here at Sesame Disk by Nihao Cloud, but a different category. Meaning we are not a Chinese Cloud Storage provider, but we provide services for people collaborating with China as well. We focus on cross borders file transfer and team collaboration and that is how it includes China. Our services include Free Cloud Storage as well as premium plans.

How else are we different; We are not bound by the Chinese laws security wise. We are the data bridge that does not really exist. Our typical customer has to share and collaborate with some regularity with different geolocations and China may or may not be included. If you have requirements that include that we are your solution; see our products!

Alibaba Cloud – The biggest Chinese Cloud Storage

Alibaba Cloud, also known as Aliyun, is China’s largest cloud computing and backend service provider. The world-famous Alibaba Group provides it to meet the complex computing demands of Chinese enterprises. Their cloud storage OSS is very similar to AWS s3.

Aside It has several vital features that work in collaboration with the company’s many resources and e-commerce operations. Furthermore, Alibaba Cloud customers have the option of purchasing feature-rich cloud servers at a discounted price. Elastic Computer, Relational Databases, Content Delivery Networks, Data Storage, and Big-Data Processing are the primary aspects of this cloud corporation.

Alibaba Cloud also provides enterprises with disk and memory-based storage. Users have access to agile reading and writing capability, and data integrity is also retained. Alibaba Cloud simplifies backup and recovery for users by providing a variety of recovery alternatives. As a consequence, the probability of data loss and mistakes is significantly decreased.

Tencent Cloud Computing

Tencent Cloud is a dependable and high-performance cloud computing service from Tencent, a well-known Chinese technology business in Asia. It provides a diverse range of services to its massive user base through platforms such as WeChat. Tencent Cloud simplifies application development, and users may implement changes to the console setup. Developers depend on Tencent Cloud for its affordable services, which include pay-per-use options. Global data centers guarantee that cloud computing services are delivered quickly and reliably to the provider’s clients. Tencent Cloud also provides cloud services to the top downloaded game in the world i.e PUBG.

China Telecom Cloud Computing

China Telecom Cloud’s Cloud Server provides customers with a virtual solution for meeting their application needs. Users may depend on the service to rent resources, launch quickly, and easily expand.

By using Cloud Servers, customers can also reduce reliance on IT resources and avoid incurring excessive infrastructure expenditures. They get access to a broad range of computer resources and services to maximize their processing capability.

Elastic computing is a characteristic that enables local resources and the ease of storage and computation. China Telecom Cloud leverages virtualization to allow customers to install quickly and easily. Additionally, disaster recovery is easy with this supplier, and consumers can save expenditures on management and IT resources .

AWS China

Amazon Web Services, or AWS, operates a subsidiary in China known as AWS China. Sinnet is responsible for the Amazon Web Services China (Beijing) Region, whereas NWCD is responsible for the Amazon Web Services China (Ningxia) Region. It’s a different Chinese company and not AWS due to regulatory requirements. Therefore AWS had to find a local partner who owns and operates the business.

Users have access to a platform that includes technologies comparable to those found in other AWS Regions. Developers in China may now build cloud applications quickly and easily, utilizing the same operational standards, protocols, and APIs as AWS customers globally.

Unless the client switches locations, data stored in Amazon Web Services China (Ningxia) and Amazon Web Services China (Beijing) Regions are replicated.

Huawei Cloud

Huawei Cloud Stack is yet another cloud computing provider for commercial and government customers, allowing them to switch between on-premises and cloud environments seamlessly.

It is available in several versions that address various requirements, including big data analytics, legacy application migration, and artificial intelligence-based training.

It has a variety of capabilities that enable extensive cloud computing capability. Huawei Cloud Stack is a complete hybrid cloud solution that includes the FusionSphere OpenStack platform, the FusionSphere Virtualization layer, and numerous cloud service layer tools.

Kingsoft

Kingsoft Cloud is a subsidiary of the Kingston Group and a market leader in cloud computing in China and worldwide. Established in 2012, the firm has also grown to become one of China’s top three corporations.

Cloud Physical Host, Cloud Server, Object Storage, Relational Database, CDN, Cloud DNS, and Virtual Private Servers are among the products offered to users.

Additionally, users have access to a variety of cloud solutions that address the demands of most sectors.

Baidu Cloud Computing

Baidu Cloud is a market-leading cloud computing service in China, operated by Baidu Incorporation. It has several capabilities, including client software, third-party connectors, cloud storage, and file management.

Following the setup of a client terminal, the Baidu Cloud service enables customers to execute automated synchronization between Internet terminals. Since its inception in 2012, the company has become one of the most popular cloud computing solutions in China.

Also known as Baidu Wangpan, the organization offers a complimentary 6 GB of storage space. It provides file sizes of up to 4 GB for free and up to 20 GB for a fee.

China’s Azure

Azure China is Amazon’s branch in China. It provided a suite of services and features tailored to the requirements of Chinese enterprises in a variety of industries and was made feasible via local engagement.

21Vianet is the operator of Azure in China, providing consumers with access to Microsoft-licensed technology. Azure China’s service availability and operational models vary somewhat from those of the worldwide Azure service.

The services use Azure technology, which Microsoft’s worldwide cloud service offers, and provide varying degrees of customer assistance. Moreover, the service provider offers solutions to clients through contracts and agreements with 21Vianet, which is Microsoft Azure’s official partner in China.

Conclusion

The cloud service providers mentioned above are the finest in China and provide organizations with all the features and functionality they want.

They can assist businesses across sectors in achieving and surpassing their criteria and improving their customer service. You can research on the characteristics and functioning of each to help you make the best choice.

Additionally, it is essential to remember that China’s policies and laws are pretty hard regarding data held on these platforms such as need of ICP license to operate inside China.

Moreover, Specific files may be deemed inappropriate, and it is more probable that these service providers will closely monitor the files as directed by the government.

The government has cautioned some of these providers in China to keep an eye on cloud storage material. Moreover, it results in a complete lack of privacy.

Additionally, it is critical to remember that these security warnings may apply to cloud storage customers in China.

Hits: 14