Лабораторная работа №3-4

Цель. Разработка основных объектов базы данных и связей между ними.

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

Описание предметной области.

Для магазина торгующего картинами разрабатывается база данных.

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

Таблица CUSTOMER содержит данные о покупателях Таблица ARTIST содержит данные о художниках Таблица WORK содержит данные о картинах Таблица TRANSACTION содержит данные о сделках

Таблица CUSTOMER_ARTIST_INT содержит данные о предпочтениях покупателя к определенному художнику.

Последовательность выполнения.

Создание первичного ключа двумя способами

Создать таблицы CUSTOMER, ARTIST и CUSTOMER_ARTIST_INT

Замечание! В листинге есть ОШИБКИ, поэтому не советую «не глядя» копировать этот текст в SQL* Plus!!! (Л.И.)

CREATE TABLE CUSTOMER( CustomerID int NOT NULL, Name varchar(25) NOT NULL, Street varchar(30) NULL, City varchar(35) NULL,

24

State varchar(2) NULL, Zip varchar(5) NULL,

Area_Code varchar(3) NULL, Phone Number varchar(S) NULL;

ALTER TABLE CUSTOMER

ADD CONSTRAINTЕ CustomerPK PRIMARY KEY (CustomerID); CREATE INDEX CustonerNameIndex ON CUSTOMER(Name);

CREATE TABLE ARTIST(

ArfistID int PRIMARY KEY, Name varchar(25) NOT NULL, Nationality varchar(30) NULL, Birthdate date NULL, DeceasedDate date NULL);

CREATE UNIQUE INDEX Artist NameIndex ON ARTIST(Name);

CREATE TABLE CUSTOMER_ARTIST_INT( ArtistID int NOT NULL,

CustomerID int NOT NULL);

ALTER TABLE CUSTOMER)_ARTIST_INT

ADD CONSTRAINT CustomerArtistPK PRIMARY KEY ( ArttstID, CustomerID ),

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

!!!Для создания композитного первичного ключа можно использовать только первый(?) метод, поскольку в CREATE TABLE можно указывать свойство PRIMARY KEY только для одного столбца.

Для упрощения работы создайте файл Createl.sql для его запуска с помощью

Start Createl

Отобразите структуру всех трех таблиц на экране.

Создание суррогатных ключей с помощью последовательностей

Create Sequence CustID Increment by 1 start with 100:

25

Вспомним, что Метод NextVal выдает следующее значение в последовательности, а метод CurrVal выдает текущее значение в последовательности.

Вставим строку в таблицу CUSTOMER

INSERT INTO CUSTOMER CustomerID, Name, Area_Code, Phorie_Nuniber) VALUES (CustID.NextVal, 'Mary Jones', '350', '555-1234'):

Этот оператор создаст в таблице CUSTOMER строку, где столбцу CustomerID будет присвоено следующее значение в последовательности CustID. Выполнив этот оператор можно считать только что созданную строку с помощью метода CurrVaL:

SELECТ * FROM CUSTOMER WHERE CustomerID = CustID.CurrVal;

Здесь метод CustID.CurrVal возвращает текущее значение в последовательности, то есть только что использованное значение.

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

Создайте с помощью SQL Plus следующие последовательности:.

Create Sequence CustID Increment by 1 start with 1000; Create Sequence ArtistID Increment by 1 start with 1; Create Sequence WorkID Increment by 1 start with 500; Create Sequence TransID Increment by 1 start with 100;

Ввод данных

Запустите файл ACIns.sql, предварительно набрав и ПРОВЕРИВ текст:

INSERT INTO ARTIST (ArtlstID, Name, Nationality) Values (ArtistID.NextVal, 'Tobey', 'US');

INSERT INTO ARTIST (ArtistID, Name, Nationality) Values (ArtistID.NextVal, Miro, 'Spanish');

INSERT INTO ARTIST (ArtistID, Name, Nationality) Values (ArtistID.NextVal, ‘Frings', 'US');

INSERT INTO ARTIST (ArtistID, Name, Nationality) Values (ArtistID.NextVal, 'Foster', 'English'):

INSERT INTO ARTIST (ArtistID, Name, Nationality) Values (ArtistID.NextVal, 'van Vronkin', 'US'):

INSERT INTO CUSTOMER (CustomerID, Name, Area__Code, Phone Number) Values

26

(CustID.NextVal, 'Jeffrey Janes', ‘206’, 555-1234');

INSERT INTO CUSTOMER (CustomerID, Name, Area__Code, Phone Number) Values (CustID.NextVal, 'David Smith', '206', ‘555-443');

INSERT INTO CUSTOMER (CustomerID, Name, Area__Code, Phone Number) Values (CustID.NextVal, 'Tiffany Twilight', '360', ‘555-1040');

Отобразите на экране столбцы ArtistID, Name, Nationality из таблицы ARTIST; CustomerID, Name, AreaCode, PhoneNumber из таблицы CUSTOMER.

Создание связей

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

таблицами CUSTOMER и CUSTOMER_ARTIST_INT и между таблицами ARTIST и CUSTOMER_ARTIST_INT:

ALTER TABLE CUSTOMER_ARTIST_INT ADD CONSTRAINT ArtlstIntFK FOREIGN KEY(ArtistID) REFERENCES ARTIST ON DELETE CASCADE; ALTER TABLE CUSTOMER_ARTIST_INT ADD CONSTRAINT CustonerIntFK FOREIGN KEY(CustomerID) REFERENCES CUSTOMER ON DELETE CASCADE;

Ограничениям даны имена ArtistIntFK и CustomerIntFK. Эти имена не играют особой роли для Oracle и могут выбираться разработчиком. Обратите внимание, что для родительской таблицы указан только столбец, являющийся внешним ключом. Oracle предполагает, что внешний ключ будет связан с первичным ключом родительской таблицы, поэтому указывать столбец первичного ключа нет необходимости. Фраза ON DELETE CASCADE указывает на то, что при удалении строк из родительской таблицы соответствующие строки дочерних таблиц должны быть также удалены. Слово cascade (каскад) используется здесь потому, что удаление идет каскадом от родительской таблицы к дочерней.

Введите эти операторы в редактор SQL Plus и заполните несколько строк таблицы пересечения. Теперь, если вы удалите данные о покупателе или

27

художнике, соответствующие строки в таблице пересечения будут также удалены.

Создайте таблицы WORK и TRANSACTION, Обратите внимание, что в определениях ограничений по внешнему ключу отсутствует фраза ON DELETE CASCADE. Так, ограничение ArtistFK сделает невозможным удаление тех строк в таблице ARTIST, которые имеют дочерние строки и таблице WORK. Ограничения WorkFK и CustomerFK функционируют сходным образом.

ALTER TABLE WORK ADD CONSTRAINT ArtistFK

FOREIGN KEY (ArtistID) REFERENCES ARTIST:

ALTER TABLE TRANSACTION ADD CONSTRAINT WorkFK FOREIGN KEY (WorkID) REFERENCES WORK;

ALTER TABLE TRANSACTION ADD CONSTRAINT CustomerFK FOREIGN KEY (CustomerID) REFERENCES CUSTOMER;

Оператор ALTER можно также использовать для удаления ограничения. Оператор

ALTER TABLE MYTABLE DROP CONSTRAINT MyConstraint

удалит ограничение MyConstraint из таблицы MyTable.

28