Chapter 4 Logical Database Design and the Relational Model

1

Objectives
• • • • Define terms for the relational data model Brief introduction to SQL Transform E-R diagrams to relations Create tables with entity and relational integrity constraints

2

Steps in Database Problem Solving
Study and Analyze w/Team Interviews & Integrated Model Conceptual Model (E-R) Transformation (Six Cases) Logical M d l L i l Model (Relations) Normalization (Three Steps) Logical Model (3NF Relations) IMPLEMENTATION
3

Business Problem

Advantages of Relational Model g
• • • • • Can represent all kinds of information Based on Math (relations) Natural to people Relatively simple p We know how to implement it fast

4

Motivating Example
• Make a list of students in the class, keeping their ID, name and phone number

5

Motivating Example
• Make a list of students in the class, keeping their ID, name and phone number • You’d probably come up with something like this: p y p g
ID xx yy Name Mike Mik Elisa Phone 111 222

• This is the basic structure of the relational model, a table or relation
6

Extra Assumptions
• You would not repeat the same row twice • No two rows have the same ID, but they may have the same name and phone number p
ID xx yy Name Mike Mik Elisa Phone 111 222

• ID would be the PRIMARY KEY (PK).

7

Now add emails … (many!)
• Now you need to add the emails of each student, but you do not know how many emails • Can you come up with a solution? Try it … y p y

8

Many Fields y
• Could come up with something like this
ID xx yy Name Mike Elisa Phone 111 222 Email1 bad bad Email2 idea  idea 

• Above would not work very well. How many fields?
– Wasted space – What if a student has more emails? – How to process it in my program?

9

Un normalized Un-normalized
• Could also try this: ID xx xx yy Name Mike Mike Elisa