Database Management System Lecture 8 LOGICAL DATABASE DESIGN

Database Management System Lecture - 8

LOGICAL DATABASE DESIGN

INTRODUCTION TO RELATIONAL DATA MODEL

TWO MAJOR STRENGTHS ØSimplicity ØStrong Mathematical Foundation

RELATIONAL DATA MODEL Presented by E. F. Codd in 1970, then of IBM Before Relational Data Model, two older data models were in use; Hierarchical, Network

RELATIONAL DATA MODEL First DBMS built on Relational Data Model (RDM) was system R Another Relational DBMS built during those days was INGRES

BASIC OF RDM used for external, conceptual, and to some extent physical schema The basic structure is relation Both entities and relationships are modeled using tables/relations

BASICS OF RDM Relations physically represented as tables Table is a two dimensional representation of a relation Consists of rows and columns

Columns represent attributes and rows represent records Rows, records and tuples all these terms are used interchangeably BASICS OF RDM

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

Basic Properties of a Table 3. The values of the attributes come from the same domain 4. The order of the columns is immaterial

BASIC PROPERTIES OF A TABLE 5. The order of the rows is immaterial 6. Each row/tuple/record is distinct, no two rows can be same

A TABLE st. ID st. Name cl. Name do. B sex S 001 M. Suhail 12/6/84 M S 002 M. Shahid BCS 3/9/86 M S 003 Naila S. MCS 7/8/85 F S 004 Rubab A. MBA 23/4/86 F S 005 Ehsan M. BBA 22/7/88 M MCS

MATHEMATICAL RELATIONS Consider two sets A = {x, y} B = {2, 4, 6} Cartesian product of these sets A X B= {(x, 2), (x, 4), (x, 6), (y, 2), (y, 4), (y, 6)}

MATHEMATICAL RELATIONS A relation is some subset of this Cartesian product, For example, R 1= {(x, 2), (y, 2), (x, 6), (x, 4)} R 2 = {(x, 4), (y, 6), (y, 4)}

The same notion of Cartesian product and relations can be applied to more than two sets, e. g. in case of three sets, we will have a relation of ordered triplets MATHEMATICAL RELATIONS

Thinking in some real world scenario § Name = {Ali, Sana, Ahmed, Sara} § Age = {15, 16, 17, 18, ……. , 25} DATABASE RELATIONS

Cartesian product of Name & Age Name X Age= {(Ali, 15), (Sana, 15), (Ahmed, 15), (Sara, 15), …. , (Ahmed, 25), (Sara, 25)} CLASS RELATIONS = {(Ali, 18), (Sana, 17), (Ali, 20), DATABASE (Ahmed, 19)}

DATABASE RELATIONS Let A 1, A 2, A 3, …, An be some attributes and D 1, D 2, D 3, …, Dn be their domains A relation scheme relates certain attributes with their domain in context of a relation

Can be represented as R = (A 1: D 1, A 2: D 2, ……, An: Dn) STD = (st. Id: Text, st. Name: text, st. Adres: Text, do. B: Date) OR STD(st. Id, st. Name, st. Adres, do. B) RELATION SCHEME

DATABASE RELATIONS According to this scheme we can have a relation (instance of this scheme), like STD={(st. Id: S 001, st. Name: Ali, st. Adres: Lahore, do. B: 12/12/76), (st. Id: S 003, st. Name: A. Rehman, st. Adres: RWP, do. B: 2/12/77)}

DATABASE RELATIONS STD={(S 001, Ali, Lahore, 12/12/76), (S 003, A. Rehman, RWP, 2/12/77)} st. Id st. Name S 001 Ali st. Adres do. B Lahore S 002 A. Rehman RWP 12/12/76 2/12/77

Properties of DB relations are similar to those of Mathematical relations, except The order of columns in Mathematical relation does matter DB AND MATH RELATIONS

DEGREE AND CARDINALITY The number of rows in a relation is its cardinality and the number of columns is its degree

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

Consider table EMP and DEPT EMP (emp. Id, emp. Name, qual, dep. Id) DEPT (dep. Id, dep. Name, num. Emp) FOREIGN KEY

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 a value in its home relation

SIGNIFICANCE OF CONSTRAINTS Constraints help to maintain the correctness, validity or integrity of the database Like null constraints, 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

MAPPING ENTITY TYPES Each regular entity type is transformed straightaway into a relation PK of the ET is declared as PK of relation Simple attributes of ET are included into the relation

MAPPING REGULAR ET

Since tables can contain only atomic values composite attributes need to be represented as a separate relation Quality becomes a limitation COMPOSITE ATTRIBUTES

COMPOSITE ATTRIBUTES EXAMPLE

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 ATTRIBUTES

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

MV ATTRIB EXAMPLE

THANKS