Разработка базы данных «Бухгалтерия (учет материальных ценностей) »

[Введите текст]

МИНИСТЕРСТВО ТРАНСПОРТА РОССИЙСКОЙ ФЕДЕРАЦИИ ФЕДЕРАЛЬНОЕ АГЕНТСТВО ЖЕЛЕЗНОДОРОЖНОГО ТРАНСПОРТА Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования САМАРСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ПУТЕЙ СООБЩЕНИЯ

(СамГУПС) Курсовая работа по дисциплине: «Базы данных»

на тему: «Разработка базы данных «Бухгалтерия (учет материальных ценностей)»

Самара 2013

1. Теоретическая часть

1.1 Уровни представления баз данных. Трехуровневая архитектура баз данных

1.2 Внешний уровень

1.3 Концептуальный уровень

1.4 Внутренний уровень

2. Практическая часть

2.1 Задание

2.2 ER-модели

2.2.1 Логическая модель

2.2.2 Физическая модель

2.3 SQL скрипты

2.3.1 Скрипт базы данных

2.3.2 Триггеры и последовательности

2.3.3 Запросы Заключение Список литературы

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

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

1. Теоретическая часть

1.1 Уровни представления баз данных. Трехуровневая архитектура баз данных

Архитектура ANSI/SPARC включает три уровня: внутренний, внешний и концептуальный (рис. 1.1). В общих чертах они представляют собой следующее.

Рис. 1.1 — Три уровня архитектуры ANSI/SPARC

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

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

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

Если внешний уровень связан с индивидуальными представлениями пользователей, токонцептуальный уровень связан с обобщенным представлением пользователей.

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

Для лучшего понимания этих идей рассмотрим пример, представленный на рис. 1.2.

Рисунок 1.2 — Пример трех уровней представления базы данных Здесь отображено концептуальное представление простой базы данных о персонале, а также соответствующие ему внутреннее и два внешних представления (одно — для пользователя, применяющего язык PL/I, а другое — для пользователя, применяющего язык COBOL; Традиционные языки программирования PL/I и COBOL, послужившие основой для данного примера, все еще широко используются в программном обеспечении, установленном на многих предприятиях.). Конечно, этот пример полностью гипотетичен и мало похож на реальные системы, поскольку в нем умышленно исключены многие не относящиеся к делу детали.

Рассматриваемый здесь пример нуждается в пояснениях.

1. На концептуальном уровне база данных содержит информацию о типе сущности с именем EMPLOYEE (служащий). Каждый экземпляр сущности EMPLOYEE включает атрибуты номера служащего EMPLOYEE_NUMBER (шесть символов), номера отдела DEPARTMENT_NUMBER (четыре символа) и зарплаты служащего SALARY (пять десятичных цифр).

2. На внутреннем уровне служащие представлены типом хранимой записи STORED_EMP, длина которой составляет 20 байтов. Запись STORED_EMP содержит четыре хранимых поля: шестибайтовый префикс (возможно, содержащий управляющую информацию, такую как флажки или указатели) и три поля данных, соответствующие трем свойствам сущности, которая представляет служащего. Кроме того, записи STORED_EMP индексированы по полю ЕМР# с помощью индекса ЕМРХ.

3. Пользователь, применяющий язык PL/I, имеет дело с соответствующим внешним представлением базы данных. В нем каждый сотрудник представлен записью на языке PL/I, содержащей два поля (номера отделов данному пользователю не требуются, поэтому в представлении они опущены). Тип записи определен с помощью обычной структуры, соответствующей правилам языка PL/I.

4. Аналогично, пользователь, применяющий язык COBOL, имеет дело с собственным внешним представлением базы данных, в котором каждый сотрудник представлен записью на языке COBOL, содержащей, опять же, два поля (в данном случае опущен размер оклада). Тип записи определен с помощью обычного описания на языке COBOL в соответствии с принятыми в нем стандартными правилами.

