Logical Schema Design: Relational Data Model, Exams of Design

Download Logical Schema Design: Relational Data Model and more Exams Design in PDF only on Docsity!

Logical Schema Design:Logical Schema Design:The Relational Data ModelThe Relational Data ModelBasics of the Relational ModelFrom Conceptual to Logical Schema

Logical Schema DesignLogical Schema Design  FU-Berlin, DBS I 2006, Hinze / Scholz

Select data model^ ƒ^ Hierarchical data model: hierarchies of record typesmainframe oldie, still in use, outdated^ ƒ^ Network data model: graph like data structures, still inuse, outdated^ ƒ^ Relational data model: the most important one today^ ƒ^ Object-Oriented data model: more flexible datastructures, less powerful data manipulation language^ ƒ^ Object-Relational: the best of two worlds?  Transform conceptual model into logical schema ofdata model^ ƒ^ easy for Relational Data Model (RDM)^ ƒ^ can be performed automatically (e.g. by Oracle Designer)

Logical Schema Design: Relational Data ModelLogical Schema Design: Relational Data Model  FU-Berlin, DBS I 2006, Hinze / Scholz

Relation Schema R(A

, A, …, A 12

)n

notation sometimes R:{[A

, A, …, A 1 2

]}n^

^ Relation R

⊆^ dom(A

) x dom(A 1

) x … x dom(A 2

)n

^ Attribute set

R^ ={A^1

, A, …, A^2

}n

^ Degree of a relation: number of attributes ^ Example:^ Student(Fname, Name, Email, SID)Student

