Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
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
This course is about database management systems. Main topics covered in this course are: manipulate data, use standard query language, creating a database and logical query language. ODL Subclasses, Keys in ODL, ODL Class without Relationships, BCNF Violation, ODL Relationships, Decompose into 4NF, MVD's Name, FD's, OQL, OQL Types, Path Expressions, OQL Select-from-where, Rename Fields, Change the Collection Type
Typology: Slides
1 / 20
This page cannot be seen from the preview
Don't miss anything!
ODL Sub classes
Follow name of sub class by colon and its sup erclass.
Example: Ales are Beers with a Color
class Ales:Beers { attribute string color; }
Ob jects of the Ales class acquire all the attributes and relationships of the Beers class.
While E/R entities can have manifestations in a class and sub class, in ODL we assume each ob ject is a memb er of exactly one class.
Keys in ODL
Indicate with key(s) following the class name, and a list of attributes forming the key.
Several lists may b e used to indicate several alternative keys.
Parentheses group memb ers of a key, and also group key to the declared keys.
Thus, (key(a 1 ; a 2 ; : : : ; an )) = \one key consisting of all n attributes." (key a 1 ; a 2 ; : : : ; an ) = \each ai is a key by itself."
Example
class Beers (key name) { attribute string name ...
Remember : Keys are optional in ODL. The \ob ject ID" suces to distinguish ob jects that have the same values in their elements.
Translating ODL to Relations
a) Treat the relationship separately, as in E/R. b) Attach a many-one relationship to the relation for the \many."
ODL Class Without Relationships
Problem: ODL allows attribute typ es built from structures and collecti on typ es.
Structure: Make one attribute for each eld.
Set: make one tuple for each memb er of the set. F More than one set attribute? Make tuples for all combinations.
Problem: ODL class may have no key, but we should have one in the relation to represent \OID."
ODL Relationships
If the relationship is many-one from A to B , put key of B attributes in the relation for class A.
If relationship is many-many, we'll have to duplicate A-tuples as in ODL with set-valued attributes. F Wouldn't you really rather create a separate relation for a many-many- relationship?
F You'll wind up separating it anyway, during BCNF decomp osition.
Example
class Drinkers (key name) { attribute string name; attribute string addr; relationship Set
Drinkers(name, addr, b eerName, favBeer, wife, buddy)
OQL
Motivatio n:
Relational languages su er from impedance mismatch when we try to connect them to conventional languages like C or C++. F The data mo dels of C and SQL are radically di erent, e.g. C do es not have relations, sets, or bags as primitive typ es; C is tuple-at-a-time, SQL is relation-at-a- time.
OQL is an attempt by the OO community to extend languages like C++ with SQL-like, relation-at-a-time dictions.
OQL Typ es
Basic typ es: strings, ints, reals, etc., plus class names.
Typ e constructors:
F Struct for structures.
F Collecti on typ es: set, bag, list, array.
Like ODL, but no limit on the numb er of times we can apply a typ e constructor.
Set(Struct()) and Bag(Struct()) play sp ecial roles akin to relations.
class Bar (extent Bars) { attribute string name; attribute string addr; relationship Set
class Beer (extent Beers) { attribute string name; attribute string manf; relationship Set
class Sell (extent Sells) { attribute float price; relationship Bar bar inverse Bar::beersSold; relationship Beer beer inverse Beer::soldBy; }
Path Expressions
Let x b e an ob ject of class C.
If a is an attribute of C , then x:a = the value of a in the x ob ject.
If r is a relationship of C , then x:r = the value to which x is connected by r. F Could b e an ob ject or a collect i on of ob jects, dep ending on the typ e of r.
If m is a metho d of C , then x:m( ) is the result of applying m to x.
OQL Select-From-Where
SELECT FROM
WHERE
Collecti ons in FROM can b e:
Following a collecti on is a name for a typical memb er, optionally preceded by AS.
Example
Get the menu at Jo e's.
SELECT s.beer.name, s.price FROM Sells s WHERE s.bar.name = "Joe's Bar"
Notice double-quoted strings in OQL.
Example
Another way to get Jo e's menu, this time fo cusing on the Bar ob jects.
SELECT s.beer.name, s.price FROM Bars b, b.beersSold s WHERE b.name = "Joe's Bar"
Notice that the typical ob ject b in the rst collecti on of FROM is used to help de ne the second collecti on.
Typical Usage
If x is an ob ject, you can extend the path expression, like s or s.beer in s.beer.name.
If x is a collect i on, you use it in the FROM list, like b.beersSold ab ove, if you want to access attributes of x.
Rename Fields
Pre x the path with the desired name and a colon.
Example
SELECT beer: s.beer.name, s.price FROM Bars b, b.beersSold s WHERE b.name = "Joe's Bar"
has typ e:
Bag(Struct( beer: string, price: real ))
Change the Collectio n Typ e
Use SELECT DISTINCT to get a set of structs.
Example
SELECT DISTINCT s.beer.name, s.price FROM Bars b, b.beersSold s WHERE b.name = "Joe's Bar"
Use ORDER BY clause to get a list of structs.
Example
joeMenu = SELECT s.beer.name, s.price FROM Bars b, b.beersSold s WHERE b.name = "Joe's Bar" ORDER BY s.price ASC
ASC = ascending (default); DESC = descending.
We can extract from a list as if it were an array, e.g. cheapest = joeMenu[1].name;
