Курсовые-задачи (СУБД - задачи)

Задание 1. Задача пенсионного фонда. Рассмотрим таблицу следующей структурыCREATE TABLE пенсии (иннCHAR(10) NOT NULL,пен_годINTEGER NOT NULL,раб_месINTEGER NOT NULL DEFAULT 0CHECK (раб_мес BETWEEN 0 AND 12),заработано DECIMAL(16,2) NOT NULL DEFAULT 0.00,PRIMARY KEY (инн,пен_год));В этой таблице инн — это индивидуальный номер налогоплательщика, служащий для однозначной идентификации последнего. Столбец пен_год — календарный год, например 1999, 2000, 2001 и т.д.Столбец раб_мес — это количество месяцев, которые данный налогоплательщик отработал в соответствующем году.

В столбце заработано находится суммарный заработок налогоплательщика в данномгоду.Задача состоит в том, чтобы вычислить суммарный заработокналогоплательщика за те последние 60 месяцев, которые он отработал.Задание 2. Рассмотрим длинный ряд пронумерованных подряд корзин, в каждой из которых может лежать по одному шару. Состояниекорзин в данный момент времени содержится в таблицекорзины (номер_корзинысостояниеINTEGER NOT NULL UNIQUE,CHAR(1) NOT NULLCHECK (состояние = ’0’ ORсостояние = ’1’)),где значение поля состояние, равное «0» означает, что корзинас данным номером пуста, а «1» — что в корзине есть шар.Задача состоит в том, чтобы написать запрос, определяющий множество всех серий из n подряд идущих пустых корзин.Задание 3. Транспортная компания, занимающаяся грузоперевозками, ведет ежемесячный учет рабочего времени своих водителей в таблицерабочее_время ( водительмесяцчасыCHAR(25) NOT NULL,INTEGER NOT NULL,INTEGER));Таблица заполняется по мере того, как водители сдают листы учетарабочего времени, в которых каждый указывает свою фамилию, календарный месяц и количество часов, которые он уже отработал в этоммесяце.Требуется составить отчет о рабочем времени водителей за последние полгода.

В каждой строке этого отчета должны быть указаныфамилия водителя и количество часов, которые он работал в течение каждого из последних шести месяцев. Допустимы ситуации, когданекоторые водители вообще не работали в течение некоторых месяцеви, поэтому, сведения о часах своей работы в эти месяцы они не подавали.Задание 4. Рассмотрим базу данных сотрудников, состоящую из следующих двух таблиц:сотрудники(номер_сотрудника INTEGER PRIMARY KEY,фамилия CHAR(20),...)подчиненные (номер_сотрудникаINTEGER NOT NULL,номер_подчиненногоINTEGER NOT NULL,фамилия_подчиненного CHAR(20) NOT NULL,...PRIMARY KEY (номер_сотрудника,номер_подчиненного),FOREIGN KEY номер_сотрудника REFERENCESсотрудники(номер_сотрудника))Задача состоит в том, чтобы написать запрос, показывающий сколькоподчиненных у каждого сотрудника.

Точнее, запрос должен выдаватьномер сотрудника, фамилию сотрудника, количество подчиненных уэтого сотрудника.Задание 5. В таблицедни_рождения (фамилиядатаCHAR(20) NOT NULL,DATENOT NULL)задан список фамилий и дат рождения сотрудников некоторой организации. Задача стоит в том, чтобы написать запрос, возвращающийсписок всех сотрудников, дни рождения которых наступят в течениепредстоящих 45 дней, начиная с сегодняшнего.Замечание. Решение может зависеть от конкретных функций работы с датами в Вашей СУБД.Задание 6.

В некотором ВУЗе ведется подсчет рейтинга каждого студента — суммы баллов, полученных студентом при выполнении всехконтрольных мероприятий (тестов, зачетов, контрольных работ и пр.).Для этого в базе данных ВУЗа предназначена таблицастуденты (студент#рейтингINTEGER,NUMBER(9,2)),содержащая список всех студентов и их рейтинги. Кроме того, длякаждой дисциплины каждый преподаватель ведет таблицуоценки (студент#работа#попытка#оценкаINTEGERINTEGERINTEGERINTEGERNOTNOTNOTNOTNULL,NULL,NULL,NULL)в которой он фиксирует оценки студентов за контрольные работы поданной дисциплине.

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

Поэтому он написал такой запросUPDATE студентыSET рейтинг = рейтинг +(SELECT SUM (оценка)FROM оценки sWHERE студент# = s.студент#);Однако, в деканате преподавателю объяснили, что в качестве итоговой оценки студента за контрольную работу, которая и должна вносить вклад в рейтинг следует брать среднее арифметическое оценок,полученных им за все попытки написать данную работу.Сначала преподаватель хотел просто заменить в своем оператореSUM() на AVG(). Но после некоторого размышления, он понял, что этобыло бы ошибкой.

Почему он так решил? Каково правильное решение?Указание. Учесть ситуацию, когда студент по каким-либо причинам еще не писал контрольную работу.Задание 7. Аминистратор WEB-сервера ведет учет его посещаемостив таблицепосещаемость (часINTEGER NOT NULL,посетители INTEGER NOT NULL,PRIMARY KEY (час))содержащей сведения за одни сутки о количестве посетителей WEBсервера в течение каждого часа, начиная с 0-го до 23-го. Написатьзапрос, возвращающийа) все интервалы из t последовательных часов, в течение которых наWEB-сервер не заходил ни один посетитель. Величина t можетизменяться и должна быть параметром запроса.б) все интервалы из t последовательных часов, в течение каждогоиз которых на WEB-сервер заходило не менее n посетителей.Величины t и n должны быть параметрами запроса.Задание 8.

