www.ddss.arch.tue.nl 7m822 uml profile for databases 11 november 2010
TRANSCRIPT
![Page 1: Www.ddss.arch.tue.nl 7M822 UML Profile for Databases 11 November 2010](https://reader031.vdocuments.us/reader031/viewer/2022013114/5519b5d85503467a578b47c8/html5/thumbnails/1.jpg)
www.ddss.arch.tue.nl
7M822
UML Profile for Databases
11 November 2010
![Page 2: Www.ddss.arch.tue.nl 7M822 UML Profile for Databases 11 November 2010](https://reader031.vdocuments.us/reader031/viewer/2022013114/5519b5d85503467a578b47c8/html5/thumbnails/2.jpg)
www.ddss.arch.tue.nl
7M822
DatabaseDatabase
• A database is a collection of data.• Databases can be stored in one or more files, or can be
managed by a software system called Database Management System (DBMS)
![Page 3: Www.ddss.arch.tue.nl 7M822 UML Profile for Databases 11 November 2010](https://reader031.vdocuments.us/reader031/viewer/2022013114/5519b5d85503467a578b47c8/html5/thumbnails/3.jpg)
www.ddss.arch.tue.nl
7M822
What makes a databaseWhat makes a database
• Add Data• Delete Data• Change Data• Lookup or search for data• Organize Data
![Page 4: Www.ddss.arch.tue.nl 7M822 UML Profile for Databases 11 November 2010](https://reader031.vdocuments.us/reader031/viewer/2022013114/5519b5d85503467a578b47c8/html5/thumbnails/4.jpg)
www.ddss.arch.tue.nl
7M822
Key TerminologyKey Terminology
• Table• Column• Record• Field
• Primary Key• Foreign Key• Index
![Page 5: Www.ddss.arch.tue.nl 7M822 UML Profile for Databases 11 November 2010](https://reader031.vdocuments.us/reader031/viewer/2022013114/5519b5d85503467a578b47c8/html5/thumbnails/5.jpg)
www.ddss.arch.tue.nl
7M822
Database Example (1)Database Example (1)
Name Adres Number Course Grade
Joran VRT 9.13 146432 7m822 7
Joran VRT 9.13 146432 7m701 8
Joran VRT 9.13 146432 7m711 6
Jan VRT 9.12 693731 7m822 8
Jan VRT 9.12 693731 7m701 7
Grades
![Page 6: Www.ddss.arch.tue.nl 7M822 UML Profile for Databases 11 November 2010](https://reader031.vdocuments.us/reader031/viewer/2022013114/5519b5d85503467a578b47c8/html5/thumbnails/6.jpg)
www.ddss.arch.tue.nl
7M822
Database Example (2)Database Example (2)
Name Adres Number
Joran VRT 9.13 146432
Jan VRT 9.12 693731
Number Course Grade
146432 7m822 7
146432 7m701 8
146432 7m711 6
693731 7m822 8
693731 7m701 7
Student
Grade
![Page 7: Www.ddss.arch.tue.nl 7M822 UML Profile for Databases 11 November 2010](https://reader031.vdocuments.us/reader031/viewer/2022013114/5519b5d85503467a578b47c8/html5/thumbnails/7.jpg)
www.ddss.arch.tue.nl
7M822
Types of databasesTypes of databases
• Flat databases• Indexed databases• DBMS based databases
![Page 8: Www.ddss.arch.tue.nl 7M822 UML Profile for Databases 11 November 2010](https://reader031.vdocuments.us/reader031/viewer/2022013114/5519b5d85503467a578b47c8/html5/thumbnails/8.jpg)
www.ddss.arch.tue.nl
7M822
DBMS ServicesDBMS Services
• Schemas• Consistency checks• Guarantee of no data corruption after crash• Concurrent access for multiple readers and
writers.• Backup and recovery• Authentication and access control• Support for Structured Query Language (SQL)
![Page 9: Www.ddss.arch.tue.nl 7M822 UML Profile for Databases 11 November 2010](https://reader031.vdocuments.us/reader031/viewer/2022013114/5519b5d85503467a578b47c8/html5/thumbnails/9.jpg)
www.ddss.arch.tue.nl
7M822
DBMS FlavorsDBMS Flavors
• From relational• To object oriented
![Page 10: Www.ddss.arch.tue.nl 7M822 UML Profile for Databases 11 November 2010](https://reader031.vdocuments.us/reader031/viewer/2022013114/5519b5d85503467a578b47c8/html5/thumbnails/10.jpg)
www.ddss.arch.tue.nl
7M822
DBMS SystemsDBMS Systems
• Oracle• Sybase• Microsoft SQL Server• MySQL
![Page 11: Www.ddss.arch.tue.nl 7M822 UML Profile for Databases 11 November 2010](https://reader031.vdocuments.us/reader031/viewer/2022013114/5519b5d85503467a578b47c8/html5/thumbnails/11.jpg)
www.ddss.arch.tue.nl
7M822
Jet Database EngineJet Database Engine
• Database Engine of MS-Access• Contained in an *.MDB or *.ACCDB File• Accessed trough ADO or DAO• Allows multiuser access• Enforces referential integrity checks
![Page 12: Www.ddss.arch.tue.nl 7M822 UML Profile for Databases 11 November 2010](https://reader031.vdocuments.us/reader031/viewer/2022013114/5519b5d85503467a578b47c8/html5/thumbnails/12.jpg)
www.ddss.arch.tue.nl
7M822
UML ProfilesUML Profiles
• Way to customize UML diagrams for your own needs.• Stereotypes• Tagged value• Constraints• Graphical representation
![Page 13: Www.ddss.arch.tue.nl 7M822 UML Profile for Databases 11 November 2010](https://reader031.vdocuments.us/reader031/viewer/2022013114/5519b5d85503467a578b47c8/html5/thumbnails/13.jpg)
www.ddss.arch.tue.nl
7M822
UML Profile ExampleUML Profile Example
«pk» -attribute1-attribute2
«table»Class1
{author = Joran,version = 1.0}
![Page 14: Www.ddss.arch.tue.nl 7M822 UML Profile for Databases 11 November 2010](https://reader031.vdocuments.us/reader031/viewer/2022013114/5519b5d85503467a578b47c8/html5/thumbnails/14.jpg)
www.ddss.arch.tue.nl
7M822
UML Profile for Database DesignUML Profile for Database Design
• Rational Software Corporation• For designing databases• Uses tagged parameters and stereotypes• Only a subset will be explained
![Page 15: Www.ddss.arch.tue.nl 7M822 UML Profile for Databases 11 November 2010](https://reader031.vdocuments.us/reader031/viewer/2022013114/5519b5d85503467a578b47c8/html5/thumbnails/15.jpg)
www.ddss.arch.tue.nl
7M822
Database Diagram ElementsDatabase Diagram Elements
• Table• Column• Primary key• Foreign key• Identifying relationship• Non-identifying relationship
![Page 16: Www.ddss.arch.tue.nl 7M822 UML Profile for Databases 11 November 2010](https://reader031.vdocuments.us/reader031/viewer/2022013114/5519b5d85503467a578b47c8/html5/thumbnails/16.jpg)
www.ddss.arch.tue.nl
7M822
Database Diagram Elements (2)Database Diagram Elements (2)
Table (stereotype <<table>>)
Not used
Not used
Primary key (stereotype <<pk>>)
Foreign key (stereotype <<fk>>)
Primary/Foreign key (stereotype <<pk/fk>>)
Non-identifying relationship
Identifying relationship
![Page 17: Www.ddss.arch.tue.nl 7M822 UML Profile for Databases 11 November 2010](https://reader031.vdocuments.us/reader031/viewer/2022013114/5519b5d85503467a578b47c8/html5/thumbnails/17.jpg)
www.ddss.arch.tue.nl
7M822
Example 1Example 1
![Page 18: Www.ddss.arch.tue.nl 7M822 UML Profile for Databases 11 November 2010](https://reader031.vdocuments.us/reader031/viewer/2022013114/5519b5d85503467a578b47c8/html5/thumbnails/18.jpg)
www.ddss.arch.tue.nl
7M822
Mapping Logical Design To Database DesignMapping Logical Design To Database Design
• Synchronization• Classes > Tables• Attributes > Columns• Associations > Relations• Normalization• There are more ways to
do it
![Page 19: Www.ddss.arch.tue.nl 7M822 UML Profile for Databases 11 November 2010](https://reader031.vdocuments.us/reader031/viewer/2022013114/5519b5d85503467a578b47c8/html5/thumbnails/19.jpg)
www.ddss.arch.tue.nl
7M822
Mapping Classes To TablesMapping Classes To Tables
• Map persisted classes to tables• Many to many associations must be broken down to one to
many associations using an association table.
«pk» +Class1ID
«table»Class1
«pk» +Class2ID
«table»Class2
«pk/fk» +Class1ID«pk/fk» +Class2ID
«table»Association Class
1
*
1
*
![Page 20: Www.ddss.arch.tue.nl 7M822 UML Profile for Databases 11 November 2010](https://reader031.vdocuments.us/reader031/viewer/2022013114/5519b5d85503467a578b47c8/html5/thumbnails/20.jpg)
www.ddss.arch.tue.nl
7M822
Mapping Subtype Classes to TablesMapping Subtype Classes to Tables
• One table per class
• One table per concrete class
• One table per hierarchy
![Page 21: Www.ddss.arch.tue.nl 7M822 UML Profile for Databases 11 November 2010](https://reader031.vdocuments.us/reader031/viewer/2022013114/5519b5d85503467a578b47c8/html5/thumbnails/21.jpg)
www.ddss.arch.tue.nl
7M822
Mapping Attributes to ColumnsMapping Attributes to Columns
• Map persistent attributes• Don’t map calculated attributes• Can use Generic Types first, later use database
specific types.
Generic Types: Boolean, Currency, Date, Double, Integer, Long, Single, String
![Page 22: Www.ddss.arch.tue.nl 7M822 UML Profile for Databases 11 November 2010](https://reader031.vdocuments.us/reader031/viewer/2022013114/5519b5d85503467a578b47c8/html5/thumbnails/22.jpg)
www.ddss.arch.tue.nl
7M822
Example 2Example 2
![Page 23: Www.ddss.arch.tue.nl 7M822 UML Profile for Databases 11 November 2010](https://reader031.vdocuments.us/reader031/viewer/2022013114/5519b5d85503467a578b47c8/html5/thumbnails/23.jpg)
www.ddss.arch.tue.nl
7M822
Example 3 – Logical DiagramExample 3 – Logical Diagram
-Name-Architect-BuildingYear
Building
-Function
Space-Price-Type
Building Element
-Name-Address
Person
1
*
1
*
* *
0..1 *
![Page 24: Www.ddss.arch.tue.nl 7M822 UML Profile for Databases 11 November 2010](https://reader031.vdocuments.us/reader031/viewer/2022013114/5519b5d85503467a578b47c8/html5/thumbnails/24.jpg)
www.ddss.arch.tue.nl
7M822
Example 3 – Database DiagramExample 3 – Database Diagram
-Name-Architect-BuildingYear«pk» -BuildingID
«table»Building
-Function«pk» -SpaceID«pk/fk» -BuildingID
«table»Space
-Price-Type«pk» -ElementID«pk/fk» -BuildingID
«table»Building Element
-Name-Address«pk» -PersonID«fk» -BuildingID
«table»Person
1
*1 *
0..1 *
«pk/fk» -SpaceID«pk/fk» -ElementID«pk/fk» -BuildingID
SpaceElement
1 *
1*
![Page 25: Www.ddss.arch.tue.nl 7M822 UML Profile for Databases 11 November 2010](https://reader031.vdocuments.us/reader031/viewer/2022013114/5519b5d85503467a578b47c8/html5/thumbnails/25.jpg)
www.ddss.arch.tue.nl
7M822
Microsoft AccessMicrosoft Access
• Create and fill tables• Execute database queries• Build on the Jet Engine• Visual Basic for Applications• Forms and Reports• IDE
![Page 26: Www.ddss.arch.tue.nl 7M822 UML Profile for Databases 11 November 2010](https://reader031.vdocuments.us/reader031/viewer/2022013114/5519b5d85503467a578b47c8/html5/thumbnails/26.jpg)
www.ddss.arch.tue.nl
7M822
Database Desktop Example (1)Database Desktop Example (1)
+ISBN+Title+Description
Book
+Count
Library Book
+Name+Address
Client
+Lend+Days
BookClient*
*
1 0..1
![Page 27: Www.ddss.arch.tue.nl 7M822 UML Profile for Databases 11 November 2010](https://reader031.vdocuments.us/reader031/viewer/2022013114/5519b5d85503467a578b47c8/html5/thumbnails/27.jpg)
www.ddss.arch.tue.nl
7M822
Database Desktop Example (2)Database Desktop Example (2)
«table»Book
«pk» +ISBN+Title+Description
«table»LibraryBook
«pk» +BookID«fk» +ISBN+Count
«table»Client
«pk» +ClientID+Name+Adres
«table»BookClient
«pk/fk» +ClientID«pk/fk» +BookID+Lend+Days
1
*
1 *
1 *0..1