# CPS 430/542 Lecture notes: From ODL Designs to Relational Designs and the Object-relational model

Coverage: [FCDB] §§4.4-4.5 (pp. 155-173)

## Converting atomic attributes

```class Movie (extent Movies) {
attribute string title;
attribute integer year;
attribute integer length;
attribute enum Film {color,blackAndWhite} filmType;
};

⇒

Movies(title, year, length, filmType)
```

## Issues

• no keys in ODL
• non-atomic attributes
• methods
(most result from a richer type system)

## Converting non-atomic attributes

```class Star (extent Stars) {
attribute string name;
};

(simply flatten) ⇒

Stars(name, street, city)
```

## Converting set-valued attributes

```class Star (extent Stars) {
attribute string name;
};

(use multiple tuples) ⇒

Stars(name, street, city)
```
however this conversion approach can lead to unnormalized relations, e.g.,
```class Star (extent Stars) {
attribute string name;
attribute Date birthdate;
};

⇒

Stars(name, street, city, birthdate)
```
key: ???

problems: ???

one set-valued attribute and a single-valued attribute leads to _____???_____

two set-valued attributes leads to _____???_____

solution approaches:
• normalize
• introduce many-many relationships

## Converting other type constructors

• bags
• lists
• arrays
• dictionaries

```class Movie (extent Movies key title year) {
attribute string title;
attribute integer year;
attribute integer length;
attribute enum Film {color,blackAndWhite} filmType;

relationship Set<Star> stars inverse Star::starredIn;
relationship Studio ownedBy inverse Studio::owns;
};

class Studio (extent Studios key name) {
attribute string name;
relationship Set<Movie> owns inverse Movie::ownedBy;
};

⇒

----
StudioOf(title, year, studioName)
-----  ----
Movies(title, year, length, filmType)
-----  ----

// but remember we can combine StudioOf and Movies, why?

// final relational database schema
----
Movies(title, year, length, filmType, studioName)
-----  ----
```
```Movies(title, year, length, filmType, studioName, starName)
```
What if there is no key?
```Stars(cert#, name, street, city, birthdate)

StarsIn(title, year, cert#)
```

## The Object-Relational Model

extends relational model with
• structured types for attributes (set of structs; essentially a ?el?ti??)
• methods
• identifiers for tuples
• references

## Nested Relations

```Stars(name, address(street, city), birthdate, movies(title, year, length))
```

the nested relation analog of not being in BCNF!

## References

```Movies(title, year, length)
```

## Object-oriented model vs. object-relational model

• object's value is a struct; a tuple is likewise a struct
• methods: see SQL-99 standard which supports object-relational idea in the same manner as ODL
• both have rich type systems
• references and OIDs
• pure OO: OIDs are hidden from the user
• object-relational model allows references to be part of the schema
• backwards compatibility

## Converting ODL designs into object-relation designs

• most non-atomic types in ODL have analogs in the object-relational model (e.g., SQL-99)
• for those non-atomic types with no analog, use techniques presented here for converting from ODL designs to relational designs

## References

 [FCDB] J.D. Ullman and J. Widom. A First Course in Database Systems. Prentice Hall, Upper Saddle River, NJ, Second edition, 2002.