База данных "Библиотека"

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

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

База данных "Библиотека"

Разработать систему, помогающую обслуживать библиотеку. Система должна предусматривать режимы ведения системного каталога, отражающего перечень областей знаний, по которым имеются книги в библиотеке. Внутри библиотеки области знаний в систематическом каталоге могут иметь уникальный внутренний номер и полное наименование. Каждая книга может содержать сведения из нескольких областей знаний. Каждая книга в библиотеке может присутствовать в нескольких экземплярах.

В библиотеке ведется картотека читателей. На каждого читателя в картотеку заносятся следующие сведения:

Фамилия, Имя , Отчество

домашний адрес

телефон (Будем считать, что у нас два телефона - рабочий и домашний)

дата рождения

Каждому читателю присваивается уникальный номер читательского билета.

Каждый читатель может одновременно держать на руках не более 5 книг. Читатель не должен одновременно держать более одного экземпляра книги одного названия.

Каждая книга, хранящаяся в библиотеке, характеризуется следующими параметрами:

уникальный шифр

название

фамилия автора (может отсутствовать)

фамилии соавторов (могут отсутствовать)

место издания (город)

издательство

год издания

количество страниц.

количество экземпляров книги в библиотеке

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

уникальный инвентарный номер

шифр книги, который совпадает с уникальным шифром из описания книг

присутствие в библиотеке.

В случае отсутствия данного экземпляра книги должны быть записаны следующие сведения:

номер билета читателя, который взял книгу

дата выдачи книги

дата возврата.

Каждый экземпляр книги может находиться на руках только у одного читателя.

Предусмотреть следующие ограничения на информацию в системе:

Не может быть информации о соавторах, если нет информации об авторе. Книга может не иметь ни автора ни соавтора.

В библиотеке должны быть записаны читатели не моложе 17 лет.

В библиотеке присутствуют книги, изданные начиная с 1960 по текущий год

Каждый читатель может держать на руках не более 5 книг.

Каждый читатель при регистрации в библиотеке должен дать телефон для связи он может быть рабочим или домашним.

Каждая область знаний может содержать ссылки на множество книг, но каждая книга может относиться к различным областям.

Создание БД и таблиц:

автоматизированный информационный библиотека

CREATE DATABASE Biblioteka

ON PRIMARY

(NAME = Biblioteka_db,

FILENAME = 'c:\Biblioteka\Biblioteka_db.mdf',

SIZE = 8MB,

MAXSIZE = 10MB,

FILEGROWTH = 1MB),

FILEGROUP maysecondarys_group

(NAME = maysecondarydata1,

FILENAME = 'c:\Biblioteka\maysecondarydata.ndf',

SIZE = 8MB,

MAXSIZE = 10MB,

FILEGROWTH = 1MB)

LOG ON

(NAME = logdata1,

FILENAME = 'c:\Biblioteka\logdata.ldf',

SIZE = 8MB,

MAXSIZE = 10MB,

FILEGROWTH = 2MB)

Создаем таблицы:

CREATE TABLE Knigi

( N_knigi char(8) Not null CONSTRAINT PK_1 primary key,

Nazv char(20),

avtor char(20) ,

Izdatelstvo char(20) ,

God_izdaniy DateTime constraint ch1 check (God_izdaniy>1960) ,

chisl_str char(8),

Kol_vo_v_bibl int,

cena int )

CREATE TABLE chitatel

( N_chit_bileta char(8) Not null CONSTRAINT PK_2 primary key ,

FIO char(20),

DOB DateTime,

adress char(20),

Dom_tel int Not null,

mobail_tel int Not null,

constraint ch2 check( DOB>1994))

CREATE TABLE Ekzemplyri

( K_ekzemplyra char(8) Not null CONSTRAINT PK_3 primary key ,

N_knigi char(8) )

CREATE TABLE Sotrudniki

( FIO char(50) Not null CONSTRAINT PK_6 primary key ,

adress char(20),

svyz_n1 char(20),

svyz_n2 char(20))

CREATE TABLE Regist_vidachi

( Sotrudnik char(50) CONSTRAINT FK_2 foreign key (sotrudnik ) references Sotrudniki(FIO) on delete cascade on update cascade,

K_ekzemplyra char(8) CONSTRAINT FK_3 foreign key ( K_ekzemplyra ) references Ekzemplyri( K_ekzemplyra ) on delete cascade on update cascade,

N_knigi char(8) CONSTRAINT FK_4 foreign key (N_knigi) references Knigi(N_knigi) on delete cascade on update cascade,

N_chit_bileta char(8) CONSTRAINT FK_5 foreign key (N_chit_bileta) references chitatel(N_chit_bileta) on delete cascade on update cascade,

chislo_knig int constraint ch3 check( chislo_knig<5),

Data_vidacji DateTime,

Data_vozvrata DateTime )

