table design
Download
Skip this Video
Download Presentation
Table Design

Loading in 2 Seconds...

play fullscreen
1 / 18

Table Design - PowerPoint PPT Presentation


  • 47 Views
  • Uploaded on

Table Design. Mapping from Class Diagram (Domain Model) to Relational Model. Implementation Strategy. How are objects stored in a relational database? We need to store: Identity State Class Structures: generalisation association aggregation. prgVar1. prgVar2.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Table Design' - brooklyn


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
table design

Table Design

Mapping from Class Diagram (Domain Model) to Relational Model

FEN 2014-03-23

implementation strategy
ImplementationStrategy
  • How are objects stored in a relational database?
  • We need to store:
    • Identity
    • State
    • Class
    • Structures:
      • generalisation
      • association
      • aggregation

prgVar1

prgVar2

FEN 2014-03-23

mapping identity state and class
Mapping: Identity, State (and Class)
  • Each class is mapped onto a table (relation):
    • Class name is used as table name
    • All simple-value attributes are transferred to the table (a column)
    • A primary key (id) is added to the table
    • For each attribute consider:
      • Domain (type)
      • NULLs
      • Uniqueness
    • For composite attributes only the components are added
    • For list attributes (multi value) a new table holding the primary key and the value of the list attribute is created

FEN 2014-03-23

slide4

Mapping: Structure

  • The structures can cause new attributes
  • Semantic of the class diagram must be preserved
  • There are more than one possible mapping for a structure
  • Mapping object-relationships:
    • Associations and aggregations:
      • One-to-one
      • One-to-many
      • Many-to-many
  • Class structure:
    • Inheritance:
      • Three different solutions

FEN 2014-03-23

structure many to many or n m

CustomerIDCPR Name Address

  • 1 010155-2321 Jens Andersen Søndergade 6
  • 2 101289-7566 Oda Nielsen Algade 99
  • 1251 060967-2390 Pia Schrøder Bispensgade 27
  • CustomerId AccountID
  • 1 2
  • 1 4
  • 2 1
  • 2 2
  • 2 4
  • 3 3
  • 4 2
  • 4 1251
  • 5 1251
  • 256 25
  • AccountIdAccountno Balance type
  • 1 615-6789 280295 checkkonto
  • 2 931-1453 311294 lån
  • 256 112-7290 120395 checkkonto
Structure: many-to-many (*..* or n:m)

New table with the two primary keys as foreign keys

CustomerAccount

Customer

Customer

CPR

Name

Address

1:m

0:m

Account

Account

AccountNo

Balance

Type

FEN 2014-03-23

structure one to many 1 or 1 n
Structure: one-to-many (1..* or 1:n)

Two alternatives

1. As many to many.

2. The key from the one-side is added as an attribute to the many-side as a foreign key

Customer

CPR

Name

Address

1

Account

1:m

Account

AccountNo

Balance

Date

  • AccountID AccountNo Balance Type CustomerId
  • 1 615-6789 280295 checkkonto 2
  • 2 931-1453 311294 lån 2
  • 1251 112-7290 120395 checkkonto 5

FEN 2014-03-23

structure one to one 1 1 or 1 1
Structure: one-to-one (1..1 or 1:1)
  • Two alternatives
    • 1. As many to many.
    • 2. The key from one of the sides is added as an attribute to the other side as a foreign key.

Customer

CPR

Name

Address

1

Account

1

Account

AccountNo

Balance

Date

  • AccountID AccountNo Balance Type CustomerId
  • 1 615-6789 280295 checkkonto 2
  • 2 931-1453 311294 lån 2
  • 1251 112-7290 120395 checkkonto 5

FEN 2014-03-23

structure one to one 1 1 or 1 11

Customer

  • CusID CPR name addresse accountId
  • 1 010155-2321 Jens Andersen Søndergade 6 2
  • 2 101289-7566 Oda Nielsen Algade 99 1
  • 1251 060967-2390 Pia Schrøder Bispensgade 27 1251
Structure: one-to-one (1..1 or 1:1)

The key from one of the sides is

added as an attribute to the other

side as a foreign key.

But at which side?

Customer

CPR

Name

Address

1

1

Account

AccountNo

Balance

Date

FEN 2014-03-23

structure one to one 1 1 or 1 12

1

1

Person

Car

Structure: one-to-one (1..1 or 1:1)

