Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2012
    Posts
    9

    Need your help in normalisation

    Hello people,

    i'm new to this forum, still i hope to find people helping me.

    I need to bring some schemata in 3rd normal form.

    Starting with a simple concern schema which looks like this:

    concern := ConNr, ConName, Address, DepartmentName1, DepartmentName2, DepartmentName3

    employee := EmpNr, Name, Forename, BirthDate, DepartmentName, Activity

    worker := WorkerNr, Name, Forename, Birthdate, DepartmentName, Activity


    now there seems to be a dissent to 1st NF, which is:
    #1 the repetition of department names in relation concern
    #2 the address in concern, which needs to be split up
    #3 (maybe?) activity of both employee and worker - maybe one employee/worker has more than just one activity

    Also i now add the primary keys and my new schema looks like this:

    concern := ConNr (PK), ConName, County, City, Street, Zipcode

    department := ConNr(PK,FK), DepartmentName(PK)

    employee := EmpNr(PK), Name, Forename, BirthDate, DepartmentName, Activity

    employeeActivity := EmpNr(PK,FK), Activity(PK)

    worker := WorkerNr(PK), Name, Forename, Birthdate, DepartmentName, Activity

    workerActivity := WorkerNr(PK,FK), Activity(PK)

    I hope it's correct to set the PK of the activity-relations on emp/worker-Nr AND the activity since it has to be distinct.

    To come to the second normal form, i don't have to normalize in this schema since there are no relations with "merged" primary keys (how do you call it correct?) with other non-pk attributes

    Also the third normal form is given because i can't see any transitive relationships of attributes.

    So is this really in third NF? My brain tells me yes - my gut says no.
    Don't I need a relation of worker/employee to the concern? Maybe there are more than one concern, so I won't be able to identify which worker/employee works in which concern - am I right?

    I would really appreciate any help.
    Have a wonderful evening

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    can a department span multiple locations (addresses)?
    can a concern have multiple locations?
    can a concern have multiple addresses within one physical location?
    what is the difference between an employee and a worker?
    what is activity, is it an entity in its own right?
    can en employee or worker or both be a member of more than one department?
    does an employee have a single address or can they work in multiple locations?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2012
    Posts
    9
    Quote Originally Posted by healdem View Post
    can a department span multiple locations (addresses)?
    No, i guess not (since there is no address for department) but i see your point.
    So it would be a N:M relationship and i need to create a communicating table?
    Would this be correct?

    department := ConNr(PK,FK), DepartmentName(PK),
    dep_adr := ConNr(PK,FK), DepartmentName(PK), departmentAdress_Code(FK)
    departmentAdress := departmentAdress_Code(FK), County, City, Street, Zipcode

    looks somehow redundant

    Quote Originally Posted by healdem View Post
    can a concern have multiple locations?
    I don't know either, the more complicated way would be yes. So i would like to know if this is correct (since M:N in location to department looks odd, i better see it as a 1:n cardinality):

    concern := ConNr (PK), ConName
    concernAdress := ConNr(PK,FK), County(PK), City(PK), Street(PK), Zipcode(PK)

    This is one long primary key in 'concernAdress' but since i didn't create a synthetic primary key, it has to be that, right?

    Quote Originally Posted by healdem View Post
    can a concern have multiple addresses within one physical location?
    What do you mean with 'physical location'?
    Quote Originally Posted by healdem View Post
    what is the difference between an employee and a worker?
    I can't tell, it was preset - maybe the type of insurance
    Quote Originally Posted by healdem View Post
    what is activity, is it an entity in its own right?
    It might just be a multi-value attribute which just isn't atomic
    Quote Originally Posted by healdem View Post
    can en employee or worker or both be a member of more than one department?
    I think there is the possibility of that. But how can i realize this?
    Does this work?

    worker := WorkerNr(PK), Name, Forename, Birthdate

    DepartmentForWorker := WorkerNr(PK,FK), DepartmentName(PK)

    employee := EmpNr(PK), Name, Forename, Birthdate

    DepartmentForEmp := EmpNr(PK,FK), DepartmentName(PK)

    Quote Originally Posted by healdem View Post
    does an employee have a single address or can they work in multiple locations?
    I think they have one primary adress.

    I hope this makes it easier to verify my schema.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    I'm not going to verify your design
    its your coursework / assignment

    one physical location
    ie there is a single plant, but it could have multiple addresses/postcodes
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Oct 2012
    Posts
    9
    But maybe you could give me some advice or hints where I should take a second look.
    I don't expect you to do my work at all, but i could need some feedback on my thoughts.

  6. #6
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Are there are several of you in this class working on this or a similar exercise?

    Possibly, some of you (along with a mentor or teacher) could review what each has done and what you now understand. And where you need to next proceed.

Posting Permissions

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