Нужна помощь в проверке логической схемы базы данных в третьей нормальной форме

Question

Первоначально это должно было стать "обновлением" логической схемы, представленной в другом вопросе: получение ошибок ERROR 1701, ERROR 1452 и ERROR 1305 в MySQL. Требуются некоторые знания...

Я думаю, что я успешно проверил эту схему для 1-й и 2-й нормальной формы, но не уверен, что она соответствует 3-й нормальной форме. Вот эта модель:

А вот соответствующий код (примечание: по какой-то причине я не могу воссоздать отношения 1:1 в коде sql, как показано выше в логической модели):

-- database_schema.sql.
-- This sql script creates the structure.
-- of the rugby club database.

DROP DATABASE IF EXISTS database_rugby;

CREATE DATABASE database_rugby;

USE database_rugby;

-- Create the "person" table.
--
-- This table has one:one relationships
-- with the parent, coach and player 
-- tables.
DROP TABLE IF EXISTS `person` ;
CREATE TABLE `person` (
  `personID` INT(5) NOT NULL AUTO_INCREMENT ,
  `firstName` VARCHAR(50) NOT NULL ,
  `lastName` VARCHAR(50) NOT NULL ,
  `dateOfBirth` DATE NOT NULL ,
  `streetAddress` VARCHAR(150) NOT NULL ,
  `suburbAddress` VARCHAR(150) NULL DEFAULT NULL ,
  `cityAddress` VARCHAR(150) NOT NULL ,
  `photo` BLOB NULL DEFAULT NULL ,
  PRIMARY KEY (`personID`))
ENGINE = InnoDB;

-- Create the "parent" table.
DROP TABLE IF EXISTS `parent` ;
CREATE TABLE `parent` (
  `parentID` INT(5) NOT NULL ,
  `personID` INT(5) NOT NULL ,
  PRIMARY KEY (`parentID`, `personID`), 
  FOREIGN KEY (`personID`) REFERENCES `person` (`personID`)
  ON DELETE CASCADE 
  ON UPDATE CASCADE)
ENGINE = InnoDB;


-- Create the "school" table.
DROP TABLE IF EXISTS `school` ;
CREATE TABLE `school` (
  `schoolID` INT(5) NOT NULL AUTO_INCREMENT ,
  `schoolName` VARCHAR(100) NOT NULL ,
  PRIMARY KEY (`schoolID`))
ENGINE = InnoDB;


-- Create the "player" table.
--
-- Inherits fields from the "person"
-- and "school" tables.
DROP TABLE IF EXISTS `player` ;

