Вы на НеОфициальном сайте факультета ЭиП

На нашем портале ежедневно выкладываются материалы способные помочь студентам. Курсовые, шпаргалки, ответы и еще куча всего что может понадобиться в учебе!
Главная Контакты Карта сайта
 
Где мы?

Реклама


Лекции по SQL. Часть 2.

Просмотров: 1451 Автор: admin

Лекции по SQL

1.1.  Каталог.

 

Каталог - это системная БД, содержащая информацию, представляющую ин­терес для самой системы. Каталог содержит 20-25 таблиц. Рассмотрим не­ко­то­рые из них.

SYSTABLES (имя таблицы,  имя создателя,  число столбцов...) В этой таб­ли­це име­ется кортеж для каждой таблицы (базовой или VIew) , в том числе и сис­­темных. В частности таблицы SYSTABLES содержит описание смой себя.  Для каждой таблицы указывается  имя  таблицы (NAME), создатель (CREATOR), число столбцов (COL­COUNT) и т. д.

SYSCOLUMNS - содержит описание столбцов таблиц.  Для  столбца указано имя (NAME), имя таблицы (TBNAME), тип (COLTYPE) и др.

SYSINDEXES - содержит описание индексов.

Создателем системных таблиц является SYSADM.

 

Поскольку каталог состоит из таблиц, то к нему могут быть обращены за­просы языка SQL.  Нельзя,  однако, обновлять каталог командами SQL. Данные в ка­­талог за­но­сятся при выполнении  операторов CREATE, DROP, INDEX... Ка­та­лог также вклю­чает в себя и  записи для таблиц  каталога,  то есть он содержит свое ­собственное  опи­сание. Эти записи создаются в процессе установки сис­те­мы.

Каждая из  таблиц  каталога  SYSTABLES  и SYSCOLUMNS включает стол­бец, назы­ваемый REMARKS (комментарий). Оператор COMMENT языка SQL мо­жет туда что либо поместить.

    COMMENT ON TABLE  S  IS "Описание  одного  поставщика"

    COMMENT ON COLUMN P.CITY IS "Расположение склада деталей"

 

Примеры запросов:

1) Какие таблицы содержат поле NP ?

 

SELECT TBNAME FROM SYSTABLES WHERE NAME='NP;

 

2) Из каких столбцов состоит таблица S ?

 

SELECT NAME FROM SYSADM.SYSCOLUMNS WHERE TBNAME='S';

 

1.2. Синонимы

Синоним - альтернативное имя таблицы или VIEW.  Пусть пользователь А вы­­пол­­нил команду:

    CREATE TABLE PRIMER

Тогда пользователь В может обратиться к таблице:

    SELECT * FROM A.PRIMER

Но он может также выполнить оператор

    CREATE SYNONYM  ijk  FOR A.PRIMER и в дальнейшем обращаться к

этой таблице по имени ijk.

Синоним может быть отменен оператором

    DROP SYNONYM <синоним>

1.3.Представления (VIEW).

 

Представление (View)  -  это  виртуальная  таблица.  Описание представ­ле­ний хра­нится в системном каталоге  (таблица  SYSVIEWS). Представление соз­да­ется  опера­тором CREATE VIEW:

 

    CREATE VIEW <имя  VIEW>

    [(<имя столбца>[,<имя столбца>]...)]

    AS <подзапрос>

    [WITH CHECK OPTION]

 

После того,  как представление создано ,  к нему можно  обращаться как к обыч­ной таблице.

Как обычно,  подзапрос не может содержать UNION или ORDER BY.

Например:

 

    CREATE VIEW RED_DETAILS (ND, D_NAME, WEIGHT,CITY)

    AS SELECT ND,D_NAME,WEIGHT,CITY FROM P

    WHERE COLOR="Красный"

 

Для представление также может быть введен синоним. Существует взаимно  од­­­но­­значное соответствие между полями VIEW и выражениями в операторе SELECT.  Пред­ставление в дальнейшем могут участвовать в любых запросах, в том числе воз­мож­но использование уже существующих представлений для соз­дания новых.

Фраза WITH CHECK OPTION указывает, что для операций UPDATE и INSERT над этим представлением должна выполняться проверка, которая обес­печивает удов­ле­тво­рение фразы WHERE. Если в результате обновления появ­ля­ет­ся запись, не удов­ле­тво­ря­ющая WHERE, то обновление будет  отвергнуто. (GUPTA поместила запись в основное отношение)

 

Представление может быть уничтожено оператором

 

DROP VIEW <имя представления>

 

