Sql-ddl. Ознакомление с основами проектирования схемы БД, способами нормализации отношений в БД, языком описания сущностей и ограничений БД SQL-DDL

Санкт-Петербургский Государственный Политехнический Университет

__________________________________________________________________

Факультет Технической Кибернетики

Компьютерных Систем и Программных Технологий

Отчет

по лабораторным работам

Базы данных

Работу выполнил:

группа 4081/1

Преподаватель:

Санкт-Петербург

2011

Лабораторная 1. SQL-DDL

1.1.  Цели работы

Ознакомление с основами проектирования схемы БД, способами нормализации отношений в БД, языком описания сущностей и ограничений БД SQL-DDL.

1.2. Программа работы

1)Представить схему БД, соответствующую заданию (должно получиться не менее 7 таблиц)

2)Привести схему БД к 3НФ

3)Создание скрипта БД в соответствии с согласованной схемой (должны присутствовать первичные и внешние ключи, ограничения на диапазоны значений).

4)Создание скрипта, заполняющего все таблицы БД данными.

5)Выполнение SQL-запросов, изменяющих схему созданной БД по заданию преподавателя.

1.3. Выполнение работы

Задание: Спортивный клуб.

База хранит в себе список клиентов клуба, их карточек, возможных тарифов, упражнений, связь между  тарифами и упражнениями, тренеров, расписание и таблицу скидок для частных клиентов.

Диаграмма базы приведена на рисунке 1:

Рис. 1. Диаграмма базы данных спортклуб.

Диаграмма содержит таблицы:

• CLIENTS – таблица с информацией о клиентах.

• CARDS – таблица с информацией о картах клиентов (один клиент может иметь несколько карт).

• TARIFS – таблица с информацией о тарифах.

• TARIF_EXER – таблица с информацией о том, к какому тарифу относится каждое упражнение (у одного тарифа может быть множество упражнений и несколько тарифов могут использовать одно упражнение).

• EXERCISES – таблица с информацией о упражнениях в клубе.

• COACHS – таблица с информацией о тренерах спортклуба.

• TIMETABLE – таблица с информацией о расписании клуба.

• SALES – таблица с информацией о скидках на карты клиентов.

Скрипт создания БД приведён в листинге 1:

Листинг 1:

connect 'E:\Base\sportclub.fdb' user 'SYSDBA' password 'masterkey';

drop database;

commit;

create database 'E:\Base\sportclub.fdb' user 'SYSDBA' password 'masterkey';

commit;

create table clients(

client_id int primary key,

name varchar(15) not null,

second_name varchar(30) not null);

create table exercises(

exer_number int primary key,

exer_name varchar(20) unique);

create table tarifs(

tarif_number int primary key,

tarif_name varchar(20) unique);

create table cards(

client_id int references clients(client_id),

card_id int primary key,

date_of_issue date unique,

tarif int references tarifs(tarif_number),

time_on_tarif date);

create table coachs(

name varchar(15) not null,

second_name varchar(30) primary key,

exercise int references exercises(exer_number));

create table sales(

client_card int references cards(card_id),

exercise int references exercises(exer_number),

sale_time int);

create table tarifs_exer(

tarif_number int references tarifs(tarif_number),

exer_number int references exercises(exer_number));

create table timetable(

time_dat date not null,

time_tim time not null,         

exercise int references exercises(exer_number),

place varchar(20) not null,

coach varchar(30) references coachs(second_name));

create index client_index on clients(client_id);

create index card_index on cards(card_id);

create index tarif_num_index on tarifs(tarif_number);;

create index exer_num_index on exercises(exer_number);

create index name_index on coachs(name);

commit;

Скрипт заполнения БД приведёт в листинге 2:

Листинг 2:

insert into clients values (1, 'Platon', 'Ulzutuev');

insert into clients values (2, 'Ivan', 'Tyamushev');

insert into clients values (3, 'Alexey', 'Pervakov');

insert into clients values (4, 'Kirill', 'Kisel');

insert into clients values (5, 'Vasiliy', 'Michailov');

insert into clients values (6, 'Anton', 'Kyznecov');

insert into clients values (7, 'Evgeniy', 'Averkin');

insert into clients values (8, 'Dritriy', 'Solovyov');

insert into clients values (9, 'Alexander', 'Sergeev');

insert into clients values (10, 'Alexander', 'Dryshkin');

insert into clients values (11, 'Maria', 'Tokareva');

insert into clients values (12, 'Yulya', 'Bykova');

insert into clients values (13, 'Yulya', 'Petritskaya');

insert into clients values (14, 'Anna', 'Dobrolej');

insert into clients values (15, 'Svetlana', 'Shlyajko');

insert into exercises values (1, 'gym');

insert into exercises values (2, 'personal traning');