CREATE TABLE `player` (
  `playerID` INT(5) NOT NULL ,
  `personID` INT(5) NOT NULL ,
  `schoolID` INT(5) NOT NULL ,
  PRIMARY KEY (`playerID`, `personID`), 
    FOREIGN KEY (`personID`) 
    REFERENCES `person` (`personID`)
    ON DELETE CASCADE 
    ON UPDATE CASCADE, 
    FOREIGN KEY (`schoolID`)
    REFERENCES `school` (`schoolID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- Create the "coach" table.
DROP TABLE IF EXISTS `coach`;
CREATE TABLE `coach`(
  `coachID` INT(5) NOT NULL ,
  `dateBeganCoaching` DATE NOT NULL ,
  `personID` INT(5) NOT NULL ,
  PRIMARY KEY (`coachID`, `personID`), 
  FOREIGN KEY (`personID`) 
  REFERENCES `person` (`personID`)
  ON DELETE CASCADE 
  ON UPDATE CASCADE)
ENGINE = InnoDB;


-- Create the "family" table.
--
-- This is a linking table 
-- that describes the many:many 
-- relationship between "parent" 
-- and "player" tables.
DROP TABLE IF EXISTS `family` ;
CREATE TABLE `family` (
  `parentID` INT(5) NOT NULL ,
  `playerID` INT(5) NOT NULL ,
    FOREIGN KEY (`playerID` )
    REFERENCES `player` (`playerID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    FOREIGN KEY (`parentID`)
    REFERENCES `parent` (`parentID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- Create the "grade" table.
DROP TABLE IF EXISTS `grade`;
CREATE  TABLE `grade`(
  `gradeID` INT(5) NOT NULL AUTO_INCREMENT ,
  `gradeName` VARCHAR(50) NOT NULL ,
  `minWeight` INT(3) NOT NULL ,
  `maxWeight` INT(3) NOT NULL ,
  `minAge` INT(3) NOT NULL ,
  `maxAge` INT(3) NOT NULL ,
  `ballSize` INT(1) NOT NULL ,
  PRIMARY KEY (`gradeID`) )
ENGINE = InnoDB;


-- Create the "coachQualification" table.
DROP TABLE IF EXISTS `coachQualification` ;

CREATE TABLE `coachQualification` (
  `qualID` INT(5) NOT NULL AUTO_INCREMENT ,
  `qualName` CHAR(5) NOT NULL ,
  `gradeID` INT(5) NOT NULL ,
  PRIMARY KEY (`qualID`) ,
    FOREIGN KEY (`gradeID`)
    REFERENCES `grade` (`gradeID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- Create the "homePhone" table.
DROP TABLE IF EXISTS `homePhone` ;
CREATE TABLE `homePhone` (
  `homePhoneID` INT(5) NOT NULL AUTO_INCREMENT ,
  `homeNumber` CHAR(9) NOT NULL ,
  PRIMARY KEY (`homePhoneID`))
ENGINE = InnoDB;


-- Create the "mobilePhone" table.
DROP TABLE IF EXISTS `mobilePhone` ;

CREATE TABLE `mobilePhone` (
  `mobilePhoneID` INT(5) NOT NULL AUTO_INCREMENT ,
  `mobileNumber` CHAR(10) NULL DEFAULT NULL ,
  PRIMARY KEY (`mobilePhoneID`))
ENGINE = InnoDB;


-- Create the "emailAddress" table.
DROP TABLE IF EXISTS `emailAddress` ;

CREATE TABLE `emailAddress` (
  `emailAddressID` INT(5) NOT NULL AUTO_INCREMENT ,
  `emailAddress` CHAR(10) NULL DEFAULT NULL ,
  PRIMARY KEY (`emailAddressID`))
ENGINE = InnoDB;


-- Create the "Contact" table
--
-- This is a linking table 
-- that describes the many:many 
-- relationships between "person" 
-- and the "homePhone", "mobilePhone", 
-- and "emailAddress" tables.
DROP TABLE IF EXISTS `contact` ;
CREATE TABLE `contact` (
  `personID` INT(5) NOT NULL ,
  `homePhoneID` INT(5) NOT NULL ,
  `mobilePhoneID` INT(5) NULL DEFAULT NULL ,
  `emailAddressID` INT(5) NULL DEFAULT NULL ,
    FOREIGN KEY (`personID` )
    REFERENCES `person` (`personID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    FOREIGN KEY (`homePhoneID`)
    REFERENCES `homePhone` (`homePhoneID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    FOREIGN KEY (`mobilePhoneID`)
    REFERENCES `mobilePhone` (`mobilePhoneID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    FOREIGN KEY (`emailAddressID`)
    REFERENCES `emailAddress` (`emailAddressID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- Create the "qualificationSet" table.
--
-- This is a linking table 
-- that describes the many:many 
-- relationship between "coach" 
-- and "coachQualification" tables.
DROP TABLE IF EXISTS `qualificationSet` ;
CREATE TABLE `qualificationSet` (
  `coachID` INT(5) NOT NULL ,
  `qualID` INT(5) NOT NULL ,
    FOREIGN KEY (`coachID`)
    REFERENCES `coach` (`coachID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    FOREIGN KEY (`qualID`)
    REFERENCES `coachQualification` (`qualID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- Create the "team" table.
DROP TABLE IF EXISTS `team` ;
CREATE TABLE `team` (
  `teamID` INT(5) NOT NULL AUTO_INCREMENT ,
  `teamName` VARCHAR(50) NOT NULL ,
  `teamYear` INT(2) NOT NULL ,
  `gradeID` INT(5) NOT NULL ,
  PRIMARY KEY (`teamID`) ,
    FOREIGN KEY (`gradeID`)
    REFERENCES `grade` (`gradeID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- Create the "teamAllocation" table
--
-- this is a linking table for a 
-- many:many relationship between
-- team and player tables.
DROP TABLE IF EXISTS `teamAllocation` ;

CREATE TABLE `teamAllocation` (
  `teamID` INT(5) NOT NULL ,
  `playerID` INT(5) NOT NULL ,
    FOREIGN KEY (`teamID` )
    REFERENCES `team` (`teamID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    FOREIGN KEY (`playerID`)
    REFERENCES `player` (`playerID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- Create the "teamCoachAllocation" table.
--
-- This is a linking table 
-- that describes the many:many 
-- relationship between "coach" 
-- and "team" tables.
DROP TABLE IF EXISTS `teamCoachAllocation` ;
CREATE TABLE `teamCoachAllocation` (
  `coachID` INT(5) NOT NULL ,
  `teamID` INT(5) NOT NULL ,
    FOREIGN KEY (`coachID`)
    REFERENCES `coach` (`coachID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    FOREIGN KEY (`teamID`)
    REFERENCES `team` (`teamID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

Из этих ссылок ниже:

• http://en.wikipedia.org/wiki/First_normal_form
• http://en.wikipedia.org/wiki/Second_normal_form
• http://en.wikipedia.org/wiki/Third_normal_form

Это мое понимание нормализации в 3NF:

• Первая нормальная форма означает, что нельзя повторять значения
• Вторая нормальная форма означает, что 1NF и атрибуты зависят от всего первичного ключа, а не от части первичного ключа (я думаю, что это как таблицы разбиения, если значения в этой таблице должны каким-либо образом соотноситься друг с другом и иметь сравнения).
• Третья нормальная форма означает 2NF и не имеет транзитивных значений (например, если x = y и y = z, x = z)

Применение этих знаний из теории на практике довольно сложно для меня, особенно перевод этой "практики" в рабочий нормализованный код MySQL. Если кто-то сможет помочь мне пройти модель и дать несколько советов по нормализации модели до 3NF, я был бы очень признателен.

Заранее спасибо!

Answer 1

Я думаю, что это не в 3NF, во всем contact Таблица. Если я не прав, это все равно плохой способ хранения данных, и, вероятно, его следует изменить.

Извините, если это немного запутано...

Вполне возможно иметь следующую структуру в вашем contact Таблица, поскольку вся таблица является первичным ключом:

+ ---------- + ------------- + --------------- + -------- - +
| лицо | домашний телефон | мобильный телефон | Emailid |
+----------+-------------+---------------+---------+
|        1 |           1 |             1 |       1 |
|        1 |           1 |             1 |       2 |
|        1 |           1 |             2 |       3 |
+----------+-------------+---------------+---------+

Как вы можете видеть оба homephoneid а также mobilephoneid дублируются, поэтому обновление таблицы homephone приведет к 3 обновлениям contact,

Я не согласен с моделью данных, так как вам требуется person иметь homehone У меня его нет, только мобильный. В этой ситуации при создании нового person Вы также должны создать новый contact и новый homephone,

Как contact это просто первичный ключ, и значение первичного ключа не может быть нулевым, вам также необходимо создать mobilephone и emailaddress, Который означает, что person зависит от emailaddress,

Как emailaddress зависит от contact который в свою очередь зависит от person Вы создали круговую зависимость, которая нарушает 3NF.

На мой взгляд, у вас есть два варианта, если вы хотите, чтобы у людей был домашний телефон:

1. Если вы хотите только person иметь один homephone затем добавьте это в person Таблица. Это уникальная информация об уровне контакта и должна храниться там.
2. Если вы хотите, чтобы люди имели несколько домашних телефонных номеров - помня, что несколько человек могут использовать один и тот же номер телефона - но не заботитесь о мобильных телефонах, то вам нужно создать таблицу personhomephones скажем, с первичным ключом personid, homephoneid и не ставить homephoneid в контактной таблице.

Лично я бы не сделал ни одного из этих. Я бы не стал гарантировать, что у кого-то должен быть домашний номер телефона, а не основной номер телефона, где вам все равно, какой это тип. Я хотел бы, чтобы люди добавляли разные способы контакта, но позволяли бы им не существовать

Для этого потребуется следующая структура:

• person - добавлять primaryPhoneID
• primaryphone ( primaryphoneID, phonenumber) - ПК primaryphoneID

Тогда для контактных методов, которые могут не существовать:

• contactType ( contactTypeID, contactType ) - ПК contactTypeID
• contact ( contactID, contactTypeID, value ) - ПК contactID, contactTypeID
• personContact ( personID, contactID, contactTypeID ) - ПК все

Хотя это может привести к дублированию между contact а также primaryphone это отдельные биты данных, и я думаю, что это нормально. Если вы настаиваете на том, чтобы вообще не допускать дублирования, вам придется отделить телефоны от других способов связи, что усложняет модель:

• phonetype ( phoneTypeId, phoneType )
• phone ( phoneID, phoneTypeID, phonenumber) - ПК phoneID, phoneTypeID
• contactPhone ( personID, phoneTypeID, phoneID ) - ПК все

Answer 2

Существует алгоритм, который в конечном итоге разлагает каждое отношение в вашей схеме, чтобы получить эквивалентную схему в 3NF. Google хорош для этого!!

Чтобы получить советы по дизайну вашей схемы, вы должны хотя бы описать контекст и функциональные ограничения для сущностей, которые вы должны представлять.