бд. метод указ Проектирование БД. Методическое указания для выполнение лабораторных работ по дисциплине
 Скачать 0.74 Mb.
|
|
USE lab_study; Создание таблицы Кафедры: CREATE TABLE Chair ( Chair_ID int PRIMARY KEY, Chair_NAME varchar(20) NOT NULL, Chair_PHONE varchar (10) , Chair_CHIEF varchar (15)) ; Создание таблицы Преподаватели: CREATE TABLE Teacher ( Teach_ID bigint not null PRIMARY KEY , Teach_FAM varchar (20) NOT NULL, Teach_IMYA varchar (10), Teach_OTCH varchar (15), Teach_POSITION varchar (18), Teach_STEPEN varchar (12), Chair_ID int NOT NULL) ; Создание таблицы Группы: CREATE TABLE Grup ( Grup_ID int identity (1,1) PRIMARY KEY, Grup_NAME varchar (9) NOT NULL, Grup_COURSE int NOT NULL ) ; Так как слово GROUP является зарезервированным (часть конструкции GROUP BY), чтобы использовать его в качестве названия, необходимо всегда брать его в квадратные скобки [ ] либо не использовать это служебное слово в названиях элементов БД. Создание таблицы Студенты (здесь приведены варианты описания ключей): CREATE TABLE Students ( Stud_ID bigint, Stud_FAM char(20) NOT NULL, Stud_IMYA char(10), Stud_OTCH char(15), Stud_DATE datetime, Stud_ADDRESS char(25), Group_ID int NOT NULL REFERENCES [Grup] (Grup_ID ), Stud_STAR bigint, CONSTRAINT FK_students PRIMARY KEY (Stud_ID), CONSTRAINT FK_students_students FOREIGN KEY (Stud_STAR) REFERENCES Students (Stud_ID) ); Создание таблицы Предметы: CREATE TABLE Subject ( Subj_ID int PRIMARY KEY , Subj_NAME varchar(20) NOT NULL, Total_Hours int , Lection_Hours int, Practice_Hours int, Labor_Hours int ) ; Создание таблицы Учебный план: CREATE TABLE Study ( Grup_ID int NOT NULL REFERENCES Grup (Grup_ID ) ON DELETE CASCADE, Subj_ID int NOT NULL REFERENCES Subject (Subj_ID) , Teach_ID bigint NOT NULL REFERENCES Teacher (Teach_ID), Kredit_count int, Lesson_Hours int not null, CONSTRAINT PK_Study PRIMARY KEY (Grup_ID, Subj_ID, Teach_ID, Kredit_count) ) ; СозданиетаблицыУспеваемость: CREATE TABLE Progress ( Stud_ID bigint not null FOREIGN KEY REFERENCES Students (Stud_ID), Grup_ID int not null, Subj_ID int not null, Teach_ID bigint not null, Kredit_count int, Pr_DATE datetime null, OCENKA integer CHECK ( OCENKA in (0,1,2,3,4,5,6,7,8,9)) DEFAULT(0), CONSTRAINT FK_Progress_Study FOREIGN KEY (Grup_ID, Subj_ID, Teach_ID, Kredit_count) REFERENCES Study (Grup_ID, Subj_ID, Teach_ID, Kredit_count ), CONSTRAINT PK_Progress PRIMARY KEY (Stud_ID, Grup_ID, Subj_ID, Teach_ID, Kredit_count ) ); 3.2.3 Изменение структуры таблиц Структуру таблиц можно изменять командой ALTER TABLE. Добавление полей. Добавьте в таблицу Students поле, где будет храниться информация о стипендии студентов: ALTER TABLE Chair ADD Chair_Cab char(10) not null; Удаление полей из таблицы. Удалите введенное поле из таблицы: ALTER TABLE Chair DROP COLUMN Chair_Cab ; Добавление ограничений. Если в таблице не были определенны первичные или внешние ключи, это также можно исправить с помощью ALTER TABLE. Предположим, что в таблице Chair не был определен первичный ключ: ALTER TABLE Chair ADD CONSTRAINT PK_Chair PRIMARY KEY (Chair_ID) В таблице Teacher (Преподаватели) не был описан один из внешних ключей, добавим его: ALTER TABLE Teacher ADD CONSTRAINT PK_Teacher_Chair FOREIGN KEY (Chair_ID) REFERENCES Chair (Chair_ID) ; 3.2.4 Удаление таблиц Для удаления таблиц предназначена команда DROP. Например DROP Teacher Будьте осторожны с командой DROP! Контрольные вопросы 1. Что означает аббревиатура SQL? 2. Каковы главные отличия технологии клиент/сервер от технологии, использующей мэйнфрейм? 3. В рамках технологии клиент/сервер персональный компьютер является клиентом или сервером? 4. Какие типы данных допустимы при создании таблицы? 5. Как выполнить создание таблицы средствами меню программы MS SQL Server 2008? 6. Как выполнить создание таблицы средствами языка SQL? 7. Каким образом выполнить простейшие операции вставки строк данных в таблицу средствами SQL? 8. Каким образом выполнить простейшие операции модификации строк таблицы средствами SQL? Лабораторная работа № 3 Манипуляция над данными 1 Цель работы: а) научить студентов заполнять базу данных скриптом и графической средой СУБД MS SQL Server 2008; б) научить студентов удалять строки из таблицы базы данных скриптом в среде СУБД MS SQL Server 2008; в) научить студентовизмененять значения поля скриптом. 2 Задание на лабораторную работу 1. Заполните данными все таблицы вашей базы данных (команда INSERT). Придерживайтесь следующих правил: а) первыми заполняются таблицы, имеющие наименьшее количество связей (справочники); б) соблюдайте правило категорной целостности: никакой ключевой атрибут строки не может быть пустым; в) соблюдайте правило целостности на уровне ссылок: значение каждого внешнего ключа должно быть либо пустым, либо равным одному из текущих значений ключа другой таблицы. 2. Добавьте в таблицу Students поле Stud_STIP, в котором будет храниться информация о стипендии студентов (ALTER TABLE) 3. Заполните поле Stud_STIP таблицы Students различными данными, учитывая (используйте команду UPDATE). 4. Выполните по своему усмотрению команды по изменению данных в таблицах базы данных. 3 Методические указания к выполнению лабораторной работы 3.1 Команды манипуляции данными Добавление новых строк в таблицу. Синтаксис INSERT [INTO] table_name [ ( column_list ) ] { VALUES ( { DEFAULT | NULL | expression } [ ,...n] ) | derived_table } Замечание: аргументы и ограничения рассматриваются в справке оператора INSERT Примечания: чтобы заменить данные в таблице, необходимо использовать инструкцию DELETE для очистки существующих данных перед загрузкой новых данных с помощью INSERT. Чтобы изменить значения столбца в существующей строке воспользуйтесь инструкцией UPDATE; если вставка column_list пропущена, список вставки столбцов, в котором указываются все столбцы таблицы в возрастающем порядке в соответствии с их порядковыми номе-рами, становится неявным; в column_list столбец таблицы можно определить только один раз. Если в column_list столбец отсутствует, SQL Server должен предоставить значение на основании определения столбца; в противном случае строку загрузить не удастся. SQL Server автоматически предоставляет значение для столбца в столбце: • имеется свойство IDENTITY. Используется следующее значение приращения для идентификатора; • имеется стандартное значение. Используется стандартное значение для столбца; • неопределенное значение. Используется значение Null. При вставке явных значений в столбец идентификаторов необходимо использовать список столбцов и список VALUES. Если в списке VALUES значения не расположены аналогично столбцам таблицы или отсутствуют значения для каждого столбца таблицы, необходимо использовать column_list для явного указания столбца, в котором хранится каждое входящее значение. Если для указания значения столбца используется значение DEFAULT, то для этого столбца вставляется стандартное значение. Если стандартного значения для столбца не существует и в столбце могут быть значения Null, то вставляется значение NULL. Значение DEFAULT недопустимо для столбца идентификаторов. При вставке строк применяются следующие правила: • если значение загружается в столбец с типом данных char, varchar или varbinary, добавление или усечение замыкающих пробелов (пробелов для char и varchar, нулей для varbinary) определяется в соответствии с приведенной ниже таблицей;
• если инструкция INSERT нарушает константу или правило, либо в ней присутствует значение, несовместимое с типом данных столбца, при выполнении инструкции происходит сбой и отображается сообщение об ошибке; • если INSERT загружает несколько строк с помощью SELECT, любое нарушение правила или ограничения, возникающее в результате загрузки значений, приводит к остановке полной инструкции и строки не загружаются. Примерыкода: INSERT INTO Chair (Chair_ID,Chair_NAME,Chair_PHONE, Chair_CHIEF) VALUES (50763,'Инж.Кибернетики','123456','Хисаров Б.’); Создание записи «преподаватели»: INSERT INTO Teacher (Teach_FAM, Teach_IMYA, Teach_OTCH, Teach_POSITION, Teach_STEPEN, Chair_ID,Teach_ID) VALUES ('Ахметова', 'Галия ', 'Сериковна', ' ', ' ',50763,77512); 3.2 Удаление строк из таблиц. Это еще одна операция, которую необходимо уметь выполнять для поддержки базы данных. Для удаления строк из таблицы используется команда DELETE. Синтаксис DELETE [ FROM ] table_name [ WHERE < search_condition > ] Замечание: аргументы и ограничения рассматриваются в справке оператора DELETE Примечания: если предложение WHERE не указано, DELETE удаляет все строки из таблицы; если указаны условия поиска, они применяется к каждой строке таблицы. Все строки, результат условий поиска для которых — TRUE, помечаются для удаления; до проведения удалений для каждой строки в таблице проводится оценка условий поиска; все строки, помеченные для удаления, удаляются в конце выполнения инструкции DELETE перед проверкой ограничения целостности; сбой инструкции DELETE может произойти, если она нарушит ограничение FOREIGN KEY. Если инструкция DELETE удаляет несколько строк и одна из них нарушает ограничение, происходит отмена инструкции и возврат ошибки, удаления строк не происходит. Пример кода В следующем примере показано удаление всех строк из таблицы «Преподаватели»: DELETE FROM Teacher В следующем примере показано удаление всех строк из таблицы «Преподаватели», для которых «Должность» равно «Ассистент»: DELETE FROM Teacher WHERE (Teach_Position= 'Ассистент'); В отличие от файловых СУБД, SQL Server не помечает записи как удаленные, а удаляет их физически, то есть восстановлению они не подлежат. Будьте осторожны с командой DELETE! 3.3 Изменение значения поля Для изменения существующих значений в столбцах таблицы используется инструкция UPDATE. Синтаксис UPDATE table_name [ WITH ( < table_hint > ) ] SET { column_name = { expression | DEFAULT | NULL }} [ ,...n ] [ WHERE < search_condition > ] Замечание: аргументы и ограничения рассматриваются в справке оператора UPDATE Примечания • столбцы идентификаторов обновлять нельзя; • если не указано предложение WHERE, обновляются все строки в таблице; •перед обновлением строк в предложении WHERE проводится оценка условий поиска для каждой строки в таблице; • если обновление строки нарушает ограничение или правило, либо настройку значения NULL для столбца, а также если новое значение является несовместимым типом данных, происходит отмена инструкции и возврат ошибки без обновления записей; • все столбцы char имеют определенную границу по правому краю, до которой их можно заполнять; • из данных, добавленных в varchar, удаляются все замыкающие пробелы, за исключением строк, содержащих только пробелы. Такие строки усекаются до одной пустой строки. Пример В следующем примере показано обновление таблицы преподавателей путем установления ученой степени преподавателю с порядковым номером 2: UPDATE Teacher SET Teach_Stepen = 'Профессор' WHERE Teach_ID = 2; Контрольные вопросы 1. Какие команды манипуляции данными вы знаете? 2. Дайте определение целостности данных. 3. Сформулируйте правило целостности на уровне ссылок. 4. Что означает определение поля NOT NULL? 5. Если поле определено как NULL, значит ли это, что в это поле обязательно должно быть что-нибудь введено? 6. Что означает определение поля identity? Лабораторная работа № 4. Простые запросы к базе данных. 1 Цель работы: научить студентов создавать простые запросы к базе данных скриптом и в графической среде СУБД MS SQL Server 2008. 2 Задание на лабораторную работу Перед выполнением задания рекомендуется проработать все примеры, приведенные в п.п. 3.1 – 3.6. Выполните поиск информации в отдельных таблицах: 1. Список преподавателей с указанием их должностей в алфавитном порядке. 2. Названия кафедр с фамилиями заведующих. 3. Список студентов с различными фамилиями, обучающихся в первой группе (предполагается, что в этой группе есть однофамильцы). 4. Список студентов, у которых стипендия больше 2000. 5. Список студентов, проживающих в Астане и Караганде. 6. Список студентов второй группы, у которых нет стипендии. 7. Список студентов третьей группы, фамилии которых начинаются на букву «А». 8. Список студентов, которые родились в 1988 году. 9. Посчитайте суммарную стипендию студентов третьей группы. 10. Посчитайте среднее число лекционных часов по всем предметам. 11. Сколько студентов введено в базу данных? 12. Выведите всю информацию о предметах. 13. Список студентов, которые не проживают в Алматы. 14. Список студентов, чьи дни рождения в мае. 15. Номера студентов с минимальной оценкой из ведомости успеваемости. 16. Номера студентов с максимальной оценкой из ведомости успеваемости. |