 | |
Article claims the following table is not in 1NF
comp.databases.theory comp.databases.theory
 |
| | Thread Tools | Display Modes |
#51
 
| |||
| |||
|
Re: Article claims the following table is not in 1NF -
10-23-2008
, 05:23 PM
|
On Oct 23, 5:38*am, Sru... (AT) gmail (DOT) com wrote: greetings 1) One site claims the following table is not in first normal form – but the definition for 1NF just says that data shouldn’t *contain repeating groups of fields. And it’s quite obvious that there are no repeating columns in the following table, since I wouldn’t consider Last_name column being same as Cust_lastname or Address same as Cust_address ( but I would consider ORDER ( ORDER_ID, ITEM1, ITEM2, ITEM3 ) to have repeating columns ) … COMPANY_DATABASE ( Emp_id#, Last_name, First_name, Address, City, State, Position, Cust_id#, Cust_lastname, Cust_firstname, Cust_address, Cust_city, Ord_num, Ord_date, Prod_id, Cost ) Yes you are correct on that point! *Data is UNF if it is unstructured. We structure it into relations (represented in the system as tables). Particular chunks of data are called relvars (represented in the tables as rows). *A relvar is in 1NF iff every legal value of that relvar contains exactly one value for each attribute. *SO by the time you've organised your data into table rows, it's in 1NF. *It looks like it's in 1NF, not 2NF though  Seems to be a link between employee and customer. *Does an employee have 0..many customers assigned to them? *Does a customer get served by more than one employee? If an employee deals with several customers, will the Emp_id# half of the row be repeated on each row? *If a customer is dealt with by more than one employee, will the customer information need to be repeated on each row? *Presumably an employee with no customers has nulls in thos columns. Yuk. 2) My book claims that if table is not normalized, then primary key can’t be made out of just one attribute. But how can that ALWAYS be true, since even if a table has multi-valued attributes or duplicative columns, we could still have an attribute ( ORDER_ID ) that would uniquely identify the row: ORDER ( ORDER_ID, ITEM1, ITEM2, ITEM3 ) Ahh... only if ORDER_ID is unique. *If so, then it's probably a dataless, unique, unchanging, unambiguous sequence number you made up to go with the items, in which case it is called a surrogate key and a primary key. *If so, your example is not 1NF any more because every non-key attribute is completely dependent on the PK. If ORDER_ID is not unique, you'll need more columns to uniquely identify the row you want won't you? Now even though the above table has repeating columns ( ITEM1, ITEM2, ITEM3 ), ORDER_NUM column would still be able to uniquely identify the row! Similarly, if ITEM column is multi valued, we could still have ORDER_ID as unique key: ORDER ( ORDER_ID, ITEM ) thank you In addition to that... |
It's very important to get this across to people studying database
design: as well as showing the tables, attributes and keys, it is
ESSENTIAL for you to WRITE DOWN THE RULES that govern the data. The
reasons for doing this are 1. the rules contain the information about
Whether or not there is a FD 2. helps stop you making mistakes
yourself.
Examples of rules: Say your employee-customer relation was for
recording current assignments about butlers who are assigned to men...
or PAs contracted to managers in third-party companies if you prefer.
In that case, R1: an emp# cannot possibly be associated with more than
one customer and R2: a customer never has more than one emp. Also
(because you are recording associations) you will R3: never have an
emp that isn't associated with a customer and R4: never have a
customer without a corresponding emp. So every relvar will be unique.
On the other hand, if the same information is to be used as a little
black book listing extractor-fan salesmen and the regular clients on
their list for the geographic area they cover, every single rule is
flipped around.
The same thing applies to your list of items. Are you modelling a
queue of people who are allowed to take up to three uniquely-numbered
plates off a converor belt? If an item is 'claimed' by the fact is
appears in another row, that has a big impact on what your FDs are.
It will create an enormous amount of confusion if you don't write down
the rules. I consider that to be documentation and I normally stick
them in the table and column comments for posterity.
Thanks
#52
| |||
| |||
|
|
On Oct 23, 5:38*am, Sru... (AT) gmail (DOT) com wrote: greetings 1) One site claims the following table is not in first normal form – but the definition for 1NF just says that data shouldn’t *contain repeating groups of fields. And it’s quite obvious that there are no repeating columns in the following table, since I wouldn’t consider Last_name column being same as Cust_lastname or Address same as Cust_address ( but I would consider ORDER ( ORDER_ID, ITEM1, ITEM2, ITEM3 ) to have repeating columns ) … COMPANY_DATABASE ( Emp_id#, Last_name, First_name, Address, City, State, Position, Cust_id#, Cust_lastname, Cust_firstname, Cust_address, Cust_city, Ord_num, Ord_date, Prod_id, Cost ) Yes you are correct on that point! *Data is UNF if it is unstructured. We structure it into relations (represented in the system as tables). Particular chunks of data are called relvars (represented in the tables as rows). *A relvar is in 1NF iff every legal value of that relvar contains exactly one value for each attribute. *SO by the time you've organised your data into table rows, it's in 1NF. *It looks like it's in 1NF, not 2NF though Seems to be a link between employee and customer. *Does an employee have 0..many customers assigned to them? *Does a customer get served by more than one employee? If an employee deals with several customers, will the Emp_id# half of the row be repeated on each row? *If a customer is dealt with by more than one employee, will the customer information need to be repeated on each row? *Presumably an employee with no customers has nulls in thos columns. Yuk. 2) My book claims that if table is not normalized, then primary key can’t be made out of just one attribute. But how can that ALWAYS be true, since even if a table has multi-valued attributes or duplicative columns, we could still have an attribute ( ORDER_ID ) that would uniquely identify the row: ORDER ( ORDER_ID, ITEM1, ITEM2, ITEM3 ) Ahh... only if ORDER_ID is unique. *If so, then it's probably a dataless, unique, unchanging, unambiguous sequence number you made up to go with the items, in which case it is called a surrogate key and a primary key. *If so, your example is not 1NF any more because every non-key attribute is completely dependent on the PK. If ORDER_ID is not unique, you'll need more columns to uniquely identify the row you want won't you? Now even though the above table has repeating columns ( ITEM1, ITEM2, ITEM3 ), ORDER_NUM column would still be able to uniquely identify the row! Similarly, if ITEM column is multi valued, we could still have ORDER_ID as unique key: ORDER ( ORDER_ID, ITEM ) thank you In addition to that... |
It's very important to get this across to people studying database
design: as well as showing the tables, attributes and keys, it is
ESSENTIAL for you to WRITE DOWN THE RULES that govern the data. The
reasons for doing this are 1. the rules contain the information about
Whether or not there is a FD 2. helps stop you making mistakes
yourself.
Examples of rules: Say your employee-customer relation was for
recording current assignments about butlers who are assigned to men...
or PAs contracted to managers in third-party companies if you prefer.
In that case, R1: an emp# cannot possibly be associated with more than
one customer and R2: a customer never has more than one emp. Also
(because you are recording associations) you will R3: never have an
emp that isn't associated with a customer and R4: never have a
customer without a corresponding emp. So every relvar will be unique.
On the other hand, if the same information is to be used as a little
black book listing extractor-fan salesmen and the regular clients on
their list for the geographic area they cover, every single rule is
flipped around.
The same thing applies to your list of items. Are you modelling a
queue of people who are allowed to take up to three uniquely-numbered
plates off a converor belt? If an item is 'claimed' by the fact is
appears in another row, that has a big impact on what your FDs are.
It will create an enormous amount of confusion if you don't write down
the rules. I consider that to be documentation and I normally stick
them in the table and column comments for posterity.
Thanks
#53
| |||
| |||
|
|
On Oct 23, 5:38*am, Sru... (AT) gmail (DOT) com wrote: greetings 1) One site claims the following table is not in first normal form – but the definition for 1NF just says that data shouldn’t *contain repeating groups of fields. And it’s quite obvious that there are no repeating columns in the following table, since I wouldn’t consider Last_name column being same as Cust_lastname or Address same as Cust_address ( but I would consider ORDER ( ORDER_ID, ITEM1, ITEM2, ITEM3 ) to have repeating columns ) … COMPANY_DATABASE ( Emp_id#, Last_name, First_name, Address, City, State, Position, Cust_id#, Cust_lastname, Cust_firstname, Cust_address, Cust_city, Ord_num, Ord_date, Prod_id, Cost ) Yes you are correct on that point! *Data is UNF if it is unstructured. We structure it into relations (represented in the system as tables). Particular chunks of data are called relvars (represented in the tables as rows). *A relvar is in 1NF iff every legal value of that relvar contains exactly one value for each attribute. *SO by the time you've organised your data into table rows, it's in 1NF. *It looks like it's in 1NF, not 2NF though Seems to be a link between employee and customer. *Does an employee have 0..many customers assigned to them? *Does a customer get served by more than one employee? If an employee deals with several customers, will the Emp_id# half of the row be repeated on each row? *If a customer is dealt with by more than one employee, will the customer information need to be repeated on each row? *Presumably an employee with no customers has nulls in thos columns. Yuk. 2) My book claims that if table is not normalized, then primary key can’t be made out of just one attribute. But how can that ALWAYS be true, since even if a table has multi-valued attributes or duplicative columns, we could still have an attribute ( ORDER_ID ) that would uniquely identify the row: ORDER ( ORDER_ID, ITEM1, ITEM2, ITEM3 ) Ahh... only if ORDER_ID is unique. *If so, then it's probably a dataless, unique, unchanging, unambiguous sequence number you made up to go with the items, in which case it is called a surrogate key and a primary key. *If so, your example is not 1NF any more because every non-key attribute is completely dependent on the PK. If ORDER_ID is not unique, you'll need more columns to uniquely identify the row you want won't you? Now even though the above table has repeating columns ( ITEM1, ITEM2, ITEM3 ), ORDER_NUM column would still be able to uniquely identify the row! Similarly, if ITEM column is multi valued, we could still have ORDER_ID as unique key: ORDER ( ORDER_ID, ITEM ) thank you In addition to that... |
It's very important to get this across to people studying database
design: as well as showing the tables, attributes and keys, it is
ESSENTIAL for you to WRITE DOWN THE RULES that govern the data. The
reasons for doing this are 1. the rules contain the information about
Whether or not there is a FD 2. helps stop you making mistakes
yourself.
Examples of rules: Say your employee-customer relation was for
recording current assignments about butlers who are assigned to men...
or PAs contracted to managers in third-party companies if you prefer.
In that case, R1: an emp# cannot possibly be associated with more than
one customer and R2: a customer never has more than one emp. Also
(because you are recording associations) you will R3: never have an
emp that isn't associated with a customer and R4: never have a
customer without a corresponding emp. So every relvar will be unique.
On the other hand, if the same information is to be used as a little
black book listing extractor-fan salesmen and the regular clients on
their list for the geographic area they cover, every single rule is
flipped around.
The same thing applies to your list of items. Are you modelling a
queue of people who are allowed to take up to three uniquely-numbered
plates off a converor belt? If an item is 'claimed' by the fact is
appears in another row, that has a big impact on what your FDs are.
It will create an enormous amount of confusion if you don't write down
the rules. I consider that to be documentation and I normally stick
them in the table and column comments for posterity.
Thanks
#54
| |||
| |||
|
|
On Oct 23, 5:38*am, Sru... (AT) gmail (DOT) com wrote: greetings 1) One site claims the following table is not in first normal form – but the definition for 1NF just says that data shouldn’t *contain repeating groups of fields. And it’s quite obvious that there are no repeating columns in the following table, since I wouldn’t consider Last_name column being same as Cust_lastname or Address same as Cust_address ( but I would consider ORDER ( ORDER_ID, ITEM1, ITEM2, ITEM3 ) to have repeating columns ) … COMPANY_DATABASE ( Emp_id#, Last_name, First_name, Address, City, State, Position, Cust_id#, Cust_lastname, Cust_firstname, Cust_address, Cust_city, Ord_num, Ord_date, Prod_id, Cost ) Yes you are correct on that point! *Data is UNF if it is unstructured. We structure it into relations (represented in the system as tables). Particular chunks of data are called relvars (represented in the tables as rows). *A relvar is in 1NF iff every legal value of that relvar contains exactly one value for each attribute. *SO by the time you've organised your data into table rows, it's in 1NF. *It looks like it's in 1NF, not 2NF though Seems to be a link between employee and customer. *Does an employee have 0..many customers assigned to them? *Does a customer get served by more than one employee? If an employee deals with several customers, will the Emp_id# half of the row be repeated on each row? *If a customer is dealt with by more than one employee, will the customer information need to be repeated on each row? *Presumably an employee with no customers has nulls in thos columns. Yuk. 2) My book claims that if table is not normalized, then primary key can’t be made out of just one attribute. But how can that ALWAYS be true, since even if a table has multi-valued attributes or duplicative columns, we could still have an attribute ( ORDER_ID ) that would uniquely identify the row: ORDER ( ORDER_ID, ITEM1, ITEM2, ITEM3 ) Ahh... only if ORDER_ID is unique. *If so, then it's probably a dataless, unique, unchanging, unambiguous sequence number you made up to go with the items, in which case it is called a surrogate key and a primary key. *If so, your example is not 1NF any more because every non-key attribute is completely dependent on the PK. If ORDER_ID is not unique, you'll need more columns to uniquely identify the row you want won't you? Now even though the above table has repeating columns ( ITEM1, ITEM2, ITEM3 ), ORDER_NUM column would still be able to uniquely identify the row! Similarly, if ITEM column is multi valued, we could still have ORDER_ID as unique key: ORDER ( ORDER_ID, ITEM ) thank you In addition to that... |
It's very important to get this across to people studying database
design: as well as showing the tables, attributes and keys, it is
ESSENTIAL for you to WRITE DOWN THE RULES that govern the data. The
reasons for doing this are 1. the rules contain the information about
Whether or not there is a FD 2. helps stop you making mistakes
yourself.
Examples of rules: Say your employee-customer relation was for
recording current assignments about butlers who are assigned to men...
or PAs contracted to managers in third-party companies if you prefer.
In that case, R1: an emp# cannot possibly be associated with more than
one customer and R2: a customer never has more than one emp. Also
(because you are recording associations) you will R3: never have an
emp that isn't associated with a customer and R4: never have a
customer without a corresponding emp. So every relvar will be unique.
On the other hand, if the same information is to be used as a little
black book listing extractor-fan salesmen and the regular clients on
their list for the geographic area they cover, every single rule is
flipped around.
The same thing applies to your list of items. Are you modelling a
queue of people who are allowed to take up to three uniquely-numbered
plates off a converor belt? If an item is 'claimed' by the fact is
appears in another row, that has a big impact on what your FDs are.
It will create an enormous amount of confusion if you don't write down
the rules. I consider that to be documentation and I normally stick
them in the table and column comments for posterity.
Thanks
#55
| |||
| |||
|
|
On Oct 23, 5:38*am, Sru... (AT) gmail (DOT) com wrote: greetings 1) One site claims the following table is not in first normal form – but the definition for 1NF just says that data shouldn’t *contain repeating groups of fields. And it’s quite obvious that there are no repeating columns in the following table, since I wouldn’t consider Last_name column being same as Cust_lastname or Address same as Cust_address ( but I would consider ORDER ( ORDER_ID, ITEM1, ITEM2, ITEM3 ) to have repeating columns ) … COMPANY_DATABASE ( Emp_id#, Last_name, First_name, Address, City, State, Position, Cust_id#, Cust_lastname, Cust_firstname, Cust_address, Cust_city, Ord_num, Ord_date, Prod_id, Cost ) Yes you are correct on that point! *Data is UNF if it is unstructured. We structure it into relations (represented in the system as tables). Particular chunks of data are called relvars (represented in the tables as rows). *A relvar is in 1NF iff every legal value of that relvar contains exactly one value for each attribute. *SO by the time you've organised your data into table rows, it's in 1NF. *It looks like it's in 1NF, not 2NF though Seems to be a link between employee and customer. *Does an employee have 0..many customers assigned to them? *Does a customer get served by more than one employee? If an employee deals with several customers, will the Emp_id# half of the row be repeated on each row? *If a customer is dealt with by more than one employee, will the customer information need to be repeated on each row? *Presumably an employee with no customers has nulls in thos columns. Yuk. 2) My book claims that if table is not normalized, then primary key can’t be made out of just one attribute. But how can that ALWAYS be true, since even if a table has multi-valued attributes or duplicative columns, we could still have an attribute ( ORDER_ID ) that would uniquely identify the row: ORDER ( ORDER_ID, ITEM1, ITEM2, ITEM3 ) Ahh... only if ORDER_ID is unique. *If so, then it's probably a dataless, unique, unchanging, unambiguous sequence number you made up to go with the items, in which case it is called a surrogate key and a primary key. *If so, your example is not 1NF any more because every non-key attribute is completely dependent on the PK. If ORDER_ID is not unique, you'll need more columns to uniquely identify the row you want won't you? Now even though the above table has repeating columns ( ITEM1, ITEM2, ITEM3 ), ORDER_NUM column would still be able to uniquely identify the row! Similarly, if ITEM column is multi valued, we could still have ORDER_ID as unique key: ORDER ( ORDER_ID, ITEM ) thank you In addition to that... |
It's very important to get this across to people studying database
design: as well as showing the tables, attributes and keys, it is
ESSENTIAL for you to WRITE DOWN THE RULES that govern the data. The
reasons for doing this are 1. the rules contain the information about
Whether or not there is a FD 2. helps stop you making mistakes
yourself.
Examples of rules: Say your employee-customer relation was for
recording current assignments about butlers who are assigned to men...
or PAs contracted to managers in third-party companies if you prefer.
In that case, R1: an emp# cannot possibly be associated with more than
one customer and R2: a customer never has more than one emp. Also
(because you are recording associations) you will R3: never have an
emp that isn't associated with a customer and R4: never have a
customer without a corresponding emp. So every relvar will be unique.
On the other hand, if the same information is to be used as a little
black book listing extractor-fan salesmen and the regular clients on
their list for the geographic area they cover, every single rule is
flipped around.
The same thing applies to your list of items. Are you modelling a
queue of people who are allowed to take up to three uniquely-numbered
plates off a converor belt? If an item is 'claimed' by the fact is
appears in another row, that has a big impact on what your FDs are.
It will create an enormous amount of confusion if you don't write down
the rules. I consider that to be documentation and I normally stick
them in the table and column comments for posterity.
Thanks
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.