announcement
DESCRIPTION
Announcement. Recitation time Before midterm: 6-7pm, by Earl Wagner After midterm: 5-6pm, by Yi Qiao Newsgroup safe to subscribe Will not cause you to added to the CS mailing list Send all course related questions there for timely response (unless privacy needed). The Relational Data Model. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Announcement](https://reader035.vdocuments.us/reader035/viewer/2022081519/568144cf550346895db19aa5/html5/thumbnails/1.jpg)
1
Announcement• Recitation time
Before midterm: 6-7pm, by Earl Wagner After midterm: 5-6pm, by Yi Qiao
• Newsgroup safe to subscribe Will not cause you to added to the CS
mailing list Send all course related questions there for
timely response (unless privacy needed)
![Page 2: Announcement](https://reader035.vdocuments.us/reader035/viewer/2022081519/568144cf550346895db19aa5/html5/thumbnails/2.jpg)
2
The Relational Data Model
TablesSchemas
Conversion from E/R to Relations
![Page 3: Announcement](https://reader035.vdocuments.us/reader035/viewer/2022081519/568144cf550346895db19aa5/html5/thumbnails/3.jpg)
3
A Relation is a Table
name manfWinterbrew Pete’sBud Lite Anheuser-
BuschBeers
Attributes(columnheaders)
Tuples(rows)
![Page 4: Announcement](https://reader035.vdocuments.us/reader035/viewer/2022081519/568144cf550346895db19aa5/html5/thumbnails/4.jpg)
4
Schemas
• Relation schema = relation name and attribute list. Optionally: types of attributes. Example: Beers(name, manf) or
Beers(name: string, manf: string)• Database = collection of relations.• Database schema = set of all
relation schemas in the database.
![Page 5: Announcement](https://reader035.vdocuments.us/reader035/viewer/2022081519/568144cf550346895db19aa5/html5/thumbnails/5.jpg)
5
Why Relations?
• Very simple model.• Often matches how we think about
data.• Abstract model that underlies SQL,
the most important database language today.
![Page 6: Announcement](https://reader035.vdocuments.us/reader035/viewer/2022081519/568144cf550346895db19aa5/html5/thumbnails/6.jpg)
6
From E/R Diagrams to Relations
• Entity set -> relation. Attributes -> attributes.
• Relationships -> relations whose attributes are only: The keys of the connected entity sets. Attributes of the relationship itself.
![Page 7: Announcement](https://reader035.vdocuments.us/reader035/viewer/2022081519/568144cf550346895db19aa5/html5/thumbnails/7.jpg)
7
Entity Set -> Relation
Relation: Beers(name, manf)
Beers
name manf
![Page 8: Announcement](https://reader035.vdocuments.us/reader035/viewer/2022081519/568144cf550346895db19aa5/html5/thumbnails/8.jpg)
8
Relationship -> Relation
Drinkers BeersLikes
Likes(drinker, beer)Favorite
Favorite(drinker, beer)
Married
husband
wife
Married(husband, wife)
name addr name manf
Buddies
1 2
Buddies(name1, name2)
![Page 9: Announcement](https://reader035.vdocuments.us/reader035/viewer/2022081519/568144cf550346895db19aa5/html5/thumbnails/9.jpg)
9
Combining Relations
• OK to combine into one relation:1. The relation for an entity-set E 2. The relations for many-one
relationships from E (“many”) to F
• Example: Drinkers(name, addr) and Favorite(drinker, beer) combine to make Drinker1(name, addr, favBeer).
![Page 10: Announcement](https://reader035.vdocuments.us/reader035/viewer/2022081519/568144cf550346895db19aa5/html5/thumbnails/10.jpg)
10
Combining Relations (II)
• The combined relation schema consists of All attributes of E The key attributes of F Any attributes belonging to the
relationship R
• Can we combine one-one relationship?
• What about many-many?
![Page 11: Announcement](https://reader035.vdocuments.us/reader035/viewer/2022081519/568144cf550346895db19aa5/html5/thumbnails/11.jpg)
11
Risk with Many-Many Relationships
• Combining Drinkers with Likes would be a mistake. It leads to redundancy, as:
name addr beerSally 123 Maple BudSally 123 Maple Miller
Redundancy
![Page 12: Announcement](https://reader035.vdocuments.us/reader035/viewer/2022081519/568144cf550346895db19aa5/html5/thumbnails/12.jpg)
12
Handling Weak Entity Sets
• Relation for a weak entity set must include attributes for its complete key (including those belonging to other entity sets), as well as its own, nonkey attributes.
• A supporting relationship is redundant and yields no relation.
![Page 13: Announcement](https://reader035.vdocuments.us/reader035/viewer/2022081519/568144cf550346895db19aa5/html5/thumbnails/13.jpg)
13
Example
Logins HostsAt
name name
Hosts(hostName, location)Logins(loginName, hostName, billTo)At(loginName, hostName, hostName2)
Must be the same
billTo
At becomes part ofLogins
location
What if “At” has some attributes ?
![Page 14: Announcement](https://reader035.vdocuments.us/reader035/viewer/2022081519/568144cf550346895db19aa5/html5/thumbnails/14.jpg)
14
Case Study
cities
counties states
Popu. name
Located
Co. Popu. Co. name
capitals
Ci. Popu. Ci. name
Belongs-to
![Page 15: Announcement](https://reader035.vdocuments.us/reader035/viewer/2022081519/568144cf550346895db19aa5/html5/thumbnails/15.jpg)
15
Sample Solution• States (name, popu)• Conuties (co name, state name, co
popu)• Cities (ci name, co name, state
name, ci popu)• Capitals (state name, ci name, co
name)
![Page 16: Announcement](https://reader035.vdocuments.us/reader035/viewer/2022081519/568144cf550346895db19aa5/html5/thumbnails/16.jpg)
16
Subclasses: Three Approaches
1. Object-oriented : One relation per subset of subclasses, with all relevant attributes.
2. Use nulls : One relation; entities have NULL in attributes that don’t belong to them.
3. E/R style : One relation for each subclass: Key attribute(s). Attributes of that subclass.
![Page 17: Announcement](https://reader035.vdocuments.us/reader035/viewer/2022081519/568144cf550346895db19aa5/html5/thumbnails/17.jpg)
17
Example
Beers
Ales
isa
name manf
color
![Page 18: Announcement](https://reader035.vdocuments.us/reader035/viewer/2022081519/568144cf550346895db19aa5/html5/thumbnails/18.jpg)
18
Object-Oriented
name manfBud Anheuser-Busch
Beers
name manf colorSummerbrew Pete’s dark
Ales
Good for queries like “find thecolor of ales made by Pete’s.”
![Page 19: Announcement](https://reader035.vdocuments.us/reader035/viewer/2022081519/568144cf550346895db19aa5/html5/thumbnails/19.jpg)
19
E/R Stylename manfBud Anheuser-BuschSummerbrew Pete’s
Beers
name colorSummerbrew dark
Ales
Good for queries like“find all beers (includingales) made by Pete’s.”
![Page 20: Announcement](https://reader035.vdocuments.us/reader035/viewer/2022081519/568144cf550346895db19aa5/html5/thumbnails/20.jpg)
20
Using Nulls
name manf colorBud Anheuser-Busch NULLSummerbrew Pete’s dark
Beers
Saves space unless there are lotsof attributes that are usually NULL.
![Page 21: Announcement](https://reader035.vdocuments.us/reader035/viewer/2022081519/568144cf550346895db19aa5/html5/thumbnails/21.jpg)
21
Isa
staff faculty student assistant
Case Study
employee
ssno
salary
name
position rank PercentageTime
![Page 22: Announcement](https://reader035.vdocuments.us/reader035/viewer/2022081519/568144cf550346895db19aa5/html5/thumbnails/22.jpg)
22
Relations:
employee(ssno, name, salary)
staff(ssno, name, salary,position)
faculty(ssno, name, salary, rank)
studentassistant(ssno, name, salary, percentagetime)
Key: ssno for all the relations
Isa
staff faculty Student assistant
employee
ssno
salary
name
position rank Time percentage
Subclass – Object-oriented
![Page 23: Announcement](https://reader035.vdocuments.us/reader035/viewer/2022081519/568144cf550346895db19aa5/html5/thumbnails/23.jpg)
23
Relations: employee(ssno, name, salary)staff(ssno, position) faculty(ssno, rank) studentassistant(ssno, percentage_time)
Key: ssno for all relations
Isa
staff faculty student assistant
Subclass – E/R Style
employee
ssno
salary
name
position rank PercentageTime
![Page 24: Announcement](https://reader035.vdocuments.us/reader035/viewer/2022081519/568144cf550346895db19aa5/html5/thumbnails/24.jpg)
24
Isa
staff faculty Student assistant
employee
ssno
salary
name
position rank PercentageTime
Relation:
employee(ssno, name, salary, position, rank, percentage-time)
Key : ssno as key
Note: Sometimes we add an attribute “jobType” to make queries easier.
Subclass – null value