Вы отвечаете за учет состояния склада в некоторой компании. Вам ежедневно поступают со склада сводки, в которых указаноколичество оборудования, которое вернули или выдали со склада. Этоколичество может быть положительным числом (означающим поступления на склад), а может быть и отрицательным (означающим выдачисо склада). Данные из поступивших сводок сохраняются в таблицесводки ( датаколичествоDATE NOT NULL,INTEGER NOT NULLCHECK (количество <> 0))Задача состоит в том, чтобы составить ежедневный баланс состояниясклада, т.е. количества оборудования на складе в каждый день. Решение оформить как представление над таблицей сводки, добавив ещеодин столбец на_складе. Результат должен выглядеть примерно так:ДАТА----------2001-НОЯ-012001-НОЯ-022001-НОЯ-032001-НОЯ-042001-НОЯ-05КОЛИЧЕСТВО НА_СКЛАДЕ---------- --------100100120220-1507050120-3585Задание 9.

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

База данныхдолжна находиться в карманном компьютере распорядителя. ПоэтомуВаша задача состоит в том, чтобы спроектировать эту базу данных стаким расчетом, чтобы она занимала как можно меньше памяти карманного компьютера распорядителя. После этого, требуется написатьSQL-запрос, показывающий распорядителю список свободных мест втекущий момент времени. Предполагается, что каждый номер места— это целое число, занимающее 2 байта.Задание 10.

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

Вашазадача состоит в том, чтобы составить список сотрудников и их активных на сегодня номеров допуска. Для обозначения состояния жетонаиспользовать «А» если жетон активен и «Н», если неактивен.Задание 11. В базе данных некоторой гостинницы для учета свободных номеров служит таблицаCREATE TABLE номера (номерINTEGER NOT NULL,дата_приездаDATENOT NULL,дата_отъездаDATENOT NULL,клиентCHAR(30),PRIMARY KEY (номер, дата_приезда),CHECK (дата_отъезда >= дата_приезда));В настоящем виде ограничение целостности CHECK() обеспечиваетневозможность ввода даты отъезда, которая предшествует дате приезда.

Однако, требуется большее — гарантировать невозможность бронирования номера с какой-либо даты, если она противоречит предшествующей дате отъезда из этого номера. Как это сделать?Задание 12. С помощью новой системы публикации баз данных Высобираетесь организовать электронный телефонный справочник Вашейорганизации. В Вашем распоряжении есть таблицыCREATE TABLE сотрудникиномер_сотрудникаимяфамилия(INTEGER PRIMARY KEY,CHAR(20) NOT NULL,CHAR(20) NOT NULL);CREATE TABLE телефоны (номер_сотрудника INTEGER NOT NULL,тип_телефонаCHAR(3), NOT NULLCHECK (тип_телефонаIN (’дом’,’фкс’))телефонCHAR(12) NOT NULL,PRIMARY KEY(номер_сотрудника,тип_телефона,телефон),FOREIGN KEY номер_сотрудникаREFERENCES сотрудники(номер_сотрудника)Коды «дом» и «фкс» указывают является ли данный номер домашнимтелефоном сотрудника или номером факса.

Требуется напечатать отчет, в котором на каждого сотрудника отводится по одной строке, и вкотором будут указаны оба телефона, либо NULL в случае отсутствиякакого-либо из них.Замечание. Ссылочное ограничение в данном случае означает,что Вы не можете указать телефон кого-либо, не являющегося сотрудником. Первичный ключ выглядит на первый взгляд слишком большим. Однако, возможны ситуации, когда женатые пары имеют одинаковые номера телефона и факса.

Или, например, у некоторых сотрудников телефон и факс могут быть подключены к одной телефоннойлинии.Задание 13. Поставьте следующий эксперимент. Создайте таблицуCREATE TABLE tab (редкий INTEGER NOT NULL, -- 5 разных значенийчастыйINTEGER NOT NULL, -- по 500 разных значений-- на каждое из значений-- столбца ’редкий’пустышка CHAR(100) NOT NULL)); -- что угодноИспользуя какое-либо процедурное расширение SQL заполните таблицу тестовыми данными так, что в столбце частый будет 5 различныхзначений, а в столбце редкий на каждое из 5-ти значений столбцачастый будет приходиться по 5000 различных значений.

Измерьтевремя выполнения нескольких операторов выборки из таблицы tabпоочередно для каждого из следующих четырех индексовCREATECREATECREATECREATEINDEXINDEXINDEXINDEXind1ind2ind3ind4ONONONONtabtabtabtab(редкий);(редкий,частый);(частый);(частый,редкий);Объясните результаты измерений..