Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2012
    Posts
    1

    Unanswered: Need help interpreting this project

    This project description is really confusing to me. What exactly am I suppose to do? If anyone can give me a hint of how many tables I'll need that would be a great start.

    Dependent columns:

    First Name Last Name Family Spouse Dependent 1 Dependent 2 Dependent 3 Dependent4

    Employee columns:

    EmpFirst EmpLast HireDate City State Salary HealthInsurance Family

    This small startup company, Deko is growing, and little has been done to organize company information.

    Currently, two Excel workbooks and a bunch of Word documents are used to manage employee information.

    The EMPLOYEE workbook stores employee information. The DEPENDENTS workbook stores specific information related to employee families/dependents.

    Employees are periodically evaluated and the information is recorded in a form that is stored as a Word document.

    Each Evaluation Form is currently filed by employee name in their hardcopy folder in the office’s file cabinet. There is no system to manage the annual review, nor any indication when an employee’s review is due. New salaries and commission rates are set each year for each employee, but there is no procedure to track annual salaries, commission rates, nor “when” for anybody.

    The company needs to start tracking birth date information for each employee and each dependent. This information is not currently included anywhere.

    Clearly, relationships among any resulting/required tables are necessary.

    THE TASK

    Create the necessary RELATED tables to improve the existing system.
    Don’t bother creating forms or reports.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Assuming that your teacher has already covered normalisation, you need to analyse the data currently being gathered, and work out how to break it into tables that are normalised.

    I can see a need for at least four tables, and that's without seeing any data that they might have to contain.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Dec 2012
    Posts
    4
    I'm not a pro or anything but I think if you break the problem down, the solution should start to show itself.

    ----------------------------------------
    Two workbooks exist;
    1. Dependants.xls -> Information related to employees
    2. Employees.xls -> Employee info

    and a bunch of word documents;
    3. EvaluationForm.doc -> Employee evaluation (Each document contains each employees evaluation)

    ----------------------------------------
    1. Dependants - Information related to employees
    First Name, Last Name, Family, Spouse, Dependent 1, Dependent 2, Dependent 3, Dependent4
    2. Employees - Employee info
    EmpFirst EmpLast HireDate City State Salary HealthInsurance Family
    3. Evaluation - Employee evaluation
    Annual review date, salary at time of evaluation, commision rate at time of evaluation

    =======================
    We Need:
    1. Employee birth date
    2. Dependant birth date

    ...But a birthdate is just a date. So really, we need just Birthdate because we can reference the birthdate we want whether the person is an employee or a dependant. You could make a table to store all the birthdates and then relate that table to both employees and dependants.

    ----------------------------------------
    If we look at all the columns, and identify what they are, we can group them into related data:

    Columns

    First Name - a dependants first name
    Last Name - a dependants last name
    Family - the family a dependant is related to
    Spouse - the employee the depenant is related to
    Dependent 1
    Dependent 2
    Dependent 3
    Dependent 4 - 1-4 are more or less like children, A problem worth noting is if the family has a 5th child your database will not be able to accomodate the additional dependant.

    EmpFirst - an employees first name
    EmpLast - an employees last name
    HireDate - the date the employee started working
    City - the city the employee resides in
    State - the state the city resides in
    Salary - the current salary of the employee
    HealthInsurance - the employees health insurance number
    Family - the family the employee belongs to

    ARDate - the date the annual review was conducted on
    ARSalary - the salary at the time of the AR
    ARCommision - the commision the employee receives at the time of the AR

    -----------------------------------------
    Grouping:
    Employees
    Dependants
    Families
    AnnualReview
    Cities
    States
    Birthdates
    Hiredates

    *I grouped Birthdates and Hiredates separatley to understand the layout easier. Although I think you could put them together.
    ------------------------------------------
    Looking closer:
    Employees
    -> ID_Employee, EmpFirst, EmpLast, Salary, Health Insurance
    Dependants
    -> ID_Dependant, First Name, Last Name
    Families
    -> ID_Family
    AnnualReview
    -> ID_AnnualReview, Salary, Commision
    Cities
    -> ID_City, City
    States
    -> ID_State, State
    Birthdates
    -> ID_Birthdate, Birthdate
    Hiredate
    -> ID_Hiredate, Hiredate

    ===========================

    Again, like I said, I am not a pro, but I hope this helps to get you in the right direction.

    I stopped short of indicating the relationships in the last section.

    Good luck.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what have you got so far?
    what are you struggling with
    Not many on this forum will do the work for an assignment, nto many will 'validate and existing model'. But you will get help if you can show you understand the process, but have got stuck on one or two things.

    in the mean time perhaps looking at normalisation may be a good idea
    Fundamentals of Relational Database Design -- r937.com
    and
    The Relational Data Model, Normalisation and effective Database Design
    are good starting points
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    I would put all Employee staff in a Employee table


    Employee table
    EmployeeID PK
    FirstName
    Surname
    Hdate
    DOB
    City <= this could be a combo box look at its self
    ...
    ...
    ...
    ...


    Dependent
    DependentID PK
    FEmployeeID FK
    MEmployeeID FK
    Depname
    DOB

    just in case mum and dad at same place
    FEmployeeID FK
    MEmployeeID FK





    AnnualReview
    AnnualReviewID
    EmployeeID
    Rdate
    Salary
    ....
    ....
    ....

    that my 5 cents
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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