Is this a 1NF failure?

Question

When considering the failure of 1NF non-duplicate groups of elements, what if you want to have an established limit on the number of repeating groups?

For example, you want a student to have only 3 phone numbers. No more. Will the table below be considered a 1NF failure?

Student 1    Phone1    Phone2    Phone3
Sally        111-1111 222-2222   333-3333
John         555-5555 999-9999   NULL

You will create a limit. Is this an acceptable, efficient database design?

Would it be better to put the phone numbers in a separate table, as 1NF requires? How would you create a limit of 3 numbers per user if it were in a separate table?

Answer 1

, . , , , , . (StudentPhoneNumbers, ), . , , .

Answer 2

1NF . , :

Student     Phones
'John D'    '555-5555, 666-6666, 777-7777'
'Sally S'   '111-1111, 222-2222'

2NF, Name, Phone, Address Phone:

Name        Phone       Address
'John D'    '555-5555'  '1 Square Village'
'John D'    '666-6666'  '1 Square Village'
'John D'    '777-7777'  '1 Square Village'
'Sally S'   '111-1111'  '999 Flash City'
'Sally S'   '222-2222'  '999 Flash City'

3NF, AreaName , :

Name        Area    Phone   AreaName
'John D'    '555'   '5555'  '111name'
'John D'    '666'   '6666'  '666name'
'John D'    '777'   '7777'  '777name'
'Sally S'   '111'   '1111'  '111name'
'Sally S'   '222'   '2222'  '222name'

1NF, . PhoneNumber .

, , 1NF. . , - , ? . . . .

, . ! Stack Overflow.

Answer 3

(relvar) 1NF, , , , : , 1NF. , , .

, "", . Chris Date:

, null . : "", , ( ); "" , ( ); "", , ( , ). , nulls , : . , nulls , , , ( , , ); , .

1NF , . " " , " ".

, null , relvar 1NF, , (, ), , .

Answer 4

user1122200, , . (, : "", "",...). . , , (, , - ), :

select *
from students
where 
  Phone1 = '91112223' or
  Phone2 = '91112223' or
  Phone3 = '91112223'

Answer 5

3 , ?

, , .

SQL- SQL-92:

CREATE TABLE Students
(
 student_name VARCHAR(20) NOT NULL UNIQUE
);

CREATE TABLE StudentPhonebook
(
 student_name VARCHAR(20) NOT NULL
    REFERENCES Students (student_name), 
 phone_number CHAR(8) NOT NULL
    CHECK (phone_number LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'), 
 UNIQUE (student_name, phone_number)
);

CREATE ASSERTION students_max_three_phone_numbers
   CHECK (
          NOT EXISTS (
                      SELECT *
                        FROM (
                              SELECT student_name, COUNT(*) AS tally
                                FROM StudentPhonebook
                               GROUP 
                                  BY student_name
                             ) AS DT1
                       WHERE tally > 3
                     )
         );

MySQL does not support CHECKany taste, and the SQL product does not support CREATE ASSERTION, so the above restrictions must be written using procedural code, for example. triggers.

Of interest, if your SQL product supports row CHECK-level constraints at the row level (as is usually done), you can use an attribute occurrencewith a constraint BETWEEN 1 AND 3, and then include this attribute in a key constraint, for example.

CREATE TABLE StudentPhonebook
(
 student_name VARCHAR(20) NOT NULL
    REFERENCES Students (student_name), 
 phone_number CHAR(8) NOT NULL
    CHECK (phone_number LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'), 
 occurrence INTEGER DEFAULT 1 NOT NULL
    CHECK (occurrence BETWEEN 1 AND 3), 
 UNIQUE (student_name, phone_number), 
 UNIQUE (student_name, occurrence)
);