Приветствую всех, сегодня поговорим о проектировании баз данных.
НОРМАЛИЗАЦИЯ-Normalization
Нормализация таблиц – это формальный аппарат ограничений на формирование таблиц, который позволяет устранить дублирование данных, обеспечивает непротиворечивость хранимых в базе данных, уменьшает трудозатраты на ведение базы данных (ввод и корректировку данных).
Для нормализации таблиц БД используют ограничения:
- Первой нормальной формы.
- Второй нормальной формы.
- Третей нормальной формы.
Ограничения первой нормальной формы-The First Normal Form
Первая нормальная форма (1NF) – отсутствие повторяющихся данных, любое поле любой записи хранит только одно значение.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 |
DROP TABLE Orders CREATE TABLE Orders ( CustFName varchar(15) NOT NULL, CustMName varchar(15) NOT NULL, CustLName varchar(15) NOT NULL, CustomerCity varchar(15), CustomerAddress varchar(25), Phone varchar(12) NOT NULL, OrderDate date NOT NULL, ProductID char(5) NOT NULL, ProductDescription varchar(15), UnitPrice smallmoney NOT NULL, Qty int NOT NULL, TotalPrice money, EmpFName varchar(15) NOT NULL, EmpMName varchar(15) NOT NULL, EmpLName varchar(15) NOT NULL, CHECK (Phone LIKE '([0-9][0-9][0-9])[0-9][0-9][0-9][0-9][0-9][0-9][0-9]') ) INSERT Orders VALUES ('Василий', 'Петрович', 'Лященко', 'Харьков', 'Лужная 15', '(092)3212211', '2009-12-28', 'LV231', 'Джинсы', 45, 5, 225, 'Иван', 'Иванович', 'Белецкий'), ('Василий', 'Петрович', 'Лященко', 'Харьков', 'Лужная 15', '(092)3212211', '2009-12-28', 'DG30', 'Ремень', 30, 5, 145, 'Иван', 'Иванович', 'Белецкий'), ('Василий', 'Петрович', 'Лященко', 'Харьков', 'Лужная 15', '(092)3212211', '2009-12-28', 'LV12', 'Обувь', 26, 5, 125, 'Иван', 'Иванович', 'Белецкий'), ('Зигмунд', 'Федорович', 'Унакий', 'Киев', 'Дегтяревская 5', '(092)7612343', '2010-09-01', 'GC11', 'Шапка', 32, 10, 320, 'Светлана', 'Олеговна', 'Лялечкина'), ('Зигмунд', 'Федорович', 'Унакий', 'Киев', 'Дегтяревская 5', '(092)7612343', '2010-09-01', 'GC111', 'Футболка', 20, 15, 300, 'Светлана', 'Олеговна', 'Лялечкина'), ('Олег', 'Увстафьевич', 'Выжлецов', 'Чернигов', 'Киевская 5', '(044)2134212', '2010-09-18', 'LV12', 'Обувь', 26, 20, 520, 'Светлана', 'Олеговна', 'Лялечкина' ), ('Олег', 'Увстафьевич', 'Выжлецов', 'Чернигов', 'Киевская 5', '(044)2134212', '2010-09-18', 'GC11', 'Шапка', 32, 18, 576, 'Светлана', 'Олеговна', 'Лялечкина' ) SELECT * FROM Orders -- Перед тем как переходить к рассмотрению второй и третьей нормальной формы следует обеспечить сущностную целостность -- для таблицы Orders(определить первичный ключ) DROP TABLE Orders CREATE TABLE Orders ( OrderID int NOT NULL, LineItem int NOT NULL, OrderDate date NOT NULL, CustFName varchar(15) NOT NULL, CustMName varchar(15) NOT NULL, CustLName varchar(15) NOT NULL, CustomerCity varchar(15), CustomerAddress varchar(25), Phone varchar(12) NOT NULL, ProductID char(5) NOT NULL, ProductDescription varchar(15), UnitPrice smallmoney NOT NULL, Qty int NOT NULL, TotalPrice money, EmpFName varchar(15) NOT NULL, EmpMName varchar(15) NOT NULL, EmpLName varchar(15) NOT NULL, CHECK (Phone LIKE '([0-9][0-9][0-9])[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), PRIMARY KEY (OrderId, LineItem) ) INSERT Orders VALUES ( 1,1,'2009-12-28', 'Василий', 'Петрович', 'Лященко', 'Харьков', 'Лужная 15', '(092)3212211', 'LV231', 'Джинсы', 45, 5, 225, 'Иван', 'Иванович', 'Белецкий'), ( 1,2,'2009-12-28', 'Василий', 'Петрович', 'Лященко', 'Харьков', 'Лужная 15', '(092)3212211', 'DG30', 'Ремень', 30, 5, 145, 'Иван', 'Иванович', 'Белецкий'), ( 1,3, '2009-12-28', 'Василий', 'Петрович', 'Лященко', 'Харьков', 'Лужная 15', '(092)3212211', 'LV12', 'Обувь', 26, 5, 125, 'Иван', 'Иванович', 'Белецкий'), ( 2,1,'2010-09-01', 'Зигмунд', 'Федорович', 'Унакий', 'Киев', 'Дегтяревская 5', '(092)7612343', 'GC11', 'Шапка', 32, 10, 320, 'Светлана', 'Олеговна', 'Лялечкина'), ( 2,2, '2010-09-01', 'Зигмунд', 'Федорович', 'Унакий', 'Киев', 'Дегтяревская 5', '(092)7612343', 'GC111', 'Футболка', 20, 15, 300, 'Светлана', 'Олеговна', 'Лялечкина'), ( 3,1,'2010-09-18', 'Олег', 'Увстафьевич', 'Выжлецов', 'Чернигов', 'Киевская 5', '(044)2134212', 'LV12', 'Обувь', 26, 20, 520, 'Светлана', 'Олеговна', 'Лялечкина' ), ( 3,2, '2010-09-18', 'Олег', 'Увстафьевич', 'Выжлецов', 'Чернигов', 'Киевская 5', '(044)2134212', 'GC11', 'Шапка', 32, 18, 576, 'Светлана', 'Олеговна', 'Лялечкина' ) SELECT * FROM Orders |
Ограничения второй нормальной формы-The second normal form
Вторая нормальная форма (2NF) – требует предварительного приведения исходной таблицы к первой НФ, а так же каждый не ключевой столбец таблицы находящейся в 1НФ, должен зависеть от всего ключа.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 |
DROP TABLE Orders DROP TABLE Employees DROP TABLE Customers DROP TABLE OrderDetails CREATE TABLE Employees ( EmployeeID int NOT NULL IDENTITY PRIMARY KEY, FName nvarchar(15) NOT NULL, LName nvarchar(15) NOT NULL, MName nvarchar(15) NOT NULL, Salary money NOT NULL, PriorSalary money NOT NULL, HireDate date NOT NULL, TerminationDate date NULL, ManagerEmpID int NULL ) GO CREATE TABLE Customers ( CustomerNo int NOT NULL IDENTITY PRIMARY KEY, FName nvarchar(15) NOT NULL, LName nvarchar(15) NOT NULL, MName nvarchar(15) NULL, Address1 nvarchar(50) NOT NULL, Address2 nvarchar(50) NULL, City nchar(10) NOT NULL, Phone char(12) NOT NULL UNIQUE, DateInSystem date NULL, CHECK (Phone LIKE '([0-9][0-9][0-9])[0-9][0-9][0-9][0-9][0-9][0-9][0-9]') ) GO CREATE TABLE Orders ( OrderID int NOT NULL IDENTITY PRIMARY KEY, OrderDate date NOT NULL, CustomerNo int, EmployeeID int, FOREIGN KEY(CustomerNo) REFERENCES Customers(CustomerNo), FOREIGN KEY(EmployeeID) REFERENCES Employees(EmployeeID), ) CREATE TABLE OrderDetails ( OrderID int NOT NULL, LineItem int NOT NULL, ProductID char(5) NOT NULL, ProductDescription varchar(15), UnitPrice smallmoney NOT NULL, Qty int NOT NULL, TotalPrice as Qty * UnitPrice, FOREIGN KEY(OrderID) REFERENCES Orders(OrderID), PRIMARY KEY (OrderId, LineItem) ) INSERT Employees (FName, MName, LName, Salary, PriorSalary, HireDate, TerminationDate, ManagerEmpID) VALUES ('Василий', 'Петрович', 'Лященко', 5000, 800, '11/20/2009', NULL, NULL), ('Иван', 'Иванович', 'Белецкий', 2000, 0, '11/20/2009', NULL, 1), ('Петр', 'Григорьевич', 'Дяченко', 1000, 0, '11/20/2009', NULL, 2), ('Светлана', 'Олеговна', 'Лялечкина', 800, 0, '11/20/2009', NULL, 2); GO INSERT Customers (LName, FName, MName, Address1, Address2, City, Phone,DateInSystem) VALUES ('Круковский','Анатолий','Петрович','Лужная 15',NULL,'Харьков','(092)3212211','11/20/2009'), ('Дурнев','Виктор','Викторович','Зелинская 10',NULL,'Киев','(067)4242132','08/03/2010'), ('Унакий','Зигмунд','федорович','Дихтяревская 5',NULL,'Киев','(092)7612343','08/17/2010'), ('Левченко','Виталий','Викторович','Глущенка 5','Драйзера 12','Киев','(053)3456788','08/20/2010'), ('Выжлецов','Олег','Евстафьевич','Киевская 3','Одесская 8','Чернигов','(044)2134212','09/18/2010'); GO INSERT Orders VALUES ( '2009-12-28', 1, 2), ( '2010-09-01', 3, 4), ( '2010-09-18', 5, 4) INSERT OrderDetails VALUES ( 1, 1, 'LV231', 'Джинсы', 45, 5 ), ( 1, 2, 'DG30', 'Ремень', 30, 5 ), ( 1, 3, 'LV12', 'Обувь', 26, 5 ), ( 2, 1, 'GC11', 'Шапка', 32, 10 ), ( 2, 2, 'GC111', 'Футболка', 20, 15 ), ( 3, 1, 'LV12', 'Обувь', 26, 20 ), ( 3, 2, 'GC11', 'Шапка', 32, 18 ) SELECT * FROM Customers SELECT * FROM Employees SELECT * FROM Orders SELECT * FROM OrderDetails |
Ограничения третей нормальной формы-The third normal form
Третья нормальная форма (3NF) – требует предварительного приведения исходной таблицы ко второй НФ, а так же ни в одном не ключевом столбце не может быть зависимости от другого не ключевого столбца. Так же не допускается наличие в таблице производных данных.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
DROP TABLE OrderDetails DROP TABLE Products CREATE TABLE Products ( ProductID char(5) NOT NULL PRIMARY KEY, [Description] varchar(15), UnitPrice smallmoney NOT NULL, [Weight] numeric(18, 0) NULL ) CREATE TABLE OrderDetails ( OrderID int NOT NULL, LineItem int NOT NULL, ProductID char(5) NOT NULL, Qty int NOT NULL, FOREIGN KEY(OrderID) REFERENCES Orders(OrderID), FOREIGN KEY(ProductID) REFERENCES Products(ProductID), PRIMARY KEY (OrderId, LineItem) ) INSERT Products VALUES ( 'LV231', 'Джинсы', 45, 2), ( 'DG30', 'Ремень', 30, 1), ( 'GC111', 'Футболка', 20, 2), ( 'LV12', 'Обувь', 26, 2), ( 'GC11', 'Шапка', 32, 1) INSERT OrderDetails VALUES ( 1, 1, 'LV231', 5 ), ( 1, 2, 'DG30', 5 ), ( 1, 3, 'LV12', 5 ), ( 2, 1, 'GC11', 10 ), ( 2, 2, 'GC111', 15 ), ( 3, 1, 'LV12', 20 ), ( 3, 2, 'GC11', 18 ) SELECT * FROM Customers SELECT * FROM Employees SELECT * FROM Orders SELECT * FROM OrderDetails SELECT * FROM Products |
Денормализация
Денормализация – процесс понижения нормальной формы. Осуществляется если приведенная высшая форма приводит к ухудшению практического использования.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
DROP TABLE OrderDetails CREATE TABLE OrderDetails ( OrderID int NOT NULL, LineItem int NOT NULL, ProductID char(5) NOT NULL, Qty int NOT NULL, TotalPrice money, FOREIGN KEY(OrderID) REFERENCES Orders(OrderID), FOREIGN KEY(ProductID) REFERENCES Products(ProductID), PRIMARY KEY (OrderId, LineItem) ) INSERT OrderDetails VALUES ( 1, 12, 'LV231', 5, 5 * (select UnitPrice FROM Products where ProductID = 'LV231')), ( 1, 2, 'DG30', 5, 5 * (select UnitPrice FROM Products where ProductID = 'DG30')), ( 1, 3, 'LV12', 5, 5 * (select UnitPrice FROM Products where ProductID = 'LV12')), ( 2, 1, 'GC11', 10, 10 * (select UnitPrice FROM Products where ProductID = 'GC11') ), ( 2, 2, 'GC111', 15, 15 * (select UnitPrice FROM Products where ProductID = 'GC111') ), ( 3, 1, 'LV12', 20, 20 * (select UnitPrice FROM Products where ProductID = 'LV12') ), ( 3, 2, 'GC11', 18, 18 * (select UnitPrice FROM Products where ProductID = 'GC11') ) SELECT * FROM OrderDetails |