Prepare for your exams
Get points
Guidelines and tips
Sell on Docsity
Docsity AI

Prepare for your exams

Study with the several resources on Docsity

Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips

Sell on Docsity

Docsity AI

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search for your university

Find the specific documents for your university's exams

Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents

20 Points

For each uploaded document

Answer questions

5 Points

For each given answer (max 1 per day)

All the ways to get free points

Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

CS 180: Winter 2002 Lecture Notes on Relational Model and Functional Dependencies, Study notes of Database Management Systems (DBMS)

University of California-Santa Cruz Database Management Systems (DBMS)

A set of lecture notes from cs 180, a database systems course taught by arthur keller during the winter 2002 semester. The notes cover the topics of the relational model, functional dependencies, and the conversion of entity-relationship diagrams to relations. They include examples, explanations, and diagrams to help illustrate the concepts.

Typology: Study notes

Pre 2010

Uploaded on 08/19/2009

koofers-user-y5g 🇺🇸

10 documents

1 / 34

This page cannot be seen from the preview

Don't miss anything!

Winter 2002 Arthur Keller – CS 180 3–1

Schedule

•Today: Jan. 10 (TH)

◆Relational Model, Functional Dependencies.

◆Read Sections 3.1-3.5.

•Jan. 15 (T)

◆Normal Forms, Multivalued Dependencies.

◆Read Sections 3.6-3.7. Assignment 1 due.

•Jan. 17 (TH)

◆Relational Algebra.

◆Read Chapter 5. Project Part 1 due.

•Jan. 22 (T)

◆SQL Queries.

◆Read Sections 6.1-6.2. Assignment 2 due.

Discover Study notes of Database Management Systems (DBMS) University of California-Santa Cruz

Partial preview of the text

Download CS 180: Winter 2002 Lecture Notes on Relational Model and Functional Dependencies and more Study notes Database Management Systems (DBMS) in PDF only on Docsity!

Winter 2002^

Arthur Keller – CS 180^

Schedule

-^ Today: Jan. 10 (TH)^ ◆^ Relational Model, Functional Dependencies.^ ◆^ Read Sections 3.1-3.5.•^ Jan. 15 (T)^ ◆^ Normal Forms, Multivalued Dependencies.^ ◆^ Read Sections 3.6-3.7. Assignment 1 due.•^ Jan. 17 (TH)^ ◆^ Relational Algebra.^ ◆^ Read Chapter 5. Project Part 1 due.•^ Jan. 22 (T)^ ◆^ SQL Queries.^ ◆^ Read Sections 6.1-6.2. Assignment 2 due.

Winter 2002^

Arthur Keller – CS 180^

Relational Model• Table = relation. • Column headers =^ attributes. • Row = tuple^ Beers • Relation schema^ = name(attributes) + other structure info.,e.g., keys, other constraints. Example:

Beers(name, manf) ◆^ Order of attributes is arbitrary, but in practice we need to assume theorder given in the relation schema.• Relation instance^ is current set of rows for a relation schema.• Database schema^ = collection of relation schemas.

name^ manfWinterBrew^ Pete’sBudLite^ A.B.…^ …

Winter 2002^

Arthur Keller – CS 180^ Name^ address^ tel #^5

Relation: ExampleCardinality of domain Domains N A T N1 A1 T1 N2 A2 T2 N3 A3 T3 N4 T4 N5 T5 T6 T

Domain of Arity^ Relation N^ A^ T N1 A1 T1N1 A1 T2N1 A1 T3... N1 A1 T7N1 A2 T1N1 A3 T1N2 A1 T

(^3) Cardinality <=5x3x7of relation Tuple μ Domain

Component Attribute

Winter 2002^

Arthur Keller – CS 180^

Relation Instance Name Address^

Telephone Bob^ 123 Main St

Bob^ 128 Main St

Pat^ 123 Main St

Harry^ 456 Main St

Sally^ 456 Main St

Pat^ 12 State St

Winter 2002^

Arthur Keller – CS 180^

Why Relations?• Very simple model.• Often a good match for the way we thinkabout our data.• Abstract model that underlies SQL, themost important language in DBMS’s today. ◆ But SQL uses “bags” while the abstractrelational model is set-oriented.

Winter 2002^

Arthur Keller – CS 180^

Relational DesignSimplest approach (not always best): convert each E.S. toa relation and each relationship to a relation.Entity Set^ →

Relation

E.S. attributes become relational attributes.Becomes:^ Beers(name, manf)

name^ manf^ Beers

Winter 2002^

Arthur Keller – CS 180^

E/R Relationships

→^ Relations

Relation has attribute for

key^ attributes of

each E.S. that participates in therelationship.• Add any attributes that belong to therelationship itself.• Renaming attributes OK.^ ◆^ Essential if multiple roles for an E.S.