On which side should the foreign key be included?

  • Choose the side that minimises NULL values

Customer

CPR

Name

Address

1

1

Account

AccountNo

Balance

Date

On which side should the foreign key be included?

FEN 2014-03-23

structure one to one 1 1 or 1 13

1

1

Person

Car

Structure: one-to-one (1..1 or 1:1)

Minimise NULL values:

- are most cars assigned to a specific person?

- do most persons have car to their disposal?

What if the company is a car rental company?

On which side should the foreign key be included?

FEN 2014-03-23

mapping structure

1

1

Person

Car

1

*

Person

Car

*

*

Person

Car

Mapping: Structure
  • Three kinds multiplicities:
    • One-to-one (1-1):
      • One person is associated with one car
    • One-to-many (1-n):
      • One person may be associated with several cars, but one car is only associated with one person
    • Many-to-many (n-m):
      • One person may be associated with several cars, and one car may be associated with many persons

FEN 2014-03-23

mapping object structure

1

1

Person

Car

1

*

Person

Car

*

*

Person

Car

Mapping: Object Structure
  • Three kinds multiplicities:
    • One-to-one (1-1):
      • Include the primary key from one of the sides on the other side (minimise NULLs).
    • One-to-many (1-n):
      • Include the primary key from the one-side on the many-side.
    • Many-to-many (n-m):
      • Create a new table with the primary keys from both sides as foreign keys. The combination of the to foreign keys becomes primary key in the new table.

FEN 2014-03-23

slide13

Mapping: Class Structure –

Inheritance

Three Approaches for Transformation

  • Create a table for the super-class and a table for each subclass (“The Nice Way”).
  • Create tables only for the subclasses (“Pull-Down”).
  • Create a table only for the super-class (“Pull-Up”).

FEN 2014-03-23

slide14

Mapping: Class Structure –

Inheritance (1: “The Nice Way”)

  • accID accNo date accType
  • 1 615-6789 280295 checkAcc
  • 2 931-1453 311294 loan
  • 256 112-7290 120395 checkAcc
  • accID intRate nextBook
  • 1 0,1 100395
  • 256 0,5 221294
  • accID amount payment dateForPay
  • 2 25000 2500 30

Account

Loan

CheckAccount

  • Conceptually attractive. Clear and simple, traceability is good, and the design may always be used.
  • Easy to maintain.
  • Accessing objects requires joins.

FEN 2014-03-23

slide15

Mapping: Class Structure –

Inheritance (2: “Pull-Down”))

CheckAccount

  • accD accNo date intRate nextBook
  • 1 615-6789 280295 0,1 100395
  • 256 112-7290 120395 0,5 221294
  • No table for the super-class.
  • Easy to retrieve all information if the type is known.
  • Works best, if there are few attributes in the super-class.
  • Does not work, if objects may belong to more than one subclass, or if there exists objects of the superclass
  • Code duplication

FEN 2014-03-23

slide16

Mapping: Class Structure –

Inheritance (3: “Pull-Up”))

  • No tables for subclasses.
  • Easy access.
  • Works best if there are few attributes in the subclasses.
  • Problems with null values

FEN 2014-03-23

summary domain model mapping to rdb
Summary: Domain Model Mapping to RDB

Each domain class is mapped to a table:

  • Attributes, keys, data types, NULLs?
  • Primary key is added (or chosen).

Associations and aggregations are represented by foreign key references:

  • 1-1: Include the primary key of the one-side as foreign key on the other side (minimise NULLs).
  • 1-n: Include the primary key of the one-side as foreign key on the n-side.
  • n-m: Create a new table with both sides primary key as foreign keys.

Generalisation: choose one of these designs:

  • Each class (both super- and subclasses) is mapped to a table. The general and special parts of an object are connected by the same primary key. The superclass may have added a type flag (preferable, but may be expensive in joins).
  • Only subclasses are mapped to tables that include the attributes from the superclass (no overlap allowed!).
  • Only the superclass is mapped to a table that includes a type flag and all attributes from the subclasses. All attributes from irrelevant subclasses are NULL (may cause many NULLs).

FEN 2014-03-23

exercise
Exercise
  • Design a database for alibrary with this domain model. I.e. Define relational table schemas.
  • Add some relevant attributes, consider if nulls are allowed.
  • On the association Reservation there are some attributes (date, the state , etc.).

FEN 2014-03-23

ad