Обратите внимание, что в каждом случае соответствующие элементы данных могут иметь различные имена. Например, к номеру сотрудника обращаются как к полю ЕМР# в представлении для языка PL/I и как к полю EMPNO. В представлении для языка COBOL. Этот же атрибут в концептуальном представлении имеет имя EMPLOYEE_NUMBER, а во внутреннем представлении — имя ЕМР#. Конечно, в системе должны быть известны все эти соответствия. Например, известно, что поле EMPNO В представлении для языка COBOL образовано из концептуального поля EMPLOYEE_NUMBER, которое, в свою очередь, отвечает хранимому полю ЕМР# ВО внутреннем представлении. Такие соответствия, или отображения (mapping), на рис. 1.2 явно не показаны.

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

Во-первых, концептуальный уровень в такой системе определенно будет реляционным в том смысле, что видимые на этом уровне объекты являются реляционными таблицами, а используемые операторы — реляционными операторами (включая операторы выборки строк и столбцов).

Во-вторых, каждое внешнее представление, как правило, также будет реляционным или достаточно близким к нему. Например, объявления записей в PL/I и COBOL, представленные на рис. 1.2, упрощенно можно считать аналогами объявлений таблиц в реляционной системе.

Примечание. Следует иметь в виду, что термин внешнее представление (часто-просто представление) в реляционном контексте имеет, к сожалению, довольно специфический смысл, который не полностью совпадает со смыслом, приписанным ему в этой главе.

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

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

Рисунок 1.3 — Подробная схема архитектуры системы баз данных

1.2 Внешний уровень

Внешний уровень — это индивидуальный уровень пользователя. Как было сказано ранее, пользователь может быть прикладным программистом или конечным пользователем с любым уровнем профессиональной подготовки. Особое место среди пользователей занимает администратор базы данных (АБД). В отличие от остальных пользователей, АБД интересуют также концептуальный и внутренний уровни.

У каждого пользователя есть свой язык для работы с СУБД.

Для прикладного программиста это либо один из распространенных языков программирования (например, PL/I, C++ или Java), либо специальный язык рассматриваемой системы. Такие оригинальные языки называют языками четвертого поколения на том (не вполне формальном!) основании, что машинный код, язык ассемблера и такие языки, как PL/I, можно считать языками трех первых «поколений», а оригинальные языки модернизированы по сравнению с языками третьего поколения в такой же степени, как языки третьего поколения улучшены по сравнению с языком ассемблера.

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

Для нашего обсуждения важно, что все эти языки включают подъязык данных, т. е. подмножество операторов всего языка, связанное только с объектами баз данных и операциями с ними. Иначе говоря, подъязык данных встроен в базовый язык, который дополнительно предоставляет различные не связанные с базами данных средства, такие как локальные (временные) переменные, вычислительные операции, логические операции и т. д. Система может поддерживать любое количество базовых языков и любое количество подъязыков данных. Однако существует один язык, который поддерживается практически всеми сегодняшними системами. Это язык SQL.

Большинство систем позволяет использовать язык SQL и интерактивно, как самостоятельный язык запросов, и в форме внедрения его операторов в другие языки программирования, такие как PL/I и Java.

Хотя с точки зрения архитектуры удобно различать подъязык данных и включающий его базовый язык, на практике они могут быть неразличимы настолько, насколько это имеет отношение к пользователю. Безусловно, с точки зрения пользователя предпочтительнее, чтобы они были неразличимыми. Если они неразличимы или трудноразличимы, их называют сильно связанными (и сочетание этих языков именуется языком программирования базы данных). Если они ясно и легко различаются, говорят, что эти языки слабо связаны. В то время как некоторые коммерческие системы (включая и конкретные продукты SQL, такие как СУБД Oracle) поддерживают сильную связь, многие системы обеспечивают лишь слабую связь. Системы с сильной связью могли бы предоставить пользователю более унифицированный набор возможностей, но очевидно, что они требуют больше усилий со стороны системных проектировщиков и разработчиков, поэтому, вероятно, и сохраняется такое положение дел.

