Intro to Relat Dbase - Assignment Example

INTRODUCTION TO RELATIONAL DATABASE Introduction to Relational Database Affiliation Normalization “Basically, the normalization is the process of decomposing large, inefficiently structured tables into smaller, more efficiently structured tables without losing any data in this process” (Hernandez, 2003)Step for converting database tables into First Normal From1st normal FormA relation is believed to be in 1st normal form if it does not contain any repeating group, the value in each attribute should be atomic, and every tuple should be unique (SQA, 2014):Steps performed in 1st Normal FormEliminate the repeating groups.

Form a new entityExampleDept_noDept_namestudent_nostudent_name10ITS01James20SES02S03BillDavidThis table needs to be converted into first normal form because there is a repeating group in this table.Dept_noDept_namestudent_nostudent_name10ITS01James20SES02Bill20SES03DavidStep for converting database tables into second Normal From2nd Normal Form:A relation is in 2nd normal form if it is in 1NF and its all non-key attributes are fully functionally dependent on the whole key (IBM, 2005).Steps Convert tables into first normal form.

Remove partial dependency.ExampleWe need to convert the above table into 2nd normal form because there is a partial dependency in this table: Dept_noDept_name10IT20SE20SEStudent_noStudent_nameS01JamesS02BillS03DavidNow above table is converted into two tables that are satisfying the condition of 2nd normal form.Step for converting database tables into third Normal From3rd Normal Form:A relation is in 3rd normal form if it is in 2NF and does not have any transitive dependency (1KeyData, 2014).StepsConvert table into second normal formRemove transitive dependency ExampleStudent_noStudent_nameStudent_subjectS01JamesITS02BillSES03DavidCSNow we need to convert the above table into 3rd normal form because there is a transitive dependency.

Student_noStudent_nameS01JamesS02BillS03DavidStudent_noStudent_subjectE01ITE02SEE03CSNow these tables are in 3rd normal form because there is no transitive dependency.Explain typical situations when denormalizing a table is acceptable. Provide one (1) example of denormalizing a database table to justify your response.Denormalization:It is a process of introducing a redundancy in a normalized database in order to address performance problems (Microsoft Corporation, 2014).ExampleFor example we have a table with the following attributes:Student_noStudent_nameE01JamesE02BillE03DavidStudent_noStudent_subjectE01ITE02SEE03CSOur database has the above given tables.

If any student wants to know the student subject they should know the student number. In the same way if a student wants to know the subject they also know the student number. So there are two types of students for instance, some querying about subjects and some for name. Hence, a query has to search through two tables because both have same student number column then check the next column is either it is student name or subject. So it is better to improve performance by joining these table and apply denormalization.

It will reduce the number of queries.So the denormliazed table of the above two tables is given below:Student_noStudent_nameStudent_subjectS01HamidITS02HammadSES03HuzaifaCSExplain the significant manner in which business rules impact both database normalization and the decision to denormalize database tables.For removing redundancy, partial dependency, transitive dependency we need to apply normalization. If databases are fully normalized we have to join more tables for collecting results from different tables.

So, it depends on data, when a user should normalize data when a user should denormalize data. For improving performance, reducing joins usually there is need for denormalization (Downs, 2008).References1KeyData. (2014). Third Normal Form (3NF). Retrieved from http://www.1keydata.com/database-normalization/third-normal-form-3nf.phpDowns, K. (2008, 10 19). The Argument For Denormalization. Retrieved from http://database-programmer.blogspot.com/2008/10/argument-for-denormalization.htmlHernandez, M. J. (2003).

Understanding Normalization. Retrieved from http://web.calstatela.edu/faculty/pthomas/CIS405A/UnderstandingNormalization.pdfIBM. (2005). Second Normal Form. Retrieved from IBM.com: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.ddi.doc/ddi54.htmMicrosoft Corporation. (2014). Lesson 3: Optimizing the Database Design by Denormalizing. Retrieved from Microsoft.com: http://msdn.microsoft.com/en-us/library/cc505841.aspxSQA. (2014). Convert to First Normal Form (1NF).

Retrieved from http://www.sqa.org.uk/e-learning/SDM04CD/page_05.htm