locked
removing duplicity of data RRS feed

  • Question

  • Hi

    I have 1 table. I have to remove the redundant data from table. I have to use foreign key concept but unable to get idea. please suggest how do it.


    thanks and regard love4csharp



    • Edited by love4csharp Wednesday, April 3, 2013 4:49 PM
    • Moved by Naomi NEditor Wednesday, April 3, 2013 5:02 PM Design Question
    Wednesday, April 3, 2013 4:43 PM

Answers

  • I may be missing something, but it looks to me that, although there is some duplication, there are no rows that are exactly the same.  It looks to me like this table needs to be broken out into a couple different tables, I mean, I am still new to database stuff, but this table looks like it is not even 2nd normal form.  

    It looks to me like this table is storing data on grades and course info, so I would break it out into a Course table and Grades table.  Something like:

    Grades: id,  courseid, maxmarks, passmarks, securemarks

    Courses: id, coursename

    Topics: id, TopicName, courseid

    Employee: id, fname, lname, etc....

    or something along those lines.


    Simon.

    Wednesday, April 3, 2013 4:57 PM

All replies

  • kindly share your ideas

    thanks and regard love4csharp

    Wednesday, April 3, 2013 4:50 PM
  • I may be missing something, but it looks to me that, although there is some duplication, there are no rows that are exactly the same.  It looks to me like this table needs to be broken out into a couple different tables, I mean, I am still new to database stuff, but this table looks like it is not even 2nd normal form.  

    It looks to me like this table is storing data on grades and course info, so I would break it out into a Course table and Grades table.  Something like:

    Grades: id,  courseid, maxmarks, passmarks, securemarks

    Courses: id, coursename

    Topics: id, TopicName, courseid

    Employee: id, fname, lname, etc....

    or something along those lines.


    Simon.

    Wednesday, April 3, 2013 4:57 PM
  • hi

    simon

    you r thinking in a right direction. kindly elaborate your ideas as i am also new to database.


    thanks and regard love4csharp

    Wednesday, April 3, 2013 5:00 PM
  • You may want to start with better defining your column names. What is the business model you're trying to describe? You need to start from identifying your entities and then you will be able to model the database. Right now your column names are too vague and short to figure out what data they represent.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, April 3, 2013 5:03 PM
    Answerer
  • Hi

    naomi

    I Think column name is clear.

    serialnumber , employee -id , employeename, exam date , courses, sub topic, maximum marks, passing marks and secure marks.

    I hope now table is clear to you.

    if not plz let me know.



    thanks and regard love4csharp

    Wednesday, April 3, 2013 5:16 PM
  • In this case, it's also clear enough - you need 

    Employees

    SerialNumber

    EmployeeId -- Primary Key

    EmployeeName

    etc

    Exams

    ExamId

    ExamDate

    ExamName

    ----------

    It is not exactly clear if Courses, Sub Topic and Maximum Marks, Passing Marks and Secure Marks are attributes of exam or not.

    What exactly does your current table model?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, April 3, 2013 5:30 PM
    Answerer
  • hi

    naomi

     it is attribute of exam....

    i have to remove duplication of data



    thanks and regard love4csharp


    • Edited by love4csharp Wednesday, April 3, 2013 5:38 PM
    Wednesday, April 3, 2013 5:37 PM
  • If the last columns are attributes of the Exam, then move them to Exams table.

    So, now you have Employees table and Exams tables. You then can also make a table of Qualifications (I am not exactly sure what is the good name of the table yet). It is when Employee takes an exam (assuming the employee needs to take exam).

    That's why I said you may want to first describe your business case, think about entities, and what can they do?

    Say, Employee (an entity) can take an Exam (an entity). 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Wednesday, April 3, 2013 6:34 PM
    Answerer
  • Employee to Exam is many-to-many relationship.

    So you need a junction table:

    EmpExamXref

    EmpID,

    ExamID,

    PRIMARY KEY(EmpID, ExamID),

    Grade,

    ExamDate


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Thursday, April 4, 2013 12:34 AM
  • You need to learn how to normalize the table, right now you have the table in First Normal form  

    http://en.wikipedia.org/wiki/Second_normal_form

    http://en.wikipedia.org/wiki/Third_normal_form


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, April 4, 2013 5:39 AM
  • could u please tell me how to do it?

    thanks and regard love4csharp

    Thursday, April 4, 2013 6:01 AM
  • hi

    this is approach is correct or not

    First Table                                                         Second Table                                                                   Third Table                          

    empid ename        Course_id Course_name  course_marks   secure_marks      serialno examdate subtopic passmarks empid course_id

    1      First.net               1          first_Course           100         45                         1        4/12/2013     C            75           2          1

    2       Second.net          2         second_course        75          36                         2        5/12/2013     C++        65          1           2

    in this table tell me primary key and foreign key???


    thanks and regard love4csharp

    Thursday, April 4, 2013 6:09 AM
  • It may be a correct approach. As I said in my very first response, start from defining good entities and column names. Right now the column names are not intuitive and therefore it's hard to understand your tables (which you not even give good names). 

    You need to understand what exactly the table is modelling first. Once you understand that, you need to figure out the objects and attributes of each object.

    Say, is the Employee an object (entity)? If yes, what are the attributes of the Employee?

    The table name should be plural (e.g. Employees or, like Celko prefers, Personnel).

    What is your second object (entity)? Is it course? Or is it exam? What are the main attributes? Or may be you have both - you have Course and you have an Exam? 

    But in this case I think we're talking about Students, not Personnel.

    In other words, first you need to understand your business case. What process you're modeling? Do you want to model a university? Or a school? Or you want to model some business (enterprise) where you have employees?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, April 4, 2013 2:42 PM
    Answerer
  • anyone has anyidea then plz share

    thanks and regard love4csharp

    Thursday, April 4, 2013 4:32 PM
  • Didn't I already shared several ideas with you? So, can you describe in words your business case and then it will be possible to model necessary tables with correct column names and relations? But without knowing your business case we can not suggest anything besides what already has been suggested.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, April 4, 2013 5:05 PM
    Answerer
  • The graphic appears to be cutoff on my display.  I believe there are other columns to help complete the normalization exercise.  This looks remarkably like a school project.
    Thursday, April 4, 2013 9:43 PM