Практическая работа 1. Проектирование базы данных
 Скачать 2.91 Mb.
|
|
PRIMARY KEY гарантирует уникальность данных, оно часто определяется для столбцов-счетчиков. Создание ограничения целостности PRIMARY KEY возможно как при создании, так и при изменении таблицы. Одним из назначений первичного ключа является обеспечение ссылочной целостности данных нескольких таблиц. Естественно, это может быть реализовано только при определении соответствующих внешних ключей в других таблицах. Пример 2. Создание таблицы Товар с ограничением первичного ключа. CREATE TABLE Товар (КодТовара INT IDENTITY(1,1) PRIMARY KEY, Название VARCHAR(50) , Цена MONEY , Тип VARCHAR(50) , Сорт VARCHAR(50) , Город VARCHAR(50) , Остаток INT ); Примечание. Прежде чем выполнять sql-код удалите ранее созданную таблицу Товар из базы данных и обновите ее. Выполните sql-код. Обновите базу данных и просмотрите созданную таблицу. Сохраните sql-запрос под именем Пример2.sql в папке ФИО_студента/Лаб4. Первичные ключи более чем одного поля Ограничение PRIMARY KEY может также быть применено для многочисленных полей, составляющих уникальную комбинацию значений. Предположим что ваш первичный ключ - это имя, и вы имеете первое имя и последнее 91 имя сохраненными в двух различных полях ( так что вы можете организовывать данные с помощью любого из них ). Очевидно, что ни первое ни последнее имя нельзя заставить быть уникальным самостоятельно, но мы можем каждую из этих двух комбинаций сделать уникальной. Мы можем применить ограничение таблицы PRIMARY KEY для пар: Пример 3. Создание таблицы Сотрудники с ограничением первичного ключа. CREATE TABLE Сотрудники ( Фамилия char (10), Имя char (10) , Город char (10), PRIMARY KEY ( Фамилия, Имя )); Выполните sql-код. Обновите базу данных и просмотрите созданную таблицу. Сохраните sql-запрос под именем Пример3.sql в папке ФИО_студента/Лаб4. Одна проблема в этом подходе та, что мы можем вынудить появление уникальности - например, введя Иванов Андрей и Иванов А. Это может ввести в заблуждение, потому что ваши служащие могут не знать кто из них кто. Обычно более надежный способ чтобы определять числовое поле которое могло бы отличать одну строку от другой, это иметь первичный ключ, и применять ограничение UNIQUE для двух имен полей. 4.6.2. Использование ограничений для исключения пустых( NULL ) указателей Вы можете использовать команду CREATE TABLE чтобы предохранить поле от разрешения в нем пустых (NULL) указателей с помощью ограничения NOT NULL. Это ограничение накладывается только для разнообразных столбцов. NULL - это специальное обозначение которое отмечает поле как пустое. NULL может быть полезен, когда имеются случаи, когда вы хотите быть от них гарантированы. Очевидно, что первичные ключи никогда не должны быть пустыми , поскольку это будет подрывать их функциональные возможности. Кроме того, такие поля как имена, требуют в большинстве случаев, определенных значений. Например, вы вероятно захотите иметь информацию о должности занимаемым каждым сотрудником в таблице Сотрудники. Если вы поместите ключевые слова NOT NULL сразу после типа данных ( включая размер ) столбца, любая попытка поместить значение NULL в это поле будет отклонена. В противном случае, SQL понимает, что NULL раз решен. Например, давайте улучшим наше определение таблицы Сотрудники, не позволяя помещать NULL значения в столбец Должность : Пример 4. Создание таблицы Сотрудники с ограничением пустых значений. CREATE TABLE Сотрудники ( Фамилия char (10), Имя char (10) , Город char (10), ……….Должность ……сhar (10) NOT NULL, PRIMARY KEY ( Фамилия, Имя )); 92 Выполните sql-код. Обновите базу данных и просмотрите созданную таблицу. Сохраните sql-запрос под именем Пример4.sql в папке ФИО_студента/Лаб4. Важно помнить, что любому столбцу с ограничением NOT NULL должно быть установлено значение в каждом предложении INSERT воздействующем на таблицу. При отсутствии NULL, SQL может не иметь значений для установки в эти столбцы, если конечно значение по умолчанию, описанное ра нее в этой главе, уже не было назначено. Если ваша система поддерживает использование ALTER TABLE чтобы добавлять новые столбцы к уже существующей таблице, вы можете вероятно помещать ограничение столбцов, типа NOT NULL, для этих новых столбцов. Однако, если вы предписываете новому столбцу значение NOT NULL, текущая таблица должна быть пустой!!!!. 4.6.2. Использование ограничений для уникальности значений В предыдущей лаб.работе мы обсудили использование уникальных индексов чтобы зас тавить поля иметь различные значения для каждой строки. Эта практика - осталась с прежних времен, когда SQL поддерживал ограничение UNIQUE. Уникальность - это свойство данных в таблице, и поэтому его более логично назвать как ограничение этих данных, а не просто как свойство логического отличия, связывающее объект данных ( индекс ). Несомненно, уникальные индексы - один из самых простых и наиболее эффективных методов предписания уникальности. По этой причине, некото рые реализации ограничения UNIQUE используют уникальные индексы; то-есть они создают индекс не сообщая вам об этом. Остается фактом, что вероятность беспорядка в базе данных достаточно мала, если вы предписываете уникальность вместе с ограничением. Уникальность как ограничение столбца Время от времени, вы хотите убедиться, что все значения введеные в столбец отличаются друг от друга. Например, первичные ключи достаточно ясно это показывают. Если вы помещаете ограничение столбца UNIQUE в поле при создании таблицы, база данных отклонит любую попытку ввода в это поле для одной из строк, значения, которое уже представлено в другой строке. Это ограничение может применяться только к полям которые были обьявлены как непустые (NOT NULL), так как не имеет смысла позволить одной строке таблицы иметь значение NULL, а затем исключать другие строки с NULL значениями как дубликаты. Имеется дальнейшее усовершенствование нашей команды создания таблицы Сотрудники : Пример 5. Создание таблицы Сотрудники с ограничением уникальности. CREATE TABLE Сотрудники ( Фамилия char (10) NOT NULL UNIQUE, Имя char (10) NOT NULL UNIQUE, Город char (10), ……….Должность ……сhar (10) NOT NULL, PRIMARY KEY ( Фамилия, Имя )); Выполните sql-код. Обновите базу данных и просмотрите созданную таблицу. Сохраните sql-запрос под именем Пример5.sql в папке ФИО_студента/Лаб4. 93 Когда вы обьявляете поля Фамилия уникальным, убедитесь, что в вашей базе данных не будет двух Ивановых или Петровых. В то же время это не так уж необходимо с функциональной точки зрения - потому что поле Имя в качестве первичного ключа, все равно обеспечит отличие этих двух строк - что проще для людей использующих данные в таблицах, чем помнить, что эти Ивановы не идентичны. Столбцы ( не первичные ключи ) чьи значения требуют уникальности, называются ключами-кандидатами или уникальными ключами. 4.6.3. Ограничение по умолчанию (DEFAULT) Столбцу может быть присвоено значение по умолчанию. Оно будет актуальным в том случае, если пользователь не введет в столбец никакого иного значения. Отдельно необходимо отметить пользу от использования значений по умолчанию при добавлении нового столбца в таблицу. Если для добавляемого столбца не разрешено хранение значений NULL и не определено значение по умолчанию, то операция добавления столбца закончится неудачей. Когда вы вставляете строку в таблицу без указания значений в ней для каждого поля, SQL должен иметь значение по умолчанию для включения его в определенное поле, или же команда будет отклонена. Наиболее общим значением по умолчанию является - NULL. Это - значение по умолчанию для любого столбца, которому не было дано ограничение NOT NULL или который имел другое назначение по умолчанию. Значение DEFAULT(ПО УМОЛЧАНИЮ) указывается в команде CREATE TABLE тем же способом что и ограничение столбца, хотя, с технической точки зрения, значение DEFAULT не ограничительного свойства - оно не ограничивает значения которые вы можете вводить, а просто определяет, что может случиться если вы не введете любое из них. Предположим, что вы работаете в г. Москва и подавляющее большинство ваших сотрудников живут в этом городе. Вы можете указать г. Москва в качестве значения поля Город, по умолчанию, для вашей таблицы Сотрудников: Пример 7. Создание таблицы Сотрудники с значением по умолчанию. CREATE TABLE Сотрудники ( Фамилия char (10) NOT NULL UNIQUE, Имя char (10) NOT NULL UNIQUE, Город char (10) DEFAULT 'Москва', ……….Должность ……сhar (10) NOT NULL, PRIMARY KEY ( Фамилия, Имя )); Выполните sql-код. Обновите базу данных и просмотрите созданную таблицу. Сохраните sql-запрос под именем Пример7.sql в папке ФИО_студента/Лаб4. Конечно, вводить значение Москва в таблицу каждый раз когда назна чается новый сотрудник, не такая уж необходимость, и можно просто пре небречь им ( не вводя его ) даже если оно должно иметь некоторое зна чение. Другой способ использовать значение по умолчанию - это использовать его как альтернативу для NULL. Так как NULL (фактически) неверен при любом сравнении, ином чем IS NULL, он может быть исключен с помощью большинства предикатов. 94 Иногда, вам нужно видеть пустые значения ваших полей не обрабатывая их каким-то определенным образом. Вы можете уста- новить значение по умолчанию, типа нуль или пробел, которые функцио нально меньше по значению чем просто не установленное значение - пус тое значение(NULL). Различие между ними и обычным NULL в том, что SQL будет обрабатывать их также как и любое другое значение. 4.6.4. Ограничение проверочное (CHECK) Данное ограничение используется для проверки допустимости данных, вводимых в конкретный столбец таблицы, т.е. ограничение CHECK обеспечивает еще один уровень защиты данных. Ограничения целостности CHECK задают диапазон возможных значений для столбца или столбцов. В основе ограничений целостности CHECK лежит использование логических выражений. Допускается применение нескольких ограничений CHECK к одному и тому же столбцу. В этом случае они будут применимы в той последовательности, в которой происходило их создание. Возможно применение одного и того же ограничения к разным столбцам и использование в логических выражениях значений других столбцов. Проверка значений полей Конечно, имеется любое число ограничений, которые можно устанавливать для данных вводимых в ваши таблицы, чтобы видеть, например, находятся ли данные в соответствующем диапазоне или правильном формате, о чем SQL естественно не может знать заранее. По этой причине, SQL обеспечивает вас ограничением CHECK, которое позволяет вам установить условие которому должно удовлетворять значение вводимое в таблицу, прежде чем оно будет принято. Ограничение CHECK состоит из ключевого слова CHECK сопровождаемого предложением предиката, который использует указанное поле. Любая попытка модифицировать или вставить значение поля которое могло бы сделать этот предикат неверным - будет отклонена. Давайте рассмотрим таблицу Продавцы. Столбец комиссионных выражается десятичным числом и поэтому может быть умножен непосредственно на сумму приобретений в результате чего будет получена сумма комиссионных(в долларах) продавца с установленым справа значком доллара( $ ) . Кто-то может использовать понятие процента, однако ведь, можно об этом и не знать. Если человек введет по ошибке 14 вместо .14 чтобы указать в процентах свои комиссионные, это будет расценено как 14.0 , что является законным десятичным значением, и будет нормально воспринято системой. Чтобы предотвратить эту ошибку, мы можем наложить ограничение столбца - CHECK чтобы убедиться, что вводимое значение меньше чем 1. Пример 8. Создание таблицы Продавцы и с проверкой значений полей CREATE TABLE Продавцы ( КодПродавца integer NOT NULL PRIMARY KEY, Фамилия char(10) NOT NULL UNIQUE, Город char(10), Комиссионные decimal CHECK (Комиссионные < 1 )); Использование - CHECK, чтобы предопределять допустимое вводимое значение 95 Мы можем также использовать ограничение CHECK чтобы защитить от ввода в поле определенных значений, и таким образом предотвратить ошибку. Например, предположим, что единствеными городами в которых мы имели ведомства сбыта являются Лондон, Барселона, Сан Хосе, и Нью Йорк. Если вам известны все продавцы работающие в каждом из этих ведомств, нет необходимости позволять ввод других значений. Если же нет, использова ние ограничения может предотвратить опечатки и другие ошибки. Пример 8. Создание таблицы Продавцы и с проверкой значений полей, чтобы предопределять допустимое вводимое значение CREATE TABLE Продавцы ( КодПродавца integer NOT NULL PRIMARY KEY, Фамилия char(10) NOT NULL UNIQUE, Город char(10) CHECK (Город IN (' Лондон ', 'Барселона', ' Сан Хосе ', ' Нью Йорк ')), Комиссионные decimal CHECK (Комиссионные < 1 )); Выполните sql-код. Обновите базу данных и просмотрите созданную таблицу. Сохраните sql-запрос под именем Пример8.sql в папке ФИО_студента/Лаб4. Конечно, если вы собираетесь сделать это, вы должны быть уверены что ваша компания не открыла уже новых других ведомств сбыта. Большинство программ баз данных поддерживают команду ALTER TABLE , которая позволяет вам изменять определение таблицы, даже когда она находится в использовании. Однако, изменение или удаление ограничений не всегда возможно для этих команд, даже там где это вроде бы поддерживается. Если вы использовали систему, которая не может удалять ограничения, вы будете должны создавать (CREATE) новую таблицу и передавать информацию из старой таблицы в нее всякий раз, когда вы хотите изменить ограничение. Конечно же Вы не захотите делать это часто, и со временем вообще перестанете это делать. Проверка условий, базирующийся на многочисленых полях Вы можете также использовать CHECK в качестве табличного ограничения. Это полезно в тех случаях, когда вы хотите включить более одного поля строки в условие. Предположим что комиссионные 0.15 и выше, будут разрешены только для продавца из Барселоны. Вы можете указать это со следующим табличным ограничением CHECK : Пример 9. Создание таблицы Продавцы и с проверкой значений полей, базирующийся на многочисленых полях CREATE TABLE Продавцы2 ( КодПродавца integer NOT NULL PRIMARY KEY, Фамилия char(10) NOT NULL UNIQUE, Город char(10) , Комиссионные decimal, CHECK (Комиссионные < 0.15 OR Город='Барселона')); Выполните sql-код. Обновите базу данных и просмотрите созданную таблицу. Сохраните sql-запрос под именем Пример9.sql в папке ФИО_студента/Лаб4. 96 Как вы можете видеть, два различных поля должны быть проверены чтобы определить, верен предикат или нет. Имейте в виду, что это - два разных поля одной и той же строки. Хотя вы можете использовать многочис леные поля, SQL не может проверить более одной строки одновременно. Вы не можете, например использовать ограничение CHECK чтобы удостовериться что все комиссионные в данном городе одинаковы. Чтобы сделать это, SQL должен всякий раз просматривая другие строки таблицы, когда вы модифицируете или вставляете строку, видеть, что значение комиссионных указано для текущего города. SQL этого делать не умеет. Фактически, вы могли бы использовать сложное ограничение CHECK для вышеупомянутого, если бы знали заранее, каковы должны быть комиссионные в разных городах. Самостоятельно измените ограничение в примере 9 на следующее: Если комиссионные равны 0.15 , то будут разрешены только для продавца из Лондана Если комиссионные равны 0.14 , то будут разрешены только для продавца из Барселоны Если комиссионные равны 0.13 , то будут разрешены только для продавца из Сан-Хосе Если комиссионные равны 0.12 , то будут разрешены только для продавца из Нью-Йорка Вы получили идею. Чем налагать такой комплекс ограничений, вы могли бы просто использовать представление с предложением WITH CHECK OPTION, которое имеет все эти условия в своем предикате. Пользователи могут обращаться к представлению таблицы вместо самой таблицы. Одним из преимуществ этого будет то, что процедура изменения в ограничении не будет такой болезненной или трудоемкой. Представление с WITH CHECK OPTION - хороший заменитель ограничению CHECK. Пример 10. Создание таблицы Клиент с ограничениями. CREATE TABLE Клиент (КодКлиента INT IDENTITY(1,1) PRIMARY KEY, Фирма VARCHAR(50) NOT NULL, Фамилия VARCHAR(50) NOT NULL, Город VARCHAR(50) NOT NULL, Телефон CHAR(10) NOT NULL CHECK (Телефон LIKE '[1-9][0-9]-[0-9][0-9]-[0-9][0-9]')); Выполните sql-код. Обновите базу данных и просмотрите созданную таблицу. Сохраните sql-запрос под именем Пример10.sql в папке ФИО_студента/Лаб4. 4.6.5. Ограничение внешнего ключа (FOREIGN KEY) Ограничение внешнего ключа - это основной механизм для поддержания ссылочной целостности между таблицами реляционной базы данных. Столбец дочерней таблицы, определенный в качестве внешнего ключа в параметре FOREIGN KEY, применяется для ссылки на столбец родительской таблицы, являющийся в ней первичным ключом. 97 Имя родительской таблицы и столбцы ее первичного ключа указываются в предложении REFERENCES. Данные в столбцах, определенных в качестве внешнего ключа, могут принимать только такие же значения, какие находятся в связанных с ним столбцах первичного ключа родительской таблицы. Совпадение имен столбцов для связи дочерней и родительской таблиц необязательно. Первичный ключ может быть определен для столбца с одним именем, в то время как столбец, на который наложено ограничение FOREIGN KEY, может иметь совершенно другое имя. Единственным требованием остается соответствие столбцов по типу и размеру данных. На первичный ключ могут ссылаться не только столбцы других таблиц, но и столбцы, расположенные в той же таблице, что и собственно первичный ключ; это позволяет создавать рекурсивные структуры. Внешний ключ может быть связан не только с первичным ключом другой таблицы. Он может быть определен и для столбцов с ограничением UNIQUE второй таблицы или любых других столбцов, но таблицы должны находиться в одной базе данных. Столбцы внешнего ключа могут содержать значение NULL, однако проверка на ограничение FOREIGN KEY игнорируется. Внешний ключ может быть проиндексирован, тогда сервер будет быстрее отыскивать нужные данные. Внешний ключ определяется как при создании, так и при изменении таблиц. Ограничение ссылочной целостности задает требование, согласно которому для каждой записи в дочерней таблице должна иметься запись в родительской таблице. При этом изменение значения столбца связи в записи родительской таблицы при наличии дочерней записи блокируется, равно как и удаление родительской записи (запрет каскадного изменения и удаления), что гарантируется параметрами ON DELETE NO ACTION и ON UPDATE NO ACTION, принятыми по умолчанию. Для разрешения каскадного воздействия следует использовать параметры ON DELETE CASCADE и ON UPDATE CASCADE. Если пользователь предпринимает попытку удалить из родительской таблицы строку, на которую ссылается одна или более строк дочерней таблицы, язык SQL предоставляет следующие возможности: CASCADE - выполняется удаление строки из родительской таблицы, сопровождающееся автоматическим удалением всех ссылающихся на нее строк дочерней таблицы; SET NULL - выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы записывается значение NULL; SET DEFAULT - выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы заносится значение, принимаемое по умолчанию; NO ACTION - операция удаления строки из родительской таблицы отменяется. Именно это значение используется по умолчанию в тех случаях, когда в описании внешнего ключа фраза ON DELETE опущена. Те же самые правила применяются в языке SQL и тогда, когда значение потенциального ключа родительской таблицы обновляется. Определитель MATCH позволяет уточнить способ обработки значения NULL во внешнем ключе. 98 При определении таблицы предложение FOREIGN KEY может указываться произвольное количество раз. В операторе CREATE TABLE используется необязательная фраза DEFAULT, которая предназначена для задания принимаемого по умолчанию значения, когда в операторе INSERT значение в данном столбце будет отсутствовать. Фраза CONSTRAINT позволяет задать имя ограничению, что позволит впоследствии отменить то или иное ограничение с помощью оператора ALTER TABLE. Пример 11. Создание таблицы Склад с ограничениями первичного ключа и внешнего ключа. CREATE TABLE Сделка (КодСделки INT IDENTITY(1,1) PRIMARY KEY, КодТовара INT NOT NULL, КодКлиента INT NOT NULL, Количество INT NOT NULL DEFAULT 0, Дата DATETIME NOT NULL DEFAULT GETDATE(), Остаток INT, CONSTRAINT fk_Товар FOREIGN KEY(КодТовара) REFERENCES Товар, CONSTRAINT fk_Клиент FOREIGN KEY(КодКлиента) REFERENCES Клиент); Выполните sql-код. Обновите базу данных и просмотрите созданную таблицу. Сохраните sql-запрос под именем Пример11.sql в папке ФИО_студента/Лаб4. 4.7. Изменение таблиц Для внесения изменений в уже созданные таблицы стандартом SQL предусмотрен оператор ALTER TABLE, предназначенный для выполнения следующих действий: добавление в таблицу нового столбца; удаление столбца из таблицы; добавление в определение таблицы нового ограничения; удаление из определения таблицы существующего ограничения; задание для столбца значения по умолчанию; отмена для столбца значения по умолчанию. Оператор изменения таблицы имеет следующий обобщенный формат: <изменение_таблицы> ::= ALTER TABLE имя_таблицы [ADD [COLUMN]имя_столбца тип_данных [ NOT NULL ][UNIQUE] [DEFAULT <значение>][ CHECK (<условие_выбора>)]] [DROP [COLUMN] имя_столбца [RESTRICT | CASCADE ]] [ADD [CONSTRAINT [имя_ограничения]] [{PRIMARY KEY (имя_столбца [,...n]) |[UNIQUE (имя_столбца [,...n])} |[FOREIGN KEY (имя_столбца_внешнего_ключа [,...n]) REFERENCES имя_род_таблицы [(имя_столбца_род_таблицы [,...n])], 99 [ MATCH {PARTIAL | FULL} [ON UPDATE {CASCADE| SET NULL | SET DEFAULT | NO ACTION}] [ON DELETE {CASCADE| SET NULL | SET DEFAULT | NO ACTION}] |[CHECK(<условие_выбора>)][,...n]}] [DROP CONSTRAINT имя_ограничения [RESTRICT | CASCADE]] [ALTER [COLUMN] SET DEFAULT <значение>] [ALTER [COLUMN] DROP DEFAULT] Здесь параметры имеют то же самое назначение, что и в определении оператора CREATE TABLE. Оператор ALTER TABLE реализован не во всех диалектах языка SQL. В некоторых диалектах он поддерживается, однако не позволяет удалять из таблицы уже существующие столбцы. В дополнение к уже названным параметрам определим параметр {ENABLE | DISABLE } TRIGGER ALL_, предписывающий задействовать или отключить конкретный триггер или все триггера, связанные с таблицей. Пример 12. Удаление ограничения внешнего ключа. ALTER TABLE Сделка DROP CONSTRAINT fk_Товар Пример 13. Добавления ограничения внешнего ключа, реализующего каскадные обновления и изменения. ALTER TABLE Сделка ADD CONSTRAINT fk_Товар FOREIGN KEY (КодТовара) REFERENCES Товар ON UPDATE CASCADE ON DELETE CASCADE Пример 14. Пример создания вычисляемого поля. ALTER TABLE Товар ADD Налог AS Цена*0.05 Пример 15. Пример удаления поля ALTER TABLE Товар DROP COLUMN Остаток 4.7. Удаление таблиц Удаление таблицы выполняется командой: DROP TABLE имя_таблицы Удалить можно любую таблицу, даже системную. К этому вопросу нужно подходить очень осторожно. Однако удалению не подлежат таблицы, если существуют объекты, ссылающиеся на них. К таким объектам относятся таблицы, связанные с удаляемой таблицей посредством внешнего ключа. Поэтому, прежде чем удалять родительскую таблицу, необходимо удалить либо ограничение внешнего ключа, либо дочерние таблицы. Если с таблицей связано хотя бы одно представление, то таблицу также удалить не удастся. Кроме того, связь с таблицей может быть установлена со стороны функций и процедур. Следовательно, перед удалением таблицы необходимо удалить все объекты базы данных, которые на нее ссылаются, либо изменить их таким образом, чтобы ссылок на удаляемую таблицу не было. Самостоятельно удалите таблицу Продавцы. 100 Задание для практической работы №4 Самостоятельно, используя команды языка SQL, в базе данных Университет создать: 1). Новую таблицу под именем STUDENT (Студент) с помощью sql-операторов с полями: STUDENT_ID – целого типа для уникальной идентификации записей в таблице первичный ключ тип счетчик, SUTNAME – текстового типа для обозначения имени студента, SUTFNAME - текстового типа для обозначения фамилии, STIPEND – действительного типа для обозначения стипендии. При этом на это поле наложено ограничение числом – величина размера стипендии должна быть меньше 500 грн. KURS - целого типа для обозначения курса. При этом на это поле наложено ограничение – курс на котором может учиться студент может принимать значение от 1 до 5 , CITY - текстового типа для обозначения города, BIRTDAY –типа даты/времени для обозначения день рождения, GROUP - текстового типа для обозначения студенческой группы, KOD_KAFEDRU – целого типа для обозначения названия кафедры, на которой учится студент. Поле KOD_KAFEDRU из таблицы STUDENT и поле KOD_KAFEDRU из таблицы KAFEDRA связаны тем, что описывают одни и те же данные, т.е. содержат идентификаторы кафедр, информация о которых содержит база данных. Более того, значение идентификаторов кафедр, которые допустимы в таблице STUDENT, должны выбираться только из списка значений поля KOD_KAFEDRU, т.е. принадлежащих реально описанных в базе данных кафедрам. Т.е. между этими полями имеется прямая связь. Т.о. поле KOD_KAFEDRU из таблицы STUDENT будет являться внешним ключом. Кроме того, при определении таблицы STUDENT запрещено использовать значение NULL – значений для столбцов STUDENT_ID, SUTNAME, SUTFNAME. В качестве первичного ключа принято значение столбца STUDENT_ID. Выполните sql-код. Обновите базу данных и просмотрите созданную таблицу. Сохраните sql-запрос под именем Студент.sql в папке ФИО_студента/Лаб4. 2). Новую таблицу под именем TEACHER (Преподаватели) с помощью sql- операторов. Эта таблица содержит информацию о преподавателях вуза. Каждый преподаватель может работать на одной кафедре, иметь множество лекционных занятий и быть куратором более чем одной группы. Описание столбцов таблицы TEACHER KOD_TEACHER Уникальный идентификатор преподавателя. Является первичным ключом KOD_KAFEDRU Уникальный идентификатор кафедры, на которой работает преподаватель. Является внешним ключом NAME_TEACHER Фамилия преподавателя INDEF_KOD Идентификационный код. Является уникальным для преподавателя DOLGNOST Должность, может принимать только определенные значения из списка, такие как 'профессор', 'доцент', 'старший преподаватель', 'ассистент'. Значение по умолчанию 'ассистент'. ZVANIE Научное звание, может принимать только определенные 101 значения из списка, такие как 'к.т.н', 'к.г.у', 'к.с.н', 'к.ф.м.н.', 'д.т.н', 'д.г.у', 'д.с.н', 'д.ф.м.н', 'нет'. Значение по умолчанию 'нет'. SALARY ставка зарплаты . Значение по умолчанию 1000 грн. Зарплата должна быть больше нуля. RISE надбавка к зарплате. Ее значение по умолчанию =0 и не может быть отрицательным числом. DATA_HIRE дата приема на работу. По умолчанию текущая дата. BIRTHDAY день рождения POL пол, может принимать только определенные значения из списка, 'ж', 'Ж', 'м', 'М' TEL_TEACHER Телефон. Может принимать значения только в виде '[1-9][0-9]- [0-9][0-9]-[0-9][0-9]'. В качестве первичного ключа принято значение столбца KOD_TEACHER. Поле KOD_KAFEDRU из таблицы TEACHER и поле KOD_KAFEDRU из таблицы KAFEDRA связаны тем, что описывают одни и те же данные, т.е. содержат идентификаторы кафедр, информация о которых содержит база данных. Более того, значение идентификаторов кафедр, которые допустимы в таблице TEACHER, должны выбираться только из списка значений поля KOD_KAFEDRU, т.е. принадлежащих реально описанных в базе данных кафедрам. Т.е. между этими полями имеется прямая связь. Т.о. поле KOD_KAFEDRU из таблицы TEACHER будет являться внешним ключом. Выполните sql-код. Обновите базу данных и просмотрите созданную таблицу. Сохраните sql-запрос под именем Преподаватель.sql в папке ФИО_студента/Лаб4. САМОСТОЯТЕЛЬНО используя команды языка SQL!!!: Создать на языке Transact-SQL файл базы данных согласно номеру варианта (присвоить ей новое имя, несовпадающие с именем базы данных созданной в лаб.№3). База данных разрабатывается на основе спроектированной концептуальной модели данных в лаб.№1. Создать программно на языке SQL все таблицы, с указанием первичных и внешних ключей и ограничения целостности. Все программные инструкции команд SQL сохранять в файлах с расширением *.sql в папке ФИО_студента/Лаб4. Заполнить таблицы данными по 5 записей в каждой. Создать текстовый отчет, в котором отобразить sql-команды разработанных запросов и скриншоты результатов работы из СУБД SQL Server Management Studio. Самостоятельно заполните вручную данными таблицы Студент и Преподаватель согласно рис. 1-2, приведенным ниже. Также ранее должны были введены следующие данные: 102 Новые данные ввести вручную. 103 104 ПРАКТИЧЕСКАЯ РАБОТА №5. СОЗДАНИЕ ЗАПРОСОВ НА ВЫБОРКУ. ОТБОР СТРОК ПО УСЛОВИЮ 5.1. Цель практической работы Изучить используемый в реляционных СУБД оператор извлечения данных из таблиц. Получить навыки работы с оператором SELECT в программе ‘SQL Server Managmant Studio’. 5.2. Исходные данные Исходными данными является индивидуальное задание и результат предыдущих практических работ. 5.3. Используемые программы Программы ‘ SQL Server Managmant Studio ‘. 5.4. Теоретические сведения В SQL имеется единственный оператор, который предназначен для выборки данных из базы данных. Оператор относится к подмножеству DML. Ниже приведен почти полный синтаксис оператора SELECT. SELECT [DISTINCT | ALL] {* | <величина> [, <величина> ...]} [INTO :Переменная [, :Переменная ...]] FROM [WHERE <условие поиска>] [GROUP BY Колонка [, Колонка ...]] [HAVING <условие поиска>] [UNION [ALL] [ORDER BY <список сортировки>]; <величина> = {Колонка | :Переменная | <константа> | <выражение> | <функция> | udf ([<величина> [, <величина> ...]]) | NULL | USER} [AS Псевдоним] <константа> = Число | 'Строка' <выражение> = SQL выражение, возвращающее единичное значение <функция> = COUNT (* | [ALL] <величина> | DISTINCT <величина>) | SUM ([ALL] <величина> | DISTINCT <величина>) | AVG ([ALL] <величина> | DISTINCT <величина>) | MAX ([ALL] <величина> | DISTINCT <величина>) | MIN ([ALL] <величина> | DISTINCT <величина>) | CAST(<величина> AS <тип данных>) | UPPER (<величина>) | GEN_ID (Имя_Генератора, <величина>) | procedure[(<величина> [, <величина> ...])]} 105 [Псевдоним] ON <условие поиска> | ( <условие поиска> = <величина> <оператор сравнения> {<величина> | ( | <величина> [NOT] BETWEEN <величина> AND <величина> | <величина> [NOT] LIKE <величина> | <величина> [NOT] IN (<величина> [, <величина> ...] | | <величина> IS [NOT] NULL | <величина> {>= | <=} <величина> | <величина> [NOT] {= | < | >} <величина> | {ALL | SOME | ANY} ( | EXISTS ( | SINGULAR ( | <величина> [NOT] CONTAINING <величина> | <величина> [NOT] STARTING [WITH] <величина> | (<условие поиска>) | NOT <условие поиска> | <условие поиска>OR <условие поиска> | <условие поиска>AND <условие поиска> <оператор сравнения> = {= | < | > | <= | >= | !< | !> | <> | !=} <список сортировки> = {Колонка | Номер} [ASC | DESC] [, <список сортировки> ...] Некоторые параметры, входящие в этот оператор, описаны в табл. 5.1. 106 Таблица 5.1 Описание параметров оператора SELECT Параметр Описание DISTINCT | ALL DISTINCT – предотвращает дублирование данных, которые будут извлечены. ALL (по умолчанию) – приведет к извлечению всех данных {* | <величина> [, <величина> ...]} Звездочка (*) означает, что надо извлекать все колонки из указанных таблиц. <величина> [, <величина> ...] – извлекает список указанных колонок, переменных или выражений INTO :Переменная [, :Переменная ...] Используется только в триггерах и хранимых процедурах для операторов SELECT, возвращающих не более одной строки. Указывается список переменных, в которые извлекаются величины FROM Указывает список таблиц, просмотров и хранимых процедур, из которых извлекаются данные. Список может включать соединения и соединения могут быть вложенными table Имя существующей в базе данных таблицы view Имя существующего базе данных просмотра procedure Имя существующей хранимой процедуры, предназначенной для использования в операторе SELECT Псевдоним Короткое альтернативное имя для таблицы, просмотра или колонки. После описания в join_type Задает тип соединения, которое может быть внутренним или внешним WHERE <условие поиска> Указывает условие, которое ограничивает количество извлекаемых строк GROUP BY Колонка [, Колонка ...] Разбивает результат запроса на группы, содержащие все строки с идентичными значениями указанными в списке колонок HAVING <условие поиска> Использует совместно с GROUP BY. Задает условие, которое ограничивает количество возвращаемых групп UNION [ALL] Объединяет результаты нескольких запросов. Все запросы должны извлекать одинаковое количество столбцов, тип данных каждого столба первого запроса должен совпадать с типом данных других запросов, имена столбцов в разных запросах мо- гут отличаться. Необязательный параметр ALL указывает, что надо выводить одинаковые строки ORDER BY <список сортировки> Указывает колонки, по которым будет производиться сортировка извлекаемых строк. Можно указывать либо имена коло- нок, либо их порядковые номера в списке извлекаемых колонок. Если указать ASC, то строки будут выдаваться в порядке возрастания значений сортируемых полей, если DESC – в порядке убывания значений Как видно из синтаксиса оператора SELECT, обязательными являются только предложение SELECT с перечнем выдаваемых колонок и предложение FROM. Пример простейшего оператора SELECT: -- Выдать перечень всех служащих: SELECT * FROM Employee; 107 Ниже приведено несколько упрощенных вариантов синтаксиса оператора SELECT, помогающих научиться составлять простые запросы. Упрощенный синтаксис внутреннего соединения (стандарт SQL-92): SELECT Колонка [, Колонка ...] | * FROM [ON <условие поиска>] [WHERE <условие поиска>]; Упрощенный синтаксис внешнего соединения: SELECT Колонка [, Колонка ...] | * FROM {LEFT | RIGHT | FULL} [OUTER] JOIN [ON <условие поиска>] [WHERE <условие поиска>]; Упрощенный синтаксис использования подзапроса: SELECT [DISTINCT] Колонка [, Колонка ...] FROM WHERE {expression {[NOT] IN | <оператор сравнения>} | [NOT] EXISTS } (SELECT [DISTINCT] Колонка [, Колонка ...] FROM WHERE <условие поиска> ); 5.5. Задание Практическую работу следует выполнять в следующем порядке: 1. Изучить синтаксис оператора SELECT и примеры запросов к учебной базе данных ‘University.mdf’. 2. Выполнить в окне ‘SQL Editor’ 27 запроса к базе данных, согласно приведенным в практической работе образцам выполнения запросов и сохранять каждый под именами ‘Lab5-k.sql’, где k – номер запроса по порядку, в своей рабочей папке. Каждый запрос должен иметь комментарии с описанием, а файл в целом должен иметь комментарии со сведениями об авторе и дате создания. 5.6. Ход работы Примечание. У вас должны быть перед выполнением этой практической работы созданы все таблицы базы данных университета, созданы ключи, а также заполнены данными. Выполнение sql-запросов Для выполнения запросов SELECT в программе ‘SQL Server Managmant Studio’ необходимо выполнить следующие действия: 1. Подключиться к базе данных и выполнить команду ‘Создать запрос’. В результате откроется окно ‘Конструктора запросов’ (рис. 1). 108 TP PT Рис. 1. Окно выполнения запросов 2. Ввести текст запроса согласно рис.1. 3. Нажать на панели инструментов кнопку [Выполнить] . 4. Если запрос правильный, то в результате произойдет его выполнение и результат будет отображен на вкладке ‘Результаты’ (рис. 2). Рис. 2. Окно с результатом выполнения запроса 5. Количество извлеченных в результате выполнения запроса строк отображается над сеткой с данными справа. На рис.2 там содержится строка ‘3 строк’. В данном примере извлечено столько строк, сколько требуется, чтобы заполнить сетку (в ней помещается только 3 строки) * . 6. Чтобы узнать, сколько всего строк соответствуют выполненному оператору, надо перейти в конец отображаемого набора данных. Чтобы выполнить другой запрос, надо вернуться на вкладку ‘Редактора’, создать новый запрос и повторить те же действия. К тексту ранее выполнявшихся правильных запросов можно вернуться, если перейти на вкладку ‘История’. |