Релационните бази данни съхраняват информацията за обектите си в таблици. Много важно е да осигурим всеки обект в базата да бъде еднозначно идентифициран. Затова въвеждаме понятието първичен ключ (primary key).

Първичен ключ (PK):
Първичен ключ е уникално свойство(атрибут) на всеки обект в базата данни. За първичен ключ в една релация ще избираме такава колона, стойностите на която за всеки обект ще бъдат уникални. Например в таблицата person може да изберем като първичен ключ EGN. Това е така, тъй като всеки човек има различно егн. Лесно може да идентифицираме който и да е обект от таблицата само по егн. Името или адреса на човек не може да послужи като уникален идентификатор – съществуват повече от един човека с еднакви имена или с еднакви адреси. Много често се среща изборът на първичен ключ да бъде допълнителна колона тип цяло число –id. Друг вариант е комбинация от колони да бъде първичен ключ. Например комбинацията марка – модел на таблица автомобили. Важното е при всички случаи стойностите на записите в избраната колона или комбинация да са уникални и да не са NULL. Определени колони биха могли да имат стойност NULL- това означава, че са на практика празни- когато все още не сме наясно с каква стойност да попълним колоната в момента.

Външен ключ(FK):
Всъщност ключовете са в основата на осъществяване на връзките между отделните релации в релационните бази данни. За да свържем две таблици, казваме че колона от едната таблица(външен ключ) трябва да сочи към колона-първичен ключ от друга таблица. Не е задължително втората да бъде първичен ключ- обратният случай е по-рестриктивният вариант, но затова ще споменем повече, когато говорим за ограничения(constraints). Външен ключ (foreign key) е такава колона от дадена таблица, която може да приема само ограничено множество от стойности, съвпадащо с множеството от стойностите на определена колона – първичен ключ в друга таблица или най-много стойност NULL. Можем да приемем външния ключ като референция на едната към другата таблица.

Преминаване от ER-модел към DB-модел:

Класовете в ER-диаграмата се превръщат в таблици, а техните атрибути– в атрибути на таблиците.

Характеризиращите обекти са обикновени таблици, с външен ключ към таблицата, която характеризират.

Подклас-обектите са си таблици, свързани с таблиците на основните им обекти обикновено чрез връзка 1:1, като в зависимост от спецификата на предметната област е възможно и свързване 1:М.

Връзка от тип 1:1 между две таблици се осъществява с налагане ограничение UNIQUE(забранява повтарящи се стойности) върху колона външен ключ във втората таблица, която сочи към първичния ключ от първата.

Връзка тип 1:М между две таблици се осъществява като колона от втората таблица стане външен ключ, сочещ към първичния на първата таблица.

Връзка тип М:М между две таблици с реализира с помощта на трета допълнителна (свързваща) таблица, в която атрибутите са два външни ключа, сочещи съответно към свързваните таблици.

Създаване на база данни:

SQL е разделен най-общо в две направления – структурна дефиниция и манипулация над данните. Затова езикът условно се разделя на DDL(Data Definition Language) и DML(Data Manipulation Language).

DDL– съдържа команди, с които се дефинира структурата на базата данни – схеми, таблици, ограничения и връзки между таблиците. Това са всички заявки от типа:

• CREATE DATABASE/ CREATE TABLE – създаване на база данни/ създаване на таблица
• ALTER DATABASE/ALTER TABLE – промяна дефиницията на база/ на таблица
• DROP DATABASE/ DROP TABLE – изтриване на база/ на таблица
• CREATE INDEX – създаване на индекс
• DROP INDEX – изтриване на индекс

DML – съдържа команди за обработка над данните – вмъкване, извличане, промяна, изтриване на данни:

• INSERT- вмъкване на данни
• SELECT – извличане на данни
• UPDATE – промяна на данни
• DELETE – изтриване на данни

Предстои да разгледаме синтаксиса на всички тези команди, заедно със съответните им особености и възможности.

Можете да си изтеглите MySQL сървър от следния линк:

http://dev.mysql.com/downloads/mysql/

Също така, MySQL WorkBench предоставя добър потребителски интерфейс  за работа със сървъра. Можете да го свалите от тук:

http://dev.mysql.com/downloads/workbench/

Заявката за създаване на базата данни е:

CREATE DATABASE <име на базата>;

Заявка за използване на базата е :

USE <име на базата>;

Синтаксис на CREATE TABLE заявка:

CREATE TABLE <име на таблицата>(
колона1 <тип данни>  <…ограничения…>,
колона2 <тип данни>  <…ограничения…>,
колона3 <тип данни>  <…ограничения…>
);

Например, нека създадем таблица “книги”:

CREATE TABLE books(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
publishedYear YEAR, 
author_number INT NOT NULL
);

