Relational Database Design

DSC340

Mike Pangburn

Difficulties of Managing Data

Data are scattered and collected by many individuals using various methods and devices.

Data come from many sources including internal sources, personal sources and external sources.

Vast amounts of data can be collected and processed e.g., Amazon.com collecting clickstream data.

What is a relational database?

You have all heard of the major players in the RDBMS (relational database mgmt system) market E.g., Oracle, MySQL, Microsoft SQL-Server

What makes a database relational? Relational db’s are organized around _______ Earlier database systems (“hierarchical”, “network”)

were harder to use, and never developed the popularity of relational systems

Database Design terminology

Entity is a kind of thing you want to store information about (e.g., students, courses, buildings, etc.).

Attribute is a characteristic or quality of a particular entity Note: same idea as a class attribute in programming – a

piece of information out an object

Primary key is a field that uniquely identifies that record.

Foreign keys are fields that have reflect identifying information from another entity (allowing you to “look up” the associated entity).

What are relationships between entities?

A relationship between 2 database entities represents the possibility that two entities may be associated with each other E.g., a customer may rent a video

There are 3 basic types of relationships: 1-to-1 1-to-Many Many-to-Many

Relationship types

One-to-one when a single instance (row) in the first table can be related to

at most one row in the second table, and vice versa

One-to-many when a single instance (row) in the first table can be related to

many rows in the second table, AND when a single instance (row) in the second table can be related

to at most one row in the first table

Many-to-many when a single instance (row) in the first table can be related to

multiple rows in the second table, and vice versa

Relationship types

Examples below for one-to-many and many-to-many

Video(videoId)OwnsStore

(storeId)

101

123

90987

145

99787

3

5

13

12

115

123

145

90987

99787

Video(videoId)PreviouslyRented

101

102

103

104

Customer(accountId)

The database design process

A good order in which to ask yourself some questions… What real-world entities (“things”) am I

collecting information about? Each entity will become a table (think of a table as a list of things)

What pieces of information (attributes) about those entities do I wish to store? Also, what subset of that information

will be used to identify the things (e.g., SSN would be a common choice for a table containing people things)

How are the entities related?

We need a key (an “identifier”) for the things in each table (list)

Table Plausible Identifier (a “key”)

Customer of brick-and-mortar video store

account ID

Online customer of video store e-mail address

Working person in the United States

Social Security number

DVD video 12-digit UPC bar code number

In the last case (books), you might need to track specific copies.

A simple design example

You and several of your bird-loving neighbors want to keep track of the birds that come to feed in the various yards in the neighborhood.

What things are we collecting information about? Each implies a separate database table What entities are in the bird-study problem?

Birds Birdfeeders Yards

List (table) of Yards

Yards

Yard Number

Owner

Address

Phone Number

1 2

3

4

List (table) of Birdfeeders

Bird Feeders

Bird Feeder Number

Material (Wood, Plastic, Metal)

Location in Yard(Sun, Partial/Full Shade)

Table of Birds

Bird Data

Data Number

Date

Time

Bird Type

# of this type

Relationship: One Yard, Many Feeders

Bird Feeders

Bird Feeder Number

Material

Location in Yard

Yard Number

Yards

Yard Number

Owner

Address

Phone Number

Each Yard can have many Birdfeeders, but each Birdfeeder can only be in one Yard. This is called a one to many (1 - ∞) Relationship.

1

∞

There must be one field in both tables that is the same, so that the database knows how the tables connect. In this case, we need to know which yard each bird feeder is located.

Relationship: One Birdfeeder, Many Birds

Bird Feeders

Bird Feeder Number

Material

Location in Yard

Yard Number

1

∞

Again, there must be one field in both tables that is the same, so that the database knows how the tables connect. In this case one birdfeeder can be visited by many birds.

Bird Data

Data Number

Date

Time

Bird Type

Bird Feeder Number

Most relationships in the world (of data) turn out tobe One-to-Many.

More details on Implementing relationships One-to-one

Add a foreign key column to one of the tables E.g., consider a university where each faculty member

has one office, and each office holds one faculty member Two design option: (1) include an “Room Num” foreign key

column in the faculty table, or (2) include a “Fac SSN” foreign key column in the rooms table

Room Num Building Type

122 West Hall Office

382 Condon Hall Office

1001 High Tower Office

