Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2007
    Posts
    18

    Unanswered: Complete schema help is required...

    I want to make a complete database. so I need help you.. Please extend your help.

    What my database should contains :--
    I will store duty_roster, retrieve duty_roster, calculate over time allowance, night allowance, etc. There will be some condition.. I will give all the details to make it easy.

    My duty_roster will look like follows ...


    DUTY ROSTER FOR OPERATIONAL STAFF

    -----------------------------------------------------------------------------
    DAY DATE 0001 0800 1300 1900 1000 1100 EL OFF REMARKS
    0800 1300 1900 2359 1700 1800
    ----------------------------------------------------------------------------
    SUN 01/01/07 ED G D GE - B - - A -
    ----------------------------------------------------------------------------
    MON 02/01/07 GE D E DG A B - - - -
    ----------------------------------------------------------------------------
    TUE 03/01/07 DG E G ED A B - - - GH
    ----------------------------------------------------------------------------
    WED 04/01/07 ED G D GE A B - - - -
    -----------------------------------------------------------------------------
    THU 05/01/07 GE D E DG A B - - - -
    -----------------------------------------------------------------------------
    FRI 06/01/07 DG E G ED A B - - - -
    -----------------------------------------------------------------------------
    SAT 07/01/07 ED G D GE A - B -
    -----------------------------------------------------------------------------

    1. MR. G.K ANTONY MANAGER A …. HRS
    2. MR. J.S.KATHA MANAGER B ….. HRS
    3. MR D. SUSANI TEAM LEADER C ….. HRS
    4. MR K GUIA WORKER D ……HRS
    5. MR. P.S.ABRAM WORKER E ……HRS
    6. MR. Y.S. HILTON WORKER F …… HRS
    7. MR. H..A. DOAH WORKER G …… HRS

    *GH--Gazated Holiday


    I want to insert data in one go.. want to retrive data in this format.
    to take printout also.

    condition... 1. different staff have different designation, different salary, different code, different department code, different section, different shift etc.
    2. how to calculate ota__: to calculate ota, one staff have to perform 42 hrs in one week, more that of that and up to 56 hrs i.e. total 14 hrs will be counted as ota hrs. in one block i.e. 4week total 56 hrs will be counted as ota.

    3. there will be some holiday such as national holiday, gazeted holiday etc. every occurance of that holiday, 6 hours will be deducted from normal hours i.e 42-6=36 hrs will be as normal performing hours and beyond that ota hours.

    4. ota rate depends upon salary of the staff.

    5. how to calculate night weitage : every staff performing night duty ie from 2200 to 0600 hrs will be counted night allowance. total 8 hours and for every hour there will be 10 minutes i.e total 80 minutes or 1 hrs 20 minutes will be counted as night allowance for ever night duty performs. Its rate also depends upon the salary of the staff. It will be calculated in a block i.e. in a 4 week bunch. there is no limit for night allowance.


    6.Staff code may change after joining of senior or junior staff. according that will reflect in duty_roster.
    e.g. lets mr. a.guri's code was a on 01/01/06 but after joining mr. b.tuli on 01/01/07 , a.guri's code become b and mr. b.tuli's code become a.
    vice versa also possible.

    therefore if I quired duty_roster for the week 01/01/06 then it will show mr. a.guri's code as a and ota/na/and other details will be as per that. but if I quired duty_roster for the week 01/01/07 the it will show mr. b.tuli's code as a and mr. a.guri's code as b and other details will be accordingly.

    7. there are several department in office such as production, operational, maintenance etc. accordingly query will be performed.

    If you need more informtion , I will be given for your easy understanding. But please help me to make a working schema for this database.

    In this database ..
    1. details of staff members will be stored.
    2. duty performed will be stored.
    3. there leave details , salary details, any other details will be stored.

    Thanks in advance.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down



    So, you don't know how to do your job and are looking for somebody else to do it for you? Or is it Homework.




    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Sep 2007
    Posts
    18
    No this is not to do my job by some body. Its my job to do . But forum is for help one another. and this is not a commercial database. Its for my own homework.

    I have made one schema. but there is some problem. that is why I request for all expert to help me. because I am not expert in this field. Though I know something.

    again I am requesting all of you for extend the helping hand for me.

    thanks...

  4. #4
    Join Date
    Sep 2007
    Posts
    18
    please help

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >But forum is for help one another.

    Who have you helped?
    One learns best by doing; rather than being spoonfed solutions.

    >Its for my own homework.
    Which school, which course, which teacher?
    We don't do homework assignments since many consider that to be cheating.

    >I have made one schema.

    Which schema did you make? Please post all relevant DDL.

    I am not convinced you know what a schema actually is.

    >but there is some problem.
    Problem? What problem? I don't see any problem that requires fixing.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Sep 2007
    Posts
    18
    ok My schema is like this...

    If you convinced then let me know..

    Emp_master
    ----------------
    Section_code varchar2(4) -- uk
    emp_code varchar2(1)-- uk
    emp_no varchar2(4) -- pk
    emp_name varchar2(40)
    basic number(6)


    Duty-roster
    -------------
    section_code varchar2(4) -- cpk
    week_id varchar2(3)
    emp_no varchar2(4)--cpk
    shift_id varchar2(1)--cpk
    duty_date date -- cpk


    Leave_details
    ---------------
    section_code varchar2(4) -- cpk
    Duty_date date -------cpk
    emp_no varchar2(4) ---cpk
    el varchar2(1)
    cl varchar2(1)
    w_off varchar2(1)
    Remarks varchar2(5)



    Now let me know your comment...
    I will show you the problem I am facing...

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    CPK? OK, I give up.
    http://en.wikipedia.org/wiki/CPK
    somehow I doubt any of the definitions in WIKIPEDIA is what OP meant.

    >>Which schema did you make? Please post all relevant DDL; where DDL means the actual SQL statements to create the schema & objects.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I think that "cpk" means composite primary key.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Sep 2007
    Posts
    18
    Yes "cpk" means composite primary key.

  10. #10
    Join Date
    Sep 2007
    Posts
    18
    uk-- unique key
    pk-- primary key
    cpk--composite primary key

Posting Permissions

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