Создание многотабличного представления — КиберПедия 

История развития пистолетов-пулеметов: Предпосылкой для возникновения пистолетов-пулеметов послужила давняя тенденция тяготения винтовок...

Автоматическое растормаживание колес: Тормозные устройства колес предназначены для уменьше­ния длины пробега и улучшения маневрирования ВС при...

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

2020-06-02 150
Создание многотабличного представления 0.00 из 5.00 0 оценок
Заказать работу

Чтобы получать ответы на имеющиеся вопросы, часто приходится выбирать данные не менее чем из двух таблиц. Скажем, вы работаете в магазине спорттоваров, и для рассылки рекламы по почте вам нужен список клиентов, купивших у вас в прошлом году лыжное снаряжение. Скорее всего, потребуется информация из следующих таблиц: CUSTOMER (клиент), PRODUCT (товар), INVOICE (счет-фактура) и INVOICE_LINE (строка счета-фактуры). На их основе можно создать многотабличное представление, которое покажет нужные данные. Создав представление, его можно использовать снова и снова. При каждом таком использовании представление отображает последние изменения в таблицах, на основе которых это представление создано.

В базе данных магазина спорттоваров имеются четыре таблицы: CUSTOMER, PRODUCT, INVOICE и INVOICE_LINE. Структура каждой из них показана в табл. 3.1.

Таблица 3.1. Таблицы базы данных магазина спорттоваров.

Таблица Столбец Тип данных Ограничение

CUSTOMER

Customer iD (идентификационный номер клиента) INTEGER NOT NULL (не может быть неопределенным значением) FirstName (имя) CHARACTER (15)   LastName (фамилия) CHARACTER (20) NOT NULL Street (улица) CHARACTER (25)   City (ГОРОД) CHARACTER (20)   State (штат) CHARACTER (2)   Zipcode (ПОЧТОВЫЙ КОД) INTEGER   Phone (телефон) CHARACTER (13)  

PRODUCT

Product id (идентификационный номер товара) INTEGER NOT NULL Name (название) CHARACTER (25)   Description (описание) CHARACTER (30)   Сategory (категория) CHARACTER (15)   Vendor id (идентификационный номер поставщика) INTEGER   VendorName (наименование поставщика) CHARACTER (30)  

INVOICE

InvoiceNumber (номер счета-фактуры) INTEGER NOT NULL CustomeriD (идентификационный номер покупателя) INTEGER   InvoiceDate (дата выписки счета-фактуры) DATE   Totalsale (всего продано на сумму) NUMERIC (9.2)   TotalRemitted (всего оплачено) NUMERIC (9.2)   Formof Payment (форма платежа) CHARACTER (10)  

INVOICE_LINE

LineNumber (номер строки) INTEGER NOT NULL InvoiceNumber (номер счета-фактуры) INTEGER   Product id (идентификационный номер товара) INTEGER   Quantity (количество) INTEGER   SalePrice (продано по цене) NUMERIC (9.2)  

Обратите внимание, что в некоторых столбцах табл. 3.1 имеется ограничение NOT NULL (не может быть неопределенным значением). Эти столбцы являются или первичными ключами соответствующих таблиц, или вы решили, что есть другие причины, по которым их значения обязательно должны быть определенными. Первичный ключ таблицы должен однозначно идентифицировать каждую ее строку. Значение этого ключа в каждой строке должно быть определенным. (Подробно о ключах говорится в главе 5.)

Таблицы связываются друг с другом посредством общих столбцов. Ниже описаны связи между таблицами. (Отношения таблиц представлены на рис. 3.3.)

· Таблицу CUSTOMER связывает с другой таблицей, INVOICE, отношение "один ко многим". Один клиент может сделать множество покупок, в результате чего получится множество счетов-фактур. Однако каждый счет-фактура имеет отношение к одному и только одному клиенту.

· Таблицу INVOICE связывает с таблицей INVOICE_LINE также отношение "один ко многим". Ведь в счете-фактуре может быть множество строк, но каждая строка находится в одном и только одном счете-фактуре.

· Таблицу PRODUCT с таблицей INVOICE_LINE связывает отношение "один ко многим". Каждый товар может быть во множестве строк в одном или многих счетах-фактурах. Однако каждая строка относится к одному и только одному товару.


