Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2016
    Posts
    15

    Post Answered: How to design db for interview report

    Hi Experts,

    Hope I get some help from You all with the basic questions in designing the database.

    I have a sample Interview report (To track selection of candidates and the person who conducted interview).

    Now am trying to design database based on the report I have in spreadsheet and am unable to succeed and hence reaching out for Your help.

    Here are the requirements:
    1. Candidate will have one or more interviewer(s).
    2. Interviewer(s) will have at least one candidate.
    3. Interviews are conducted based on rounds (For Example: If candidate is selected in first round, then second round will be conducted and so on).
    4. I should be able to add interviewers dynamically. (I mean to say if in webpage, I click on "add new interviewer" button, it should appear in table so that I can assign him for future selections).
    5. I should be able to add rounds of interview. (Say today I build table that has only first and second round interview data, tomorrow I should be able to add another round say third round).

    I tried learning basics of database design with 1NF, 2NF and 3NF but finally I got confused with mappings like "one to many" and "many to many" and could not build database for the requirement.

    I also would like to know on how to design database with simple logic or the methods You follow in day to day life. First of all am not able to understand if I have to do one to one mapping or one to many mapping at all. If I get help on this with the above example then I will be more glad and appreciate any help provided.

    Am attaching spreadsheet details with the thread for reference. Please find the attached files below:

    SampleInterview.txt

    Click image for larger version. 

Name:	SampleInterview.JPG 
Views:	3 
Size:	62.2 KB 
ID:	16762

    Regards,
    Richa

  2. Best Answer
    Posted by cis_groupie

    "Have you looked at LISTAGG? Here's a clear example of it:
    https://blog.nraboy.com/2014/10/aggr...ws-single-row/"


  3. #2
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    What advice has your tutor offered you?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  4. #3
    Join Date
    Feb 2016
    Posts
    15

    Exclamation

    Quote Originally Posted by cis_groupie View Post
    What advice has your tutor offered you?
    Thanks for the reply. Am doing self study and created example of my own but am not getting which entity I must make as "one to many" and "many to many" mapping.

    If I get help on the logic at least, I can proceed.

    Regards,
    Richa

  5. #4
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    am not getting which entity I must make as "one to many" and "many to many" mapping.
    The problem with database design is that if you can't understand it then you won't get far in designing your database solution. So, someone could give you a solution to your problem but then you still won't be able to design a database, which is what this exercise is all about.
    The best advice I can offer is to browse the internet for articles on Normalization - until you understand it you won't get far. Remember that normalization is not language-specific, so the principles apply regardless of what software you're using, which means you can read any article on the subject without worrying whether or not it applies to Oracle.
    Once you're happy with normalization then you should be able to cope with the different relationship types - read lots of articles, as each one will explain things in a different way that should help if you're stuck understanding a particular concept.

    Hopefully then you should have a clearer idea about what you should be doing.

    If after all this you're still stuck, then ask for help, but ask specific questions. Don't say "here's my spreadsheet, design me a database" or "how do you design databases in your job?". Do say "I've got to do xxx, so far I've done xxx, but I can't work out how to show the relationship between Table x and Table y".
    Good luck with your studies
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  6. #5
    Join Date
    Feb 2016
    Posts
    15

    Unhappy

    Quote Originally Posted by cis_groupie View Post
    The problem with database design is that if you can't understand it then you won't get far in designing your database solution. So, someone could give you a solution to your problem but then you still won't be able to design a database, which is what this exercise is all about.
    The best advice I can offer is to browse the internet for articles on Normalization - until you understand it you won't get far. Remember that normalization is not language-specific, so the principles apply regardless of what software you're using, which means you can read any article on the subject without worrying whether or not it applies to Oracle.
    Once you're happy with normalization then you should be able to cope with the different relationship types - read lots of articles, as each one will explain things in a different way that should help if you're stuck understanding a particular concept.

    Hopefully then you should have a clearer idea about what you should be doing.

    If after all this you're still stuck, then ask for help, but ask specific questions. Don't say "here's my spreadsheet, design me a database" or "how do you design databases in your job?". Do say "I've got to do xxx, so far I've done xxx, but I can't work out how to show the relationship between Table x and Table y".
    Good luck with your studies
    Hi,
    Thanks for the suggestion and help. I did as per Your guidance and built logical diagram and relational diagram.
    Attaching same for reference.
    Logical Diagram
    Click image for larger version. 

Name:	Logic.JPG 
Views:	1 
Size:	30.2 KB 
ID:	16764

    Relational Diagram
    Click image for larger version. 

