Александрова Наталья

Методическая разработка урока "Лекция-провокация в игровой форме" по предмету "Основы проектирования баз данных" Тема: «Создание, редактирование и удаление таблиц БД с помощью операторов языка SQL»

Материал опубликован 25 October

Методическая разработка

Урока "Лекция-провокация в игровой форме"

по дисциплине "Основы проектирования баз данных"

Тема: «Создание, редактирование и удаление таблиц БД с помощью операторов языка SQL»

Содержание

1.	Пояснительная записка	3
2.	План проведения открытого урока	4
3.	Технологическая карта занятия	4
4.	Проведение открытого урока	5
5.	Приложения Приложение А. Содержание лекции с ошибками Приложение Б. Рабочий лист студента Приложение В. Сводная таблица для подведения итогов	6 16 17

ПОЯСНИТЕЛЬНАЯ ЗАПИСКА

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

В начале лекции преподаватель сообщает, что в ней будет сделано определённое количество ошибок и задача студентов найти их и дать правильные варианты ответов. На лекцию, запланированную на 45 минут, приходится 13 ошибок.

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

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

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

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

2. ПЛАН ПРОВЕДЕНИЯ открытого урока

Вступительное слово преподавателя.

Разделение группы на две команды и выдача листов для отметки ошибок

Чтение лекции преподавателем

Студенты внимательно слушают лекцию и отмечают допущенные по их мнению ошибки в своих рабочих листах.

Обсуждение каждой командой найденных ошибок и выбор ответственного, который будет отвечать.

Проверка найденных ошибок

Подведение итогов. Заключительное слово преподавателя.

ТЕХНОЛОГИЧЕСКАЯ КАРТА ЗАНЯТИЯ

Дисциплина: ОП.08 "Основы проектирования баз данных"

Тема: «Создание, редактирование и удаление таблиц БД с помощью операторов языка SQL»

Специальность: 09.02.07. «Информационные системы и программирование»

Группа: ИС-3-1

Форма урока: лекция с заранее запланированными ошибками

Метод обучения: интерактивный

Цели деятельности преподавателя:

Обучающие:

обобщить знания по теме «Создание, редактирование и удаление таблиц БД с помощью операторов языка SQL»

Развивающие:

развитие умений слушать, наблюдать, анализировать и обобщать полученную информацию.

Воспитательные:

способствовать расширению общего кругозора студентов;

воспитывать чувство значимости предмета в профессиональной деятельности.

социализация личности, через совместную работу;

Цели деятельности студентов:

- систематизировать и закрепить знания по данной теме;

- воспитать интерес к выбранной профессии.

Оснащение: проектор, ПК, экран.

Структура занятия:

1. Организационный момент – 5 мин.

2. Сообщение темы и цели занятия – 5 мин.

3. Разделение группы на 2 команды - 5 мин.

4. Чтение лекции преподавателем- 45 мин.

5. Обсуждение найденных ошибок каждой командой - 10 мин.

6. Совместное обсуждение найденных ошибок и правильных ответов группами с преподавателем - 15 мин

7. Подведение итогов занятия – 5 мин.

Итого: 1,5 часа.

Ход занятия

Этапы занятия	Содержание	Приме- чание
1	Организационный момент – приветствие студентов, проверка отсутствующих.
2	Сообщение темы и цели занятия – тема «Закрепление свойств CSS», обобщить знания по данной теме
3	Разделение группы на 2 команды и пересаживание студентов таким образом, чтобы им было удобно потом работать совместно. Выдача рабочих листов каждому студенту.
4	Чтение лекции преподавателем. Лекция читается с использованием проектора и доски. Студенты в это время внимательно слушают и отмечают найденные по их мнению ошибки в своих рабочих листах.
5	Каждая команда совместно обсуждает найденные каждым студентом ошибки, и сводит их в один общий список. Затем выделяют студента, который будет отвечать от имени команды.
6	Преподаватель призывает студентов к совместному обсуждению найденных ошибок и поиску правильных ответов. От каждой группы отвечает выбранный студент, преподаватель заслушивает поочередно каждую команду, а затем отмечает правильные ответы в сводном листе, отображаемом на экране проектора.
7.	Подведение итогов - определяется лидирующая команда, делаются выводы, выставляются оценки.

