Подготовка скриптов в sql developer data modeler
Эта статья и её продолжение появились благодаря вопросам студентов на семинарах по СУБД. Каждый студент должен был выбрать тему для проектирования базы данных, реализовать полный цикл проектирования от логической и физической диаграммы в Oracle SQL Developer Data Modeler ( SDDM ) до работающей базы данных в СУБД Oracle с использованием APEX. Затем стать пользователем своей разработки: заполнить схему данными и написать аналитические запросы. Некоторые возможности SDDM оказались неочевидными и мы потратили полтора занятия, что бы рассмотреть самое необходимое.
Некоторым студентам, имеющим некоторый стихийно накопленный опыт разработки приложений с использованием СУБД, тяжело перестраиваться на анализ предметной области, трудно понять важность методик проектирования реляционной модели. Потому статья начнется с напоминания порядка разработки.
Не надо сразу делать таблицы. Порядок разработки следующий:
Статью готовил я, Присада Сергей Анатольевич, сейчас работаю в Финансовом университете при Правительстве РФ, почта sergey.prisada на яндексе.
Рассмотрим на некоторых абстрактных отношениях следующие возможности:
Задачи
1. Создать домен атрибутов с 4 значениями (Value List). Указать строковый тип и параметры типа.
Меню “Tools” – “Domains Administrator”
Домен атрибутов будет использоваться для создания ограничений значений стрибутов на уровне таблицы. Ограничения могут быть не только списком значений, но также ограничивать диапазоны численных данных, можно указать конкретные значения диапазона.
Создать список допустимых значений домена.
Домены атрибутов сохраняются в файл defaultdomains.xml в каталоге с настройками Oracle Data Modeler профиля пользователя, или в каталоге с установленной программой – зависит от операционной системы.
Файл с доменами атрибутов необходимо сохранить в каталоге с моделью с новым именем, подключить его в настройках и сохранить модель. Для этого открыть настройки доменов:
Меню “Tools” – “Domains Administrator”, выбрать файл
2. Создать 3 сущности, каждая с 4 атрибутами. В каждой сущности 1 атрибут использует домен значений. 2 атрибута обязательные, два не обязательные, один атрибут уникальный, но не первичный UID. Сущности используют суррогатные ключи, первичные ключи не устанавливаем. Каждому атрибуту и сущности сделать комментарий для RDBMS.
Первичные ключи указывать не нужно, только уникальные атрибуты в разделе “Uniaue Identifiers”.
Использование суррогатных ключей для сущности.
Домен атрибутов и комментарий к атрибуту, параметр Mandatory (обязательности) устанаваливаем у двух из четырех.
Комментарий в свойствах сущности. Он появится на логической диаграмме, отобразится в свойствах таблицы и будет создан в физической модели.
Первичных и уникальных ключей сами не создаем (пусто на вкладке)
Создать связи между сущностями 1:N и в свойствах связей установить использование суррогатных (Искусственных) ключей “Use surrogate keys”. Свойства связи: переносимость Transferable, обязательность Optional и кардинальность Cardinality выбираем какие угодно, это же пример.
3. Создать и применить глоссарий имён.
Использование глоссария облечает работу с правилами именования в модели данных. Имя сущности должно быть в единственном числе, производная из неё таблица во множественном. Имя атрибута может быть длинным и понятным при разработке, но имя производного столбца должно быть кратким для уменьшения кода и удобства работы с запросами. Как правило, для имени столбца используют аббревиатуру имени атрибута. Имя атрибута сущности для автоматически создаваемого первичного ключа будет состоять из имени сущности с добавлением “_id”. Также в Oracle Data Modelerотдельно есть настройки правил для формирования имён внешних ключей, составных первичных ключей, индексов, ограничений уникальности.
Глоссарий имён можно создать новый, но также можно создать шаблон из уже разработанной логической
Предварительно необходимо сделать настройки имён в свойствах Oracle Data Modeler.
В настройках в Oracle Data Modeler, убрать чек-бокс.
Создать глоссарий имен из готовой логической диаграммы. Сохранить его как файл в каталоге с моделью.
Глоссарий обязательно должен содержать множественную форму для имени каждой сущности и аббревиатуру для каждого атрибута. Большие глоссарии можно редактировать выгрузив их в таблицу Excel. Меню редактирования глоссария находится в меню “Tools” – “Glossary Editor”. Используйте глоссарий во множестве проектов, нарабатывайте его в своей практике.
Меню сохранения глоссария
В настройках модели подключить глоссарий.
И примените правила именования к логической модели.
Преобразовать в реляционную.
Результат преобразования будет содержать имена из глоссария, комментарии.
Преобразовать реляционную модель в физическую.
В диалоговом окне можно выбрать не только сохранение, но также вид конкретной СУБД в которой будет использоваться готовая модель. Напомню, что проектирование не зависит от физической реализации СУБД. Мы выберем СУБД Oracle последней доступной в планировщике версии.
В настройках генерации физической модели можно указать множество параметров, например выбрать только определенные объекты модели (например вы разработали только представления View). Обязательно установить чек-боксы как на картинке. Все подробности в документации.
Результат – DDL файл с инструкциями для создания схемы в базе данных. Внимательно изучите его, найдите все элементы, которые были созданы в логической диаграмме, при наличии ошибок выяснить причину и устранить. Например домены атрибутов, каким образом создаются ограничения и т.п.
Этот скрипт готов для импорта в базу данных.
4. Отображение комментариев.
Отображение комментариев в логической и реляционной моделях делает диаграммы более читаемыми во время работы.
При открытой логической диаграмме в меню Oracle Data Modeler включить в меню отображение комментариев.
5.Скачайте пример
Модель из статьи вы можете скачать по ссылке на Github: https://github.com/saprisada/odm
Изучите её как пример, создайте аналог, прочтите дополнительно документацию и примените знания в своём проекте.
2 Data Modeler Tutorial: Modeling for a Small Database
In this tutorial, you will use Data Modeler to create models for a simplified library database, which will include entities for books, patrons (people who have library cards), and transactions (checking a book out, returning a book, and so on).
This tutorial uses the same entities as for the tutorial provided with the SQL Developer online help. The model is deliberately oversimplified and would not be adequate for any actual public or organizational library. For more advanced tutorials and other materials, see For More Information About Data Modeling.
If the instructions do not mention a particular dialog box, tab, or field, then do not specify anything for it.
This simplified tutorial uses only a subset of the possible steps for the Top-Down Modeling approach. (For information about the approaches, see Approaches to Data Modeling.)
You will perform the following major steps:
2.1 Develop the Logical Model
The logical model for the database includes three entities: Books (describes each book in the library), Patrons (describes each person who has a library card), and Transactions (describes each transaction involving a patron and a book). However, before you create the entities, create some domains that will make the entity creation (and later DDL generation) more meaningful and specific.
To start developing the logical model, go to Adding Domains.
2.1.1 Adding Domains
In planning for your data needs, you have determined that several kinds of fields will occur in multiple kinds of records, and many fields can share a definition. For example, you have decided that:
The first and last names of persons can be up to 25 characters each.
Street address lines can be up to 40 characters.
City names can be up to 25 characters.
State codes (United States) are 2-character standard abbreviations.
Book identifiers can be up to 20 characters.
Other identifiers are numeric, with up to 7 digits (no decimal places).
Titles (books, articles, and so on) can be up to 50 characters.
You therefore decide to add appropriate domains, so that you can later use them to specify data types for attributes when you create the entities. (These added domains will also be available after you exit Data Modeler and restart it later.)
In the Domains Administration dialog box, add domains with the following definitions. Click Add to start each definition, and click Apply after each definition.
Precision: 7, Scale: 0
Click Close to close the dialog box.
2.1.2 Creating the Books Entity
The Books entity describes each book in the library. Create the Books entity as follows:
Primary UID (unique identifier). (The Dewey code or other book identifier.)
M (mandatory, that is, must not be null).
Domain: Person Name
M (mandatory, that is, must not be null).
Domain: Person Name
(Author’s first name; not mandatory, but enter it if the author has a first name.)
Logical type: NUMERIC (Precision=2, Scale= 0)
(Librarian’s personal rating of the book, from 1 (poor) to 10 (great).)
2.1.3 Creating the Patrons Entity
The Patrons entity describes each library patron (that is, each person who has a library card and is thus able to borrow books). Create the Patrons entity as follows:
Primary UID (unique identifier). (Unique patron ID number, also called the library card number.)
Domain: Person Name
M (mandatory, that is, must not be null). 25 characters maximum.
Domain: Person Name
(Patron’s first name.)
Domain: Address Line
(Patron’s street address.)
(City or town where the patron lives.)
(2-letter code for the state where the patron lives.)
(Postal code where the patron lives.)
Structured type: SDO_GEOMETRY
Oracle Spatial and Graph geometry object representing the patron’s geocoded address.
2.1.4 Creating the Transactions Entity
The Transactions entity describes each transaction that involves a patron and a book, such as someone checking out or returning a book. Each record is a single transaction, regardless of how many books the patron brings to the library desk. For example, a patron returning two books and checking out three books causes five transactions to be recorded (two returns and three checkouts). Create the Transactions entity as follows:
Primary UID (unique identifier). (Unique transaction ID number)
Logical type: Datetime
M (mandatory, that is, must not be null). Date and time of the transaction.
M (mandatory, that is, must not be null). (Numeric code indicating the type of transaction, such as 1 for checking out a book.)
Note that you do not explicitly define the patron_id and book_id attributes, because these will be automatically added to the Transactions entity after you create relations between the entities (see Creating Relations Between Entities); they will be added as foreign keys when you generate the relational model (see Develop the Relational Model).
2.1.5 Creating Relations Between Entities
Relations show the relationships between entities: one-to-many, many-to-one, or many-to-many. The following relationships exist between the entities:
Books and Transactions: one-to-many. Each book can be involved in multiple sequential transactions. Each book can have zero or one active checkout transactions; a book that is checked out cannot be checked out again until after it has been returned.
Patrons and Transactions: one-to-many. Each patron can be involved in multiple sequential and simultaneous transactions. Each patron can check out one or many books in a visit to the library, and can have multiple active checkout transactions reflecting several visits; each patron can also return checked out books at any time.
Create the relationships as follows. When you are done, the logical model pane in the main area should look like the following figure. Note that for this figure, Bachman notation is used (you can change to Barker by clicking View, then Logical Diagram Notation, then Barker Notation).
In the logical model pane in the main area, arrange the entity boxes as follows: Books on the left, Patrons on the right, and Transactions either between Books and Patrons or under them and in the middle. (If the pointer is still cross-hairs, click the Select icon at the top left to change the pointer to an arrow.)
Suggestion: Turn off auto line routing for this exercise: right-click in the Logical pane, and ensure that Auto Route is not checked.
Click the New 1:N Relation icon.
Click first in the Books box, then in the Transactions box. A line with an arrowhead is drawn from Books to Transactions.
Click the New 1:N Relation icon.
Click first in the Patrons box, then in the Transactions box. A line with an arrowhead is drawn from Patrons to Transactions.
Optionally, double-click a line (or right-click a line and select Properties) and view the Relation Properties information.
2.2 Develop the Relational Model
The relational model for the library tutorial database consists of tables that reflect the entities of the logical model (Books, Patrons, and Transactions) and all attributes of each entity. In the simplified data model for this tutorial, a single relational model reflects the entire logical model; however, for other data models you can create one or more relational models, each reflecting all or a subset of the logical model. (To have a relational model reflect a subset of the logical model, use the «filter» feature in the dialog box for engineering a relational model.)
Develop the relational model as follows:
Expand the Relational Models node in the object browser on the left side of the window, and expand Relational_1 and optionally nodes under it that contain any entries (such as Tables and Columns), to view the objects created.
2.3 Generate DDL
Generate Data Definition Language (DDL) statements that you can use to create database objects that reflect the models that you have designed. The DDL statements will implement the physical model (type of database, such as Oracle Database 11 g ) that you specify.
Develop the physical model as follows:
Optionally, view the physical model before you generate DDL statements:
Expand the Physical Models node under the Library relational model, and expand the newly created physical model and the Tables node under it, to view the table objects that were created.
Later, run the script (for example, using a database connection and SQL Worksheet in SQL Developer) to create the objects in the desired database.
Click Close to close the DDL file editor.
2.4 Save the Design
You can later open the saved design and continue working on it, as explained in Saving, Opening, Exporting, and Importing Designs.
2 Data Modeler Tutorial: Modeling for a Small Database
In this tutorial, you will use Data Modeler to create models for a simplified library database, which will include entities for books, patrons (people who have library cards), and transactions (checking a book out, returning a book, and so on).
This tutorial uses the same entities as for the tutorial provided with the SQL Developer online help. The model is deliberately oversimplified and would not be adequate for any actual public or organizational library. For more advanced tutorials and other materials, see Section 1.11, «For More Information About Data Modeling».
If the instructions do not mention a particular dialog box, tab, or field, then do not specify anything for it.
This simplified tutorial uses only a subset of the possible steps for the Top-Down Modeling approach. (For information about the approaches, see Section 1.4, «Approaches to Data Modeling».)
You will perform the following major steps:
2.1 Develop the Logical Model
The logical model for the database includes three entities: Books (describes each book in the library), Patrons (describes each person who has a library card), and Transactions (describes each transaction involving a patron and a book). However, before you create the entities, create some domains that will make the entity creation (and later DDL generation) more meaningful and specific.
To start developing the logical model, go to Section 2.1.1, «Adding Domains».
2.1.1 Adding Domains
In planning for your data needs, you have determined that several kinds of fields will occur in multiple kinds of records, and many fields can share a definition. For example, you have decided that:
The first and last names of persons can be up to 25 characters each.
Street address lines can be up to 40 characters.
City names can be up to 25 characters.
State codes (United States) are 2-character standard abbreviations.
Book identifiers can be up to 20 characters.
Other identifiers are numeric, with up to 7 digits (no decimal places).
Titles (books, articles, and so on) can be up to 50 characters.
You therefore decide to add appropriate domains, so that you can later use them to specify data types for attributes when you create the entities. (These added domains will also be available after you exit Data Modeler and restart it later.)
In the Domains Administration dialog box, add domains with the following definitions. Click Add to start each definition, and click Apply after each definition.
| Name | Logical Type | Other Information |
|---|---|---|
| Person Name | VARCHAR | Size: 25 |
| Address Line | VARCHAR | Size: 40 |
| City | VARCHAR | Size: 25 |
| State | VARCHAR | Size: 2 |
| Zip | VARCHAR | Size: 10 |
| Book Id | VARCHAR | Size: 20 |
| Numeric Id | NUMERIC | Precision: 7, Scale: 0 |
| Title | VARCHAR | Size: 50 |
Click Close to close the dialog box.
2.1.2 Creating the Books Entity
The Books entity describes each book in the library. Create the Books entity as follows:
In the main area (right side) of the Data Modeler window, click the Logical tab.
Click the New Entity icon.
Click in the logical model pane in the main area; and in the Logical pane press, diagonally drag, and release the mouse button to draw an entity box. The Entity Properties dialog box is displayed.
Click General on the left, and specify as follows:
Click Attributes on the left, and use the Add (+) icon to add the following attributes, one at a time. (For datatypes, select from the Domain types except for Rating, which is a Logical type.)
| Name | Datatype | Other Information and Notes |
|---|---|---|
| book_id | Domain: Book Id | Primary UID (unique identifier). (The Dewey code or other book identifier.) |
| title | Domain: Title | M (mandatory, that is, must not be null). |
| author_last_name | Domain: Person Name | M (mandatory, that is, must not be null). |
| author_first_name | Domain: Person Name | (Author’s first name; not mandatory, but enter it if the author has a first name.) |
| rating | Logical type: NUMERIC (Precision=2, Scale= 0) | (Librarian’s personal rating of the book, from 1 (poor) to 10 (great).) |
Click OK to finish creating the Books entity.
2.1.3 Creating the Patrons Entity
The Patrons entity describes each library patron (that is, each person who has a library card and is thus able to borrow books). Create the Patrons entity as follows:
In the main area (right side) of the Data Modeler window, click the Logical tab.
Click the New Entity icon.
Click in the logical model pane in the main area; and in the Logical pane press, diagonally drag, and release the mouse button to draw an entity box. (Suggestion: draw the box to the right of the Books box.) The Entity Properties dialog box is displayed.
Click General on the left, and specify as follows:
Click Attributes on the left, and use the Add (+) icon to add the following attributes, one at a time. (For datatypes, select from the Domain types, except for location, which uses the structured type SDO_GEOMETRY.)
| Attribute Name | Type | Other Information and Notes |
|---|---|---|
| patron_id | Domain: Numeric Id | Primary UID (unique identifier). (Unique patron ID number, also called the library card number.) |
| last_name | Domain: Person Name | M (mandatory, that is, must not be null). 25 characters maximum. |
| first_name | Domain: Person Name | (Patron’s first name.) |
| street_address | Domain: Address Line | (Patron’s street address.) |
| city | Domain: City | (City or town where the patron lives.) |
| state | Domain: State | (2-letter code for the state where the patron lives.) |
| zip | Domain: Zip | (Postal code where the patron lives.) |
| location | Structured type: SDO_GEOMETRY | Oracle Spatial and Graph geometry object representing the patron’s geocoded address. |
Click OK to finish creating the Patrons entity.
2.1.4 Creating the Transactions Entity
The Transactions entity describes each transaction that involves a patron and a book, such as someone checking out or returning a book. Each record is a single transaction, regardless of how many books the patron brings to the library desk. For example, a patron returning two books and checking out three books causes five transactions to be recorded (two returns and three checkouts). Create the Transactions entity as follows:
In the main area (right side) of the Data Modeler window, click the Logical tab.
Click the New Entity icon.
Click in the logical model pane in the main area; and in the Logical pane press, diagonally drag, and release the mouse button to draw an entity box. (Suggestion: Draw the box below and centered between the Books and Patrons boxes.) The Entity Properties dialog box is displayed.
Click General on the left, and specify as follows:
Click Attributes on the left, and use the Add (+) icon to add the following attributes, one at a time. (For datatypes, select from the Domain types, except for transaction_date, which uses a Logical type.)
| Attribute Name | Type | Other Information and Notes |
|---|---|---|
| transaction_id | Domain: Numeric Id | Primary UID (unique identifier). (Unique transaction ID number) |
| transaction_date | Logical type: Datetime | M (mandatory, that is, must not be null). Date and time of the transaction. |
| transaction_type | Domain: Numeric Id | M (mandatory, that is, must not be null). (Numeric code indicating the type of transaction, such as 1 for checking out a book.) |
Note that you do not explicitly define the patron_id and book_id attributes, because these will be automatically added to the Transactions entity after you create relations between the entities (see Section 2.1.5); they will be added as foreign keys when you generate the relational model (see Section 2.2).
Click OK to finish creating the Transactions entity.
2.1.5 Creating Relations Between Entities
Relations show the relationships between entities: one-to-many, many-to-one, or many-to-many. The following relationships exist between the entities:
Books and Transactions: one-to-many. Each book can be involved in multiple sequential transactions. Each book can have zero or one active checkout transactions; a book that is checked out cannot be checked out again until after it has been returned.
Patrons and Transactions: one-to-many. Each patron can be involved in multiple sequential and simultaneous transactions. Each patron can check out one or many books in a visit to the library, and can have multiple active checkout transactions reflecting several visits; each patron can also return checked out books at any time.
Create the relationships as follows. When you are done, the logical model pane in the main area should look like the following figure. Note that for this figure, Bachman notation is used (you can change to Barker by clicking View, then Logical Diagram Notation, then Barker Notation).
In the logical model pane in the main area, arrange the entity boxes as follows: Books on the left, Patrons on the right, and Transactions either between Books and Patrons or under them and in the middle. (If the pointer is still cross-hairs, click the Select icon at the top left to change the pointer to an arrow.)
Suggestion: Turn off auto line routing for this exercise: right-click in the Logical pane, and ensure that Auto Route is not checked.
Click the New 1:N Relation icon.
Click first in the Books box, then in the Transactions box. A line with an arrowhead is drawn from Books to Transactions.
Click the New 1:N Relation icon.
Click first in the Patrons box, then in the Transactions box. A line with an arrowhead is drawn from Patrons to Transactions.
Optionally, double-click a line (or right-click a line and select Properties) and view the Relation Properties information.
2.2 Develop the Relational Model
The relational model for the library tutorial database consists of tables that reflect the entities of the logical model (Books, Patrons, and Transactions) and all attributes of each entity. In the simplified data model for this tutorial, a single relational model reflects the entire logical model; however, for other data models you can create one or more relational models, each reflecting all or a subset of the logical model. (To have a relational model reflect a subset of the logical model, use the «filter» feature in the dialog box for engineering a relational model.)
Develop the relational model as follows:
Expand the Relational Models node in the object browser on the left side of the window, and expand Relational_1 and optionally nodes under it that contain any entries (such as Tables and Columns), to view the objects created.
2.3 Generate DDL
Generate Data Definition Language (DDL) statements that you can use to create database objects that reflect the models that you have designed. The DDL statements will implement the physical model (type of database, such as Oracle Database 11 g ) that you specify.
Develop the physical model as follows:
Optionally, view the physical model before you generate DDL statements:
Expand the Physical Models node under the Library relational model, and expand the newly created physical model and the Tables node under it, to view the table objects that were created.
Later, run the script (for example, using a database connection and SQL Worksheet in SQL Developer) to create the objects in the desired database.
Click Close to close the DDL file editor.
2.4 Save the Design
You can later open the saved design and continue working on it, as explained in Section 1.6, «Saving, Opening, Exporting, and Importing Designs».