CREATE TABLE avtor

( avtor char(20) Not null CONSTRAINT PK_5 primary key,

DOB char(20),

adress char(20),

svyz_n char(20),

Biografiy text)

CREATE TABLE izdatelstvo

( Nazv_izdat char(20) Not null CONSTRAINT PK_7 primary key,

adress char(20),

svyz_n1 char(20),

svyz_n2 char(20))

ALTER TABLE Knigi ADD CONSTRAINT FK_KN foreign key

(Izdatelstvo ) references izdatelstvo(Nazv_izdat) on delete cascade on update cascade

ALTER TABLE Knigi ADD CONSTRAINT FK_AV foreign key

(Avtor ) references Avtor(Avtor) on delete cascade on update cascade

Заполняем таблицы при помощи команды INSERT INTO

ER- Диаграмма

Простые запросы

1. Вывести всю информацию о читателе с номером читательского билета В07



2.Вывести книги, стоимостью дороже 1500 тг.

3.Вывести номер читательского билета и номер книги, которые должны вернуть в период с 20декабря по 5 января

4.вывести номера читательских билетов читателей, которые имеют на руках 4 книги

5.Вывести авторов, чьи фамилии начинаются на букву Б

6.вывести общее количество книг в библиотеке

7.вывести сотрудников, код экземпляра книги,номер читательского билета, тех читателей, которые взяли книги в промежутке с 29,10,11 по 18,11,11

8.Вывести название и телефон издательств, находящихся по адресу Ауэзова 108 и Сейфулина 10

9.вывести среднюю цену всех книг в библиотеке

10.Вывести макс и мин цену за книгу

11.Вывести читателей с именами на букву А

12.вывести сотрудников с телефонными операторами Beeline

13.вывести информацию об издательствах, сгруппировать по адресу

14.Вывести биографию автора М. Булгакова

15.вывести книги с количеством страниц от 300 до 500

Запросы с подзапросами:

1.вывести автора книги «Плаха» и его биографию

2.Вывести номер книги, название и число страниц выпущенных издательством «Мектеп»

3.вывести количество книг у Амерханова Олжаса и его номер читательского билета

4.вывести номер читательского билета, ФИО , номер сотового тех читателей, которые должны принести книгу в библиотеку в период с 01-12-11 по 06-12-11

5.вывести всю информацию о выдаче книг и сотрудниках, которые задействованы в выдаче книг

6.Вывести ФИО, дату рождения, число книг на руках, дата возврата тех читателей, которые брали книги

7.левое объединение таблиц издательство и книги по полю издательство

8.

9.

10.

многотабличные запросы:

1.Информация о книгах и авторах

2.

3.

4.

5.

6.

7.

создание индексов

CREATE INDEX ind_1 ON Knigi (N_knigi)

CREATE INDEX ind_2 ON regist_vidachi (Sotrudnik)

CREATE INDEX ind_3 ON avtor (avtor)

CREATE INDEX ind_4 ON izdatelstvo (Nazv_izdat)

CREATE INDEX ind_sost1 ON Knigi (N_knigi,izdatelstvo)

CREATE INDEX ind_sost2 ON regist_vidachi (K_ekzemplyra,N_chit_bileta)

работа с индексами:

SELECT * FROM knigi WITH (INDEX(ind_1)) WHERE cena between 1000 and 1500

SELECT * FROM regist_vidachi WITH (INDEX(ind_2)) WHERE chislo_knig='3'

SELECT * FROM avtor WITH (INDEX(ind_3)) WHERE avtor in('A.Chehov','A.Pushkin','M.Bulgakov')



SELECT * FROM izdatelstvo WITH (INDEX(ind_4)) WHERE Nazv_izdat like 'p%'

SELECT * FROM knigi WITH (INDEX(ind_sost1)) WHERE nazv like 'Yazik %'

SELECT * FROM regist_vidachi WITH (INDEX(ind_sost2)) WHERE data_vidacji between

'01.11.11'and '30.11.11'

создание и использование представлений:

CREATE VIEW chitateli_v AS SELECT FIO,N_chit_bileta from chitatel

CREATE VIEW kr_v

AS SELECT Knigi.N_knigi,regist_vidachi.N_chit_bileta

FROM Knigi, regist_vidachi

WHERE Knigi.N_knigi = regist_vidachi.N_knigi

GROUP BY Knigi.N_knigi,regist_vidachi.N_chit_bileta

CREATE VIEW I_v

AS SELECT *

FROM Izdatelstvo

WHERE Nazv_izdat ='Dialog'

CREATE VIEW a_v

AS SELECT avtor