Приложение А

Создание и удаление таблиц

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

Общий синтаксис создания таблицы выглядит следующим образом:

CREATE TABLE название_таблицы

(название_столбца1 тип_данных атрибуты_столбца1,

название_столбца2 тип_данных атрибуты_столбца2,

................................................

название_столбцаNтип_данныхатрибуты_столбцаN,

атрибуты_таблицы

)

После команды CREATE TABLE идет название создаваемой таблицы. Имя таблицы выполняет роль ее идентификатора в базе данных, поэтому оно должно быть уникальным. Имя должно иметь длину не больше 128 символов. Имя может состоять из алфавитно-цифровых символов, а также символов $ и знака подчеркивания (но НЕ МОЖЕТ содержать знака подчеркивания). Причем первым символом должна быть буква или знак подчеркивания . (или знак $ )

Имя объекта не может включать пробелы и не может представлять одно из ключевых слов языка Transact-SQL. Если идентификатор все же содержит пробельные символы, то его следует заключать в кавычки. Если необходимо в качестве имени использовать ключевые слова, то эти слова помещаются в квадратные скобки.

Примеры корректных идентификаторов:

_Users (НЕ КОРРЕКТНО)

tags$345

users_accounts

"users accounts"

[Table]

Например, определение простейшей таблицы Customers:

CREATE TABLE Customers

(

Id INT,

Age INT,

FirstName NVARCHAR(20),

LastName NVARCHAR(20),

Email VARCHAR(30),

Phone VARCHAR(20))

Удаление таблиц

Для удаления таблиц используется команда DROP TABLE, которая имеет следующий синтаксис:

DROPTABLEtable1 [, table2, ...]

Например, удаление таблицы Customers:

DROPTABLECustomers

Переименование таблицы

Для переименования таблиц применяется системная хранимая процедура "sp_rename". Например, переименование таблицы Users в UserAccounts в базе данных usersdb:

USE usersdb;

EXECsp_rename 'Users', 'UserAccounts';

Атрибуты и ограничения столбцов и таблиц

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

PRIMARY KEY

С помощью выражения PRIMARY KEY столбец можно сделать первичным ключом.

CREATE TABLE Customers

(

Id INT PRIMARY KEY,

Age INT,

FirstName NVARCHAR(20),

LastName NVARCHAR(20),

Email VARCHAR(30),

Phone VARCHAR(20)

)

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

Установка первичного ключа на уровне таблицы:

CREATE TABLE Customers

(

Id INT,

Age INT,

FirstName NVARCHAR(20),

LastNameNVARCHAR(20),

Email VARCHAR(30),

Phone VARCHAR(20),

PRIMARYKEY – не указан ключевой столбец, правильно PRIMARYKEY(Id)

)

Первичный ключ может быть составным (compoundkey). Такой ключ может потребоваться, если у нас сразу два столбца должны уникально идентифицировать строку в таблице. Например:

CREATE TABLE OrderLines

(

OrderId INT,

ProductId INT,

Quantity INT,

Price MONEY,

PRIMARYKEY(OrderId, ProductId)

)

Здесь поля OrderId и ProductId вместе выступают как составной первичный ключ. То есть в таблице OrderLines не может быть двух строк, где для обоих из этих полей одновременно были бы одни и те же значения.

IDENTITY

Атрибут IDENTITY позволяет сделать столбец идентификатором. Этот атрибут может назначаться для столбцов числовых типов INT, SMALLINT, BIGINT, TYNIINT, DECIMAL и NUMERIC. При добавлении новых данных в таблицу SQL Server будет инкрементировать на единицу значение этого столбца у последней записи. Как правило, в роли идентификатора выступает тот же столбец, который является первичным ключом, хотя в принципе это необязательно.

CREATE TABLE Customers

(

Id INT PRIMARY KEY IDENTITY,

Age INT,

FirstName NVARCHAR(20),

LastName NVARCHAR(20),

Email VARCHAR(30),

Phone VARCHAR(20)

)

Также можно использовать полную форму атрибута:

IDENTITY(seed, increment)

Здесь параметр seed указывает на начальное значение, с которого будет начинаться отсчет. А параметр increment определяет, насколько будет увеличиваться следующее значение. По умолчанию атрибут использует следующие значения:

Здесь параметр seed указывает насколько будет увеличиваться следующее значение. А параметр increment определяет, сколько раз это значение будет изменено. По умолчанию атрибут использует следующие значения:

IDENTITY(1, 1)

То есть последующее значение будет увеличиваться 1. И произойдет это 1 раз.

То есть отсчет начинается с 1. А последующие значения увеличиваются на единицу.

Но мы можем это поведение переопределить.

Например:

Id INTIDENTITY (2, 3)

В данном случае отсчет начнется с 2, а значение каждой последующей записи будет увеличиваться на 3. То есть первая строка будет иметь значение 2, вторая - 5, третья - 8 и т.д.

Id INTIDENTITY (1, 3)

То есть последующее значение будет увеличиваться 1. И произойдет это 3 раза.

Также следует учитывать, что в таблице только один столбец должен иметь такой атрибут.

Также следует учитывать, что в таблице такой атрибут могут иметь несколько столбцов.

UNIQUE

Если мы хотим, чтобы столбец имел только уникальные значения, то для него можно определить атрибут UNIQUE.

CREATE TABLE Customers

(

Id INT PRIMARY KEY IDENTITY,

Age INT,

FirstName NVARCHAR(20),

LastName NVARCHAR(20),

Email VARCHAR(30) UNIQUE,

Phone VARCHAR(20) UNIQUE)

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

Также мы можем определить этот атрибут на уровне таблицы:

CREATETABLECustomers

(

Id INTPRIMARYKEYIDENTITY,

Age INT,

FirstName NVARCHAR(20),

LastName NVARCHAR(20),

Email VARCHAR(30),

Phone VARCHAR(20),

UNIQUE (Email, Phone)

)

NULL и NOT NULL

Чтобы указать, может ли столбец принимать значение NULL, при определении столбца ему можно задать атрибут NULL или NOT NULL. Если этот атрибут явным образом не будет использован, то по умолчанию столбец будет допускать значение NULL( NOT NULL). Исключением является тот случай, когда столбец выступает в роли первичного ключа - в этом случае по умолчанию столбец имеет значение NOT NULL ( NULL).

CREATE TABLE Customers

(

Id INT PRIMARY KEY IDENTITY,

Age INT,

FirstName NVARCHAR(20) NOTNULL,

LastName NVARCHAR(20) NOTNULL,

Email VARCHAR(30) UNIQUE,

Phone VARCHAR(20) UNIQUE

)

DEFAULT

Атрибут DEFAULT определяет значение по умолчанию для столбца. Если при добавлении данных для столбца не будет предусмотрено значение, то для него будет использоваться значение по умолчанию.

CREATE TABLE Customers

(

Id INT PRIMARY KEY IDENTITY,

Age INT DEFAULT 18,

FirstName NVARCHAR(20) NOT NULL,

LastName NVARCHAR(20) NOT NULL,

Email VARCHAR(30) UNIQUE,

Phone VARCHAR(20) UNIQUE

);

Здесь для столбца Age предусмотрено значение по умолчанию 18.

CHECK

Ключевое слово CHECK задает ограничение для диапазона значений, которые могут храниться в столбце. Для этого после слова CHECK указывается в скобках условие, которому должен соответствовать столбец или несколько столбцов. Например, возраст клиентов не может быть меньше 0 или больше 100:

CREATE TABLE Customers

(

Id INT PRIMARY KEY IDENTITY,

Age INT DEFAULT 18 CHECK(Age >0 ANDAge < 100),

FirstName NVARCHAR(20) NOTNULL,

LastName NVARCHAR(20) NOTNULL,

Email VARCHAR(30) UNIQUE CHECK(Email !=''),

Phone VARCHAR(20) UNIQUE CHECK(Phone !='')

);

Здесь также указывается, что столбцы Email и Phone не могут иметь пустую строку в качестве значения (пустая строка не эквивалентна значению NULL).

Для соединения условий используется ключевое слово AND. Условия можно задать в виде операций сравнения больше (>), меньше (<), не равно (!=).

Также с помощью CHECK можно создать ограничение в целом для таблицы:

CREATETABLECustomers

