show class

7/28/2019 Show Class

1/52

Chapter 6:

Logical database designand the relational model


2/52

Objectives of logical

design... Translate the conceptual design into alogical database design that can beimplemented on a chosen DBMS Input: conceptual model (ERD) Output: relational schema, normalized

relations Resulting database must meet user needs

for: Data sharing Ease of access Flexibility


3/52

Relational databasecomponents

Data structure Data organized into tables

Data manipulation Add, delete, modify, and retrieve using

SQL

Data integrity Maintained using business rules


4/52

Why do I need to know

this? Mapping conceptual models to relational schema isstraight-forward CASE tools can perform many of the steps, but..

Often CASE cannot model complexity of data

and relationship (e.G., Ternary relationships,supertype/subtypes) There are times when legitimate alternates

must be evaluated

You must be able to perform a quality check onCASE tool results


5/52

Some rules... Every table has a unique name. Attributes in tables have unique

names. Every attribute value is atomic.

Multi-valued and composite attributes?

Every row is unique. The order of the columns is

irrelevant.

The order of the rows is irrelevant.


6/52

The key... Relational modeling uses primary keys andforeign keys to maintain relationships Primary keys are typically the unique

identifier noted on the conceptual model Foreign keys are the primary key of

another entity to which an entity has arelationship

Composite keys are primary keys that aremade of more than one attribute Weak entities Associative entities


7/52

Implementing itInstance

Attribute

Entity

Field


8/52

What aboutrelationships?


9/52

Constraints Domain constraints Allowable values for an attribute as

defined in the domain Entity integrity constraints

No primary key attribute may be null Operational constraints

Business rules Referential integrity constraints


10/52

Referential integrity

constraint Maintains consistency among rows oftwo entities matching of primary and foreign keys

Enforcement options for deletinginstances Restrict

Cascade

Set-to-Null


11/52

Transforming the EER

diagram into relationsThe steps: Map regular entities

Map weak entities Map binary relationships Map associative entities Map unary relationships

Map ternary relationships Map supertype/subtype relationships


12/52

Transforming E-R

diagrams into relationsMapping regular entities to relations Composite attributes: use only

their simple, component attributes Multi-valued attributes: become a

separate relation with a foreign

key taken from the superior entity


13/52

Mapping a composite attribute


14/52

relational schema

notation


15/52

Transforming E-R

diagrams into relationsMapping weak entities Becomes a separate relation with a

foreign key taken from thesuperior entity


16/52

Example of mapping a weakentity


17/52

relational schema

notation


18/52

Transforming E-R

diagrams into relationsMapping binary relationships One-to-many - primary key on the one side becomes

a foreign key on the many side Many-to-many - create a new relation (associative

entity) with the primary keys of the two entities asits primary key I like to call these intersection entities to

distinguish them from associative entities createdat the conceptual level

One-to-one - primary key on the mandatory sidebecomes a foreign key on the optional side


19/52

Example of mapping a 1:M

relationship


20/52

relational schema

notation


21/52

Example of mapping an

M:M relationship


22/52

relational schema

notation


23/52

Mapping a binary 1:1

relationship


24/52

relational schema

notation


25/52

Transforming E-R

diagrams into relationsMapping associative entities Identifier not assigned

Default primary key for theassociation relation is the primarykeys of the two entities

Identifier assigned

It is natural and familiar to end-users Default identifier may not be unique


26/52

Mapping an associative

entity with an identifier


27/52

relational schema

notation


28/52

Transforming E-Rdiagrams into relations

Mapping unary relationships

One-to-many - recursive foreign key inthe same relation

Many-to-many - two relations:

One for the entity type

One for an associative relation inwhich the primary key has twoattributes, both taken from theprimary key of the entity


29/52

For example...

EMPLOYEESupervisesEmp-Name

Emp_Address

Emp_Num


30/52

Would look like...

Emp_Num Emp_Name Emp_Address Boss_Num

references


31/52

And..

COMPONENTBOMDescription

Unit_of-Measure

Comp_Num

Num_Units


32/52

Would look like...

Comp_Num Desc Unit_of_Measure

COMPONENT

Num-of_Units Comp_Num Subassembly_Num

BOM


33/52

Transforming E-R

diagrams into relationsMapping ternary (and n-ary)relationships

One relation for each entity andone for the associative entity

app ng a ternary


34/52

app ng a ternaryrelationship


35/52

relational schema

notation


36/52

Transforming E-R

diagrams into relationsMapping Supertype/subtyperelationships

Create a separate relation for thesupertype and each of thesubtypes

Assign common attributes tosupertype

Assign primary key and unique

attributes to each subtype


37/52


38/52

Would look like this...


39/52

Lets try a couple.


40/52

Well-structured

relations Well-structured relations containminimal redundancy and allowinsertion, modification, and deletionwithout errors or inconsistencies

Anomalies are errors orinconsistencies resulting fromredundancy

Insertion anomaly Deletion anomaly Modification anomaly


41/52

Data normalization Normalization is a formal process fordeciding which attributes should begrouped together in a relation

Objective: to validate and improve alogical design so that it satisfies certainconstraints that avoid unnecessaryduplication of data

Definition: the process of decomposingrelations with anomalies to producesmaller, well-structured relations


42/52

Steps in

normalization


43/52

Functional dependencies

and keys Functional dependency: the value of oneattribute (the determinant) determinesthe value of another attribute

A -> B, for every valid instance of A,that value of A uniquely determines thevalue of B

Candidate key: an attribute or combination

of attributes that uniquely identifies aninstance Uniqueness: each non-key field is

functionally dependent on every

candidate key


44/52

First normal form No multi-valued attributes. Every attribute value is atomic.


45/52

Second normal form 1NF and every non-key attribute is

fully functionally dependent on the

primary key. Every non-key attribute must be

defined by the entire key, not by

only part of the key. No partial functional dependencies.


46/52

Third normal form 2NF and no transitive dependencies(functional dependency between non-

key attributes.)


47/52

Relation with transitive

dependency


48/52

Transitive dependency in

SALES relation


49/52

Removing a transitive

dependency


50/52

Relations in 3NF


51/52

Lets practice...


52/52

Other considerations... Synonyms: different names, samemeaning.

Homonyms: same name, differentmeanings.

show class

Documents