2913 Gurt Building Office

2940 Gurt Building Class

Fac SSN Fac Name Fac Phone Room Num

046-25-5834 John Roon 506-3998 382

156-99-5933 Bill Hill 506-9322 2913

190-03-1944 Kiefer Zorn 506-4927 122

076-55-6822 Pinzer Mohan 506-9399 1001

Foreign Key column

These two tables show the first design option:Faculty

Rooms

More details on Implementing relationships

One-to-many Add a foreign key column to the table on the “many” side E.g., painter and paintings exhibit a 1-to-many relationship

To capture the relationship, we add a “painter id” column to the painting table

ID Name Deceased Sex

2 Tore Rasmussen No M

3 Raechel Niel Yes F

4 Jon Smyth Yes M

5 Philis Stonips No F

Code Title Est. Value

Type PID

100 A Sense of Space $2000 Water 2

101 The Road to Nowhere $3000 Water 2

102 Reflections $80,000 Oil 3

103 A Journey’s End $55,000 Oil 4

104 Fisherman’s Catch $40,000 Oil 4

105 Reflections $45,000 Oil 4

106 Treescape $5000 Oil 5

107 Veiled Visions $4000 Oil 5

Painter

Painting

ForeignKey column

More details on Implementing relationships

Many-to-many Add a bridge table “between” the two table which keeps

track of all row combinations E.g., at a university, the relationship between Students

and Classes is many-to-many One student can enroll in multiple classes One class will have multiple students

Student SSN Student Name Student Phone

046-25-5834 Cosmo Rinker 541-844-9001

089-43-5569 Doogie Rupp 541-911-3579

076-55-6822 Wendy Kooper 541-074-4031

075-83-9931 Curtis Ponzol 513-567-6772

StudentsClass Num Term Room Num Time

754 F05 2940 MW 8am

755 F05 2940 MW 10am

802 S06 2940 TTh 8am

813 S06 2940 TTh 10am

Classes

Adding a column to either table (or both) will not be adequate to represent many students taking one course, and one course having many students, so we need to add a “bridge table”

Student SSN Student Name Student Phone Classes

046-25-5834 Cosmo Rinker 541-844-9001 754, 802

089-43-5569 Doogie Rupp 541-911-3579

076-55-6822 Wendy Kooper 541-074-4031

075-83-9931 Curtis Ponzol 513-567-6772

Students

Class Num Term Room Num Time Students

754 F05 2940 MW 8am 046-25-5834, 089-43-5569

755 F05 2940 MW 10am

802 S06 2940 TTh 8am

813 S06 2940 TTh 10am

Classes

Why don’t we do this?

Implementing relationship types

Adding a “bridge table” to handle the many-to-many relationship it’s just a 2 column table of data!-nothing special!

Student SSN Class Num

046-25-5834 754

046-25-5834 755

075-83-9931 755

075-83-9931 802

075-83-9931 813

089-43-5569 802

089-43-5569 813

076-55-6822 754

076-55-6822 802

The Enrollment “bridge table” below relates Students and Classes!

Student SSN Student Name Student Phone

046-25-5834 Cosmo Rinker 541-844-9001

089-43-5569 Doogie Rupp 541-911-3579

076-55-6822 Wendy Kooper 541-074-4031

075-83-9931 Curtis Ponzol 513-567-6772

Students

Class Num Term Room Num Time

754 F05 294 MW 8am

755 F05 211 MW 10am

802 S06 111 TTh 8am

813 S06 211 TTh 10am

Classes

Enrollment

Information about different real-world entities should be stored in different tables Splitting different kinds of information across different tables

is referred to as “normalized” design in the database realm

Design should store information using the smallest logical parts E.g., would it be better to have Street, City, State, ZIP fields

rather than one Address attribute? Each part defines a column

Design should not store derived attributes Calculate derived values as needed E.g., would it be better to store birth-date or age ?

Design should not cause blank (null) values

Basic Database Design rules-of-thumb

Implementing a database design

Once you have designed the set of tables you want to comprise your relational database, you need to create the same within a RDMS (Relational Database Management System) or equivalent

For example, we could implement the Students, Classes, and Enrollment tables

We will use an online database system at reports.zoho.com

After your design is implemented, you can create SQL queries to extract your information from your database design We will discuss SQL next class Databases and SQL will be the focus of the next hw