Winter 2002^

Arthur Keller – CS 180^ Drinkers • For one-one relation

Married, we can choose either^ husband

Likes(drinker, beer)Favorite(drinker,^ or^ wife^ as key.

beer) Married(husband,

wife) name^ manf^ BeersLikes Buddies(name1, name2) name^ addr Buddies Married 1 2 Favorite husband^ wife

Winter 2002^

Arthur Keller – CS 180^

Weak Entity Sets, Relationships

Relations

• Relation for a weak E.S. must include its fullkey ( i.e ., attributes of related entity sets) as wellas its own attributes.• A supporting (double-diamond) relationshipyields a relation that is actually redundant andshould be deleted from the database schema.

Winter 2002^

Arthur Keller – CS 180^

Example Hosts(hostName)Logins(loginName, hostName)At(loginName, hostName,

hostName2)

-^ In^ At,^ hostName

and^ hostName

must be the

same host, so delete one of them.• Then,^ Logins^

and^ At^ become the same relation;

delete one of them.• In this case,^ Hosts

’ schema is a subset of

Logins’

schema. Delete^ Hosts

Logins^?

Hosts@@ name^

name

Winter 2002^

Arthur Keller – CS 180^

Examplename

manfBeers Ales isa color

Winter 2002^

Arthur Keller – CS 180^ OO-Style E/R Style name^ manfBud^ A.B.^ Beers^

name^ manf^ colorSummerBrew^ Pete's^ darkAles name^ manfBud^ A.B.SummerBrew^ Pete's

name^ ColorSummerBrew^ dark Beers^

Ales name^ manf

color Bud^ A.B.

NULL SummerBrew^ Pete's

dark Beers Using NULLS

Winter 2002^

Arthur Keller – CS 180^

Example

Drinkers(name, addr, beersLiked,manf, favoriteBeer) • Reasonable FD's to assert:1.^ name^ →^

addr

2.^ name^ →^

favoriteBeer

3.^ beersLiked

name^ addr^ beersLiked^ manf^ →^ manf

favoriteBeer Janeway^ Voyager^

Bud^ A.B.^

WickedAle Janeway^ Voyager^

WickedAle^ Pete's^

WickedAle Spock^ Enterprise

Bud^ A.B.^

Bud

Winter 2002^

Arthur Keller – CS 180^

Shorthand: combine FD's with common leftside by concatenating their right sides.• Sometimes, several attributes jointlydetermine another attribute, althoughneither does by itself. Example:^ beer^ bar

CS 180: Winter 2002 Lecture Notes on Relational Model and Functional Dependencies, Study notes of Database Management Systems (DBMS)

Related documents

Partial preview of the text

Download CS 180: Winter 2002 Lecture Notes on Relational Model and Functional Dependencies and more Study notes Database Management Systems (DBMS) in PDF only on Docsity!

Schedule

Relational Model• Table = relation. • Column headers =^ attributes. • Row = tuple^ Beers • Relation schema^ = name(attributes) + other structure info.,e.g., keys, other constraints. Example:

Relation: ExampleCardinality of domain Domains N A T N1 A1 T1 N2 A2 T2 N3 A3 T3 N4 T4 N5 T5 T6 T

Relation Instance Name Address^

Why Relations?• Very simple model.• Often a good match for the way we thinkabout our data.• Abstract model that underlies SQL, themost important language in DBMS’s today. ◆ But SQL uses “bags” while the abstractrelational model is set-oriented.

Relational DesignSimplest approach (not always best): convert each E.S. toa relation and each relationship to a relation.Entity Set^ →

Relation

E.S. attributes become relational attributes.Becomes:^ Beers(name, manf)

E/R Relationships

→^ Relations

Relation has attribute for

key^ attributes of

each E.S. that participates in therelationship.• Add any attributes that belong to therelationship itself.• Renaming attributes OK.^ ◆^ Essential if multiple roles for an E.S.

Weak Entity Sets, Relationships

Relations

• Relation for a weak E.S. must include its fullkey ( i.e ., attributes of related entity sets) as wellas its own attributes.• A supporting (double-diamond) relationshipyields a relation that is actually redundant andshould be deleted from the database schema.

Example Hosts(hostName)Logins(loginName, hostName)At(loginName, hostName,

hostName2)

and^ hostName

must be the

same host, so delete one of them.• Then,^ Logins^

and^ At^ become the same relation;

delete one of them.• In this case,^ Hosts

’ schema is a subset of

Logins’

schema. Delete^ Hosts

Logins^?

Examplename

Example

Drinkers(name, addr, beersLiked,manf, favoriteBeer) • Reasonable FD's to assert:1.^ name^ →^

addr

2.^ name^ →^

favoriteBeer

3.^ beersLiked

name^ addr^ beersLiked^ manf^ →^ manf

→^ price