0

I need to normalize a relation so that it is in the 1st normal form. I know how to normalize multi-valued attributes, it is just composite attributes that are giving me issues. For example, one of the composite attributes is 'Employee-Address', and as expected, it contains sub-attributes like 'House-Number', 'Street-Name' etc.

How do I normalize this? These composite attributes are not multivalued/complex i.e a single employee may only have 1 address. I also believe the 'employee-id' attribute can be used to identify all of sub-attributes of the address. Is it as simple as breaking up the composite attribute and storing each sub-attribute as its own attribute in the relation? This way all the sub-attributes would become simple, single and stored values?

Before anyone complains; this question is related to a college assignment and I've looked through the entirety of the recommended textbook(and the internet) for the answer, which I have not found. Of course, I'd like a solution to my answer, but if you'd rather give your own example that is great; any advice or pointers are much appreciated!

Tom Fenton
  • 19
  • 1
  • 3
  • In the real world, addresses are stored as a single row in a table, with the street address stored in a string and then other location (such as postal code) in other fields. Sometimes, features are extracted into other columns (apartment number), but the address row itself is not filled with additional ids, except perhaps for higher level geographies. – Gordon Linoff Dec 31 '16 at 17:46
  • @Gordon I agree. My only guess for the address being of a composite fashion is for assignment grading purposes! I personally don't see how just removing the composite "brackets" and having each stored in separate fields would make any difference. – Tom Fenton Dec 31 '16 at 17:51
  • What do you mean by "normalize"? That & related terms are used with different meanings (and often misused) and it is not clear what you mean here. What is the input and what is the output supposed to be? What is your texbook? Re confusions around "normalization" see [this](http://stackoverflow.com/a/24038895/3404097). Also [this](http://stackoverflow.com/a/41395340/3404097). – philipxy Jan 01 '17 at 12:00

1 Answers1

0

The only requirement of 1NF is that each attribute contain only a single "atomic" value.

If the question suggests that the address is a composite value and that each part of the address is a separate sub-value, then you should create an attribute for each sub-value.

You probably want to store each part of the address in its own attribute anyway, so you can index them and efficiently run queries like "find everyone in New York City."

Willis Blackburn
  • 8,068
  • 19
  • 36
  • Thank you, makes a lot of sense. Btw, the relation has a multivalued attribute and I've decided that I am going create a separate relation for it. To do this, I have to bring the primary key of the original relation over and combined it with the multivalued attribute itself to create the primary key of the new relation. The primary key of the original relation is composite, is this okay? Thank you and happy new year! – Tom Fenton Dec 31 '16 at 18:22
  • I'm not sure I understand what you're saying. You're creating a separate relation for addresses, or something like that? Whether or not that makes sense depends on what's in the original relation. Is it "employees" or something else? – Willis Blackburn Dec 31 '16 at 18:28
  • Sorry, I will clarify. The multivalued attribute I am referring to is not Address, it is one called 'Employee-Qualifications'. The original relation is very poorly mapped, it contains all the attributes of the database, everything from customer data to employee data, and even product info. This relation was set by the assignment, I would change it if I could prior to 1NF. Basically will the composite key of the original relation work when creating a separate relation for qualifications? The original relation PK does not contain employee id however, but the textbooks say you must use the PK. – Tom Fenton Dec 31 '16 at 18:39