Если уничтожается базовая таблица,  то все  определенные  над ней пред­став­­ле­ния (и их производные) тоже уничтожаются. Оператора ALTER VIEW в языке SQL нет.

Операцию выборки из представления можно полностью представить себе как не­ко­­то­рый запрос к базовым таблицам.  Выборка корректна, если корректен такой за­прос. Рассмотрим пример:

 

CREATE VIEW pq (nd,kolvo) AS

 SELECT nd sum(amount) FROM SP GROUP BY nd;

 

Затем следует запрос:

 

SELECT * FROM PQ WHERE kolvo > 100; /*  1 */

 

Выполняя слияние этих 2-х операторов, получим:

 

SELECT nd, sum(amount) FROM sp WHERE sum(amount) >100 GROUP BY nd;

 

Здесь нельзя во фразе WHERE использовать  стандартную функцию. Отметим,  однако, что Gupta SqlWindows запрос (1) проглотила молча.

Необходимо помнить, что поля VIEW - это виртуальные поля и их моди­фикация не всегда возможна.  Представления,  которые являются подмно­жест­вами строк реаль­ных  таблиц  являются  обновляемыми,  в противном случае - нет.

Например, приведенное выше view pq не  является  модифицируемым, а  представ­ление :

 

CREATE VIEW GOOD AS SELECT * FROM S WHERE STATUS >100;

 

- является.

 

Представление, допускающее обновление должно быть производным от  един­­ственной таблицы. Более того:

а) Если поле представления продуцируется  из  арифметического выражения или константы, то над полем недопустимы операции update и insert. Однако, опе­рация DELETE допустима.

б)Если какое  либо поле продуцируется из стандартной функции, то VIEW не модифицируемо.

в) Если  в представлении используется GROUP BY,  то оно не  обновляемо.

г) Если используется DISTINCT, то VIEW не обновляемо.

д) Если определение представления включает вложенный  подзапрос, и во фразе FROM подзапроса указывается базовая таблица, над которой определяется данное пред­­­ставление,  то это  представление не обновляемо.

е) Если во фразе FROM  в  определении  представления  указано несколько  таб­лиц, то представление не обновляемо.

 

1.3.1. Представления как средство обеспечения независимости программ от данных.

 

Понятие VIEW служит, в частности, средством обеспечения  логической не­за­ви­симости данных.  Использование VIEW частично обеспечивает не­чув­стви­тель­ность уже существующих приложений  к изменению структуры.

Во первых,  добавление новых полей и таблиц не влияет на  уже суще­ству­ю­щие приложения (если в SELECT нет *).

Во вторых, реструктуризация тоже во многих случаях тоже может быть  сде­лана прозрачной для уже существующих приложений.  Например, пред­по­ло­жим, что при­нято решение расщепить базовую таблицу s на  таблицу sx(np, fam, city)  и таблицу sy(np,status).  Изменение структуры может быть выполнено по­сле­довательностью опе­ра­торов:

 

CREATE TABLE SX (NP char(5) NOT NULL, FAM char(20), CITY char(15));

CREATE TABLE SY (NP char(5) NOT NULL, STATUS smallint));

CREATE UNIQUE INDEX XSX ON SX (np);

CREATE UNIQUE INDEX XSY ON SY (np);

INSERT INTO SX(NP,FAM,CITY) SELECT NP,FAM,CITY FROM S;

INSERT INTO SY (NP,STATUS) SELECT NP,STATUS FROM S;

DROP TABLE S;

 

Если некоторое приложение  использовало  таблицу  S,  которой больше не су­ще­­ствует,  то ему можно предложить вместо нее VIEW S:

 

CREATE VIEW S (NP,FAM,STATUS,CITY) AS

  SELECT SX.NP,SY.FAM,SY.STATUS,SX.CITY

  FROM SX,SY

  WHERE SX.NP=SY.NP;

 

Однако, операция обновления уже не будет работать. (хотя могла бы).

 

Одновременно представления   дают  механизм  для  обеспечения контроля санк­ций доступа. Если программа не должна видеть некоторые поля,  то следует пре­доставить ей VIEW, которое этих полей не содержит.

1.4. Предоставление полномочий. - пока не раскрыто полностью

Предоставление полномочий на выполнение операций выборки,  пополнения и модификации выполняется оператором GRANT.

 

    GRANT <привилегии>

    [ON <тип объектов> <объекты>]

    TO  <пользователи> [WITH GRANT OPTION]

      где

    <привилегии> - список привилегий  через  запятую.  Привилегия может иметь значение:

    CREATETAB - создание таблиц

    SELECT - на выборку

    UPDATE - на изменение

    DELETE - на удаление

    INSERT - на добавление строк в таблицу

    ALTER - на использование оператора ALTER TABLE

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

    ALL PRIVILEGES - все привилегии

 