FROM avtor

CREATE VIEW S_vw

AS SELECT FIO,adress,tel_n1

FROM sotrudniki



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

1.CREATE PROCEDURE cena AS SELECT SUM(cena) AS 'obshay cena' FROM Knigi

2. CREATE PROCEDURE pr_sred1 AS SELECT k.Nazv,k.avtor,r.N_chit_bileta,r.data_vidacji

FROM Knigi k, regist_vidachi r

WHERE k.N_knigi=r.N_knigi AND r.N_chit_bileta='B03'

3. CREATE PROCEDURE chisl_knig @kniga_s char (8)

AS

select kol_vo_v_bibl

from Knigi

where N_knigi=@kniga_s

4. CREATE PROCEDURE pr4 @cena int

AS

IF (select min(cena) from knigi)<@cena

update knigi set cena=cena*1.1

GO

pr4 4000

5. CREATE PROCEDURE pr5 @cena int

AS

IF @cena>(select avg(cena) from knigi)

print ' Vywe srednego'

else

print'Nizhe srednego'

GO

6. CREATE PROCEDURE pr6

AS

SELECT FIO,adress

FROM sotrudniki

where FIO Like('% D%')

7. CREATE PROCEDURE pr7

AS

SELECT N_knigi,Nazv,avtor,izdatelstvo

FROM Knigi

Where cena>(select cena

from Knigi where izdatelstvo='SSSR')

триггеры:

CREATE TRIGGER UT

ON Knigi

FOR UPDATE

AS

IF UPDATE (God_izdaniy)

BEGIN

DECLARE @na4_data datetime, @noway_data datetime

SELECT @na4_data =god_izdaniy

FROM deleted

PRINT '@na4_data = '

PRINT CONVERT(varchar(5),@na4_data)

SELECT @noway_data = god_izdaniy

FROM inserted

PRINT '@noway_data = '

PRINT CONVERT(varchar(5),@noway_data)

IF (@noway_data <1960)

BEGIN

PRINT 'God izdaniy doljen bit bolshe 1960g.'

ROLLBACK

END

ELSE

PRINT 'Dannie obnovleni'

END

GO

UPDATE knigi

SET god_izdaniy = '01.01.19'

WHERE N_knigi = 'K01'

GO

2.

CREATE TRIGGER UT2

ON regist_vidachi

FOR UPDATE

AS

IF UPDATE (chislo_knig)

BEGIN

DECLARE @na4_chislo int, @nowoe_chislo int

SELECT @na4_chislo =chislo_knig

FROM deleted

PRINT '@na4_chislo = '

PRINT CONVERT(varchar(5),@na4_chislo)

SELECT @nowoe_chislo = chislo_knig

FROM inserted

PRINT '@nowoe_chislo = '

PRINT CONVERT(varchar(5),@nowoe_chislo)

IF (@nowoe_chislo >5)

BEGIN

PRINT 'chitatel ne doljen derjat na rukah bolshe 5 knig'

ROLLBACK

END

ELSE

PRINT 'Dannie obnovleni'

END

GO

UPDATE regist_vidachi

SET chislo_knig = 6

WHERE N_chit_bileta = 'B05'

GO



Создать пользователей, роли для БД с различными правами и привилегиями

sp_grantlogin 'MICROSOF-17B588\Bibl_ADMIN'

sp_addlogin @loginame = 'Biblioteka',

@passwd = '111' ,

@defdb = 'Biblioteka' ,

@deflanguage = 'US_English'

sp_adduser @loginame = 'Biblioteka',

@name_in_db = 'Bibl_user'

grant create procedure, create view to Bibl_user

grant insert on knigi to Bibl_user

grant delete on regist_vidachi to Bibl_user

Создать резервные копии БД

sp_addumpdevice 'disk', 'bibl_copy',

'd:\Biblioteka\bibl_copy.BAK'

sp_addumpdevice 'disk', ' bibl_diffcopy ',

'd:\Biblioteka\bibl_diffcopy.BAK'

sp_addumpdevice 'disk', 'bibl_logcopy',

'd:\Biblioteka\bibl_logcopy.BAK'

ALTER DATABASE Biblioteka

SET RECOVERY FULL

BACKUP DATABASE Biblioteka

TO Bibl_copy

WITH

DESCRIPTION = 'DB Biblioteka - Kursovay',

STATS = 10

GO

BACKUP log Biblioteka

TO Bibl_logcopy

Привести команды восстановления БД

drop database Biblioteka

RESTORE DATABASE Biblioteka

FROM bibl_copy

with RECOVERY

RESTORE DATABASE biblioteka

FROM bibl_copy

with norecovery

RESTORE log Biblioteka

FROM bibl_logcopy

with recovery

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