⊆^ string x string x string x number R (Student)^ ={Fname, Name, Email, SID

^ Database Schema is set of relation schemas

Logical Schema Design: Relational Data ModelLogical Schema Design: Relational Data Model  FU-Berlin, DBS I 2006, Hinze / Scholz

Time-variant relations^ ƒ^ Relations have

state at each point in time.

ƒ^ Integrity constraints on state part of DB schema  Tuples (rows, records) ƒ^ Not ordered ƒ^ No duplicate tuples

(Relations are sets) ƒ^ Null-values for some attributes possible ƒ^ Distinguishable based on tuple values (key-concept)Different to object identification in o-o languages:there, each object has implicit identity,usually completely unrelated to its fields

Logical Schema Design: Relational Data ModelLogical Schema Design: Relational Data Model  FU-Berlin, DBS I 2006, Hinze / Scholz

Candidate Key:superkey K of relation R such that if any attributeA^ ∈^ K is removed, the set of attributes K\A is not asuperkey of R.^ ƒ^ Example: Student(Fname, Name, Email, SID)Candidate Keys: {Email}, {SID}  Primary Key:arbitrarily designated candidate key^ ƒ^ Example: Student(Fname, Name, Email, SID)Primary Key: {SID}

Important terms

Logical Schema Design: Relational Data ModelLogical Schema Design: Relational Data Model  FU-Berlin, DBS I 2006, Hinze / Scholz

Foreign Key:set of attributes FK in relation schema R1 thatreferences relation R2 if:^ ƒ^ Attributes of FK have the same domain as the attributesof primary key K

of R 2 2 ƒ^ A value of FK in tuple t

in Reither occurs as a value of K 1 1

2

for some t

in R^ or is null. 2 2 ƒ^ Example:R: Exercise(EID, Tutor, ExcerciseHours, Lecture)^1 R: Student(Fname, Name, Email, SID)^2 K={EID}, K^1

={SID} 2

Tutor is foreign key of Student (from Exercise ).

Important terms

Logical Schema Design: EntitiesLogical Schema Design: Entities  FU-Berlin, DBS I 2006, Hinze / Scholz

Step 1: Transform entities^ ƒ^ For each entity E create relation R with all attributes^ ƒ^ Key attributes of E transform to keys of the relationRelational Schema:^ Movie(id, title, category, year, director, Price_per_day, length)

director Movie yearcategorytitle

id Price_per_daylength

Logical Schema Design: Weak EntitiesLogical Schema Design: Weak Entities  FU-Berlin, DBS I 2006, Hinze / Scholz

Step 2: Transform weak entities^ ƒ^ For each weak entity WE create relation R^ ƒ^ Include all attributes of WE^ ƒ^ Add key of identifying entity to weak entities key^ ƒ^ Part of key is foreign keyRelational Schema:

Employee(eid)Child(cid, eid)

Note: weak entity and defining relationship

transformed together! Employee

has^

Child (0,*)^

(1,1)

eid^

cid

Logical Schema Design: RelationshipsLogical Schema Design: Relationships  FU-Berlin, DBS I 2006, Hinze / Scholz

Step 3: Transform Relationships^ ƒ^ For each 1:1-relationship between E1, E2 create relation R^ ƒ^ Include all key attributes^ ƒ^ Define as key: key of entity E1 or entity E2^ ƒ^ Choose entity with total participation for key (drivinglicence) Relational Schema:

Country(CName)President(PName)has(CName, PName)

or^ has(Cname, PName) Country^

has^

President (1,1)^

(1,1)

PName

CName

Logical Schema Design: RelationshipsLogical Schema Design: Relationships  FU-Berlin, DBS I 2006, Hinze / Scholz

Step 3: Transform Relationships^ ƒ^ For each 1:N-relationship between E1, E2 create relation R^ ƒ^ Include all key attributes^ ƒ^ Define as key: key of N-side of relationshipRelational Schema:

Lecture(lnr)Lecturer(lid, name)hold(lnr,lid) Lecture^

hold^

Lecturer (1,1)^

(0,*)

lnr

lid name

Logical Schema Design: RelationshipsLogical Schema Design: Relationships  FU-Berlin, DBS I 2006, Hinze / Scholz

Step 3: Transform Relationships^ ƒ^ Include all relationship attributes in relation RRelational Schema:

require(preLNR, succLNR) Lecture ƒ Rename keys in recursive relationships

hold^

Lecturer (1,1)^

(0,N)

lnr

lid name

date Relational Schema:

Lecture(lnr)Lecturer(lid, name)hold(lnr,lid, date)

Lecture require predecessor

lnr successor (0,1)^

(0,*)

Logical Schema Design: RelationshipsLogical Schema Design: Relationships  FU-Berlin, DBS I 2006, Hinze / Scholz

Step 3: Transform Relationships^ ƒ^ For each n-ary relationship (n>2) create relation R^ ƒ^ Include all key attributes^ ƒ^ Define as key: keys from all numerously involved entities^ Lecturer

recommend

Textbook

(0,*)^

(1,) (0,) Lecture

lidname

ISBN lnr rating

title author

Relational Schema:

Lecture(lnr)Lecturer(lid, name)Textbook(ISBN, title, author)recommend(lid, lnr, ISBN, rating)

Logical Schema Design: GeneralizationLogical Schema Design: Generalization  FU-Berlin, DBS I 2006, Hinze / Scholz

(1) Separate relation for each entity^ ƒ^ Key in specialized relations:foreign key to general relation^ ƒ^ Gathering data from different tables time consuming^ ƒ^ Appropriate specialization prevents unnecessary dataaccess

B: C:

A:

aid A is-a is-a B C

c b

a

A(aid, a)B(aid, b)C(aid, c)

Logical Schema Design: GeneralizationLogical Schema Design: Generalization  FU-Berlin, DBS I 2006, Hinze / Scholz

(2) Relations for specialization^ ƒ^ Separate tables which includeA’s attributes^ ƒ^ Separate keys for relations^ ƒ^ Only valid for exhaustive specialization (no data in A only)^ ƒ^ Time consuming data retrieval for non-distinctspecializations

AB:AC:

AB(aid, a, b)AC(aid, a, c)

aid A is-a is-a B C

c b

a