Рис. 3.3. Структура базы данных магазина спорттоваров

Таблица CUSTOMER поддерживает отношение с таблицей INVOICE, используя их общий столбец CustomerlD. А отношение таблицы INVOICE с таблицей INVOICE_LINE поддерживается с помощью общего столбца InvoiceNumber. Отношение же таблицы PRODUCT с таблицей INVOICE_LINE поддерживается с помощью общего столбца ProductDD. В сущности эти отношения и делают саму базу реляционной, т.е. работающей на основе отношений.

Чтобы получить информацию о тех клиентах, которые купили лыжное оборудование, необходимы данные из следующих полей: FirstName, LastName, Street, City, State и Zipcode из таблицы CUSTOMER; Category – из таблицы PRODUCT; InvoiceNumber – из таблицы INVOICE, а также LineNumber– из таблицы INVOICE_LINE. Нужное представление можно создавать поэтапно, используя для этого следующие команды:

CREATE VIEW SKI_CUST1 AS

SELECT FirstName,

LastName, Street,

City,

State,

Zipcode,

InvoiceNumber

FROM CUSTOMER JOIN INVOICE

USING (CUSTOMER_ID);

CREATE VIEW SKI_CUST2 AS

SELECT FirstName,

LastName,

Street,

City,

State,

Zipcode,

ProductID

FROM SKI_CUST1 JOIN INVOICE_LINE

USING (InvoiceNumber);

CREATE VIEW SKI_CUST3 AS

SELECT FirstName,

LastName,

Street,

City,

State,

Zipcode,

Category

FROM SKI_CUST2 JOIN PRODUCT

USING (ProductID);

CREATE VIEW SKI_CUST AS

SELECT DISTINCT FirstName,

LastName,

Street,

City,

State,

Zipcode

FROM SKI_CUST3

WHERE CATEGORY = 'Ski';

Эти операторы CREATE VIEW соединяют данные из множества таблиц, используя для этого оператор JOIN. Диаграмма всего этого процесса показана на рис. 3.4.


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

Ниже приведены положения для четырех операторов CREATE VIEW.

· Первый оператор соединяет столбцы из таблицы CUSTOMER со столбцом из таблицы INVOICE и создает представление SKI_CUST1.

· Второй оператор соединяет представление SKLCUST1 со столбцом из таблицы INVOICE_LINE, создавая таким образом представление SKI_CUST2.

· Третий оператор соединяет представление SKLCUST2 со столбцом из таблицы PRODUCT и создает представление SKI_CUST3.

· Четвертый оператор отбрасывает все строки, где в поле категории товара отмечено не 'Ski' (лыжи). В результате получается представление SKI_CUST, в котором находятся имена, фамилии и адреса тех клиентов, которые хотя бы один раз купили товары категории 'Ski'. Каждому из этих клиентов, даже если он покупал лыжи много раз, в представлении SKI_CUST будет соответствовать только одна запись. Это достигается благодаря ключевому слову DISTINCT (отдельный), которое находится в SELECT четвертого оператора CREATE VIEW. (Об операторах JOIN подробно говорится в главе 10.)

Сборка таблиц в схемы

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

В системе, где может сосуществовать несколько несвязанных друг с другом проектов, можно соединить все таблицы, связанные друг с другом отношениями, в одну схему. А из таблиц, не вошедших в эту схему, можно образовать другие схемы. Чтобы таблицы из одного проекта не оказались случайно в другом, схемам следует дать имена. У каждого проекта имеется своя собственная схема, которую по имени можно будет отличать от других схем. Некоторые табличные имена (например, CUSTOMER, PRODUCT и т.д.) могут встречаться сразу в нескольких проектах. Если есть хоть малейший шанс, что возникнет путаница с именами, необходимо в именах таблиц указывать имя схемы (примерно так: ИМЯ_СХЕМЫ.ИМЯ_ТЛБЛИЦЫ). Если имя схемы не указано, SQL будет считать, что эта таблица относится к схеме, подразумеваемой по умолчанию.