(

Id INTPRIMARYKEYIDENTITY,

Age INTDEFAULT18,

FirstName NVARCHAR(20) NOTNULL,

LastNameNVARCHAR(20) NOTNULL,

Email VARCHAR(30) UNIQUE,

Phone VARCHAR(20) UNIQUE,

CHECK((Age >0 ANDAge<100) AND(Email !='') AND(Phone !=''))

)

Внешние ключи

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

Общий синтаксис установки внешнего ключа на уровне столбца:

[FOREIGNKEY] REFERENCES главная_таблица (столбец_главной_таблицы)

[ONDELETE{CASCADE|NOACTION}]

[ONUPDATE{CASCADE|NOACTION}]

Для создания ограничения внешнего ключа на уровне столбца после ключевого слова REFERENCES указывается имя связанной таблицы и в круглых скобках имя связанного столбца, на который будет указывать внешний ключ. Также обычно добавляются ключевые слова FOREIGN KEY, но в принципе их необязательно указывать. После выражения REFERENCES идет выражение ON DELETE и ON UPDATE.

Общий синтаксис установки внешнего ключа на уровне таблицы:

FOREIGNKEY(стобец1, столбец2, ... столбецN)

REFERENCES главная_таблица (столбец_главной_таблицы1, столбец_главной_таблицы2, ... столбец_главной_таблицыN)

[ONDELETE{CASCADE|NOACTION}]

[ONUPDATE{CASCADE|NOACTION}]

Например, определим две таблицы и свяжем их посредством внешнего ключа:

CREATE TABLEC ustomers

(

Id INT PRIMARY KEY IDENTITY,

Age INT DEFAULT18,

FirstName NVARCHAR(20) NOT NULL,

LastName NVARCHAR(20) NOT NULL,

Email VARCHAR(30) UNIQUE,

Phone VARCHAR(20) UNIQUE

);

CREATE TABLE Orders

(

Id INT PRIMARY KEY IDENTITY,

CustomerId INT REFERENCES Customers (Id),

CreatedAtDate

);

Здесь определены таблицы Customers и Orders. Customers является главной и представляет клиента. Orders является зависимой и представляет заказ, сделанный клиентом. Эта таблица через столбец CustomerId связана с таблицей Customers и ее столбцом Id. То есть столбец CustomerId является внешним ключом, который указывает на столбец Id из таблицы Customers.

Определение внешнего ключа на уровне таблицы выглядело бы следующим образом:

CREATE TABLE Orders

(

Id INT PRIMARY KEY IDENTITY,

CustomerId INT,

CreatedAt Date,

FOREIGNKEY(CustomerId) REFERENCES Customers (Id)

FOREIGNKEY(Id) REFERENCES Customers (CustomerId)

);

ON DELETE и ON UPDATE

С помощью выражений ON DELETE и ON UPDATE можно установить действия, которые выполняться соответственно при удалении и изменении связанной строки из главной таблицы. И для определения действия мы можем использовать следующие опции:

CASCADE: автоматически удаляет или изменяет строки из зависимой таблицы при удалении или изменении связанных строк в главной таблице.

CASCADE: автоматически удаляет или изменяет строки из главной таблицы при удалении или изменении связанных строк в зависимой таблице.

NO ACTION: предотвращает какие-либо действия в зависимой таблице при удалении или изменении связанных строк в главной таблице. То есть фактически какие-либо действия отсутствуют.

SET NULL: при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение NULL.

SET DEFAULT: при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение по умолчанию, которое задается с помощью атрибуты DEFAULT. Если для столбца не задано значение по умолчанию, то в качестве него применяется значение NULL.

Изменение таблицы

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

Общий формальный синтаксис команды выглядит следующим образом:

ALTER TABLE название_таблицы [WITHCHECK| WITHNOCHECK]

{ ADD название_столбца тип_данных_столбца [атрибуты_столбца] |

DROP COLUMN название_столбца |

ALTER COLUMN название_столбца тип_данных_столбца [NULL|NOTNULL] |

ADD[CONSTRAINT] определение_ограничения |

DROP[CONSTRAINT] имя_ограничения}

Таким образом, с помощью ALTER TABLE мы можем провернуть самые различные сценарии изменения таблицы. Рассмотрим некоторые из них.

