gpt4 book ai didi

mysql - 在验证第三范式的数据库逻辑模式时需要一些帮助

转载 作者:可可西里 更新时间:2023-11-01 08:36:32 25 4
gpt4 key购买 nike

这原本是对此处另一个问题中提出的逻辑模式的“更新”:Getting ERROR 1701, ERROR 1452 and ERROR 1305 errors in MySQL - Need some expertise ...

我想我已经成功地验证了这个架构是否符合第一和第二范式,但我不确定这是否符合第三范式。这是有问题的模型:

An attempt on a logical database model to Third Normal Form

下面是相关代码(注意:出于某种原因,我无法在 sql 代码中重新创建 1:1 关系,如上面的逻辑模型所示):

-- 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;

来自以下链接:

这是我对 Normalization to 3NF 的理解:

  • 第一范式表示不允许重复值
  • 第二范式表示 1NF 并且属性依赖于整个主键而不是主键的一部分(如果该表中的值需要以某种方式相互关联并进行比较,我认为这是分区表)。
  • 第三范式表示 2NF 且无晶体管值(例如,如果 x = y 且 y = z,则 x = z)

将理论知识付诸实践对我来说非常困难,尤其是将“实践”转化为正常工作的规范化 MySQL 代码。如果有人能够帮助我完成模型并给我一些关于将模型标准化为 3NF 的指示,我将非常感激。

提前致谢!

最佳答案

我认为这不在 3NF 中,在 contact 附近 table 。如果我错了,这仍然是一种糟糕的数据存储方式,应该进行更改。

对不起,如果这有点困惑......

完全有可能在你的contact中有如下结构表作为整个表的主键:

+----------+-------------+---------------+---------+| personid | homephoneid | mobilephoneid | emailid |+----------+-------------+---------------+---------+|        1 |           1 |             1 |       1 ||        1 |           1 |             1 |       2 ||        1 |           1 |             2 |       3 |+----------+-------------+---------------+---------+

如您所见homephoneidmobilephoneid重复所以更新表 homephone将对 contact 进行 3 次更新.

我不同意数据模型,因为您需要 person有一个homehone我没有,只有一部手机。在这种情况下,创建新的 person 时你还必须创建一个新的 contact和一个新的 homephone .

作为contact只是一个主键并且主键值不能为 null,您还要求创建一个 mobilephone和一个 emailaddress ,这意味着 person依赖于 emailaddress .

作为emailaddress依赖于 contact ,这又取决于 person你已经创建了一个循环依赖,它打破了 3NF。

在我看来,如果您想确保人们有家庭电话号码,您有两个选择:

  1. 如果你只想要 person拥有一个 homephone然后添加这个进入person table 。它是唯一的联系人级别信息和应该存放在那里。
  2. 如果您想让人们拥有多个家庭电话号码 -记住多人可以使用同一个电话号码 - 但是不关心手机那么你需要创建一个表 personhomephones ,比如说,用主键 personid,
    homephoneid
    而不是放 homephoneid在联系人表中。

就我个人而言,我不会这样做。我不会确保某人必须有家庭电话号码,而是一个主要电话号码,你不关心它是什么类型。我会允许人们添加不同的联系方式,但不允许这些方式存在

这需要以下结构:

  • person - 添加 primaryPhoneID
  • primaryphone ( primaryphoneID, phonenumber) - PK primaryphoneID

然后对于允许不存在的联系方式:

  • contactType ( contactTypeID, contactType ) - PK contactTypeID
  • contact ( contactID, contactTypeID, value ) - PK contactID, contactTypeID
  • personContact ( personID, contactID, contactTypeID ) - PK 一切

虽然这可能会导致 contact 之间重复和 primaryphone它们是不同的数据位,我认为这很好。如果您坚持不允许任何重复,则必须将电话与其他联系方式分开,这会使模型更加复杂:

  • phonetype ( phoneTypeId, phoneType )
  • phone ( phoneID, phoneTypeID, phonenumber) - PK phoneID, phoneTypeID
  • contactPhone ( personID, phoneTypeID, phoneID ) - PK 一切

关于mysql - 在验证第三范式的数据库逻辑模式时需要一些帮助,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/10265123/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com