Создание базы данных для электронной библиотеки учебного материала

Размещено на http://www.allbest.ru/

Оглавление

Введение

1. Постановка задачи

2. Описание объектов

3. Связи между объектами

4. Описание таблиц

5. Связи между таблицами

6. Запросы к БД

Приложение 1

Введение

Основные идеи современной информационной технологии базируются на концепции баз данных (БД). Согласно данной концепции основой информационной технологии являются данные, организованные в БД, адекватно отражающие реалии действительности в той или иной предметной области и обеспечивающие пользователя актуальной информацией в соответствующей предметной области. Первые БД появились уже на заре 1-го поколения ЭВМ представляя собой отдельные файлы данных или их простые совокупности.

Вся информация, которая есть у организации, хранится в БД. Базой данных является представленная в объективной форме, совокупность самостоятельных материалов (статей, расчетов, нормативных актов, судебных решений и иных подобных материалов), систематизированных таким образом, чтобы эти материалы могли быть найдены и обработаны с помощью электронной вычислительной машины (ЭВМ)» (Гражданский кодекс РФ, ст. 1260).

1. Постановка задачи

база данное таблица учебный материал

Цель работы заключается в создании БД предназначенной для сбора, хранения и предоставления учебного материала. Данная работа будет предоставлять актуальную информацию. БД будет включать информацию об имеющемся учебном материале (тип учебного материала, название, авторы, тематика), людях (как пользователей, так и администраторов). Эта информация необходима для того, чтобы быстро найти и получить необходимый учебный материал, учитывая разграничение доступа. Для выполнения поставленной цели будут использоваться объекты: учебный материал (далее «книга»), тип книги, автор, пользователи, группы, тема, доступ. Эти объекты связаны между собой, рассмотрим связи между ними.

2. Описание объектов

Книга описывает имеющейся в электронном виде учебный материал. Характеризуется:

· ID книги;

· Тип книги;

· Название книги;

· Сама книга

Тип книги описывает тип учебного материала. Характеризуется:

· ID типа;

· Название типа;

Автор отображают ФИО автор книги. Характеризуется:

· ID автора;

· Фамилия;

· Имя;

· Отчество;

Тема описывает ключевые слова для учебного материала. Характеризуется:

· ID темы;

· Название темы;

Пользователь описывает пользователя электронной библиотеки. Характеризуется:

· ID пользователя;

· Имя

· Логин

· Пароль

· E-mail

Группа описывает группу пользователей. Характеризуется:

· ID группы;

· Название группы;

Доступ описывает имеющиеся доступы. Характеризуется:

· ID доступа;

· Описание доступа.

3. Связи между объектами

Размещено на http://www.allbest.ru/

Все объекты на данной диаграмме связаны.

Объекты Тип книги и Книга связаны отношением один-ко-многим, это значит, что одному типу книги может соответствовать несколько книг, а одна книга может иметь только один тип.

Объекты Книга и Автор связаны отношением многие-ко-многим, это значит, что у одной книги может быть несколько авторов, а у одного автора несколько книг.

Объекты Книга и Тема связаны отношением многие-ко-многим, это значит, что у одной книги может быть несколько тем, а одной теме может соответствовать несколько книг.

Объекты Доступ и Книга связаны отношением один-ко-многим, это значит, что одной книге соответствует один доступ, а один доступ может соответствовать нескольким книгам.

Объекты Доступ и Пользователь связаны отношением многие-ко-многим, это значит, что один доступ может быть у нескольких пользователей, а один пользователь может иметь несколько доступов.

Объекты Доступ и Группа связаны отношением многие-ко-многим, это значит, что один доступ может быть у нескольких групп, а одна группа может иметь несколько доступов.

Объекты Пользователь и Группа связаны отношением многие-ко-многим, это значит, что один пользователь может состоять в нескольких группах, а группа может содержать несколько пользователей. Для нормализации БД, для связи объектов с отношением многие-ко-многим необходимо создать промежуточные таблицы.

4. Описание таблиц

Книга - books

Название	Тип данных	Ограничение	Комментарий
id	number	PK	Номер книги
type_id	number	FK	Номер типа книги
name	varchar(100 )		Название книги
ebook	clob		Сама книга

Тип книги - books_types

Название	Тип данных	Ограничение	Комментарий
id	number	PK	Номер типа книги
name	varchar(64 )		Название типа

Автор - book_authors

Название	Тип данных	Ограничение	Комментарий
id	number	PK	Номер автора
lname	varchar(32 )		Фамилия автора
fname	varchar(32 )		Имя автора
mname	varchar(32 )		Отчество автора

