1 / 6

Basic Schema Generation

Basic Schema Generation. Generate a schema for each entity set The attributes are the attributes of the entity set. The keys are the candidate keys of the entity set; choose a primary key. For each relationship set, adjust or add a schema.

alexander-roth
Télécharger la présentation

Basic Schema Generation

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. Basic Schema Generation • Generate a schema for each entity set • The attributes are the attributes of the entity set. • The keys are the candidate keys of the entity set; choose a primary key. • For each relationship set, adjust or add a schema. • 1-1: merge schemas; add any relationship attributes; select a primary key from among the primary keys of the merged schemas. • 1-m, m-1: to the schema on the many-side, add the primary-key attribute(s) of the one-side; add any relationship attributes. • m-m: make a new schema from the primary-key attributes of the connected entity sets and any relationship attributes; the key is the composite of the primary-key attributes if there are no key relationship attributes—otherwise semantically determine the key.

  2. Schema Generation – Example Generated schemas, with candidate keys underlined and primary keys double underlined: Room(RoomNr, Name, NrBeds, Cost) Guest(GuestNr, Name, StreetNr, City) Reservation(GuestNr, RoomNr, ArrivalDate, NrDays)

  3. SQL DDL create table Room( RoomNr integer primary key, Name char(20) unique, NrBeds integer, Cost float ); create table Guest( GuestNr integer primary key, Name char(20), StreetNr char(20), City char(15), unique (Name, StreetNr, City) ); create table Reservation( GuestNr integer references Guest, RoomNr integer references Room, ArrivalDate char(6), NrDays integer, primary key (RoomNr, ArrivalDate) );

  4. Sample Database Instance r = Room(RoomNr Name NrBeds Cost) ------------------------------------------ 1 Kennedy 2 90 2 Nixon 2 80 3 Carter 2 80 4 Blue 1 60 5 Green 1 50 g = Guest(GuestNr Name StreetNr City) --------------------------------------------------- 101 Smith 12 Maple Boston 102 Carter 10 Main Hartford 103 Jones 6 Elm Hartford 104 Smith 4 Oak Providence 105 Green 10 Main Boston 106 Johnson 15 Main Boston s = Reservation(GuestNr RoomNr ArrivalDate NrDays) ------------------------------------------------------ 101 1 10 May 2 101 2 20 May 1 101 3 15 May 2 102 3 10 May 5 103 1 12 May 3 104 4 10 May 2 104 4 17 May 2 104 4 24 May 2 105 1 15 May 7 106 2 11 May 2

  5. Another Schema Generation Example (note the use of roles)

  6. Basic Schema Generation for ISA Based on meaning of ISA: Collapse ISA and treat attributes as nullable attributes of the generalization: Room(RoomNr, Name, Cost, NrBeds, CompletionDate?) Generate table for specialization with the key of its generalization: RoomUnderConstruction(RoomNr, CompletionDate)

More Related