Normalization Basics – 1NF 2NF and 3NF

Description of Normalization

Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency.

Redundant data wastes disk space and creates maintenance problems. If data that exists in more than one place must be changed, the data must be changed in exactly the same way in all locations. A customer address change is much easier to implement if that data is stored only in the Customers table and nowhere else in the database.

What is an “inconsistent dependency”? While it is intuitive for a user to look in the Customers table for the address of a particular customer, it may not make sense to look there for the salary of the employee who calls on that customer. The employee’s salary is related to, or dependent on, the employee and thus should be moved to the Employees table. Inconsistent dependencies can make data difficult to access because the path to find the data may be missing or broken.

There are a few rules for database normalization. Each rule is called a “normal form.” If the first rule is observed, the database is said to be in “first normal form.” If the first three rules are observed, the database is considered to be in “third normal form.” Although other levels of normalization are possible, third normal form is considered the highest level necessary for most applications.

As with many formal rules and specifications, real world scenarios do not always allow for perfect compliance. In general, normalization requires additional tables and some customers find this cumbersome. If you decide to violate one of the first three rules of normalization, make sure that your application anticipates any problems that could occur, such as redundant data and inconsistent dependencies.

The following descriptions include examples.

First Normal Form

Eliminate repeating groups in individual tables.
Create a separate table for each set of related data.
Identify each set of related data with a primary key.

Do not use multiple fields in a single table to store similar data. For example, to track an inventory item that may come from two possible sources, an inventory record may contain fields for Vendor Code 1 and Vendor Code 2.

What happens when you add a third vendor? Adding a field is not the answer; it requires program and table modifications and does not smoothly accommodate a dynamic number of vendors. Instead, place all vendor information in a separate table called Vendors, then link inventory to vendors with an item number key, or vendors to inventory with a vendor code key.

Second Normal Form

Create separate tables for sets of values that apply to multiple records.
Relate these tables with a foreign key.

Records should not depend on anything other than a table’s primary key (a compound key, if necessary). For example, consider a customer’s address in an accounting system. The address is needed by the Customers table, but also by the Orders, Shipping, Invoices, Accounts Receivable, and Collections tables. Instead of storing the customer’s address as a separate entry in each of these tables, store it in one place, either in the Customers table or in a separate Addresses table.

Third Normal Form

Eliminate fields that do not depend on the key.

Values in a record that are not part of that record’s key do not belong in the table. In general, any time the contents of a group of fields may apply to more than a single record in the table, consider placing those fields in a separate table.

For example, in an Employee Recruitment table, a candidate’s university name and address may be included. But you need a complete list of universities for group mailings. If university information is stored in the Candidates table, there is no way to list universities with no current candidates. Create a separate Universities table and link it to the Candidates table with a university code key.

EXCEPTION: Adhering to the third normal form, while theoretically desirable, is not always practical. If you have a Customers table and you want to eliminate all possible interfield dependencies, you must create separate tables for cities, ZIP codes, sales representatives, customer classes, and any other factor that may be duplicated in multiple records. In theory, normalization is worth pursing. However, many small tables may degrade performance or exceed open file and memory capacities.

It may be more feasible to apply third normal form only to data that changes frequently. If some dependent fields remain, design your application to require the user to verify all related fields when any one is changed.

Normalizing an Example Table

loadTOCNode(2, ‘moreinformation’); These steps demonstrate the process of normalizing a fictitious student table.

1. Unnormalized table:

Student# Advisor Adv-Room Class1 Class2 Class3
1022 Jones 412 101-07 143-01 159-02
4123 Smith 216 201-01 211-02 214-01
2. First Normal Form: No Repeating Groups

Tables should have only two dimensions. Since one student has several classes, these classes should be listed in a separate table. Fields Class1, Class2, and Class3 in the above records are indications of design trouble.