Тема - book_tags

Название	Тип данных	Ограничение	Комментарий
id	number	PK	Номер темы
name	varchar(32 )		Название темы

Доступ - baccess

Название	Тип данных	Ограничение	Комментарий
id	number	PK	Номер доступа
name	varchar (64)		Описание доступа

Пользователи - busers

Название	Тип данных	Ограничение	Комментарий
id	number	PK	Номер пользователя
name	varchar(32 )		Имя пользователя
login	varchar(32 )	UNIQUE	Логин
password	varchar(32)		Пароль
email	varchar(32)	UNIQUE	E-mail пользователя

Группа - bgroups

Название	Тип данных	Ограничение	Комментарий
id	number	PK	Номер группы
name	varchar(32 )		Название группы

Доступ по пользователям - ref_userAccess

Название	Тип данных	Ограничение	Комментарий
id	number	PK	Номер записи
user_id	number	FK	Номер пользователя
access_id	number	FK	Номер доступа

Доступ по группам - ref_groupAccess

Название	Тип данных	Ограничение	Комментарий
id	number	PK	Номер записи
group_id	number	FK	Номер группы
access_id	number	FK	Номер доступа

Доступ по группам - ref_userGroup

Название	Тип данных	Ограничение	Комментарий
id	number	PK	Номер записи
user_id	number	FK	Номер пользователя
group_id	number	FK	Номер группы

Книги и их авторы - ref_bookAuthor

Название	Тип данных	Ограничение	Комментарий
id	number	PK	Номер записи
book_id	number	FK	Номер группы
author_id	number	FK	Номер доступа

Книги и их темы - ref_bookTags

Название	Тип данных	Ограничение	Комментарий
id	number	PK	Номер записи
book_id	number	FK	Номер группы
tag_id	number	FK	Номер доступа

5. Связи между таблицами

6. Запросы к БД

Для наглядного представления работы БД необходимо сделать запросы к ней:

1) Вывести книги с указанием их тематик.

select b.id, b.name "book_name", nvl(t.name, '- no tags -') "tag_name"

from books b left join ref_bookTags rt on (b.id = rt.book_id)

left join book_tags t on (rt.tag_id = t.id)

Результат:

ID	Book_name	Tag_name
1	Diff analiz	Math
1	Diff analiz	Diff
2	Algoritm	Inform
4	Skaz, kak Popov radio izobretal	Poetry XVIII century
8	Pyatna na stekle kak razum	Physics
8	Pyatna na stekle kak razum	Feng shui
5	Metodi4ka 3-23	- no tags -
3	Kak pobedit drakona	- no tags -
6	Metodi4ka 3-24	- no tags -
7	Labs	- no tags -

2) Вывести книги с указанием их типа

select b.id, b.name "book_name", t.name "type_name"

from books b join book_types t on (b.type_id = t.id

Результат:

ID	Book_name	Type_name
1	Diff analiz	uchebnik
2	Algoritm	uchebnik
3	Kak pobedit drakona	literatura
4	Skaz, kak Popov radio izobretal	literatura
5	Metodi4ka 3-23	metodichka
6	Metodi4ka 3-24	metodichka
7	Labs	labs
8	Pyatna na stekle kak razum	labs
9	Top secret research N 08123	labs

3) Вывести список групп, которые имеют доступ к книге

select b.id, b.name "book_name", g.name "group_name", a.name "by_access_name"

from books b left join book_acl acl on (b.id = acl.book_id)

left join baccess a on (acl.access_id = a.id)

left join ref_groupAccess ga on (a.id = ga.access_id)

left join bgroups g on (ga.group_id = g.id)

order by b.id

Результат:

ID	Book_name	Group_name	By_access_name
1	Diff analiz	Admin	General Access
1	Diff analiz	User	General Access
1	Diff analiz	Admin	Access for Admin Group
2	Algoritm	-	-
3	Kak pobedit drakona	-	-
4	Skaz, kak Popov radio izobretal	-	-
5	Metodi4ka 3-23	-	-
6	Metodi4ka 3-24	-	-
7	Labs	-	-
8	Pyatna na stekle kak razum	-	-

4) Вывести все пользователей и доступные им книги

select al.id "user_id", al.fio "user_name", b.name "book_name", a.name "by access" from

