Table design
Download
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


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


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


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


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


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