Logical Database Design.pptx

Two Major Strengths



_Simplicity

Relational Data Model

•

_{Presented by E. F. Codd in 1970, then}

of IBM

Relational Data Model

•

_{First DBMS built on Relational Data}

Model (RDM) was system R

Basic of RDM

•

_{The basic structure is relation}

Basics of RDM

• _{Relations physically represented as tables}

• _{Table is a two dimensional representation of a}

relation

Basics of RDM

• _{Columns represent attributes and rows}

represent records

• _{Rows, records and tuples all these terms are}

Basic Properties of a Table

1. Each cell of a table contains atomic/single value

2. Each column has a distinct name, the name of the

attribute it represents

3. The values of the attributes come from the same

domain

4. Each row/tuple/record is distinct, no two rows can

A table

stID stName clName doB

sex

Components of RDM

• _{As discussed in data model definition}

1. Structure (relation/table)

Mathematical Relations

•

_{Consider two sets}

A = {x, y} B = {2, 4, 6}

•

_{Cartesian product of these sets}

Mathematical Relations

• _{A relation is some subset of this Cartesian}

product, For example,

Mathematical Relations

Database Relations

•

_{Thinking in some real world}

scenario

Database Relations

Cartesian product of Name & Age

Name

X

Age= {(Ali,15), (Sana,15),

(Ahmed,15), (Sara,15), …., (Ahmed,25),

(Sara,25)}

Database Relations

•

Let A1, A2, A3, …, A

be some

attributes and D1, D2, D3,…, D

be

their domains

Relation Scheme

Can be represented as

R = (A1:D1, A2:D2, ……, An:Dn)

STD = (stId:Text, stName: text, stAdres:Text, doB:Date) OR

Database Relations

According to this scheme we can

have a relation (instance of this

scheme), like

Database Relations

STD={(S001, Ali, Lahore, 12/12/76), (S003,

A. Rehman, RWP, 2/12/77)}

stId

stName

stAdres doB

Relations Keys

•

_{The concept of key and all different}

types of keys are applicable to the

Relations

Foreign Key

Consider table EMP and DEPT

Integrity Constraints

Two main types

• _{Entity integrity constraint}

– _{Primary key cannot have null value}

• _{Referential integrity constraint}

– _{Value of Foreign key is either null or matches with}

Designing Logical DB

•

_{Logical DB design is obtained from}

conceptual DB design

•

_{Generally involves transforming E-R}

data model to relational data model

•

_{We have studied both DMs, now}

Transforming Rules

• _{Straightforward rules exist}

• _{Can be performed manually as well as}

automatically

• _{Evaluate even if you use some tool, since}

Mapping Entity Types

•

_{Each regular entity type is}

transformed straightaway into a

relation

•

_{PK of the ET is declared as PK of}

relation

Mapping Entity Types

Composite Attributes

• _{Since tables can contain only atomic values}

Multi-valued Attributes

• _{An ET with a multi-valued attribute is}

transformed into two relations

• _{One contains the entity and second the}

Multi-valued Attribute

•

_{The PK of the second relation is the}

PK of first relation and the attribute

itself

•

_{Values are accessed through}

Multi-valued Attribute

Mapping Weak ETs

Mapping Relationships

• _{Relationships are mapped according to their}

degree and cardinalities

Binary Relationships

Cardinality: one to many

• _{The relation against many side ET contains the}

One to Many Example

EMP

PROJ

PROJ(projId, projName, projDur, projCost)

EMP(empId, empName, projId)

Minimum Cardinality

• _{Minimum cardinality on one side needs}

special attention

• _{If optional, define FK as normal} • _{If compulsory, make FK not null}

Minimum Cardinality Example

EMP

DEPT

DEPT(deptId, deptName)

Many to Many Relationship

•

_{A third table is created for the}

relationship, sometimes also called

associative entity type

•

_{Generally, the PKs of the}

Many to Many

BOOK

STD

BOOK

STD

STD(stId, sName, sFname)

BOOK(bkId, bkTitle, bkAuth)

TRANS(bkId, stId, isDate, rtDate)

Binary one to one

•

_{A special form of one to many}

relationship

•

_{PK of one ET has to be included in}

the other as FK.

One to One Example

(not from real world)

STD

HOBBY

One to One Example

DEPT

MANAGER

Unary Relationships

• _{Unary one to many introduces the PK of same}

ET as FK

• _{Many to many unary requires creation of}

Unary One to Many Example

EMP

manages empId

empName

eQual

Unary Many to Many Example

has

ITEM(itemNo, iName, iCost)