(select u.id, u.fio, ua.access_id from busers u left join ref_userAccess ua on (u.id= ua.user_id)

union

select u.id, u.fio, ga.access_id from busers u left join ref_userGroup ug on (u.id= ug.user_id)

join bgroups g on (ug.group_id = g.id)

join ref_groupAccess ga on (g.id = ga.group_id)) al

left join baccess a on (al.access_id = a.id)

left join book_acl acl on (a.id = acl.access_id)

left join books b on (b.id = acl.book_id)

Результат:

User_id	User_name	Book_name	By Access
3	Birukov K.A.	Diff analiz	General Access
1	Avdeev S. A.	Diff analiz	General Access
6	Rozhkov I.A.	Top secret research N 08123	Access for Rozhkov
5	Gordeev A.B.	-	-
4	Sharapov M.T.	-	-
3	Birukov K.A.	-	-
2	Zykov Z.V.	-	-
1	Avdeev S. A.	-	-

Приложение 1

Создание и заполнение таблиц

#Таблица авторов книг

CREATE TABLE book_authors(

id number PRIMARY KEY,

lname varchar(32),

fname varchar(32),

mname varchar(32)

)

create sequence book_authors_seq start with 1 increment by 1 nomaxvalue;

INSERT INTO book_authors VALUES (book_authors_seq.nextval, 'Kalashnikov', 'X', 'X');

INSERT INTO book_authors VALUES (book_authors_seq.nextval, 'Kormen', 'T', '');

INSERT INTO book_authors VALUES (book_authors_seq.nextval, 'Zorich', '', '');

INSERT INTO book_authors VALUES (book_authors_seq.nextval, 'Schild', 'G', '');

INSERT INTO book_authors VALUES (book_authors_seq.nextval, 'Petrosyan', 'E', 'V');

INSERT INTO book_authors VALUES (book_authors_seq.nextval, 'Mao', 'Dze', 'Dun');

#Таблица типов книг

CREATE TABLE book_types(

id number PRIMARY KEY,

name varchar(32)

)

INSERT INTO book_types VALUES (1, 'uchebnik');

INSERT INTO book_types VALUES (2, 'metodichka');

INSERT INTO book_types VALUES (3, 'labs');

INSERT INTO book_types VALUES (4, 'literatura');

INSERT INTO book_types VALUES (5, 'protocol');

#Таблица тем для книг

CREATE TABLE book_tags(

id number PRIMARY KEY,

name varchar(32)

)

INSERT INTO book_tags VALUES (1, 'Math');

INSERT INTO book_tags VALUES (2, 'Diff');

INSERT INTO book_tags VALUES (3, 'Physics');

INSERT INTO book_tags VALUES (4, 'Inform');

INSERT INTO book_tags VALUES (5, 'Poetry XVIII century');

INSERT INTO book_tags VALUES (6, 'Feng shui');

#Таблица книг

CREATE TABLE books(

id number PRIMARY KEY,

name varchar(32),

ebook long,

type_id number,

foreign key (type_id) references book_types(id)

)

INSERT INTO books VALUES (1, 'Diff analiz','', 1);

INSERT INTO books VALUES (2, 'Algoritm','', 1);

INSERT INTO books VALUES (3, 'Kak pobedit drakona','', 4);

INSERT INTO books VALUES (4, 'Skaz, kak Popov radio izobretal','', 4);

INSERT INTO books VALUES (5, 'Metodi4ka 3-23','', 2);

INSERT INTO books VALUES (6, 'Metodi4ka 3-24','', 2);

INSERT INTO books VALUES (7, 'Labs','', 3);

INSERT INTO books VALUES (8, 'Pyatna na stekle kak razum','', 3);

INSERT INTO books VALUES (9, 'Top secret research N 08123','', 3);

#Связь авторов и книг

CREATE TABLE ref_bookAuthor(

id number primary key,

book_id number REFERENCES books (id),

author_id number REFERENCES book_authors (id)

)

INSERT INTO ref_bookAuthor VALUES (1, 1, 3);

INSERT INTO ref_bookAuthor VALUES (2, 2, 2);

INSERT INTO ref_bookAuthor VALUES (3, 8, 6);

INSERT INTO ref_bookAuthor VALUES (4, 7, 1);

INSERT INTO ref_bookAuthor VALUES (5, 4, 5);

INSERT INTO ref_bookAuthor VALUES (6, 4, 6);

#Связь книг с темами

CREATE TABLE ref_bookTags(

id number primary key,

book_id number REFERENCES books (id),

tag_id number REFERENCES book_tags (id)

)

INSERT INTO ref_bookTags VALUES (1, 1, 1);