Примеры:

    1) GRANT SELECT ON TABLE S TO JACKSON;

    2) GRANT SELECT, UPDATE (state,city) ON TABLE S TO USER1,USER2;

    3) GRANT CREATETAB ON DATABASE FIRST TO MICHAEL;

 

Если пользователь  U1 имеет полномочия на предоставление привилегии P друго­му пользователю U2,  то говорят, что U1 имеет полномочия на предоставление приви­ле­гии "с возможностью" GRANT.

Например: GRANT SELECT ON TABLE S TO U2 WITH GRANT OPTION;

Теперь U2 имеет возможность передать эти полномочия далее.     Имеются также неко­торые "пакетированные привилегии".  В частности, привилегия SYSADM позволяет де­лать любые корректные операции.

Примером некорректной операции могла бы быть попытка удалить таблицу каталога.

Оператор REVOKE отменяет привилегии.

 

    REVOKE <привилегии>

    [ON <тип объектов> <объекты>]

    FROM <пользователи>

 

Пусть была издана последовательность команд пользователями:

    U1: GRANT SELECT ON TABLE S TO U2 WITH GRANT OPTION;

    U2: GRANT SELECT ON TABLE S TO U3 WITH GRANT OPTION;

    U3: GRANT SELECT ON TABLE S TO U4 WITH GRANT OPTION;

 

    Если U1 даст команду

 

    REVOKE SELECT ON TABLE S FROM U2;

 

то отмена  будет распространяться каскадом.  Это не означает, однако,  что U2, U3, U4 лишатся возможности доступа к таблице  S - быть может такая при­ви­ле­гия была им предоставлена также и некоторым  другим пользователем.  В этом слу­чае суще­ствен­ной  становится хронология предоставления полномочий. (под­робно не рас­сма­три­ва­ется).

Результаты выполнения GRANT и REVOKE сохраняются в каталоге в фор­ме огра­ни­че­ний санкционирования доступа.

1.5. Задачи к главе "Интерактивный SQL"

1.5.1. База данных "Кооператив" (по Ульману)

Некоторый кооператив снабжает своих членов продуктами. Члены ко­о­пе­ра­ти­ва делают ему заказы. Кооператив имеет поставщиков, обеспечивающих его про­дуктами.

 

База данных имеет схему:

products(product char (10)) // носитель домена продуктов

member(fio char (10),balance number)

zakaz(fio char (10),product char (10),amount number)

provider(pname char (10)) // домен поставщиков

supply(pname char (10),product char (10))

 

Задача 1. Получить список членов кооператива - должников.

 

select fio from member where balance <0;

 

Задача 2.  Получить список поставщиков,  поставляющих хотя бы один товар, зака­зан­ный Ивановым

 

select distinct pname from supply

where product in

(select product from zakaz where fio='Иванов');

 

Задача 3.  Найти множество поставщиков таких, которые поставляют все товары, заказанные Ивановым.

Для целей решения запрос следует переформулировать в  следующей  фор­ме:  най­ти поставщиков таких, что не существует товара такого, что товар за­ка­зал Иванов, а пос­тав­щик его не поставляет.

 

select p0.pname from provider p0 where not exists

   (select p1.pname, product  from provider p1,products

      where p1.pname=p0.pname and

        product in

    (select product from zakaz where fio='Иванов')and

        product not in

          (select product from supply where p1.pname=supply.pname));

1.5.2. База данных учебный процесс.

 

Отношения:

 

teacher(teacher char(10))

subject(subject char(10))

audit(audit char(6));

grp(group char(6));

raspisan(teacher,audit,group,subject,pair,day)

 

Задача 1.  Найти  свободные  аудитории  на  первой   паре   в понедельник.

 

select distinct audit from audit where

audit not in

(select audit from raspisan where pair=1 and day=1);

 

Задача 2.  Преподаватели,  ведущие  занятия  в одной и той же группе.

 

select distinct t1.teacher, t2.teacher from raspisan t1, raspisan t2

where not t1.teacher=t2.teacher and t1.grp=t2.grp

and t1.teacher<t2.teacher;

 

Последнее условие исключает пары (x,x) и (y,x), оставляя пары (x,y).

 

