Movies

1. filmType title year length toStar Movies Voices isa isa weapon Cartoons MurderMystery Our Movie Example

2. Subclass Structures to Relations • There are two different approaches to organize relations that represent a hierarchy of classes regarding OO and E/R models. • In OO • An object belongs to exactlyone class. • An object inherits properties from all its super-classes but it is not a member of them. • In E/R model • An “object” can be represented by entities belonging to several entity sets that are related by isarelationships. • Thus, the linked entities together represent the object and give that object its properties (attributes and relationships).

3. OO approach • OO way: • Every subclass has its own relation. • All the properties of that subclass, including all its inherited properties, are represented in this relation. • Example: For our examplethe relational database schema would be: • Movies( title, year, length, filmType ) • Cartoons( title, year, length, filmType ) • MurderMysteries( title, year, length, filmType, weapon) • Cartoon-MurderMysteries( title, year, length, filmType, weapon)

4. Can we merge Cartoons with Movies? • If we do, we lose information about which moves are cartoons. • For the relationship Voices, we create: • Voices( title, year, starName ) • Is it necessary to create two relations one connecting cartoons with stars, and one connecting cartoon-murder-mysteries with stars? • Not, really.

5. E/R way • The entity set Movieswill be represented by the relation: • Movies(title, year, length, filmType). • The entity set MurderMysterywill be represented by the relation: • MurderMystery(title, year, weapon). • The entity set Cartoonswill be represented by the relation: • Cartoons(title, year). • This relation has no attribute other than the key for movies. • The relationship Voiceswill be represented by the relation: • Voices(title, year, name). • The last attribute is the key for Starsand the first two form the key for Cartoons.

6. E/R way (II) • Remark: • There is no relation corresponding to the class Cartoon-MurderMystery. • For a movie that is both, we obtain: • its voices from the Voices relation, • its weapon from the MurderMystery relation, • and all other information from the Movies relation. • The relation Cartoonshas a schema that is a subset of the schema for the relation Voices. Should we eliminate the relation Cartoons? • However there may be silent cartoons in our database. Those cartoons would have no voices and we would lose them.

7. Comparison of Approaches OO translation drawback: • Should we want to find an object, it forces us to search several relations. • For example, if we want to find “Robin Hood”, we should search four different relations, until we find the relation for the class the movie is in. E/R translation drawback: • We may have to look in several relations to gather information about a single object. For example, if we want the length and weapon used for a murder mystery film, we have to look at all the relations.

8. Comparison of Approaches (Continued) OO translation advantage: • The OO translation keeps all properties of an object together in one relation. E/R translation advantage: • The E/R translation allows us to find in one relation tuples from all classes in the hierarchy. • In simple words: • To find the value of a general (of all classes) attribute is easier in E/R translation. We just look at the relation of the super-class. • To find the value of a specific (not of all classes) attribute along with the value of a general one, is easier in the OO translation. In the E/R translation we have to join the relation of the super-class with the relation for the child.

9. Examples • What movies of 1999 were longer than 150 minutes? • Can be answered directly in the E/R approach. • In the OO approach we have to examine all the relations. • What weapons were used in cartoons of over 150 minutes in length? • More difficult in the E/R approach. • We should access Movies to find those of over 150 mins. • Then, we have to access Cartoons to see if they are cartoons. • Then we should access MurderMysteries to find the weapon. • In OO approach we need only access Cartoon-MyrderMysteries table. • However, we might want to use not too many tables. In the OO approach if we have a root and n children we need 2^n different tables!!!

10. Null Values to Combine Relations • If we are allowed to use NULL as a value in tuples, we can handle a hierarchy of classes with a single relation. • This relation has attributes for all the properties possessed by objects in any of the classes of the hierarchy. • An object is represented by a single tuple. This tuple has NULL in each attribute corresponding to a property that does not belong to the object’s class. • If we apply this approach to the Movie hierarchy, we would create a single relation whose schema is: • Movie(title, year, length, filmType, studioName, starName, voice, weapon) • A movie like “Who Framed Roger Rabbit?”, being both a cartoon and a murdermystery, would be represented by several tuples that had no NULL’s. • The Little Mermaid, being a cartoon but not a murder-mystery, would have NULL in the weapon component.

11. Null Values to Combine Relations (Continued) • It allows us to find all the information about an object in one relation. • However, an approach using NULL’s is invalid for the relational model, although most of its commercial implementations support NULL’s.

12. Create Table CREATE TABLE Movies ( title CHAR(40), year INT, length INT, type CHAR(2) ); CREATE TABLE Studios ( name CHAR(20), address VARCHAR(255), noOfEmp INT ); CREATE TABLE Stars ( name CHAR(30), address VARCHAR(255), gender CHAR(1) ); • CHAR(n) allocates a fixed space, and if the string that we store is shorter than n, then it is padded with blanks. • Differently, VARCHAR(n) denotes a string of up to n characters. • ORACLE uses also VARCHAR2(n), which is semantically the same as VARCHAR(n), which is deprecated. • VARCHAR(n) or VARCHAR2(n) allow for compression in order to save space. • Use CHAR(n) for frequently used fields, and use VARCHAR(n) otherwise.

13. Insert, Update, Delete, INSERT INTO Movies(title, year, length, type) VALUES('Godzilla', 1998, 120, 'C'); INSERT INTO Movies VALUES('Godzilla', 1998, 120, 'C'); UPDATE Movies SET title = 'Godzilla 2' WHERE title = 'Godzilla' AND year=1998; DELETE FROM Movies WHERE title='Godzilla 2';

14. Declaring primary keys DROP TABLE Movies; CREATE TABLE Movies ( title CHAR(40) PRIMARY KEY, year INT, length INT, type CHAR(2) ); DROP TABLE Movies; CREATE TABLE Movies ( title CHAR(40), year INT, length INT, type CHAR(2), PRIMARY KEY (title, year) );

15. Altering, Dropping, Defaults, Indexes ALTER TABLE Stars ADD phone CHAR(16); ALTER TABLE Stars DROP COLUMN gender; ALTER TABLE Stars MODIFY phone CHAR(26); DROP TABLE Stars; DROP TABLE Movies; DROP TABLE Studios; CREATE TABLE Stars ( name CHAR(30), address VARCHAR(255), gender CHAR(1) DEFAULT 'N' );

16. Declaring foreign keys CREATE TABLE Studios ( name CHAR(20) PRIMARY KEY, address VARCHAR(255), noOfEmp INT ); CREATE TABLE Movies ( title CHAR(40) PRIMARY KEY, year INT, length INT, type CHAR(2), studioName CHAR(20), FOREIGN KEY (studioName) REFERENCES Studios(name) );