# Database Relationship

Document Sample

```					RDBMS (Part II) – Relationships
Lecture 6 CONS 340
Professor Frink on relationships and computers:
Frink: Well, sure, the Frinkiac-7 looks impressive, but I predict that within 100 years, computers will be twice as powerful, 10,000 times larger, and so expensive that only the five richest kings of Europe will own them. Apu: Could it be used for dating? Frink: Well, theoretically, yes. But the computer matches would be so perfect as to eliminate the thrill of romantic conquest. Mw-hurgn-whey.

Learning Objectives
Get your mind around relationships Perform the second part of an EntityRelationship (E-R) Modeling exercise Choose the proper cardinality for a relationship Create full blown E-R Diagrams Apply the concepts of primary and foreign keys

Last time we covered…
The creation of tables or relations or entity sets
Supplier# 1 Sup_Name Wissler Flynn Hawks Gunn English Status 20 10 30 20 30 City
Tucson

Relation

Tuples

2 3 4 5

Bahrain Fort Collins Los Angeles St. Louis

Attributes

Today we are going create relationships

Relationships
Relationships conceptually link two entity sets
Students have Programs

Relationships describe the association between these entity sets
“is a kind of” “owns” “works for” “offers”

These are known as semantic relationships

E-R Modeling (part II) Relationships
Entities are expressed as boxes
Students Programs

Relationships are shown as diamonds
Students have Programs

Cardinality
Cardinality defines the numeric relationships between occurrences of the entities on either end of the relationship line
Students have Programs

Cardinality
Four possible cases exist:

1-to-1

1-to Many

Many-to-1

Many-to-Many

Cardinality Notation
one-to-one one-to-many many-to-one many-to-many
Students
M

(1:1) (1:M) (M:1) (M:M)
have
1

Programs

Reads: “Students have one program” and “Programs have many students”

Cardinality
What is the cardinality of these relationships?
Students enroll in Courses

Instructors

teach

Courses

Courses

take place in

Rooms

Optional vs. Mandatory
Students
M

have

1

Programs

Students

M

enroll in

M

Courses

Instructors

1

teach

M

Courses

Courses

M

take place in

1

Rooms

Cardinality Notation in E-R Modeling
Optional
Students
M

have

1

Programs

Mandatory
Students
M

have

1

Programs

Notation Examples
Students
M

have

1

Programs

Students

M

enroll in

M

Courses

Instructors

1

teach

M

Courses

Courses

M

take place in

1

Rooms

Another Method of Notation
Students have Programs

Students

enroll in

Courses

Instructors

teach

Courses

Courses

take place in

Rooms

Database Model
Instructors
1

teach

M

Courses
M

M

M

enroll in
M

consist of
M

take place in
1

Students

M

have

1

Programs

Rooms

What questions could this database answer?

From Conceptual to Logical
In E-R modeling we do not know how we would actually hook up entity sets But once we have a conceptual model we need to figure this out Therefore we move into what is refered to as Logical Modeling

Example
Students
M M

enroll in

M

Courses
M

have
1

Programs

M

consist of

The “Students” Relation
Relation Schema
Students(Student#, FirstName, LastName, GPA)

Sample Table
STUDENT Student# 00001 00002 00003 00004 FirstName LastName Bob Tom Anna Roberta Czaja Wilson Garcia Mohr GPA 3.45 2.6 4.0 3.23

The “Programs” Relation
Relation Scheme

Sample Table
PROGRAMS ProgramID 0001 0002 0003 Name Conservation Forest Resources Management Medieval Studies Faculty Forestry Forestry History Advisor
Scott Hinch John Nelson Arlene Sindelar

Modeling the Student_Program Relationship (1 to Many)
Student# 00001 00002 00003 00004 ProgramID 0001 0002 0003 FirstName LastName Bob Tom Anna Roberta Name Conservation FRM Medieval Studies Czaja Wilson Garcia Mohr Faculty Forestry Forestry History GPA 3.45 2.6 4.0 3.23 ProgramID 0001 0001 0003 0002 Advisor
Scott Hinch John Nelson Arlene Sindelar

Candidate Key
Provide tuple-level addressing system May be more than one per relation

Primary Key
One per relation (others are alternate keys)

Foreign Key
Attribute of a relation which refers to a key in another relation

Foreign Keys
a primary key occurring in another relation is a foreign key in the second relation a foreign key is the controlled duplication of an attribute in one or more relations these keys help define the relationships between relations it indicates directionality in that the foreign key comes to one relation from another (foreign) one

foreign keys do not assure uniqueness; they define associations between relations A foreign key may be null, and almost always is not unique.

this is a parent/child association a foreign key on a child relation is a primary key on a parent

Example
Students
M M

enroll in

M

Courses
M

have
1

Programs

M

consist of

Modeling the Student_Courses Relationship (Many to Many)
Look up table

Student# 00001 00002 00003 00004

FirstName LastName Bob Tom Anna Roberta Czaja Wilson Garcia Mohr

GPA 3.45 2.6 4.0 3.23

Student# CourseID 00001 00001 00002 00002 00002 00003 00001 00003

CourseID 00001 00002 00003

SubCode CONS CONS CONS

Course# 330 340 481

Name
Conservation Biology Introduction to GIS for Forestry and Conservation Conservation Planning and Wildland Recreation

Description
Fundamental concepts in conservation biology… Introduction to principles, practice and context of Geographic Information Systems (GIS) … Theory and tools needed for the selection and design of protected areas…

Many to Many
Many to Many
Students
M

enroll in

M

Courses

Converted to two 1 to many relationships
Students
1 M

enroll in

M

1

Courses

Questions
How do you logically connect a 1 to many relationship? What about a many to many relationship? Why was the “student_courses” relationship modeled as a relation?

Example
Students
M M

enroll in

M

Courses
M

have
1

Programs

M

consist of

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 431 posted: 7/1/2009 language: English pages: 27
Description: Database Relationship