Informatiesystemen in de Bouw 7M711 Joran Jessurun en Jos van Leeuwen Week 3.
-
date post
20-Dec-2015 -
Category
Documents
-
view
213 -
download
0
Transcript of Informatiesystemen in de Bouw 7M711 Joran Jessurun en Jos van Leeuwen Week 3.
![Page 1: Informatiesystemen in de Bouw 7M711 Joran Jessurun en Jos van Leeuwen Week 3.](https://reader030.fdocument.pub/reader030/viewer/2022032800/56649d4d5503460f94a2bdda/html5/thumbnails/1.jpg)
Informatiesystemen in de Bouw7M711
Joran Jessurun en Jos van Leeuwen
Week 3
![Page 2: Informatiesystemen in de Bouw 7M711 Joran Jessurun en Jos van Leeuwen Week 3.](https://reader030.fdocument.pub/reader030/viewer/2022032800/56649d4d5503460f94a2bdda/html5/thumbnails/2.jpg)
Database
• 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: Informatiesystemen in de Bouw 7M711 Joran Jessurun en Jos van Leeuwen Week 3.](https://reader030.fdocument.pub/reader030/viewer/2022032800/56649d4d5503460f94a2bdda/html5/thumbnails/3.jpg)
What makes a database
• Add Data
• Delete Data
• Change Data
• Lookup or search for data
• Organize Data
![Page 4: Informatiesystemen in de Bouw 7M711 Joran Jessurun en Jos van Leeuwen Week 3.](https://reader030.fdocument.pub/reader030/viewer/2022032800/56649d4d5503460f94a2bdda/html5/thumbnails/4.jpg)
Key Terminology
• Table• Column• Record• Field
• Primary Key• Foreign Key
![Page 5: Informatiesystemen in de Bouw 7M711 Joran Jessurun en Jos van Leeuwen Week 3.](https://reader030.fdocument.pub/reader030/viewer/2022032800/56649d4d5503460f94a2bdda/html5/thumbnails/5.jpg)
Types of databases
• Flat databases
• Index databases
• DBMS based databases
![Page 6: Informatiesystemen in de Bouw 7M711 Joran Jessurun en Jos van Leeuwen Week 3.](https://reader030.fdocument.pub/reader030/viewer/2022032800/56649d4d5503460f94a2bdda/html5/thumbnails/6.jpg)
DBMS 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 7: Informatiesystemen in de Bouw 7M711 Joran Jessurun en Jos van Leeuwen Week 3.](https://reader030.fdocument.pub/reader030/viewer/2022032800/56649d4d5503460f94a2bdda/html5/thumbnails/7.jpg)
DBMS Flavors
• From relational
• To object oriented
![Page 8: Informatiesystemen in de Bouw 7M711 Joran Jessurun en Jos van Leeuwen Week 3.](https://reader030.fdocument.pub/reader030/viewer/2022032800/56649d4d5503460f94a2bdda/html5/thumbnails/8.jpg)
DBMS Systems
• Oracle
• Sybase
• Microsoft SQL Server
• MySQL
![Page 9: Informatiesystemen in de Bouw 7M711 Joran Jessurun en Jos van Leeuwen Week 3.](https://reader030.fdocument.pub/reader030/viewer/2022032800/56649d4d5503460f94a2bdda/html5/thumbnails/9.jpg)
Paradox
• Indexed Database
• Every table is contained in a *.DB file
• The primary key is indexed in a *.PX file
• Accessed trough the Borland Database Engine
![Page 10: Informatiesystemen in de Bouw 7M711 Joran Jessurun en Jos van Leeuwen Week 3.](https://reader030.fdocument.pub/reader030/viewer/2022032800/56649d4d5503460f94a2bdda/html5/thumbnails/10.jpg)
Borland Database Engine
• Database independent
• Adds services for flat and indexed database access
• Used by Delphi
• Makes use of Aliases
• Like Microsoft’s ADO components
![Page 11: Informatiesystemen in de Bouw 7M711 Joran Jessurun en Jos van Leeuwen Week 3.](https://reader030.fdocument.pub/reader030/viewer/2022032800/56649d4d5503460f94a2bdda/html5/thumbnails/11.jpg)
UML Profile for Database Design
• Rational Software Corporation
• For designing databases
• Uses tagged parameters and stereotypes
• Only a subset will be explained
![Page 12: Informatiesystemen in de Bouw 7M711 Joran Jessurun en Jos van Leeuwen Week 3.](https://reader030.fdocument.pub/reader030/viewer/2022032800/56649d4d5503460f94a2bdda/html5/thumbnails/12.jpg)
Database Diagram Elements
• Table
• Column
• Primary key
• Foreign key
• Identifying relationship
• Non-identifying relationship
![Page 13: Informatiesystemen in de Bouw 7M711 Joran Jessurun en Jos van Leeuwen Week 3.](https://reader030.fdocument.pub/reader030/viewer/2022032800/56649d4d5503460f94a2bdda/html5/thumbnails/13.jpg)
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 14: Informatiesystemen in de Bouw 7M711 Joran Jessurun en Jos van Leeuwen Week 3.](https://reader030.fdocument.pub/reader030/viewer/2022032800/56649d4d5503460f94a2bdda/html5/thumbnails/14.jpg)
Example 1
![Page 15: Informatiesystemen in de Bouw 7M711 Joran Jessurun en Jos van Leeuwen Week 3.](https://reader030.fdocument.pub/reader030/viewer/2022032800/56649d4d5503460f94a2bdda/html5/thumbnails/15.jpg)
Mapping Logical Design To Database Design
• Synchronization• Classes > Tables• Attributes > Columns• Associations > Relations• Normalization• There are more ways to
do it
![Page 16: Informatiesystemen in de Bouw 7M711 Joran Jessurun en Jos van Leeuwen Week 3.](https://reader030.fdocument.pub/reader030/viewer/2022032800/56649d4d5503460f94a2bdda/html5/thumbnails/16.jpg)
Mapping 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 17: Informatiesystemen in de Bouw 7M711 Joran Jessurun en Jos van Leeuwen Week 3.](https://reader030.fdocument.pub/reader030/viewer/2022032800/56649d4d5503460f94a2bdda/html5/thumbnails/17.jpg)
Mapping Subtype Classes to Tables
• One table per class
• One table per concrete class
• One table per hierarchy
![Page 18: Informatiesystemen in de Bouw 7M711 Joran Jessurun en Jos van Leeuwen Week 3.](https://reader030.fdocument.pub/reader030/viewer/2022032800/56649d4d5503460f94a2bdda/html5/thumbnails/18.jpg)
Mapping 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 19: Informatiesystemen in de Bouw 7M711 Joran Jessurun en Jos van Leeuwen Week 3.](https://reader030.fdocument.pub/reader030/viewer/2022032800/56649d4d5503460f94a2bdda/html5/thumbnails/19.jpg)
Example 2
![Page 20: Informatiesystemen in de Bouw 7M711 Joran Jessurun en Jos van Leeuwen Week 3.](https://reader030.fdocument.pub/reader030/viewer/2022032800/56649d4d5503460f94a2bdda/html5/thumbnails/20.jpg)
Database Desktop
• Create and fill tables
• Execute database queries
• Build on BDE
![Page 21: Informatiesystemen in de Bouw 7M711 Joran Jessurun en Jos van Leeuwen Week 3.](https://reader030.fdocument.pub/reader030/viewer/2022032800/56649d4d5503460f94a2bdda/html5/thumbnails/21.jpg)
Database Desktop Example
«pk» +ISBN+Title+Description
«table»Book
«pk» +BookID«fk» +ISBN+Count
«table»LibraryBook
«pk» +ClientID+Name+Adres
«table»Client
«pk/fk» +ClientID«pk/fk» +BookID+Lend+Days
«table»BookClient
1
*
1 *
1 *