Помни:
У базы данных, кроме логической, есть еще и физическая схема. Физическая схема – это способ, с помощью которого данные и соответствующие им компоненты, например индексы, физически размещаются на диске компьютера. И когда в книге говорится о схеме базы данных, то имеется в виду логическая схема, а не физическая
.

Заказ по каталогу

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

При указании имени таблицы можно также использовать имена ее каталога и схемы. Таким образом, гарантируется, что никто не перепутает две таблицы с одним и тем же именем, находящиеся в схемах с одинаковым именем. Имя таблицы с указанием каталога имеет следующий формат: ИМЯ_КАТАЛОГА.ИМЯ_СХЕМЫ.ИМЯ_ТАБЛИЦЫ.

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

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

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


Рис. 3.5. Иерархическая структура типичной базы данных SQL

Знакомство с командами DDL

Язык определения данных (DDL) работает со структурой базы данных, в то время как язык манипулирования (он будет описан позже) – с данными, которые находятся в этой структуре. DDL состоит из следующих трех команд.

· Для создания основных структур базы данных используются разные формы команды CREATE (создать).

· Для изменения созданных структур применяется команда ALTER (изменить).

· Команда DROP (прекратить) применяется к таблице, чтобы не только удалить табличные данные, но и разрушить саму структуру этой таблицы.

В следующих разделах кратко описываются команды DDL. В главах 4 и 5 эти команды используются в примерах.

CREATE

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

CREATE SCHEMA SALESAUTHORIZATION SALES_MGRDEFAULT CHARACTER SET ASCII_FULL;

С помощью оператора CREATE DOMAIN (создать домен) устанавливаются ограничения на те значения, которые могут быть в столбце, или указывается порядок сопоставления. Устанавливаемые на домен ограничения определяют, какие объекты могут и какие не могут в нем находиться. Создавать домены можно после того, как установлена схема. Следующий пример демонстрирует, как можно использовать эту команду:

CREATE DOMAIN AGE AS INTEGERCHECK (AGE > 20);

Таблицы создаются с помощью оператора CREATE TABLE (создать таблицу), а представления – с помощью CREATE VIEW (создать представление). В этой главе уже приводились примеры использования операторов CREATE TABLE и CREATE VIEW. Когда с помощью оператора CREATE TABLE создается новая таблица, то в том же операторе на ее столбцы можно также установить ограничения. Впрочем, иногда требуется установить ограничения, которые относятся не только к таблице, но и ко всей схеме. В таких случаях используется оператор CREATE ASSERTION (создать утверждение).

Кроме того, имеются операторы CREATE CHARACTER SET (создать символьный набор), CREATE COLLATION (создать сопоставление) и CREATE TRANSLATION (создать трансляцию), которые предоставляют широкие возможности по созданию новых символьных наборов, последовательностей сопоставления или таблиц трансляции. (Последовательности сопоставления определяют порядок, в котором будут проводиться операции сравнения или сортировки. Таблицы трансляции управляют преобразованием символьных строк из одного символьного набора в другой.)

ALTER

Таблица не обязательно навсегда останется такой, какой ее создали. Как только ее начинают использовать, то вдруг обнаруживается, что в ней нет чего-то такого, что обязательно должно было быть. Чтобы изменить таблицу, добавив, изменив или удалив ее столбец, воспользуйтесь командой ALTER TABLE (изменить таблицу). Команду ALTER можно применять не только к таблицам, но также к столбцам и доменам.

DROP

Удалить таблицу из схемы базы данных легко. Надо только использовать команду DROP TABLE <имя_таблицы> (прекратить поддержку таблицы). В результате стираются все данные этой таблицы, а также метаданные, которые определяют ее в словаре данных, – после чего таблицы как будто и не было.


Поделиться с друзьями:

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

Биохимия спиртового брожения: Основу технологии получения пива составляет спиртовое брожение, - при котором сахар превращается...

Семя – орган полового размножения и расселения растений: наружи у семян имеется плотный покров – кожура...

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



© cyberpedia.su 2017-2024 - Не является автором материалов. Исключительное право сохранено за автором текста.
Если вы не хотите, чтобы данный материал был у нас на сайте, перейдите по ссылке: Нарушение авторских прав. Мы поможем в написании вашей работы!

0.045 с.