Spreadsheets often use the third dimension, but tables should not. Another way to look at this problem is with a one-to-many relationship, do not put the one side and the many side in the same table. Instead, create another table in first normal form by eliminating the repeating group (Class#), as shown below:

Student# Advisor Adv-Room Class#
1022 Jones 412 101-07
1022 Jones 412 143-01
1022 Jones 412 159-02
4123 Smith 216 201-01
4123 Smith 216 211-02
4123 Smith 216 214-01
3. Second Normal Form: Eliminate Redundant Data

Note the multiple Class# values for each Student# value in the above table. Class# is not functionally dependent on Student# (primary key), so this relationship is not in second normal form.

The following two tables demonstrate second normal form:

Students:

Student# Advisor Adv-Room
1022 Jones 412
4123 Smith 216

Registration:

Student# Class#
1022 101-07
1022 143-01
1022 159-02
4123 201-01
4123 211-02
4123 214-01
4.

In the last example, Adv-Room (the advisor’s office number) is functionally dependent on the Advisor attribute. The solution is to move that attribute from the Students table to the Faculty table, as shown below:

Students:

Student# Advisor
1022 Jones
4123 Smith

Faculty:

Name Room Dept
Jones 412 42
Smith 216 42
Advertisements

Difference between require, require_once, include and include_once

All these functions require, require_once, include and include_once are used to include the files in the php page but there is slight difference between these functions.

Difference between require, require_once, include, include_once
Difference between require and include is that if the file you want to include is not found then include function give you warning and executes the remaining code in of php page where you write the include function. While require gives you fatal error if the file you want to include is not found and the remaining code of the php page will not execute.

If you have many functions in the php page then you may use require_once or include_once. There functions only includes the file only once in the php page. If you use include or require then may be you accidentally add two times include file so it is good to use require_once or include_once which will include your file only once in php page. Difference between require_once and include_onceis same as the difference between require and include.

Different types of errors in PHP

Here are three basic types of runtime errors in PHP:

1. Notices: These are trivial, non-critical errors that PHP encounters while executing a script – for example, accessing a variable that has not yet been defined. By default, such errors are not displayed to the user at all – although you can change this default behavior.

2. Warnings: These are more serious errors – for example, attempting to include() a file which does not exist. By default, these errors are displayed to the user, but they do not result in script termination.

3. Fatal errors: These are critical errors – for example, instantiating an object of a non-existent class, or calling a non-existent function. These errors cause the immediate termination of the script, and PHP’s default behavior is to display them to the user when they take place.

Difference between mysql_connect() and mysql_pconnect()

mysql_connect() and mysql_pconnect() both are working for database connection but with little difference. In mysql_pconnect(), ‘p’ stands for persistance connection.

When we are using mysql_connect() function, every time it is opening and closing the database connection, depending on the request .

But in case of mysql_pconnect() function,
First, when connecting, the function would try to find a (persistent) connection that’s already open with the same host, username and password. If one is found, an identifier for it will be returned instead of opening a new connection.
Second, the connection to the SQL server will not be closed when the execution of the script ends. Instead, the connection will remain open for future use (mysql_close() will not close connection established by mysql_pconnect()).

mysql_pconncet() is useful when you have a lot of traffice on your site. At that time for every request it will not open a connection but will take it from the pool. This will increase the efficiency of your site. But for general use mysql_connect() is best.

I think this is a very imp concept in case of Database Connectivity.

MySQL interview questions

1. What is DDL, DML and DCL? – If you look at the large variety of SQL commands, they can be divided into three large subgroups. Data Definition Language deals with database schemas and descriptions of how the data should reside in the database, therefore language statements like CREATE TABLE or ALTER TABLE belong to DDL. DML deals with data manipulation, and therefore includes most common SQL statements such SELECT, INSERT, etc. Data Control Language includes commands such as GRANT, and mostly concerns with rights, permissions and other controls of the database system.

2. How do you get the number of rows affected by query? – SELECT COUNT (user_id) FROM users would only return the number of user_id’s.

3. If the value in the column is repeatable, how do you find out the unique values? – Use DISTINCT in the query, such as SELECT DISTINCT user_firstname FROM users; You can also ask for a number of distinct values by saying SELECT COUNT (DISTINCT user_firstname) FROM users;

4. How do you return the a hundred books starting from 25th? – SELECT book_title FROM books LIMIT 25, 100. The first number in LIMIT is the offset, the second is the number.

5. You wrote a search engine that should retrieve 10 results at a time, but at the same time you’d like to know how many rows there’re total. How do you display that to the user? – SELECT SQL_CALC_FOUND_ROWS page_title FROM web_pages LIMIT 1,10; SELECT FOUND_ROWS(); The second query (not that COUNT() is never used) will tell you how many results there’re total, so you can display a phrase “Found 13,450,600 results, displaying 1-10”. Note that FOUND_ROWS does not pay attention to the LIMITs you specified and always returns the total number of rows affected by query.

6. How would you write a query to select all teams that won either 2, 4, 6 or 8 games? – SELECT team_name FROM teams WHERE team_won IN (2, 4, 6, 8 )

7. How would you select all the users, whose phone number is null? – SELECT user_name FROM users WHERE ISNULL(user_phonenumber);

8. What does this query mean: SELECT user_name, user_isp FROM users LEFT JOIN isps USING (user_id) – It’s equivalent to saying SELECT user_name, user_isp FROM users LEFT JOIN isps WHERE users.user_id=isps.user_id

9. How do you find out which auto increment was assigned on the last insert? – SELECT LAST_INSERT_ID() will return the last value assigned by the auto_increment function. Note that you don’t have to specify the table name.

10. What does –i-am-a-dummy flag to do when starting MySQL? – Makes the MySQL engine refuse UPDATE and DELETE commands where the WHERE clause is not present.

11. On executing the DELETE statement I keep getting the error about foreign key constraint failing. What do I do? – What it means is that so of the data that you’re trying to delete is still alive in another table. Like if you have a table for universities and a table for students, which contains the ID of the university they go to, running a delete on a university table will fail if the students table still contains people enrolled at that university. Proper way to do it would be to delete the offending data first, and then delete the university in question. Quick way would involve running SET foreign_key_checks=0 before the DELETE command, and setting the parameter back to 1 after the DELETE is done. If your foreign key was formulated with ON DELETE CASCADE, the data in dependent tables will be removed automatically.

12. When would you use ORDER BY in DELETE statement? – When you’re not deleting by row ID. Such as in DELETE FROM techinterviews_com_questions ORDER BY timestamp LIMIT 1. This will delete the most recently posted question in the table techinterviews_com_questions.

13. How can you see all indexes defined for a table? – SHOW INDEX FROM techinterviews_questions;

14. How would you change a column from VARCHAR(10) to VARCHAR(50)? – ALTER TABLE techinterviews_questions CHANGE techinterviews_content techinterviews_CONTENT VARCHAR(50).

15. How would you delete a column? – ALTER TABLE techinterviews_answers DROP answer_user_id.

16. How would you change a table to InnoDB? – ALTER TABLE techinterviews_questions ENGINE innodb;

17. When you create a table, and then run SHOW CREATE TABLE on it, you occasionally get different results than what you typed in. What does MySQL modify in your newly created tables? –
1. VARCHARs with length less than 4 become CHARs
2. CHARs with length more than 3 become VARCHARs.
3. NOT NULL gets added to the columns declared as PRIMARY KEYs
4. Default values such as NULL are specified for each column

18. How do I find out all databases starting with ‘tech’ to which I have access to? – SHOW DATABASES LIKE ‘tech%’;

19. How do you concatenate strings in MySQL? – CONCAT (string1, string2, string3)

20. How do you get a portion of a string? – SELECT SUBSTR(title, 1, 10) from techinterviews_questions;

21. What’s the difference between CHAR_LENGTH and LENGTH? – The first is, naturally, the character count. The second is byte count. For the Latin characters the numbers are the same, but they’re not the same for Unicode and other encodings.

22. How do you convert a string to UTF-8? – SELECT (techinterviews_question USING utf8);

23. What do % and _ mean inside LIKE statement? – % corresponds to 0 or more characters, _ is exactly one character.

24. What does + mean in REGEXP? – At least one character. Appendix G. Regular Expressions from MySQL manual is worth perusing before the interview.

25. How do you get the month from a timestamp? – SELECT MONTH(techinterviews_timestamp) from techinterviews_questions;

26. How do you offload the time/date handling to MySQL? – SELECT DATE_FORMAT(techinterviews_timestamp, ‘%Y-%m-%d’) from techinterviews_questions; A similar TIME_FORMAT function deals with time.

27. How do you add three minutes to a date? – ADDDATE(techinterviews_publication_date, INTERVAL 3 MINUTE)

28. What’s the difference between Unix timestamps and MySQL timestamps? – Internally Unix timestamps are stored as 32-bit integers, while MySQL timestamps are stored in a similar manner, but represented in readable YYYY-MM-DD HH:MM:SS format.

29. How do you convert between Unix timestamps and MySQL timestamps? – UNIX_TIMESTAMP converts from MySQL timestamp to Unix timestamp, FROM_UNIXTIME converts from Unix timestamp to MySQL timestamp.

30. What are ENUMs used for in MySQL? – You can limit the possible values that go into the table. CREATE TABLE months (month ENUM ‘January’, ‘February’, ‘March’,…); INSERT months VALUES (’April’);

31. How are ENUMs and SETs represented internally? – As unique integers representing the powers of two, due to storage optimizations.

What’s the difference between LEFT, RIGHT, INNER, OUTER, JOIN?

What’s the difference between LEFT, RIGHT, INNER, OUTER, JOIN?

The difference is in the way tables are joined if there are no common records.
JOIN is same as INNER JOIN and means to only show records common to both tables. Whether the records are common is determined by the fields in join clause. For example:

FROM t1
JOIN t2 on t1.ID = t2.ID

means show only records where the same ID value exists in both tables.
LEFT JOIN is same as LEFT OUTER JOIN and means to show all records from left table (i.e. the one that precedes in SQL statement) regardless of the existance of matching records in the right table.
RIGHT JOIN is same as RIGHT OUTER JOIN and means opposite of LEFT JOIN, i.e. shows all records from the second (right) table and only matching records from first (left) table.

Affirmations: Why Are They So Powerful?

Leave Life Like King Size

Life

An affirmation is defined as something declared to be true; a positive statement or judgment.

Affirmations affirm what is going on inside your mind and your heart.

The garden of our life is developed through the planting of seeds that flow from our mouth on a daily basis.

If you respond to the state of your health, wealth and love life with negative phrases you will end up with a weedy patch for a life.

The good book reveals to us clearly that ‘life and death are in the power of the tongue.’

So to ensure that life is springing up in your garden (life), begin to speak the following phrases about:

* Your health
* Your wealth
* Your love

Here are those that I speak, and I trust that they will add great value to your life as you participate.
My Health Affirmations

* Health is my portion.
* I am energetic and enthusiastic.
* My body heals fast.
* Peace is my portion.
* No matter what has been suffered by my parents health wise, I am healthy, whole and terrific.
* Good health is mine to possess.
* My mind is brimming with healthy thoughts.
* I have a sound mind and body.
* I am well and have ‘well’ days.
* I treat my body as my temple.
* I move freely and forward.
* I treat my body with respect by feeding it healthy food, drinking healthy fluids, and exercising it regularly.

My Wealth Affirmations

* I deserve to have money in the bank.
* My income increases.
* I have an excellent credit rating.
* I am a wise spender.
* I have as much money as I can receive.
* I prosper financially.
* Bills are met with joy as I pay them on time.
* I save and invest out of every dollar I earn.
* I give at least 10% of everything I earn as my way of saying thank you for the 100% received.
* My income is without limit.
* I am creative when it comes to handling money.
* I am financially literate.

My Love Affirmations

* I love me and am secure with who I am.
* Love is forever.
* Love liberates me.
* To be in love is to be safe.
* My lover is my equal.
* By taking care of myself I take care of our love.
* I overcome jealousy by developing my own self-esteem.
* People love me for who I am.
* I deserve to be loved.
* I will develop my own love pattern, not the one set by my parents or my peers.
* Loving both others and myself gets better every day.
* The more I open myself up to loving and being loved the safer I feel.
* My lover and I respect each other and the decisions we make.
* My loving relationships are long lasting.

So go forth and speak these affirmations each and every day. Be prepared for a miracle.