В принципе, любой подъязык данных является на самом деле комбинацией по крайней мере двух подчиненных языков — языка определения данных (Data Definition LanguageDDL), который позволяет формулировать определения или объявления объектов базы данных, и языка манипулирования3 данными (Data Manipulation Language — DML), который поддерживает операции с такими объектами или их обработку. Например, рассмотрим пользователя языка PL/I (см. рис. 1.2). Подъязык данных этого пользователя включает определенные средства языка PL/I, применяемые для организации взаимодействия с СУБД.

Язык определения данных включает некоторые описательные структуры языка PL/I, необходимые для объявления объектов базы данных. Это сам оператор DECLARE (или DCL), определенные типы данных языка PL/I, а также возможные специальные дополнения для языка PL/I, предназначенные для поддержки новых объектов, которые не предусмотрены в существующей версии языка PL/I.

Язык обработки данных состоит из тех выполняемых операторов языка PL/I, которые передают информацию в базу данных и из нее; опять же, возможно, включая новые специальные операторы.

Примечание. В качестве уточнения следует отметить, что современный язык PL/I на самом деле вообще не включает никаких особых средств для работы с базами данных. Оператор языка обработки данных (оператор CALL), в частности, обычно просто обращается к СУБД (хотя такие обращения могут быть синтаксически упрощены, чтобы они стали более дружественными по отношению к пользователю).

Вернемся к архитектуре. Как уже отмечалось, отдельного пользователя интересует лишь некоторая часть всей базы данных. Кроме того, представление пользователя об этой части будет, безусловно, более абстрактным по сравнению с выбранным способом физического хранения данных. В соответствии с терминологией ANSI/SPARC, представление отдельного пользователя называется внешним представлением. Таким образом, внешнее представление — это содержимое базы данных, каким его видит определенный пользователь (т.е. для каждого пользователя внешнее представление и есть та база данных, с которой он работает).

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

В общем случае внешнее представление состоит из некоторого множества экземпляров каждого из многих типов внешних записей (которые вовсе не обязательно должны совпадать с хранимыми записями).

Предоставляемый в распоряжение пользователя подъязык данных всегда определяется в терминах внешних записей.

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

Каждое внешнее представление определяется посредством внешней схемы, которая в основном состоит из определений записей каждого из типов, присутствующих в этом внешнем представлении (см. рис. 2.2). Внешняя схема записывается с помощью языка определения данных, являющегося подмножеством подъязыка данных пользователя (Поэтому язык определения данных иногда называют внешним языком определения данных.)

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

1.3 Концептуальный уровень

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

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

Концептуальное представление определяется с помощью концептуальной схемы, включающей определения для каждого существующего типа концептуальных записей. Концептуальная схема использует другой язык определения данных — концептуальный. Чтобы добиться независимости от данных, нельзя включать в определения концептуального языка какие-либо указания о структурах хранения или методах доступа. Определения концептуального языка должны относиться только к содержанию информации. Это означает, что в концептуальной схеме не должно быть никакого упоминания о представлении хранимого файла, упорядоченности хранимых записей, индексировании, хэш-адресации, указателях или других подробностях хранения данных или доступа к ним. Если концептуальная схема действительно обеспечивает независимость от данных в этом смысле, то внешние схемы, определенные на основе концептуальной, заведомо будут обеспечивать независимость от данных.

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

COBOL (или каком-либо другом языке).

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

1.4 Внутренний уровень

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

Термин внутренняя запись относится к терминологии ANSI/SPARC и означает конструкцию, иначе называемую хранимой записью (в дальнейшем мы будем использовать именно этот термин).

