Applicants

Document Sample
Applicants Powered By Docstoc
					      Applicants

Please use speaker notes for
  additional information!
Data that I want to keep for each applicant:
         idno (assigned by the school)
         name
         address
         phone
         email
         social security number
         department applied to
         degrees (degree, where from, year)
         years of teaching experience
         years of teaching experience at a community college
         years of industry experience
         jobs held (history of jobs) - include company, position and years
         skills (skill, level, years)
         published papers (topic and year and where published)
                                                                             The circled data
Data that I want to keep for each applicant:                                 does not have
                                                                             repeating groups so
         idno (assigned by the school)                                       I will consider it for
         name                                                                the base applicant
         address                                                             table.
         phone
         email
         social security number
         years of teaching experience
         years of teaching experience at a community college
         years of industry experience


         department applied to
         degrees (degree, where from, year)
         jobs held (history of jobs) - include company, position and years
         skills (skill, level, years)
         published papers (topic and year and where published)
     Applicant Table
     idno (primary key)
     name
     address
     phone
     email
     social security number
     years of teaching experience
     years of teaching experience at a community college
     years of industry experience




Each of these data fields will become a column in the table. The primary
key is idno and it will uniquely identify one and only one record. Each of
the other columns relate directly to the primary key. Note that social
security is a candidate key.
Now I need to deal with the data that is left, first I will deal with department applied to:

I have decided that I want to have the applicant apply be able to multiple departments. For
example someone might be able to teach in both Business or Engineering or Art with
Computer Information Systems. I will make up a table that has the applicant idno and the
department code. I do not want to carry additional information about the department I should
put it in a department table. Carrying information about the department would break the rules
of normalization . Since dept is part of the key it would break the rules because data has to be
dependent on the whole key of the record. Note that if I had decided that the applicant could
only apply to one dept and had therefore put dept in the applicant table I would still need the
dept table because I would have broken the normal form rule that does not allow a nonkey field
to be dependent on another nonkey field. Dept would have been a nonkey field and if I put
dept name in the table it would be dependent on dept.

     Applied_to Table                                      Dept Table
      idno              primary                             dept (primary key)
      dept              key                                 dept name
                                                            dept chair


   The key could not be just idno because the applicant is allowed to apply to multiple departments so
   there will be multiple records for many applicants. If the applicant was only allowed to apply to
   one department, I would have carried dept on the applicant table. It should also be mote that since
   multiple applicants will apply to the same dept, dept needed idno to specify the applicant.
Continuing to look at the data that is left, I will now deal with degrees (degree, where from, year):


            Degree Table
            idno               primary
            degree             key
            year
            where_from




      It is possible for a candidate to have multiple degrees. For example, frequently a candidate
      will present multiple masters degrees. Therefore, I have included year as part of the primary
      key. The where_from field relates to all parts of the key. A specific candidate (represented
      by idno) earned a degree in a particular year from the institution specified in the where_from.
Continuing to look at the data that is left, I will now look at jobs held (history of jobs) - include
company, position and years

 I know that I want to carry the history of jobs, my problem is what to use as the key. I
 decided to use the date the job was begun combined with the idno. This gives me the added
 advantage of being able to order the file by beginning date within applicant idno and thus
 getting an ordered listing of jobs. Some applicants give the job date using month and year,
 some only give year. This means that there is a definite possibility that I could end up with
 people having a full and part-time job starting at the same time. To deal with this, I decided
 to add an arbitrary number so that job 1 in 2002 would get a 1 and job 2 in 2002 would get a
 2. I also decided to only use year in the key because month was not very reliable. In
 analyzing this problem, I decided I wanted to add a code for full-time or part-time to the
 data.

 Job_history Table
  idno              primary
  year_start        key
  seq_code
  company
  position
  type_code
  year_end
Continuing to look at the data that is left, I will now look at skills (skill, level, years)


  For my needs, I want skills to be very specific. For example in the applicant knows Java I
  want to list it specifically not just under programming and if the applicant knows Novell
  administration I want it listed under Novell not just administration. Under level, there is a
  difference between programming in Java, having taken a course in Java or supervised the
  implementation of a Java project. I will need to develop a coding method that will convey
  this information under level.

    Skills Table
     idno                primary
     skill               key
     level
     years


                                The data in the table directly relates to the whole key. The level is
                                the level of ability the specific applicant has with the skill and the
                                years are the number of years the applicant has spent working with
                                that skill.
And finally I want to look at published papers (topic and year and where published)


   Published Table

    idno          primary
    year          key
    seq_code
    topic
    title
    where_pub



                            I decided that topic and title would both be appropriate since topic can
                            be coded and title provides more information. Therefore, as I was
                            doing my analysis, I added title to the list of data I wanted to maintain
                            in this table.
When I started this project, this is the data that I wanted to keep in my database. The
layout I decided on is shown in the next slide.



 Data that I want to keep for each applicant:
          idno (assigned by the school)
          name
          address
          phone
          email
          social security number
          department applied to
          degrees (degree, where from, year)
          years of teaching experience
          years of teaching experience at a community college
          years of industry experience
          jobs held (history of jobs) - include company, position and years
          skills (skill, level, years)
          published papers (topic and year and where published)
Applicant Table
idno (primary key)
name
address
phone
email
social security number
years of teaching experience
years of teaching experience at a community college
years of industry experience


Applied_to Table                                 Degree Table
idno            primary                          idno           primary
dept            key                              degree         key
                                                 year
                                                 where_from
Dept Table
dept (primary key)
dept name
dept chair
Job_history Table             Published Table
idno           primary
                              idno         primary
year_start     key
                              year         key
seq_code
                              seq_code
company
                              topic
position
                              title
type_code
                              where_pub
year_end




Skills Table
 idno               primary
 skill              key
 level
 years

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:8
posted:12/17/2011
language:
pages:12