Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2014
    Posts
    4

    Question Unanswered: Database Project - Double Check

    Hey guys. I'm looking for a little help or guidance with a database project I have in my class. I believe we are all done but just want to double check. We are using SQL Power Architect to design the database before putting it into Access. Just in case someone whats to know what the project is about here is the info:
    With the pending implementation of the new ACA- Affordable Care Act, the University HR Department would like to have access to various reports that will aide them in getting information out to all fulltime employees. The university currently employs three groups of full-time employees: 10 month faculty, 12 month faculty, and Regular staff. The current system only has one table which results in the HR professionals having to manually manipulate the data in excel. This, in many instances, results in inaccurate information. Over time phone numbers may change and possibly the employees and their current positions. Furthermore, employees have several benefit packages and plans to choose from in which the university is associated.

    Your task is to create a database which will allow the department to possibly pull such reports as follows:

    1. All fulltime 10 month employees, including their address, who are currently enrolled in the University’s HMO plan at the Employee + child level.
    2. All Full-time, Regular Staff, including address, who were hired before 12/1/2010.
    3. Create queries to pull various other reports
    4. Create Website, PowerPoint, and Milestones for project
    5. Create Diagrams using Power Architect

    Data Located in Excel Worksheet

    Position #
    Date of Hire
    Department
    Salary
    Manager
    Full Time
    Part Time
    Active
    Terminated
    Deceased
    Leave of Absence
    10 Month
    12 Month
    Regular Staff
    Student
    Non-Paid Affiliate
    Medical
    Dental
    Life
    Spouse
    Child
    Short Term
    Long Term Employee Only
    Employee + Spouse
    Employee + Child
    Employee + Family
    Here is the Diagrams in Power Architect

    Everything is one-to-one. (I personal think that is incorrect)

    What do you guys think? Are we on the right track or back to the drawing board?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I dunno
    what knowledge are you expected to have demonstrated as part of this coursework
    in what areas do you think your model is right
    ...is wrong
    ...needs refinement
    what areas do you think needs further work
    do you think you have taken the flat spreadsheet model through the proper realtional db design process, and iteration
    these are worth a good look/read

    http://r937.com/relational.html
    http://www.tonymarston.net/php-mysql...se-design.html
    http://www.informationqualitysolutio...tionPoster.pdf

    this place won't do your home/coursework for you. it may help you resolve specific problems or issues, but it wont give overall approval/guidance. if you need that go back to your tutors and or your fellow students

    does your college / school / uni have policies in place concerning passing off other peoples work as your own?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2014
    Posts
    4
    Double post.
    Last edited by XJen; 11-23-14 at 13:59. Reason: Double Post

  4. #4
    Join Date
    Nov 2014
    Posts
    4
    Quote Originally Posted by healdem View Post
    I dunno
    what knowledge are you expected to have demonstrated as part of this coursework
    in what areas do you think your model is right
    ...is wrong
    ...needs refinement
    what areas do you think needs further work
    do you think you have taken the flat spreadsheet model through the proper realtional db design process, and iteration
    these are worth a good look/read

    http://r937.com/relational.html
    http://www.tonymarston.net/php-mysql...se-design.html
    http://www.informationqualitysolutio...tionPoster.pdf

    this place won't do your home/coursework for you. it may help you resolve specific problems or issues, but it wont give overall approval/guidance. if you need that go back to your tutors and or your fellow students

    does your college / school / uni have policies in place concerning passing off other peoples work as your own?

    Hold it. I NEVER said for you or anyone to do my project and this project is my OWN, no one else. I wouldn't have join this form for that. I join this form cause I'm looking for help and advice on this project and when I start teaching myself PHP you guys might be of some assistance. I posted this to see If anyone might see something that might be an error or that is incorrect that I did not catch. I have looking over and done Normalization. I've been to those links before.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK
    so what the difference between a 10 or 12 month review, that justifies a separate table for each
    what happens if the organisation introduces, say 6 or 1`8 month review. why would you feel the need to create a separate entity for 10 & 12, especially as they have the same data. Their presence in your design suggests you haven't gone through the normalisation process as these are repeating data elements

    what circumstances would make you choose a one to one relationship as opposed to any other type of relationship

    if SSN is good enough as a PK in 10,12 month and other tables why would you then switch to employee ID in dental, medical and so on. Because its covering US health requirements I'd expect a treatment plan./cost to be agreed for a person, and a person who links back to an employee. how you handle a treatment plan for an ex employee I dunno

    do you need separate tables for dental / medical, again they look to be holding the same data so they are possibly the same entity in disguise, or if not the same entity then possibly a sub & multiple supertypes
    what happens if say there is a 3rd category of medical intervention, say mental

    some of these are design choices which you can dispose of as part of your narrative as to how you came to your final design

    have you considered using the sub/supertypes appraoch to say people

    are your treatments for a specific person, or a specific employee. shoudl a specific person be directly related to a specific employee. is it likely that you may have more than one employee whose terms of employment cover other co dependants already registered through another employee.

    Hold it. I NEVER said for you or anyone to do my project and this project is my OWN
    What do you guys think? Are we on the right track or back to the drawing board?
    based on a coursework requirement identified as:-
    With the pending implementation of the new ACA- Affordable Care Act, the University HR Department would like to have access to various reports that will aide them in getting information out to all fulltime employees. The university currently employs three groups of full-time employees: 10 month faculty, 12 month faculty, and Regular staff. The current system only has one table which results in the HR professionals having to manually manipulate the data in excel. This, in many instances, results in inaccurate information. Over time phone numbers may change and possibly the employees and their current positions. Furthermore, employees have several benefit packages and plans to choose from in which the university is associated.

    Your task is to create a database which will allow the department to possibly pull such reports as follows:

    1. All fulltime 10 month employees, including their address, who are currently enrolled in the University’s HMO plan at the Employee + child level.
    2. All Full-time, Regular Staff, including address, who were hired before 12/1/2010.
    3. Create queries to pull various other reports
    4. Create Website, PowerPoint, and Milestones for project
    5. Create Diagrams using Power Architect

    Data Located in Excel Worksheet

    Position #
    Date of Hire
    Department
    Salary
    Manager
    Full Time
    Part Time
    Active
    Terminated
    Deceased
    Leave of Absence
    10 Month
    12 Month
    Regular Staff
    Student
    Non-Paid Affiliate
    Medical
    Dental
    Life
    Spouse
    Child
    Short Term
    Long Term Employee Only
    Employee + Spouse
    Employee + Child
    Employee + Family
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2014
    Posts
    4
    The instructions were posted to prevent someone else for asking "what are you doing?", "why are you doing this?", "What is this for?". Not to get someone to do it for me.

    We were not thinking at the time about making it "future proof". We were designing it by the instructions, thinking about now and not the future. I know that is not good practice to be using when making any kind of databases.

    As for the relationships, I'm just not sure. But I know something isn't right..

    healdem I do thank you for coming back and replying. I will take everything you have mention here and tweak it.

  7. #7
    Join Date
    Oct 2013
    Posts
    8
    This is probably a different can of worms, but... SSN's are exceedingly poor choices for primary keys. First, there is the possibility of duplicate SSN's whether through error or fraud. Secondly, what happens if a SSN is entered incorrectly. How do you anticipate making a correction across related tables?

Posting Permissions

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