Задача 3.  Кто может заменить преподавателя Марков для чтения курса 'Анализ' в понедельник на второй паре. Для этого заменяющий преподаватель должен быть сво­боден в это время и  'Анализ' должен иметься в его расписании.

 

select distinct teacher from raspisan r1 where

subject='Анализ' and not teacher='Марков' and not exists

(select teacher from raspisan where teacher=r1.teacher and

day=1 and pair=1);

 

Задача 4.  Извлечь из  расписания  те  занятия  преподавателя 'Марков', когда он занимается с той же группой, что и в понедельник на первой паре.

 

select * from raspisan  where

teacher='Марков' and grp  in

(select grp from raspisan where pair=1 and day =1

     and teacher='Марков');

 

    Задача 5.  Замена  с  возвратом.  Заменяющий  должен обладать свойствами:

   1) иметь занятия в той же группе

   2) быть свободным во время замены

3)  должно существовать время занятий, когда заменяемый свободен,   а заменяемый имеет  занятия  (условия  возможности  вернуть долг).

 

Опять будем заменять преподавателя 'Марков' на первой  паре в поне­дель­ник (day=1).  Будем в этом примере использовать временные таблицы.  Сначала соз­дадим таблицу преподавателей,  свободных во время замены:

 

create table fr_teach (teacher char (10));

 

insert into fr_teach

select teacher from teacher t1 where

   not exists

( select r1.teacher from raspisan r1 where

     day=1 and pair=1 and teacher=t1.teacher);

 

Затем таблицу преподавателей,  имеющих занятия в той же группе, что и во время замены.

 

create table gr_teach(teacher char(10));

 

insert into gr_teach

select teacher from teacher t1 where

  not teacher='Марков' and

  (select grp from raspisan where teacher='Марков'

   and day=1 and pair=1) in

  (select grp from raspisan where teacher=t1.teacher);

 

    Выполним соединение этих таблиц.

 

create table goodteach (teacher char(10));

 

insert into goodteach

select distinct gr_teach.teacher from gr_teach, fr_teach

where gr_teach.teacher=fr_teach.teacher;

 

Должна существовать возможность вернуть долг. Это значит, что среди мно­­жес­тва занятий заменяющего преподавателя  с  группой,  в которой  про­ис­ходит  за­мена  Маркова,  должно существовать время, когда Марков свободен.

 

Найдем занятия преподавателей из goodteach c группой в  которой происходит замена.

 

create table aback (teacher char(10), day number, pair number);

 

select teacher,day,pair from raspisan where

  teacher in (select teacher from goodteac)

     and grp in

  (select grp from raspisan where day=1 and pair=1

     and teacher='Марков');

 

Найдем преподавателей  на  занятиях в aback такие,  что 'Марков' в это время свободен.

 

select distinct teacher from aback where

not exists

(select * from raspisan where teacher='Марков' and

 day=aback.day and pair=aback.pair);

 

Это и  есть окончательный ответ.  Заметим,  что результат мог быть получен и более коротким путем.

 

Решение Марины Баштовой:

// Вывести все замены (когда какой учитель кого может заменить) при условии:

// 1. учителя должны иметь занятия в одной группе

// 2. быть свободными во время замены

// mari и raspisan - одно и то же


SELECT A.*,B.* FROM mari A, mari B

    WHERE A.teacher > B.teacher AND

             A.group =(select C.group FROM mari C

                                    WHERE C.day=B.day AND C.pair=B.pair AND C.teacher=B.teacher

                            ) AND

             NOT EXISTS

             (SELECT C.* FROM mari C

                     WHERE C.teacher=B.teacher AND C.day=A.day AND C.pair=A.pair

                                  ) AND

            NOT EXISTS

            (SELECT  D.* FROM mari D  WHERE D.teacher=A.teacher

                         AND D.day=B.day AND D.pair=B.pair) ;

 

// Замена для Маркова по МатАнализу на 2 паре в 5 день

SELECT A.* FROM mari A

WHERE  A.group =

   (SELECT B.group FROM mari B

    WHERE (B.day=5) AND (B.pair=2) AND (B.teacher='МАРКОВ') ) AND

    NOT EXISTS

         (SELECT C.* FROM mari C

           WHERE (C.teacher='МАРКОВ') AND (C.day=A.day) AND C.pair=(A.pair)

         )   AND

    NOT EXISTS

        (SELECT D.* FROM mari D WHERE (D.teacher=A.teacher) AND (D.day=5)

        AND (D.pair=2)

        ) ;



Популярные новости

Статистика сайта



Rambler's Top100



 
Copyright © НеОфициальный сайт факультета ЭиП