了解规范化和重复-我猜我不愿意-正在添加艺术家&标题编号 [英] Understanding Normalization & Duplicates - I Guess I Don't - Adding Artist & Title Ids

问题描述

我从一张表格开始，该表格列出了1958年至1980年按日期排列的前100首歌曲.每个日期都有100条记录.显然，随着歌曲从一周到一周的变化位置，许多歌曲将被重复.此外，艺术家将被复制很多次(想想猫王).该表中有约116,000条记录.

I began with a table listing the top 100 songs by date for the years 1958 through 1980. For each date, there are 100 records. Obviously many will be duplicates as a song changes position from week to week. Also, the artists will be duplicated (think Elvis) numerous times. There are ~ 116,000 records in the table.

此表具有以下字段

uniq,
date,
artist,
title,
position

为了消除重复(据我所知标准化)，我已经修改了表格，使其现在看起来像这样

To eliminate duplicates (normalization as I understand it) I have modified the table so that it now looks like this

uniq,
date,
artistcode,
titlecode,
position

并有两个新的桌子艺术家和头衔. 艺术家看起来像这样

And have two new tables artists and titles. Artists looks like this

artist,
artistcode

标题看起来像这样

title,
titlecode

要朝正确的方向开始，我只想重新组装(联接)这些表，以便有一个看起来像原始表的视图，即

To get started in the right direction, I simply want to reassemble (join) these tables so that I have a view that looks like the original table, ie

uniq,
date,
artist,
title,
position

并具有那些116000记录.在阅读了几本书并使用了一些教程之后，我得出的结论是，我对规范化应该做的事情有误解，否则我将朝着错误的方向前进.

and has those 116000 records. After reading a couple of books and working with several tutorials, I have come to the conclusion that I have a misconception of what normalization should do, or I am simply headed in the wrong direction.

非常感谢创建视图的SQL语法.

The SQL syntax to create the view would be much appreciated.

推荐答案

什么是重复项"? 本身没有错，多次出现相同的值.您需要开始阅读一些学术教科书/关于信息建模和关系数据库的幻灯片/课程.

What "duplicates"? There is nothing wrong per se with the same value appearing multiple times. You need to begin reading some academic textbook(s)/slides/course(s) about information modeling and relational databases.

表中是否存在的每一行都对情况进行了说明. 规范化解决的重复"和冗余"问题有时在同一张表的多行中出现对情况说同样的话. (可能涉及也可能不涉及多次出现的子行值.)

Each row that is in or not in a table makes a statement about the situation. The sort of "duplicate" and "redundancy" problems that normalization addresses arise sometimes when multiple rows of the same table say the same thing about the situation. (Which might or might not involve subrow values appearing multiple times.)

例如:如果您有一个像这样的表，但是有一个附加列，并且给定的艺术家/标题组合总是在该列中以相同的值出现(例如，如果一个艺术家从未拥有相同标题图表的多个唱片，而您加上每个录音的播放时间)，那么就会出现问题. ("...记录艺术家/标题的时间为时间分钟")并且其中的值始终以相同的艺术家/标题组合出现(例如，如果您添加了录制ID)，则可能会出现问题. ("... AND录制 recordingcode 是艺术家 artist 的标题 title 的标题.")现在没有问题.您期望得到什么答案?答案是，归一化表示没有问题，并且归一化不会影响您的印象.

Eg: If you had a table like this one but with an additional column and a given artist/title combination always appeared with the same value in that column (like if an artist never has multiple recordings with the same title charting and you added the playing time of each recording) then there would be a problem. ("... AND recording artist/title is time minutes long") If you had a table like this one but with an additional column and a value in it always appeared with the same artist/title combination (like if you added a recording id) then there would be a problem. ("... AND recording recordingcode is of title title by artist artist") Right now there is no problem. What do you expect as an answer? The answer is, normalization says there's no problem, and your impressions are not informed by normalization.

规范化不涉及用ID替换值.引入的ID值与它们的值具有完全相同的外观模式标识/替换，这样就不会消除重复"，并且会在新表中添加更多重复"的ID. 作为视图的原始表是对ID相等的新表的联接的投影.(您可能想要具有易于更新或数据压缩(例如)的ID，但要牺牲更多的表和联接(等等).这是一个单独的问题.)

Normalization does not involve replacing values by ids. Introduced id values have exactly the same pattern of appearances as the values they identify/replaced, so that doesn't "eliminate duplicates", and it adds more "duplicates" of the ids in new tables. The original table as a view is a projection of a join of the new tables on equality of ids. (You might want to have ids for ease of update or data compression (etc) at the expense of more tables & joins (etc). That's a separate issue.)

-- hit `uniq` is title `title` by artist `artist` at position `position` on date `date`
/* FORSOME h.*, a.*, t.*,
    hit h.uniq is title with id h.titlecode by artist with id h.artistcode
        at position h.position on date h.date
AND artist a.artist has id a.artistcode AND h.artistcode = a.artistcode
AND title t.title has id t.titlecode AND h.titlecode = a.title
AND `uniq` = h.uniq AND `title` = t.title AND `artist` = a.artist
    AND `position` = h.position AND `date` = h.date
*/
/* FORSOME h.*, a.*, t.*,
    Hit(h.uniq, h.titlecode, h.artistcode, h.position, h.date)
AND Artist(a.artist, a.artistcode) AND h.artistcode = a.artistcode
AND Title(t.title, t.titlecode) AND h.titlecode = a.title
AND `uniq` = h.uniq AND `title` = t.title AND `artist` = a.artist
AND `position` = h.position AND `date` = h.date
*/
create view HitOriginal as
select h.uniq, h.date, a.artist, t.title, h.position
from Hit h
join Artist a on h.artistcode = a.artistcode
join Title t on h.titlecode = t.titlecode

这篇关于了解规范化和重复-我猜我不愿意-正在添加艺术家&标题编号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！