INSERT INTO ref_bookTags VALUES (2, 1, 2);

INSERT INTO ref_bookTags VALUES (3, 2, 4);

INSERT INTO ref_bookTags VALUES (4, 4, 5);

INSERT INTO ref_bookTags VALUES (5, 8, 3);

INSERT INTO ref_bookTags VALUES (6, 8, 6);

#Таблица пользователей

CREATE TABLE busers(

id number PRIMARY KEY,

fio varchar(64 ),

login varchar(32) UNIQUE NOT NULL,

password varchar(32) ,

email varchar(32) UNIQUE

);

create sequence busers_seq start with 1 increment by 1 nomaxvalue;

INSERT INTO busers VALUES (busers_seq.nextval, 'Avdeev S.A.', 'savdeev', '123', '');

INSERT INTO busers VALUES (busers_seq.nextval, 'Zykov Z.V.', 'zzykov', '123', '');

INSERT INTO busers VALUES (busers_seq.nextval, 'Birukov K.A.', 'kbirukov', '123', '');

INSERT INTO busers VALUES (busers_seq.nextval, 'Sharapov M.T.', 'msharapov', '123', '');

INSERT INTO busers VALUES (busers_seq.nextval, 'Gordeev A.B.', 'agordeev', '123', '');

INSERT INTO busers VALUES (busers_seq.nextval, 'Rozhkov I.A.', 'arozhkov', '123', '');

#Таблица групп пользователей

CREATE TABLE bgroups(

id number PRIMARY KEY,

name varchar(32)

)

INSERT INTO bgroups VALUES (1, 'Admin');

INSERT INTO bgroups VALUES (2, 'Moder');

INSERT INTO bgroups VALUES (3, 'User');

INSERT INTO bgroups VALUES (4, 'Addvance Users');

INSERT INTO bgroups VALUES (5, 'Publicist');

INSERT INTO bgroups VALUES (6, 'Guest');

INSERT INTO bgroups VALUES (7, 'KGB');

INSERT INTO bgroups VALUES (8, 'BIO Scientist');

#Привязка пользователей к группам

CREATE TABLE ref_userGroup(

id number PRIMARY KEY,

user_id number REFERENCES busers (id),

group_id number REFERENCES bgroups (id)

);

create sequence ref_userGroup_seq start with 1 increment by 1 nomaxvalue;

INSERT INTO ref_userGroup VALUES (ref_userGroup_seq.nextval, 1, 3);

INSERT INTO ref_userGroup VALUES (ref_userGroup_seq.nextval, 1, 8);

INSERT INTO ref_userGroup VALUES (ref_userGroup_seq.nextval, 2, 7);

INSERT INTO ref_userGroup VALUES (ref_userGroup_seq.nextval, 3, 3);

INSERT INTO ref_userGroup VALUES (ref_userGroup_seq.nextval, 4, 4);

#Таблица доступов

CREATE TABLE baccess(

id number PRIMARY KEY,

name varchar(32)

)

INSERT INTO baccess VALUES (1, 'General Access');

INSERT INTO baccess VALUES (2, 'Research Access');

INSERT INTO baccess VALUES (3, 'BIO Research Access');

INSERT INTO baccess VALUES (4, 'Access for Rozhkov');

INSERT INTO baccess VALUES (5, 'Access for Admin Group');

#Доступ по пользователям

CREATE TABLE ref_userAccess(

id number primary key,

user_id number REFERENCES busers (id),

access_id number REFERENCES baccess (id)

)

INSERT INTO ref_userAccess VALUES (1, 6, 4);

#Доступ по группе

CREATE TABLE ref_groupAccess(

id number primary key,

group_id number REFERENCES bgroups (id),

access_id number REFERENCES baccess (id)

)

INSERT INTO ref_groupAccess VALUES (1, 1, 1);

INSERT INTO ref_groupAccess VALUES (2, 1, 1);

INSERT INTO ref_groupAccess VALUES (3, 3, 1);

INSERT INTO ref_groupAccess VALUES (4, 1, 5);

#Таблица необходимых доступов для книг

CREATE TABLE book_acl(

id number primary key,

book_id number references books (id),

access_id number references baccess (id)

)

INSERT INTO book_acl VALUES (1, 1, 5);

INSERT INTO book_acl VALUES (2, 1, 1);

INSERT INTO book_acl VALUES (3, 9, 5);

INSERT INTO book_acl VALUES (4, 9, 4);

INSERT INTO book_acl VALUES (5, 9, 3);

Размещено на Allbest.ru

...