DB and Math Relations - Database Management - Lecture Slides, Slides of Introduction to Database Management Systems

Download DB and Math Relations - Database Management - Lecture Slides and more Slides Introduction to Database Management Systems in PDF only on Docsity!

Database

Management

System

Lecture - 15

DB and Math Relations

Properties of DB relations are similar to those of Mathematical relations, except

The order of columns in Mathematical relation does matter

Relations Keys

The concept of key and all different types of keys are applicable to the Relations

Foreign Key: An attribute of a table B that is primary key in another table A

Foreign Key

Consider table EMP and DEPT

EMP (empId, empName, qual, depId)

DEPT (depId, depName, numEmp)

Foreign Key Example

Significance of Constraints

Constraints help to maintain the correctness, validity or integrity of the database Like null constrains, default value, domain constraint

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 how to perform transformation

Transforming Rules

Straightforward rules exist

Can be performed manually as well as automatically

Evaluate even if you use some tool, since multiple options exist

Mapping Regular ET

Composite Attributes

Since tables can contain only

atomic values composite attributes need to be represented as a separate relation

Quality becomes a limitation

Multi-valued Attributes

An ET with a multi-valued

attribute is transformed into two relations

One contains the entity type and

second the muli-valued attribute

Multi-valued Attribute

The PK of the second relation is the PK of first relation and the attribute value itself

Values are accessed through reference of the PK, that also serves as FK

Mapping Weak ETs

Identifier dependency: relation

for weak entity type is created and the PK of the strong ET is used as a whole or part of the relation against weak ET

Identifier Dependency

Example