Внутреннее представление, так же как внешнее и концептуальное, отделено от физического уровня, поскольку в нем не рассматриваются физические записи, обычно называемые блоками или страницами, и физические области устройства хранения, такие как цилиндры и дорожки. Другими словами, внутреннее представление предполагает наличие бесконечного линейного адресного пространства. Особенности методов отображения этого адресного пространства на физические устройства хранения в значительной степени зависят от используемой операционной системы и по этой причине не включены в общую архитектуру. Следует отметить, что блоки (или страницы) устройства ввода-вывода — это количество данных, передаваемых из вторичной памяти (памяти накопителя) в основную (оперативную) память за одну операцию ввода-вывода. Обычно, страницы имеют размер от 1 Кбайт и выше, но не больше 64 Кбайт (1 Кбайт = 1024 байт).

Внутреннее представление описывается с помощью внутренней схемы, которая определяет не только различные типы хранимых записей, но также существующие индексы, способы представления хранимых полей, физическую упорядоченность хранимых записей и т. д. (Соответствующий простой пример также приведен на рис. 1.2.) Внутренняя схема формируется с использованием еще одного языка определения данных — внутреннего.

Примечание. В этой работе вместо терминов внутреннее представление и внутренняя схема обычно будут использоваться интуитивно более понятные термины хранимая структура (или хранимая база данных) и определение структуры хранения, соответственно.

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

2. Практическая часть

2.1 Задание

Предметная область: Бухгалтерия (учет материальных ценностей).

Основные предметно-значимые сущности: Оборудование, Подразделения, Материально ответственные лица.

Основные предметно-значимые атрибуты сущностей:

оборудование — название, стоимость;

подразделения — название, вид подразделения;

материально ответственные лица — фамилия, имя, отчество.

Основные требования к функциям системы:

выбрать все несписанное оборудование по материально-ответственным лицам или определенному лицу;

выбрать все несписанное оборудование по подразделениям или определенному подразделению;

выбрать подразделения, не имеющие в настоящее время на балансе никакого несписанного оборудования;

выбрать списанное оборудование по подразделениям и материально-ответственным лицам;

выбрать материально-ответственных лиц с наибольшей суммой стоимости оборудования;

подсчитать общую стоимость несписанного оборудования по подразделениям.

2.2 ER-Модели

Для создания логической и физической моделей данных был применен ERwin.

ERwin-это CASE-средство для проектирования и документирования баз данных, которое позволяет создавать, документировать и сопровождать базы данных, хранилища и витрины данных.

Логическая модель базы данных «Бухгалтерия. Учет материальных ценностей» выглядит так, как показано на рисунке 2.1.

Рисунок 2.1 — Логическая модель базы данных При проектировке базы данных были использованы следующие сущности:

Помещение (Всего этажей, общая площадь, лифт).

Корпус (Адрес).

Подразделение (Вид, имя).

Должность (Название, оклад).

Сотрудник (ФИО).

Оборудование (Имя устройства).

Состояние (Состояние).

Вид (Стоимость, фирма).

Договор (Имя договора, номер, дата).

История.

Так же на основе логической модели строится физическая ER-модель показанная на рисунке 2.2.

Рисунок 2.2 — Физическая модель данных

2.3 SQL

2.3.1 DDL-скрипт

Далее чтобы перенести спроектированную модель базы данных вOracle 10gнеобходимо сгенерировать SQLскрипт:

CREATE TABLE Device

(

ID_OB INTEGER NOT NULL ,

NAME_DEV VARCHAR2(20) NULL ,

ID_VID INTEGER NULL ,

ID_SOST INTEGER NULL ,

ID_DOG INTEGER NULL

);

CREATE UNIQUE INDEX XPKDevice ON Device

(ID_OB ASC);

ALTER TABLE Device

ADD CONSTRAINT XPKDevice PRIMARY KEY (ID_OB);

CREATE TABLE Dogovor

(

ID_DOG INTEGER NOT NULL ,

NAME_DOG VARCHAR2(20) NULL ,

NUM_DOG VARCHAR2(20) NULL ,

DATA DATE NULL ,

ID_POD INTEGER NULL ,

ID_SOTR INTEGER NULL ,

ID_DOLJ INTEGER NULL

);

