1 / 18

Table Design

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.

brooklyn
Download Presentation

Table Design

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Table Design Mapping from Class Diagram (Domain Model) to Relational Model FEN 2014-03-23

  2. 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

  3. 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

  4. 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

  5. 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

  6. 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

  7. 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

  8. 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

  9. 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

  10. 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

  11. 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

  12. 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

  13. 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

  14. 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

  15. 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

  16. 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

  17. 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

  18. 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

More Related