insert into exercises values (3, 'pool');

insert into exercises values (4, 'game room');

insert into exercises values (5, 'stretching');

insert into exercises values (6, 'solarium');

insert into exercises values (7, 'massage');

insert into exercises values (8, 'aerobics');

insert into exercises values (9, 'saykl');

insert into exercises values (10, 'martial arts');

insert into coachs values ('Svetlana', 'Silakova', 1);

insert into coachs values ('Alevtina', 'Goroshinskaya', 2);

insert into coachs values ('Alexander', 'Kodzoev', 3);

insert into coachs values ('Pavel', 'Kirilenko', 4);

insert into coachs values ('Artem', 'Ivanov', 5);

insert into coachs values ('Dritriy', 'Klimov', 6);

insert into coachs values ('Inna', 'Shelehova', 7);

insert into coachs values ('Ludmila', 'Somkina', 8);

insert into coachs values ('Margarita', 'Belinskaya', 9);

insert into coachs values ('Alexander', 'Eskin', 10);

insert into tarifs values (1, 'all');

insert into tarifs values (2, 'only gym');

insert into tarifs values (3, 'only pool');

insert into tarifs values (4, 'only game room');

insert into tarifs values (5, 'only solarium');

insert into tarifs values (6, 'light traning');

insert into tarifs values (7, 'traning');

insert into tarifs values (8, 'hard traning');

insert into tarifs values (9, 'only massage');

insert into tarifs values (10, 'only martial arts');

insert into tarifs_exer values (1,1);

insert into tarifs_exer values (1,2);

insert into tarifs_exer values (1,3);

insert into tarifs_exer values (1,4);

insert into tarifs_exer values (1,5);

insert into tarifs_exer values (1,6);

insert into tarifs_exer values (1,7);

insert into tarifs_exer values (1,8);

insert into tarifs_exer values (1,9);

insert into tarifs_exer values (1,10);

insert into tarifs_exer values (2,1);

insert into tarifs_exer values (3,3);

insert into tarifs_exer values (4,4);

insert into tarifs_exer values (5,6);

insert into tarifs_exer values (6,5);

insert into tarifs_exer values (6,8);

insert into tarifs_exer values (7,2);

insert into tarifs_exer values (8,1);

insert into tarifs_exer values (8,5);

insert into tarifs_exer values (8,8);

insert into tarifs_exer values (8,9);

insert into tarifs_exer values (9,7);

insert into tarifs_exer values (10,10);

insert into cards values (1, 0000001, '2010-02-18', 1, '2012-02-18');

insert into cards values (2, 0000002, '2008-05-24', 2, '2011-07-06');

insert into cards values (3, 0000003, '2010-01-21', 3, '2012-05-05');

insert into cards values (4, 0000004, '2006-04-22', 4, '2012-09-16');

insert into cards values (5, 0000005, '2009-06-07', 5, '2011-10-14');

insert into cards values (6, 0000006, '2010-05-01', 5, '2011-10-19');

insert into cards values (7, 0000007, '2010-05-19', 10, '2011-07-06');

insert into cards values (8, 0000008, '2011-12-27', 10, '2011-09-25');

insert into cards values (9, 0000009, '2005-04-21', 6, '2011-06-30');

insert into cards values (10, 0000010, '2007-11-1', 7, '2011-07-26');

insert into cards values (11, 0000011, '2008-09-17', 8, '2011-12-11');

insert into cards values (12, 0000012, '2007-07-11', 9, '2011-11-12');

insert into cards values (13, 0000013, '2010-07-29', 3, '2012-12-25');

insert into cards values (14, 0000014, '2011-04-30', 5, '2012-08-15');

insert into cards values (15, 0000015, '2011-01-03', 6, '2012-02-01');

insert into sales values (0000001, 1, 60);

insert into sales values (0000001, 2, 60);

insert into sales values (0000001, 3, 60);

insert into sales values (0000001, 4, 60);

insert into sales values (0000001, 5, 60);

insert into sales values (0000001, 6, 60);

insert into sales values (0000001, 7, 60);

insert into sales values (0000001, 8, 60);

insert into sales values (0000001, 9, 60);

insert into sales values (0000001, 10, 60);

insert into timetable values ('2010-04-23', '10:00:00', 1, 'gymroom', 'Silakova');

insert into timetable values ('2010-04-23', '10:00:00', 2, 'gymroom', 'Goroshinskaya');

insert into timetable values ('2010-04-23', '10:00:00', 3, 'poolroom', 'Kodzoev');

insert into timetable values ('2010-04-23', '10:00:00', 4, 'gameroom', 'Kirilenko');

insert into timetable values ('2010-04-23', '10:00:00', 6, 'solariumroom', 'Klimov');

insert into timetable values