using er/studio. 13- 2 modeling a relational database using er studio we will use er studio as the...
TRANSCRIPT
![Page 1: Using ER/Studio. 13- 2 Modeling a Relational Database Using ER Studio We will use ER Studio as the modeling Tool to build ER diagrams. ER Studio Is a](https://reader030.vdocuments.us/reader030/viewer/2022032705/56649db25503460f94aa201e/html5/thumbnails/1.jpg)
Using ER/Studio
![Page 2: Using ER/Studio. 13- 2 Modeling a Relational Database Using ER Studio We will use ER Studio as the modeling Tool to build ER diagrams. ER Studio Is a](https://reader030.vdocuments.us/reader030/viewer/2022032705/56649db25503460f94aa201e/html5/thumbnails/2.jpg)
13- 2
Modeling a Relational DatabaseUsing ER Studio
• We will use ER Studio as the modeling Tool to build ER diagrams.
• ER Studio Is a CASE tool providing a Repository (or System Catalog) of an organization’s databases
• Fully specified logical models in ER studio can be used to generate DDL – the code of the create tables statements
![Page 3: Using ER/Studio. 13- 2 Modeling a Relational Database Using ER Studio We will use ER Studio as the modeling Tool to build ER diagrams. ER Studio Is a](https://reader030.vdocuments.us/reader030/viewer/2022032705/56649db25503460f94aa201e/html5/thumbnails/3.jpg)
Getting to ER Studio Data Architect
• Enter ERS in the Search are of the start menu• Click on the ER Studio Data Architect 9.6 icon• Click on tools in the top menu and then select
options from the drop-down menu
13- 3
![Page 4: Using ER/Studio. 13- 2 Modeling a Relational Database Using ER Studio We will use ER Studio as the modeling Tool to build ER diagrams. ER Studio Is a](https://reader030.vdocuments.us/reader030/viewer/2022032705/56649db25503460f94aa201e/html5/thumbnails/4.jpg)
Set Options to Produce Crow’s-Foot Notation
Pick options from tools drop-downPick
logical
Choose Crow’s feet notation
![Page 5: Using ER/Studio. 13- 2 Modeling a Relational Database Using ER Studio We will use ER Studio as the modeling Tool to build ER diagrams. ER Studio Is a](https://reader030.vdocuments.us/reader030/viewer/2022032705/56649db25503460f94aa201e/html5/thumbnails/5.jpg)
Create a New Model
• Select File from the top menu and new from the drop down
• On the dialog box, ensure that Draw a new data model is checked and that relational is selected, then click OK
13- 5
![Page 6: Using ER/Studio. 13- 2 Modeling a Relational Database Using ER Studio We will use ER Studio as the modeling Tool to build ER diagrams. ER Studio Is a](https://reader030.vdocuments.us/reader030/viewer/2022032705/56649db25503460f94aa201e/html5/thumbnails/6.jpg)
Select an Entity to Add
13- 6
![Page 7: Using ER/Studio. 13- 2 Modeling a Relational Database Using ER Studio We will use ER Studio as the modeling Tool to build ER diagrams. ER Studio Is a](https://reader030.vdocuments.us/reader030/viewer/2022032705/56649db25503460f94aa201e/html5/thumbnails/7.jpg)
Entity Editor
13- 7
Name the entitythen make surethat the Attributestab is selectedand click the Addbutton to add attributes
![Page 8: Using ER/Studio. 13- 2 Modeling a Relational Database Using ER Studio We will use ER Studio as the modeling Tool to build ER diagrams. ER Studio Is a](https://reader030.vdocuments.us/reader030/viewer/2022032705/56649db25503460f94aa201e/html5/thumbnails/8.jpg)
Adding Attributes
13- 8
For each attributespecify its name,select a datatypefrom the listbox,select the columnwidth and checkwhether nulls areto be allowed for the attribute.
Check this box if the attribute is the primary key (or a
part of the PK)
Can specify derived attributes and check this box
![Page 9: Using ER/Studio. 13- 2 Modeling a Relational Database Using ER Studio We will use ER Studio as the modeling Tool to build ER diagrams. ER Studio Is a](https://reader030.vdocuments.us/reader030/viewer/2022032705/56649db25503460f94aa201e/html5/thumbnails/9.jpg)
Other Attribute PropertiesAdd Default value using the Default tab• Simply add the default
value: e.g. a system variable as shown or a ‘Y’ for a flag variable, etc.
Add check constraints using the rule/constraint tab• Note that a constraint name can be
specified• Check Constraint Value is the full
condition to be applied by the Check constraint– The @var begins the constraint
condition with the attribute name– & we just finish out the condition
![Page 10: Using ER/Studio. 13- 2 Modeling a Relational Database Using ER Studio We will use ER Studio as the modeling Tool to build ER diagrams. ER Studio Is a](https://reader030.vdocuments.us/reader030/viewer/2022032705/56649db25503460f94aa201e/html5/thumbnails/10.jpg)
Adding Relationships by Type
13- 10
identifying
Non-identifyingMandatory
Non- identifyingOptional
![Page 11: Using ER/Studio. 13- 2 Modeling a Relational Database Using ER Studio We will use ER Studio as the modeling Tool to build ER diagrams. ER Studio Is a](https://reader030.vdocuments.us/reader030/viewer/2022032705/56649db25503460f94aa201e/html5/thumbnails/11.jpg)
Relationship Types
• Identifying – Strong relationship child entity is a weak entity – Primary key of 1st entity becomes a foreign key and part of the
primary key of 2nd entity clicked.• Non-identifying Mandatory
– Weak relationship child entity is a strong entity– Primary key of 1st entity specified becomes a foreign key in 2nd
entity and foreign key attribute is not null• Non-Identifying Optional
– Weak relationship child entity is strong– Primary key of 1st entity specified becomes a foreign key in 2nd
entity, but the foreign key attribute can be null.
![Page 12: Using ER/Studio. 13- 2 Modeling a Relational Database Using ER Studio We will use ER Studio as the modeling Tool to build ER diagrams. ER Studio Is a](https://reader030.vdocuments.us/reader030/viewer/2022032705/56649db25503460f94aa201e/html5/thumbnails/12.jpg)
Editing Relationship Properties
13- 12
Auto add of FK
![Page 13: Using ER/Studio. 13- 2 Modeling a Relational Database Using ER Studio We will use ER Studio as the modeling Tool to build ER diagrams. ER Studio Is a](https://reader030.vdocuments.us/reader030/viewer/2022032705/56649db25503460f94aa201e/html5/thumbnails/13.jpg)
Identifying Relationship Specification
For identifying relationships,
Primary key of parent is
automatically added to
primary key of the child table
To complete this specification
Phone_No should be added to the primary
key of PATRON_PHONE
![Page 14: Using ER/Studio. 13- 2 Modeling a Relational Database Using ER Studio We will use ER Studio as the modeling Tool to build ER diagrams. ER Studio Is a](https://reader030.vdocuments.us/reader030/viewer/2022032705/56649db25503460f94aa201e/html5/thumbnails/14.jpg)
Creating a Sub-Class Hierarchy
• First create the Super-Class and sub-Class entities– Add at least the primary key to the super-class entity
• From the insert Drop-down, select sub-type cluster, then – pick either complete or incomplete – Click on the super-class entity– Control-click on the first subclass entity & continue
until all sub-class have been linked in(see next slide for menu option, following slide for
result.)
![Page 15: Using ER/Studio. 13- 2 Modeling a Relational Database Using ER Studio We will use ER Studio as the modeling Tool to build ER diagrams. ER Studio Is a](https://reader030.vdocuments.us/reader030/viewer/2022032705/56649db25503460f94aa201e/html5/thumbnails/15.jpg)
Choose complete (all super-type members must belong to a sub-type, or incomplete
![Page 16: Using ER/Studio. 13- 2 Modeling a Relational Database Using ER Studio We will use ER Studio as the modeling Tool to build ER diagrams. ER Studio Is a](https://reader030.vdocuments.us/reader030/viewer/2022032705/56649db25503460f94aa201e/html5/thumbnails/16.jpg)
Right click & pick edit subtype
cluster to get this Dialog box
![Page 17: Using ER/Studio. 13- 2 Modeling a Relational Database Using ER Studio We will use ER Studio as the modeling Tool to build ER diagrams. ER Studio Is a](https://reader030.vdocuments.us/reader030/viewer/2022032705/56649db25503460f94aa201e/html5/thumbnails/17.jpg)
Saving the Model
13- 17
Change to desired J drive space
![Page 18: Using ER/Studio. 13- 2 Modeling a Relational Database Using ER Studio We will use ER Studio as the modeling Tool to build ER diagrams. ER Studio Is a](https://reader030.vdocuments.us/reader030/viewer/2022032705/56649db25503460f94aa201e/html5/thumbnails/18.jpg)
Generating the Physical Model
13- 18
![Page 19: Using ER/Studio. 13- 2 Modeling a Relational Database Using ER Studio We will use ER Studio as the modeling Tool to build ER diagrams. ER Studio Is a](https://reader030.vdocuments.us/reader030/viewer/2022032705/56649db25503460f94aa201e/html5/thumbnails/19.jpg)
Physical Model for Oracle
13- 19
![Page 20: Using ER/Studio. 13- 2 Modeling a Relational Database Using ER Studio We will use ER Studio as the modeling Tool to build ER diagrams. ER Studio Is a](https://reader030.vdocuments.us/reader030/viewer/2022032705/56649db25503460f94aa201e/html5/thumbnails/20.jpg)
Generated SQL for Data Definition Language
13- 20
-- -- TABLE: ormCustomer --CREATE TABLE ormCustomer( custnumber NUMBER(10, 0) NOT NULL, name VARCHAR2(20), CONSTRAINT PK1 PRIMARY KEY (custnumber));-- -- TABLE: ormOrder --CREATE TABLE ormOrder( ordernumber NUMBER(10, 0) NOT NULL, orderdate DATE NOT NULL, total NUMBER(10, 0), custnumber NUMBER(10, 0) NOT NULL, CONSTRAINT PK2 PRIMARY KEY (ordernumber));-- -- TABLE: ormOrder --ALTER TABLE ormOrder ADD CONSTRAINT ReformCustomer31 FOREIGN KEY (custnumber) REFERENCES ormCustomer(custnumber);