CS145 Assignment #8

This Problem Set and PDA-7 (from HW7) due Thursday Dec. 6, 2001

1. Design an ODL schema for a soccer-league database. The data will include information about players, teams, games, and goals. For each player, you need to include their name, nationality, salary, and current team. For each team you need to include its name, country, membership, current players, and biggest rival team. For each game you need to include the host and away teams, date, and result. For each goal you need to include the player who scored it, time, and distance. You may assume that all players play for a single team, player and team names are unique, and two teams can play only one game on a given date. State any additional assumptions you make. Your ODL schema should include keys and inverse relationships.

2. Consider the following ODL schema about DJ's and the songs they mix and play.

   CLASS DJ (EXTENT DJs KEY name) {
                 ATTRIBUTE string name;
                 ATTRIBUTE int age;
                 RELATIONSHIP Set<Song> plays
                     INVERSE Song::playedBy;
                 RELATIONSHIP Set<Song> mixed
                     INVERSE Song::mixedBy;
             }
   
   CLASS Song (EXTENT Songs KEY name) {
                 ATTRIBUTE string name;
   	      ATTRIBUTE float length; 
                 RELATIONSHIP Set<Song> versions INVERSE versions;
                 RELATIONSHIP Set<DJ> playedBy
                     INVERSE DJ::plays;
   	      RELATIONSHIP DJ mixedBy
   		  INVERSE DJ::mixed;
             }
   
   

   Write OQL queries for each of the following.

   (a)

   Find all songs, as objects, mixed by John Digweed.

   (b)

   Find the names and lengths, without repetitions, of all songs that are mixed and played only by DJs that are 25-35 year old.

   (c)

   Find the total length of versions of songs that are mixed by Sasha.

   (d)

   Find the names of all songs mixed by DJs that play at least two songs that Paul Oakenfold plays.

3. Specify a scenario where an OQL query looks exactly the same as an SQL query and returns the same result. You need to show the relational and ODL schemas and the query that is valid in both SQL and OQL.