Както виждате синтаксисът е относително опростен – в скобите изреждаме разделени чрез запетая дефиниции на колони. Повече за ограниченията ще научите в следваща статия, но за сега приемете, че това са рестрикции върху стойностите, които може да приема дадена колона. Едно от най-често срещаните е NOT NULL. В заявките за създаване на таблици ще използваме първични и външни ключове. Първичният ключ попада в графата <…ограничения…> и се реализира като просто напишем PRIMARY KEY. Външен ключ се създава като декларираме на отделен ред в CREATE TABLE заявката:

CONSTRAINT FOREIGN KEY (<име на колона от рефериращата таблица>) REFERENCES <таблица>(<колона от реферираната таблица>);

Например таблица клиенти и таблица за техните коли:

CREATE TABLE customers(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
egn VARCHAR(10) NOT NULL
);
CREATE TABLE cars (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
brand VARCHAR(255) NOT NULL,
model VARCHAR(255) NOT NULL,
owner_id INT,
CONSTRAINT FOREIGN KEY (owner_id) REFERENCES customers(id)
);

Синтаксис на ALTER TABLE заявки:

Понякога се налага да добавите нови колони, да модифицирате стари или да ги изтриете от дадена таблица. Също така не е възможно да създадете външен ключ към колона, която не сте декларирали предварително.  Затова често външния ключ се добавя по-късно. Става с командата ALTER TABLE. Например:

ALTER TABLE <име на табл.> 
ADD колона1 <тип данни> <…ограничения…> 

– ще добави колона към съществуващата таблица. За да я направим външен ключ:

ALTER TABLE <име на табл.> 
ADD CONSTRAINT FOREIGN KEY (име на колона от рефериращата таблица) REFERENCES <таблица>(<колона от реферираната таблица>);

Аналогично на ADD може да напишем и:

CHANGE <име на колона> <ново име на колона> <тип данни> <ограничения>

– за промяна на текуща колона.
Също така може да използваме DROP – за изтриване:

DROP <име на колона>.

Например:

ALTER TABLE cars
ADD power DOUBLE;
ALTER TABLE cars
DROP power;

Задача:

Проектирайте и създайте база данни по следните изисквания:

По даден проект училище трябва да организира извънучилищни спортни клубове. Спортните клубове могат да бъдат първоначално два типа– по футбол и по волейбол, а на следващ етап ще се добавят още.  За всеки клуб може да има много наброй групи, които тренират в различни дни и в различни часове. Учениците могат да се присъединят към който и да е от двата типа клуба едновременно като се запишат в определена група в определен ден от седмицата и в определен час. За всяка от клубните групи има по един треньор, който тренира учениците. Всеки ученик може да се идентифицира с уникален номер – ид. Допълнете таблиците с необходима информация по ваш избор.

Стъпка 1: Определяме обектите в нашата база данни:

• SportGroups
• Students
• Coaches
• Sports

Стъпка 2: Определяме връзките между тях:

Стъпка 3: Определяне на ключови атрибути(PK):

• SportGroups – id
• Students – id
• Coaches – id
• Sports – id

Стъпка 4: Определяне на останалите атрибути:

• SportGroups – location, dayOfWeek, hour, coach_id, sport_id
• Students- name, egn, address, phone, class,
• Coaches- name, egn,
• Sports – name

Стъпка 5: Изчертаване на цялостната ER – диаграма.

Първичните ключове се подчертават.

Стъпка 6: Писане на SQL CREATE TABLE код:

CREATE DATABASE SCHOOL_SPORT_CLUBS;
USE SCHOOL_SPORT_CLUBS;

CREATE TABLE students(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
egn VARCHAR(10) NOT NULL UNIQUE,
address VARCHAR(255) NOT NULL,
phone VARCHAR(20) NULL DEFAULT NULL,
class VARCHAR(10) NULL DEFAULT NULL   
);

CREATE TABLE sportGroups(
id INT AUTO_INCREMENT PRIMARY KEY,
location VARCHAR(255) NOT NULL,
dayOfWeek ENUM('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday'),
hourOfTraining TIME NOT NULL,
UNIQUE KEY(location,dayOfWeek,hourOfTraining)
);

CREATE TABLE student_sport(
student_id int not null,  
CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(id),
sportGroup_id int not null,
CONSTRAINT FOREIGN KEY (sportGroup_id) REFERENCES sportGroups(id),
PRIMARY KEY(student_id,sportGroup_id)
);

CREATE TABLE sports(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL
);

ALTER TABLE sportGroups
ADD sport_id INT NOT NULL;

ALTER TABLE sportGroups
ADD CONSTRAINT FOREIGN KEY(sport_id) REFERENCES sports(id);

CREATE TABLE coaches(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
egn VARCHAR(10) NOT NULL UNIQUE
);

ALTER TABLE sportGroups
ADD coach_id INT;

ALTER TABLE sportGroups
ADD CONSTRAINT FOREIGN KEY (coach_id) REFERENCES coaches(id);

Даниел Джолев