Download Presentation
ECIS466: Database Management LOGICAL DESIGN

Loading in 2 Seconds...

1 / 23

# ECIS466: Database Management LOGICAL DESIGN - PowerPoint PPT Presentation

ECIS466: Database Management LOGICAL DESIGN. Traditional Systems Development Lifecycle. Planning. Analysis. Logical Design. Physical Design. Implementation. Steps in Logical Database Design. Conceptual Data Model. Represent entities as relations. Represent relationships as relations.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

## PowerPoint Slideshow about 'ECIS466: Database Management LOGICAL DESIGN' - tamra

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

### ECIS466: Database Management LOGICAL DESIGN

Traditional Systems Development Lifecycle

Planning

Analysis

Logical

Design

Physical

Design

Implementation

Steps in Logical Database Design

Conceptual Data Model

Represent entities as relations

Represent relationships as relations

Normalize Relations

Merge relations

Logical Data Model (relational)

Normalization
• “Process of converting complex data structures into simple, stable data structures
• Most critical concept in logical database design
• Accomplished in stages through a sequence of normal forms
• Normal forms are the state of a relation based on its functional dependencies
Functional Dependency
• Basis for normalization
• Definition (for a given relation/table)
• if for every valid instance of attribute A
• the value of A uniquely determines the value of another attribute B
• B is functionally dependent on A

B

A

Normal Forms (5+1)
• First Normal Form (1NF)
• contains no repeating groups
• based on property of atomic values
Second Normal Form (2NF)
• A relation is in 2NF if
• it is in 1NFAND
• any one of the following conditions is true
• primary key consists of one attribute
• no non-key attributes exist
• every non-key attribute is functionally dependent on the full set of primary keys (composite key)
Third Normal Form (3NF)
• A relation is said to be 3NF if
• it is in 2NFAND
• there are no transitive dependencies
• A transitive dependency occurs when there is a functional dependence relationship between non-key fields
Example of 3NF
• Consider the relation

SALES(Cust#, Name, Salesperson, Region)

• It is in second normal form (why?)
• It has transitive dependencies (where?)
Boyce-Codd Normal Form (BCNF)
• We have only considered cases where there is one possible key
• Some cases, there is a choice as to what to use as the primary key
• These choices are called candidate keys
• BCNF is based on functional dependencies that take into account all candidate keys
Formally stated...
• A relation is said to be in Boyce-Codd Normal Form (BCNF) if, andonly if, every determinant is a candidate key.
• Every relation in BCNF is also in 3NF.
• Every relation in 3NF is not necessarily in BCNF.
Example....
• St-Maj-Adv (SSN#, Major, Advisor)
• Functional dependencies
• SSN#, Major -------------> Advisor
• Advisor -----------> Major
• Two candidate keys
• SSN#, Major
• SSN#, Advisor
• No single attribute is enough
So, what do we do?
• Arbitrarily choose (SSN#, Major) as the key
• St-Maj_Adv (SSN#, Major, Advisor)
• Clearly this is in 3NF, but....
• What happens if a student changes major modification anomaly
• What happens if we want to add Turing as an advisor for CS (not possible without a student enrolled in CS) - addition anomaly
• What if student 456 drops from school? - -- deletion anomaly
So, we break it down...

ST-ADV (SSN#, Advisor)

ADV-MAJ (Advisor, Major)

BCNF problems typically arises when candidate keys overlap

Another Example of BCNF

Client_Interview (Client#, Interview-date, Interview-Time, Staff#, Room#)

Two possible candidate keys:

1. (Client#, Interview-date)

2. (Staff#, Interview-Date, Interview-Time)

Functional Dependencies

Client#,

Interview Date

Staff#, Interview-Time, Room#

Staff#, Interview Date,

Interview-Time

Client#

Room#

Staff#, Interview Date

Break it down...
• Interview (Client#, Interview-Date, Interview-Time, Staff#)
• Staff_Room (Staff#, Interview-Date, Room#)
Other Normal Forms
• Fourth Normal Form (4NF)
• Fifth Normal Form (5NF)
• Domain-Key Normal Form (DKNF)

Usually, 3NF is sufficient to protect against most anomalies

Process of Normalization

Unnormalized form (UNF)

Process of Normalization

Unnormalized form (UNF)

remove repeating groups

First Normal Form (1NF)

Process of Normalization

Unnormalized form (UNF)

remove repeating groups

First Normal Form (1NF)

remove partial dependencies

Second Normal Form

Process of Normalization

Unnormalized form (UNF)

remove repeating groups

First Normal Form (1NF)

remove partial dependencies

Second Normal Form

remove transitive dependencies

Third Normal Form