Results 1 to 3 of 3

Thread: Help - beginner

  1. #1
    Join Date
    Nov 2008
    Posts
    2

    Help - beginner

    Hello everyone,
    I'm a beginner with databases and I'm trying to do some homework. I'm writing down the exercise and I'll be putting what i've done so far replying this thread. I'd appreciate if someone could help me saying if I'm in the right way or how should i do it.

    Thanks
    DS

    A construction company requires a database to record details about building projects. Each project has its own project number, name and employees assigned to it. Each employee has an employee number, name and job classification, such as engineer or computer technician.
    The company charges its clients by billing the hours spent on each contract. The hourly billing rate is dependent upon the employee’s position. For example, one hour of a computer technician’s time is billed a different rate than one hour of an engineer’s time.
    You can see below some example of data:
    Proj No Proj Name Emp No Emp Name Job class Charge/Hour Hours billed Total Charge
    15 Eveready 100 John Electrical Engineer 84.50 20 1690.00
    101 Mary Database designer 100.00 15 1500.00
    102 Mark Database designer 100.00 17 1700.00
    103 David Programmer 37.75 12 453.00
    104 Daniel System Analyst 90.00 14 1260.00
    Sub Total 6603.00

    Summary of operations:
    • The company manages many projects
    • Each project requires the service of many employees
    • Some employees are not assigned and perform duties not specifically related to a project. Some employees are part of a labour pool, to be shared by all project team. For example, the company’s executive secretary would not be assigned to any one particular project.
    • Each employee has a single primary job classification. This job classification determines the hourly billing rate.
    • Many employees can have the same job classification. For example, the company employs more than one electrical engineer.

    1)Draw an ERD for the database. Don't need to show attributes
    2) Normalise data into tables at least 3rd normal form
    3)Construct data dictionary containing:
    - data stores
    - data elements
    4) From the fully normalised 'non key' attributes, determine the functional dependency on the primary key.

  2. #2
    Join Date
    Nov 2008
    Posts
    2
    1)
    COMPANY - 1 ---- N - PROJECT
    PROJECT - 0N ---- N - EMPLOYEE
    EMPLOYEE - 1N ---- 1 - JOB

    2)
    Unnormalised
    (ProjNo, ProjName, (EmpNo, EmpName, JobClass, ChargeHour, HoursBilled))

    1NF
    ((ProjNo, EmpNo, ProjName, EmpName, JobClass, ChargeHour), HoursBilled)

    2NF
    PROJECT - ProjNo, ProjName
    EMPLOYEE - EmpNo, EmpName, JobClass, ChargeHour
    PROJEMP - ProjNo, EmpNo, HoursBilled

    3NF
    PROJECT - ProjNo, ProjName
    EMPLOYEE - EmpNo, EmpName, JobClass
    JOB - JobClass, ChargeHour
    PROJEMP - ProjNo, EmpNo, HoursBilled

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so far so good, your 3NF looks fine

    question 4 looks like a weird afterthought -- FDs are used to do normalization, and by the time you've reached 3NF, all the non-key attributes are supposed to be fully functionally dependent on the entire PK
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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