Consider the following enterprise, which includes movies, actors, and studios that produce movies. Actors are people with normal attributes, like Id, name, date of birth, etc. Actors play in movies. A movie has the usual attributes: title, release date, director, etc. (you choose ? we do not need many). Studios are companies. A company has an address, phone numbers (typically more than one),

name, etc. Studios have additional attributes, such as the artistic director.

Constraints: A movie has at least one actor, and exactly one studio makes each particular
movie. Every actor played in at least one movie. Some studios may be brand new and had no
time to make any movies yet.

(a) Represent the above as an E-R diagram; include all relevant constraints.

(b) Translate the above diagram into the relational model by supplying the appropriate CREATE
TABLE statements. Note that actor's Id is a 10-digit string and a phone number is a
10 digit number that never starts with a zero. Specify these as domains in SQL.
Specify all the key and foreign key constraints. Try to preserve as many participation
constraints as possible. List all the participation constraints that are present in the E-R
diagram, but not in its translation to SQL.

SOLUTION:

(a)

(b)




CREATE DOMAIN PhoneDomain INTEGER


CHECK (1000000000 < VALUE AND VALUE < 9999999999)


CREATE DOMAIN IdDomain CHAR(10)


CHECK ("1000000000" < VALUE AND VALUE < "9999999999")


CREATE TABLE Actor (


Id IdDomain,


Name CHAR(20),


DOB Date,


PRIMARY KEY (Id)


)


CREATE TABLE Movie (


Title: CHAR(20),


ReleaseDate: Date,


Director CHAR(20),


StudioName CHAR(20),


PRIMARY KEY (Title),


FOREIGN KEY (StudioName) REFERENCES Studio(Name),


)


CREATE TABLE PlayIn(


ActorId idDomain,


MovieTitle CHAR(20),


PRIMARY KEY (actorId, MovieTitle),


FOREIGN KEY (actorId) REFERENCES Actor(Id)


FOREIGN KEY (MovieTitle) REFERENCES Movie(Title)


)


CREATE TABLE Company(


CompanyName CHAR(20),


Phone PhoneDomain,


Address CHAR(20),


PRIMARY KEY (CompanyName, Phone)


)


CREATE TABLE Studio(


Name: CHAR(20),


ArtisticDirector: CHAR(20),


PRIMARY KEY (Name)


)


-- Expresses the inclusion dependency that Studio is a Company


CREATE ASSERTION StudioIsAcompany


CHECK (


NOT EXISTS ( SELECT *


FROM Studio S


WHERE S.Name NOT IN


( SELECT C.CompanyName


FROM Company C ) )


)

Participation constraints presented in the ER diagram, but not in its translation to SQL:

i. a movie has at least one actor

ii. every actor played in at least one movie

iii. Note: we cannot use something like FOREIGN KEY (Name) REFERENCES Company(

CompanyName) in Studio to express the ISA relationship between Studio and

Company. Unfortunately, CompanyName is not a key in Company, so we cannot use

foreign keys here and must use assertions instead.