CSC 433 Sample normalization SQL Question

CSC 433 Sample normalization SQL Question

• 1.
  Sample DB Normalizationand SQL query Normalization- 1. Normalize the given table to its first normal form- Order_id Order_Item Total Cus_id 1 Pizza, Clemon 1 1 2 Fried chicken, Burger 3 2 3 Burger 2 1 Solution: In First Normal Form, any row must not have a column in which more than one value is saved, like separated with commas. Rather than that, we must separate such data into multiple rows. So, the given table would be changed into its first normal form as below: Order_id Order_Item Total Cus_id 1 Pizza 1 1 1 Clemon 1 1 2 Fried chicken, Burger 3 2 3 Burger 2 1 2. Normalize the given table to its first and second normal form- Id TransactionNo FirstCompany SecondCompany TransAmount TransDate 1 AB01109876 ABC Corp. Iqra Multipurpose Society CSC 433 – Shakila Mahjabin Tonni, Faculty, Dept of CSE 1,00,000 01/10/2012 2 JQ11118044 Sagupta Fashion ABC Corp. 15,00,000 11/11/2013 3 AC10122043 Sagupta Fashoin Iqra Multipurpose Society 15,00,000 10/12/2012 4 DA02033021 ABC Corp. Sagupta Fashion 2,00,000 02/03/2013 Solution: As per the first normal form’s definition, in a single row, no two column values should be repeated/duplicate. It is true for each row of the given table, so it is in its First normal Form.
• 2.
  By definition, totransform a table into its second normal form, we have to remove subsets of data that apply to multiple rows (multiple rows have same data for a column) of a table and place them in separate tables. Here, the company names are repeating in both FirstCompany (example, ABC Corp. came twice) and SecondCompany (Iqra Multipurpose Society came twice) columns. We can create a totally new table named Company for these names. So, after transforming the table in its 2NF form, we get: Table:Company Id CompanyName 1 ABC Corp. 2 Sagupta Fashion 3 Iqra Multipurpose Society Id TransactionNo FirstCompanyID SecondCompanyID TransAmount TransDate 1 AB01109876 1 3 1,00,000 01/10/2012 2 JQ11118044 2 1 15,00,000 11/11/2013 3 AC10122043 2 3 15,00,000 10/12/2012 4 DA02033021 1 2 2,00,000 02/03/2013 3. Normalize the given data into 1NF, 2NF and 3NF-Memb er_Id Member_ name Member_p osition Member_ access CSC 433 – Shakila Mahjabin Tonni, Faculty, Dept of CSE Member_a ddress Member_cont act Member_expi re_date 14012 Sadiqul Islam General Loan House 56, Gulshan 2, Dhaka 01912098234, 8743930 Feb, 2017 14009 Naurin Alam Board of Trustee Company Share House 42, Niketon, Dhaka 01729978783, 8797268 June, 2020 12134 Atiquzza man Governing Body Company Share, Managem ent House 214, Niketon, Dhaka 01819878375, 8524632 Jan, 2015 11067 Kamrunn ahar General Loan House 04,DOHS, Dhaka 01718093014, 7210784 Dec, 2014 Solution: In first normal form, no column values should contain two values (as in Member_contact column) separated by comma. So, in 1NF:
• 3.
  Membe r_Id Member_ name Member_p osition Member_a ccess CSC 433 – Shakila Mahjabin Tonni, Faculty, Dept of CSE Member_a ddress Member_c ontact Member_expir e_date 14012 Sadiqul Islam General Loan House 56, Gulshan 2, Dhaka 019120982 34 Feb, 2017 14012 Sadiqul Islam General Loan House 56, Gulshan 2, Dhaka 8743930 Feb, 2017 14009 Naurin Alam Board of Trustee Company Share House 42, Niketon, Dhaka 017299787 83 June, 2020 14009 Naurin Alam Board of Trustee Company Share House 42, Niketon, Dhaka 8797268 June, 2020 12134 Atiquzzam an Governing Body Company Share, Managem ent House 214, Niketon, Dhaka 018198783 75 Jan, 2015 12134 Atiquzzam an Governing Body Company Share, Managem ent House 214, Niketon, Dhaka 8524632 Jan, 2015 11067 Kamrunna har General Loan House 04,DOHS, Dhaka 017180930 14 Dec, 2014 11067 Kamrunna har General Loan House 04,DOHS, Dhaka 7210784 Dec, 2014 But in this format, in Member_Id column, column values are repeating. We can’t take this column as a primary key anymore. Also, in Member_Address has repeating part. So, to convert into its second normal form we will create a new table from the above table named MemberContactDetails: Table: MemberContactDetails Id Member_contact_1 Member_contact_2 House_No AreaID 1 01912098234 8743930 56 2 01729978783 8797268 42 3 01819878375 8524632 214 4 01718093014 7210784 04 Table: Area Id Area 1 Gulshan 2, Dhaka 2 DOHS, Dhaka
• 4.
  3 Niketon, Dhaka Write necessary SQL queries to- 1. Create a table Customers with the following data- (Set Id as the primary key) Id Cus_Name Age Cus_Address Cus_Mobile Cus_Email 1 Afroza 18 Uttara, Dhaka 01719991112 afroza@hotmail.com 2 Redwan 20 Narsingdi, Dhaka CSC 433 – Shakila Mahjabin Tonni, Faculty, Dept of CSE 01616889223 ripon@gmail.com 3 Samsuzzaman 25 Kakrail, Dhaka 01718071083 sahmed@yahoo.com 2. Show customers name and address from the above table. 3. Show customer name from above table where name starts with ‘A’. 4. Show customer name and age for the customers who are more than 18 years old. 5. Create a table order and insert the following data- (Ser Order_id as the primary key) Order_id Order_Item Total Cus_id 1 Pizza 1 1 2 Clemon 5 1 3 Fried chicken 3 2 4 Burger 2 1 6. Delete rows from the above table where Cus_id is 1. 7. Add a new column named Price in the above table. 8. Show the order_items and total from the above table and change the column names as Items and Total No. 9. Show the order_item and total where total is between 1 and 4. 10. Show the order_items and total for customer whose cus_id is 2. >> Complete the above tasks and submit as assignment in group via email within 07.11.14 >> After 07.11.14, I’ll provide its solution to you.
• 5.
  Good Luck CSC433 – Shakila Mahjabin Tonni, Faculty, Dept of CSE