Добавление нового столбца

Добавим в таблицу Customers новый столбец Address:

ALTER TABLE Customers

ADD Address NVARCHAR(50) NULL;

ALTER TABLE Customers

ADD Address NVARCHAR(50) NOT NULL DEFAULT 'Неизвестно';

Удаление столбца

Удалим столбец Address из таблицы Customers:

ALTER TABLE Customers

DROP COLUMN Address;

Изменение типа столбца

Изменим в таблице Customers тип данных у столбца FirstName на NVARCHAR(200):

ALTER TABLE Customers

ALTER COLUMN FirstName NVARCHAR(200);

Добавление ограничения CHECK

При добавлении ограничений SQL Server автоматически проверяет имеющиеся данные на соответствие добавляемым ограничениям. Если данные не соответствуют ограничениям, то такие ограничения не будут добавлены. Например, установим для столбца Age в таблице Customers ограничение Age> 21.

ALTER TABLE Customers

ADD CHECK (Age > 21);

Если в таблице есть строки, в которых в столбце Age есть значения, несоответствующие этому ограничению, то sql-команда завершится с ошибкой. Чтобы избежать подобной проверки на соответствие и все таки добавить ограничение, несмотря на наличие несоответствующих ему данных, используется выражение WITH NOCHECK:

ALTER TABLE Customers WITH NOCHECK

ADD CHECK(Age > 21);

По умолчанию используется значение WITH CHECK, которое проверяет на соответствие ограничениям.

Если в таблице есть строки, в которых в столбце Age есть значения, несоответствующие этому ограничению, то sql-команда завершится с ошибкой. Чтобы выполнить проверку на ограничение, используется выражение WITH CHECK:

ALTER TABLE Customers WITH CHECK

ADD CHECK(Age > 21);

По умолчанию используется значение WITH NOCHECK, которое НЕ проверяет на соответствие ограничениям.

Добавление внешнего ключа

Пусть изначально в базе данных будут добавлены две таблицы, никак не связанные:

CREATE TABLE Customers

(

Id INT PRIMARY KEY IDENTITY,

Age INT DEFAULT 18,

FirstName NVARCHAR(20) NOT NULL,

LastName NVARCHAR(20) NOT NULL,

Email VARCHAR(30) UNIQUE,

Phone VARCHAR(20) UNIQUE

);

CREATE TABLE Orders

(

Id INT IDENTITY,

CustomerId INT,

CreatedAtDate

);

Добавим ограничение внешнего ключа к столбцу CustomerId таблицы Orders:

ALTER TABLE Orders

ADD FOREIGN KEY(CustomerId) REFERENCES Customers(Id);

ALTER TABLE Orders

ADD FOREIGN KEY(Id) REFERENCES Customers(CustomerId);

Добавление первичного ключа

Используя выше определенную таблицу Orders, добавим к ней первичный ключ для столбца Id:

ALTER TABLE Orders

ADD PRIMARY KEY(Id);

Приложение Б

Рабочий лист студента

Create Table

Удаление таблиц

Переименование таблиц

Primary key

Identity

Unique

Null и Not null

Default

Check

Внешние ключи:

Задание ключа

On delete и on update

ИЗМЕНЕНИЕ ТАБЛИЦЫ:

Добавление нового столбца

Удаление столбца

Изменение типа столбца

Добавление ограничения

Добавление внешнего ключа

Добавление первичного ключа

Приложение В

Сводная таблица для подведения итогов

	1 группа	2 группа
Create Table 3 ошибки
Удаление таблиц Нет ошибок
Переименование таблиц Нет ошибок
Primary key 2 ошибки
Identity 3 ошибки
Unique Нет ошибок
Null и Not null 1 ошибка
Default Нет ошибок
Check Нет ошибок
Внешние ключи: Задание ключа 1 ошибка On delete и on update 1 ошибка
ИЗМЕНЕНИЕ ТАБЛИЦЫ: Добавление нового столбца Нет ошибок Удаление столбца Нет ошибок Изменение типа столбца Нет ошибок Добавление ограничения 1 ошибка Добавление внешнего ключа 1 ошибка Добавление первичного ключа Нет ошибок
Итого – 13 ошибок