CREATE UNIQUE INDEX XPKDogovor ON Dogovor

(ID_DOG ASC);

ALTER TABLE Dogovor

ADD CONSTRAINT XPKDogovor PRIMARY KEY (ID_DOG);

CREATE TABLE Doljnost

(

ID_DOLJ INTEGER NOT NULL ,

NAZVAN CHAR (30) NULL ,

OKLAD DECIMAL (8,2) NULL

);

CREATE UNIQUE INDEX XPKDoljnost ON Doljnost

(ID_DOLJ ASC);

ALTER TABLE Doljnost

ADD CONSTRAINT XPKDoljnost PRIMARY KEY (ID_DOLJ);

CREATE TABLE History

(

ID_HIST INTEGER NOT NULL ,

ID_DOG INTEGER NULL ,

ID_OB INTEGER NULL

);

CREATE UNIQUE INDEX XPKHistory ON History

(ID_HIST ASC);

ALTER TABLE History

ADD CONSTRAINT XPKHistory PRIMARY KEY (ID_HIST);

CREATE TABLE Korpus

(

ID_KOR INTEGER NOT NULL ,

ADRESS CLOB NULL ,

ID_POM INTEGER NULL

);

CREATE UNIQUE INDEX XPKKorpus ON Korpus

(ID_KOR ASC);

ALTER TABLE Korpus

ADD CONSTRAINT XPKKorpus PRIMARY KEY (ID_KOR);

CREATE TABLE Podrazdelenie

(

ID_POD INTEGER NOT NULL ,

NAME_POD VARCHAR2(50) NULL ,

VID_POD VARCHAR2(20) NULL ,

ID_KOR INTEGER NULL

);

CREATE UNIQUE INDEX XPKPodrazdelenie ON Podrazdelenie

(ID_POD ASC);

ALTER TABLE Podrazdelenie

ADD CONSTRAINT XPKPodrazdelenie PRIMARY KEY (ID_POD);

CREATE TABLE Pomewenie

(

ID_POM INTEGER NOT NULL ,

ALL_LEVELS CHAR (30) NULL ,

ALL_SQUARE CHAR (30) NULL ,

LIFT SMALLINT NULL

);

CREATE UNIQUE INDEX XPKPomewenie ON Pomewenie

(ID_POM ASC);

ALTER TABLE Pomewenie

ADD CONSTRAINT XPKPomewenie PRIMARY KEY (ID_POM);

CREATE TABLE Sostoyanie

(

ID_SOST INTEGER NOT NULL ,

SS SMALLINT NULL

);

CREATE UNIQUE INDEX XPKSostoyanie ON Sostoyanie

(ID_SOST ASC);

ALTER TABLE Sostoyanie

ADD CONSTRAINT XPKSostoyanie PRIMARY KEY (ID_SOST);

CREATE TABLE Sotrudnik

(

ID_SOTR INTEGER NOT NULL ,

FAM VARCHAR2(30) NULL ,

IM VARCHAR2(30) NULL ,

OTCH VARCHAR2(30) NULL

);

CREATE UNIQUE INDEX XPKSotrudnik ON Sotrudnik

(ID_SOTR ASC);

ALTER TABLE Sotrudnik

ADD CONSTRAINT XPKSotrudnik PRIMARY KEY (ID_SOTR);

CREATE TABLE Vid

(

ID_VID INTEGER NOT NULL ,

STOIMOST DECIMAL (8,2) NULL ,

FIRM VARCHAR2(20) NULL ,

);

CREATE UNIQUE INDEX XPKVid ON Vid

(ID_VID ASC);

ALTER TABLE Vid

ADD CONSTRAINT XPKVid PRIMARY KEY (ID_VID);

ALTER TABLE Device

ADD (CONSTRAINT R₉ FOREIGN KEY (ID_VID) REFERENCES Vid (ID_VID) ON DELETE SET NULL);

ALTER TABLE Device

