Data normalization (from 0NF to 1NF)

Question

I am designing a relational database in mySQL - a library management system. I want to show examples of the data in 1NF, 2NF, 3NF, BCNF, etc. My example of 1NF looks something like this

"A relation is in first normal form if the domain of each attribute contains only atomic values, and the value of each attribute contains only a single value from that domain."

I am unsure of if the data is in 1NF. For example is the attribute 'ISBN' atomic?

ISBN is a unique numeric commercial book identifier. An ISBN consists of 4 parts (if it is a 10 digit ISBN) or 5 parts (for a 13 digit ISBN). The different parts is prefix element, registration group, registrant, publication and check digit. Would you divide the identification number into smaller units, to guarantee an atomic value?

Answer 1

Yes, it's atomic; your domain is the ISBN. If you were storing the domains for ISBNPrefix, ISBNRegistrationGroup, ISBNRegistrant, ISBNPublication and ISBNCheckDigit, then you'd split it up.

Consider the North American phone number +1-234-567-8901 ext 234567. Many systems choose to store such a number under two domains, such as PhoneNumber and PhoneNumberExtension, taking perhaps 2345678901 under the PhoneNumber domain and 234567 under the PhoneNumberExtension domain, ignoring the Phone Number Country Code altogether ( largely because it is always 1 ). Since the domains are defined as such, it is not incorrect to store them in such a manner. An example of violating 1NF in this case would be to store multiple extension numbers in the same row for a singular entity existing in the PhoneNumber domain.

| PhoneNumber | PhoneNumberExtension |
  2345678901    234567, 890123

Instead, to preserve 1NF, the PhoneNumberExtension attributes are stored separately.

| PhoneNumber | PhoneNumberExtension |
  2345678901    234567
  2345678901    890123

A system with more granular domains may choose to store phone numbers by PhoneNumberCountryCode, PhoneNumberAreaCode, PhoneNumberPrefix, PhoneNumberLineNumber and PhoneNumberExtension

| CountryCode | AreaCode | Prefix | LineNumber | Extension |
  1             234        567      8901         234567

Since the domains are defined in such a way, it is again not incorrect to store the data as such. The same logic applies to various "compound attributes," like your ISBN; geographical coordinate data and municipal mailing addresses are some common examples, but such "Domain grouping" happens even in basic data types - consider DATETIME, a raw datatype for an example - one part date, one part time.

The way you choose to define your domains will largely be a decision on how you intend to use them. In the telephone example, there tends to be little value in breaking apart AreaCode since other more appropriate demographics are often available, such as City, though splitting the domain apart is not a "wrong" choice. If there is little value for your purposes to split apart the ISBN, you are not violating 1NF by defining the domain on a less granular level - you just can't store two of them in the same row for the same entity.

Answer 2

Atomic in the context of 1NF just means "not a relation".

An ISBN number is not a relation, so it is atomic with regard to relational operators. For example you can't insert new rows in an ISBN-number.