Transcript No Slide Title

Informatiesystemen in de Bouw
7M711
Week 3
Joran Jessurun en Jos van Leeuwen
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)
What makes a database
•
•
•
•
•
Add Data
Delete Data
Change Data
Lookup or search for data
Organize Data
Key Terminology
•
•
•
•
Table
Column
Record
Field
• Primary Key
• Foreign Key
Types of databases
• Flat databases
• Index databases
• DBMS based databases
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)
DBMS Flavors
• From relational
• To object oriented
DBMS Systems
•
•
•
•
Oracle
Sybase
Microsoft SQL Server
MySQL
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
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
UML Profile for Database
Design
• Rational Software Corporation
• For designing databases
• Uses tagged parameters and
stereotypes
• Only a subset will be explained
Database Diagram
Elements
•
•
•
•
•
•
Table
Column
Primary key
Foreign key
Identifying relationship
Non-identifying relationship
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
Example 1
Mapping Logical Design
To Database Design
• Synchronization
•
•
•
•
•
Classes > Tables
Attributes > Columns
Associations > Relations
Normalization
There are more ways to
do it
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.
«table»
Class1
«pk» +Class1ID
«table»
Class2
«pk» +Class2ID
1
1
*
«table»
Association Class
«pk/fk» +Class1ID
«pk/fk» +Class2ID
*
Mapping Subtype Classes
to Tables
• One table
per class
• One table
per
concrete
class
• One table
per
hierarchy
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
Example 2
Database Desktop
• Create and fill tables
• Execute database queries
• Build on BDE
Database Desktop
Example
«table»
Book
«pk» +ISBN
+Title
+Description
*
1
«table»
LibraryBook
«pk» +BookID
«fk» +ISBN
+Count
1
*
«table»
Client
«pk» +ClientID
+Name
+Adres
1
*
«table»
BookClient
«pk/fk» +ClientID
«pk/fk» +BookID
+Lend
+Days