ADD (CONSTRAINT R₁₀ FOREIGN KEY (ID_SOST) REFERENCES Sostoyanie (ID_SOST) ON DELETE SET NULL);

ALTER TABLE Device

ADD (CONSTRAINT R₁₁ FOREIGN KEY (ID_DOG) REFERENCES Dogovor (ID_DOG) ON DELETE SET NULL);

ALTER TABLE Dogovor

ADD (CONSTRAINT R₄ FOREIGN KEY (ID_POD) REFERENCES Podrazdelenie (ID_POD) ON DELETE SET NULL);

ALTER TABLE Dogovor

ADD (CONSTRAINT R₆ FOREIGN KEY (ID_SOTR) REFERENCES Sotrudnik (ID_SOTR) ON DELETE SET NULL);

ALTER TABLE Dogovor

ADD (CONSTRAINT R₇ FOREIGN KEY (ID_DOLJ) REFERENCES Doljnost (ID_DOLJ) ON DELETE SET NULL);

ALTER TABLE History

ADD (CONSTRAINT R₁₂ FOREIGN KEY (ID_DOG) REFERENCES Dogovor (ID_DOG) ON DELETE SET NULL);

ALTER TABLE History

ADD (CONSTRAINT R₁₄ FOREIGN KEY (ID_OB) REFERENCES Device (ID_OB) ON DELETE SET NULL);

ALTER TABLE Korpus

ADD (CONSTRAINT R₂ FOREIGN KEY (ID_POM) REFERENCES Pomewenie (ID_POM) ON DELETE SET NULL);

ALTER TABLE Podrazdelenie

ADD (CONSTRAINT R₃ FOREIGN KEY (ID_KOR) REFERENCES Korpus (ID_KOR) ON DELETE SET NULL);

2.3.2 Последовательности и триггеры

Пример создания последовательности:

CREATESEQUENCEm_sequence

START WITH 1

INCREMENTBY 1;

Пример создания триггера:

CREATE OR REPLACE TRIGGER Channel_trigger

BEFORE INSERT

ON DNIWE. POMEWENIE

REFERENCING NEW AS NEW

FOR EACH ROW

BEGIN

SELECT m_sequence.NEXTVALINTO :NEW.ID_POM FROM dual;

END;

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

база данная модель бухгалтерия

2.3.3 Запросы

Выбрать все несписанное оборудование по материально-ответственным лицам или определенному лицу:

SELECT

SOTRUDNIK.FAM, SOTRUDNIK. IM, DOGOVOR. ID_DOG, DEVICE. ID_OB, SOSTOYANIE. SS, DEVICE.NAME_DEV

FROM

DOGOVOR

Inner Join SOTRUDNIK ON

DOGOVOR.ID_SOTR = SOTRUDNIK. ID_SOTR

Inner Join DEVICE ON

DEVICE.ID_DOG = DOGOVOR. ID_DOG

Inner Join SOSTOYANIE ON

DEVICE.ID_SOST = SOSTOYANIE. ID_SOST

WHERE

SOSTOYANIE.SS = 1

Выбрать все несписанное оборудование по подразделениям или определенному подразделению:

SELECT

DEVICE.ID_OB, SOSTOYANIE. SS, DEVICE.NAME_DEV, DOGOVOR. ID_POD, PODRAZDELENIE.NAME_POD

FROM

DOGOVOR

Inner Join DEVICE ON

DEVICE.ID_DOG = DOGOVOR. ID_DOG

Inner Join SOSTOYANIE ON

DEVICE.ID_SOST = SOSTOYANIE. ID_SOST

Inner Join PODRAZDELENIE ON

DOGOVOR.ID_POD = PODRAZDELENIE. ID_POD

WHERE

SOSTOYANIE.SS = 1

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

SELECT

DEVICE.ID_OB, SOSTOYANIE. SS, DEVICE.NAME_DEV, DOGOVOR. ID_POD, PODRAZDELENIE.NAME_POD