Name:	relational.JPG 
Views:	2 
Size:	71.8 KB 
ID:	16765

    I did lot and lot of study online regarding Normalization and decomposed the spreadsheet I had inorder to create meaningful database. Thanks for the help.

    In database I entered data for each table.

    Now am having problem and seek Your help. I wrote query to fetch records and getting data in two lines instead of one.

    Here is the query:
    Code:
    select AACANDIDATE.CANDIDATE_NAME as CANDIDATE_NAME,
        AACONSULTANCY.CONSULTANCY_NAME as CONSULTANCY_NAME,
        AACONSULTANCY.CONSULTANCY_ADDRESS as CONSULTANCY_ADDRESS,
        AAINTERVIEW.INTERVIEWROUND as INTERVIEWROUND,
        AAINTERVIEW.INTERVIEWDATE as INTERVIEWDATE,
        AAINTERVIEWER.INTERVIEWER_NAME as INTERVIEWER_NAME,
        AASTATUS.STATUS as STATUS,
        AAINTERVIEW.INTERVIEWCOMMENTS as INTERVIEWCOMMENTS 
     from AAINTERVIEW AAINTERVIEW,
        AASTATUS AASTATUS,
        AAINTERVIEWER AAINTERVIEWER,
        AACONSULTANCY AACONSULTANCY,
        AACANDIDATE AACANDIDATE 
     where AACONSULTANCY.CONSULTANCYID=AACANDIDATE.AACONSULTANCY_CONSULTANCYID
        and AACANDIDATE.CANDIDATEID=AAINTERVIEW.AACANDIDATE_CANDIDATEID
        and AASTATUS.STATUSID=AAINTERVIEW.AASTATUS_STATUSID
        and AAINTERVIEWER.INTERVIEWERID=AAINTERVIEW.AAINTERVIEWER_INTERVIEWERID
    Here is the result:
    Click image for larger version. 

Name:	result.JPG 
Views:	2 
Size:	30.6 KB 
ID:	16766

    How can I make the result to show in single line?

    Thanks in advance.

    Regards,
    Richa

  7. #6
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    There's nothing wrong with your query as it stands - you are getting two rows because you are returning two different records.
    Do you want just one record returned? In which case, which one?
    Or do you want to combine the two returned records into one record? In which case, how do you intend to show both sets of data for the columns "interviewround", "interviewername", "interviewcomments"?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  8. #7
    Join Date
    Feb 2016
    Posts
    15

    Unhappy

    Quote Originally Posted by cis_groupie View Post
    There's nothing wrong with your query as it stands - you are getting two rows because you are returning two different records.
    Do you want just one record returned? In which case, which one?
    Or do you want to combine the two returned records into one record? In which case, how do you intend to show both sets of data for the columns "interviewround", "interviewername", "interviewcomments"?
    Hi,

    Thanks for the reply.

    I want to do this:
    do you want to combine the two returned records into one record?
    Yes. The result record is displayed for same candidate. Candidate can have one process (including First round and second round. He can't appear again for interview if gets rejected). Hence I want result in single row as in spreadsheet.
    In which case, how do you intend to show both sets of data for the columns "interviewround", "interviewername", "interviewcomments"?
    Similar to the spread sheet from OP. So now my question is: Do I have to create additional columns in table? Like FirstRound_Interviewer_1, FirstRound_Interviewer_2, SecondRound_Interviewer1.. etc.,

    I designed logical diagram with idea:
    1. Each interview will have one or many rounds (So created separate Junction table)
    2. Each Round will have one or more Interviewers.
    3. Each Round will have only one comment.
    4. Each interview will have only one candidate.

    Please correct me if I designed my logical diagram wrong way. Based on logical diagram I created relational diagram and then tables.

    If my logical diagram is wrong in any step, then it will definitely impact my tables.

    Regards,
    Richa

  9. #8
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Don't confuse how data is stored with how it is retrieved.
    If you want to display the data in one row instead of two then this should be done by your query, not by adding columns to the table.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  10. #9
    Join Date
    Feb 2016
    Posts
    15

    Red face

    Quote Originally Posted by cis_groupie View Post
    Don't confuse how data is stored with how it is retrieved.
    If you want to display the data in one row instead of two then this should be done by your query, not by adding columns to the table.
    Thanks for the reply.

    Can You please let me know how I can modify existing query to display result in single row?

    Thanks in advance.

    Regards,
    Richa

  11. #10
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Have you looked at LISTAGG? Here's a clear example of it:
    https://blog.nraboy.com/2014/10/aggr...ws-single-row/
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •