Билет №1: Нормализация таблицы CD

Задание 1. Нормализация таблицы. Привести таблицу к 1NF 2NF 3NF

Построить схему данных, определить типы данных

CD pealkiritootjatootjamaa aastamahthindkogus 1karbiskauplusenimi
eesti muusikaBMFInglismaa2006250      100,00 €1Kaubamaja
vene muusikaBMFInglismaa2005230      120,00 €2Stockmann
klassikaKDFVenemaa2000600      500,00 €5Kaubamaja
klassika uusKDFVenemaa2001500      300,00 €1Kaubamaja
klassika vanaTDFTaani2001500      100,00 €4Stockmann
vene muusikaTDFTaani2001600      100,00 €2Kaubamaja
  • Нормализируем таблицу
  • Создаем схему в Vertabelo
  • Генерируем SQL запросы на создание таблиц.

— Created by Vertabelo (http://vertabelo.com)
— Last modification date: 2024-06-05 07:50:48.978

— Table: Kaupluse_nimi
CREATE TABLE Kaupluse_nimi (
id_kauplusenimi INT IDENTITY(1,1) PRIMARY KEY,
colukauplusenimi VARCHAR(50) NOT NULL
);

— Table: Ketta_maht
CREATE TABLE Ketta_maht (
id_maht INT IDENTITY(1,1) PRIMARY KEY,
maht INT NOT NULL
);

— Table: Kogus_ühes_karbis
CREATE TABLE Kogus_ühes_karbis (
id_kogus1karbis INT IDENTITY(1,1) PRIMARY KEY,
kogus_1karbis INT NOT NULL
);

— Table: Müük
CREATE TABLE “Müük” (
id INT IDENTITY(1,1) PRIMARY KEY,
ID_CD INT NOT NULL,

CD_pealkiri VARCHAR(50) NOT NULL,
id_tt INT NOT NULL,
id_aasta INT NOT NULL,
id_maht INT NOT NULL,
id_kogus1karbis INT NOT NULL,
id_kauplusenimi INT NOT NULL,
hind FLOAT(2) NOT NULL
);






— Table: Tootja
CREATE TABLE Tootja (
id_tootja INT IDENTITY(1,1) PRIMARY KEY,
tootja VARCHAR(50) NOT NULL
);





— Table: Tootja_riik
CREATE TABLE Tootja_riik (
id_tt INT IDENTITY(1,1) PRIMARY KEY,
id_tootja INT NOT NULL,
id_tootjamaa INT NOT NULL
);





— Table: Väljalaskeaasta
CREATE TABLE Väljalaskeaasta ( 





id_aasta INT IDENTITY(1,1) PRIMARY KEY,





 aasta INT NOT NULL 





);





— Table: Väljalaskeriik
CREATE TABLE Väljalaskeriik (
id_tootjamaa INT IDENTITY(1,1) PRIMARY KEY,
tootjamaa VARCHAR(50) NOT NULL
);





— foreign keys
— Reference: Müük_Kaupluse_nimi (table: Müük)
ALTER TABLE “Müük” ADD CONSTRAINT “Müük_Kaupluse_nimi”
FOREIGN KEY (id_kauplusenimi)
REFERENCES Kaupluse_nimi (id_kauplusenimi);





— Reference: Müük_Ketta_maht (table: Müük)
ALTER TABLE “Müük” ADD CONSTRAINT “Müük_Ketta_maht”
FOREIGN KEY (id_maht)
REFERENCES Ketta_maht (id_maht);





— Reference: Müük_Kogus_ühes_karbis (table: Müük)
ALTER TABLE “Müük” ADD CONSTRAINT “Müük_Kogus_ühes_karbis”
FOREIGN KEY (id_kogus1karbis)
REFERENCES Kogus_ühes_karbis (id_kogus1karbis);





— Reference: Müük_Tootja_riik (table: Müük)
ALTER TABLE “Müük” ADD CONSTRAINT “Müük_Tootja_riik”
FOREIGN KEY (id_tt)
REFERENCES Tootja_riik (id_tt);





— Reference: Müük_Väljalaskeaasta (table: Müük)
ALTER TABLE “Müük” ADD CONSTRAINT “Müük_Väljalaskeaasta”
FOREIGN KEY (id_aasta)
REFERENCES Väljalaskeaasta (id_aasta);





— Reference: Tootja_riik_Tootja (table: Tootja_riik)
ALTER TABLE Tootja_riik ADD CONSTRAINT Tootja_riik_Tootja
FOREIGN KEY (id_tootja)
REFERENCES Tootja (id_tootja);





— Reference: Tootja_riik_Väljalaskeriik (table: Tootja_riik)
ALTER TABLE Tootja_riik ADD CONSTRAINT Tootja_riik_Väljalaskeriik
FOREIGN KEY (id_tootjamaa)
REFERENCES Väljalaskeriik (id_tootjamaa);





— End of file.





    

  • Заполняем таблицы
    • 






— Вставка данных в таблицу Plaadi_nimi
INSERT INTO Plaadi_nimi (CD_pealkiri)
VALUES (‘eesti muusika’), (‘vene muusika’), (‘klassika’), (‘klassika uus’), (‘klassika vana’);





— Вставка данных в таблицу Tootja
INSERT INTO Tootja (tootja)
VALUES (‘BMF’), (‘KDF’), (‘TDF’);





— Вставка данных в таблицу Väljalaskeriik
INSERT INTO Väljalaskeriik (tootjamaa)
VALUES (‘Inglismaa’), (‘Venemaa’), (‘Taani’);






INSERT INTO “Müük” (ID_CD, id_tt, id_aasta, id_maht, id_kogus1karbis, id_kauplusenimi, hind)
VALUES
(1, ‘eesti muusika’, 1, 4, 2, 1, 1, 100.00),
(2, ‘vene muusika’, 1, 3, 1, 2, 2, 120.00),
(3, ‘klassika’, 2, 1, 4, 5, 1, 500.00),
(4, ‘klassika uus’, 2, 2, 3, 1, 1, 300.00),
(5, ‘klassika vana’, 3, 2, 3, 4, 2, 100.00),
(6, ‘vene muusika’, 3, ‘2, 4, 2, 1, 100.00);










    

  • Проверяем связи. 
    • 











SELECT
p.id,
pn.CD_pealkiri AS CD_pealkiri,
t.tootja AS tootja,
v.aasta AS aasta,
km.maht AS maht,
p.hind AS hind,
k.kogus_1karbis AS kogus_1karbis,
kn.colukauplusenimi AS kauplusenimi
FROM
[Müük] p
JOIN
Plaadi_nimi pn ON p.ID_CD = pn.ID_CD
JOIN
Tootja t ON p.id_tt = t.id_tootja
JOIN
Väljalaskeaasta v ON p.id_aasta = v.id_aasta
JOIN
Ketta_maht km ON p.id_maht = km.id_maht
JOIN
Kogus_ühes_karbis k ON p.id_kogus1karbis = k.id_kogus1karbis
JOIN
Kaupluse_nimi kn ON p.id_kauplusenimi = kn.id_kauplusenimi;















Задание 2.





Создать процедуру, которая добавляет в одну из таблиц новый столбец. Имя столбца вводит пользователь при запуске процедуры.





CREATE PROCEDURE AddColumnToTable
@TableName NVARCHAR(128), — Имя таблицы, в которую нужно добавить столбец
@ColumnName NVARCHAR(128), — Имя нового столбца
@DataType NVARCHAR(128) — Тип данных нового столбца
AS
BEGIN
DECLARE @sql NVARCHAR(MAX);
SET @sql = ‘ALTER TABLE ‘ + QUOTENAME(@TableName) + ‘ ADD ‘ + QUOTENAME(@ColumnName) + ‘ ‘ + @DataType + ‘;’;
EXEC sp_executesql @sql;
END;





Выполняем процедуру





EXEC AddColumnToTable ‘Kaupluse_nimi’, ‘TEST’, ‘INT’;















Отчет о выполненной работе добавить на форуме. 







Билет №1: Нормализация таблицы CD