FROM

DOGOVOR

Inner Join DEVICE ON

DEVICE.ID_DOG = DOGOVOR. ID_DOG

Inner Join SOSTOYANIE ON

DEVICE.ID_SOST = SOSTOYANIE. ID_SOST

Inner Join PODRAZDELENIE ON

DOGOVOR.ID_POD = PODRAZDELENIE. ID_POD

WHERE

SOSTOYANIE.SS = 2

Выбрать списанное оборудование по подразделениям и материально-ответственным лицам:

SELECT

SOTRUDNIK.FAM, SOTRUDNIK. IM, DEVICE. ID_OB, SOSTOYANIE. SS, DEVICE.NAME_DEV, DOGOVOR. ID_POD, PODRAZDELENIE.NAME_POD, SOTRUDNIK. OTCH

FROM

DOGOVOR

Inner Join SOTRUDNIK ON

DOGOVOR.ID_SOTR = SOTRUDNIK. ID_SOTR

Inner Join DEVICE ON

DEVICE.ID_DOG = DOGOVOR. ID_DOG

Inner Join SOSTOYANIE ON

DEVICE.ID_SOST = SOSTOYANIE. ID_SOST

Inner Join PODRAZDELENIE ON

DOGOVOR.ID_POD = PODRAZDELENIE. ID_POD

WHERE

SOSTOYANIE.ID_SOST = 1

Выбрать материально-ответственных лиц с наибольшей суммой стоимости оборудования:

SELECT

sum (VID.STOIMOST) AS EXPR1, SOTRUDNIK. FAM, SOTRUDNIK. IM, DEVICE. ID_OB, SOSTOYANIE. SS, SOTRUDNIK. OTCH, VID. STOIMOST

FROM

DOGOVOR

Inner Join SOTRUDNIK ON

DOGOVOR.ID_SOTR = SOTRUDNIK. ID_SOTR

Inner Join DEVICE ON

DEVICE.ID_DOG = DOGOVOR. ID_DOG

Inner Join SOSTOYANIE ON

DEVICE.ID_SOST = SOSTOYANIE. ID_SOST

Inner Join PODRAZDELENIE ON

DOGOVOR.ID_POD = PODRAZDELENIE. ID_POD

Inner Join VID ON

DEVICE.ID_VID = VID. ID_VID

WHERE

DEVICE.ID_SOST = 1

GROUP BY

DOGOVOR.ID_DOG, DOGOVOR. ID_SOTR, SOTRUDNIK. ID_SOTR, SOTRUDNIK. FAM, SOTRUDNIK. IM, SOTRUDNIK. OTCH, DEVICE. ID_OB, DEVICE. ID_VID, DEVICE. ID_SOST, DEVICE. ID_DOG, SOSTOYANIE. ID_SOST, SOSTOYANIE. SS, VID. ID_VID, VID. STOIMOST

HAVING

VID.STOIMOST> 5000

Заключение

В курсовой работе по дисциплине базы данных на тему «Бухгалтерия. Учет материальных ценностей» были решены поставленные задачи, а также разработаны логическая и физическая структура СУБД, реляционная база данных, последовательности и триггеры, SQLзапросы.

Список использованной литературы

1. Мартин Грубер: «Понимание SQL», Москва, 1993 г.

2. СанжейМишра и Алан Бьюли: «Секреты OracleSQL», С-П, 2003 г.

3. Том Кайт:"Oracle для профессионалов (том 1)", Москва, С-П, Киев 2003 г.

4. Том Кайт:"Oracle для профессионалов (том 2)", Москва, С-П, Киев 2003 г.

5. «Введение в системы баз данных, 8-е издание». Дейт К.Дж., Москва, С-П, Киев 2005 г.

6. Карпова Т. С. «Базы данных: модели, разработка, реализация», — Москва 2001 г.

7. Крёнке Д. «Теория и практика построения баз данных. 8-е изд.» -СПб.: Питер, 2003.