318 CHAPTER 7. CONSTRAINTS AND TRIGG 7.1.4 Exercises for Section 7.1 Exercise 7.

Question

318 CHAPTER 7. CONSTRAINTS AND TRIGG 7.1.4 Exercises for Section 7.1 Exercise 7.1.1: Our running example movie database of Section 2.2.8 has keys defined for all its relations. Movies (title, year , length, genre, studioName, StarsIn (movieTitle, movieYear, starName) MovieStar (name, address, gender, birthdate) MovieExec(name, address, cert#, netWorth) Studio (name, address, pres ) producerC#) Declare the following referential integrity constraints for the movie database as in Exercise 7.1.1. a) The producer of a movie must be someone mentioned in MovieExec. Mod ifications to MovieExec that violate this constraint are rejected.

Explanation / Answer

7.1.1

a) I am writing the required tables schema only (Movie and MovieExec)

Movie table schema :

CREATE TABLE Movie(

       title VARCHAR(255),

      year INTEGER,

       length INTEGER,

      genre VARCHAR(255),

      studioName CHAR(50),

      producerC# INTEGER REFERENCES MovieExec(certc#),

PRIMARY KEY (title,year)

);

In the above table schema , the declaration of producerC# written that

          producerC# INTEGER REFERENCES MovieExec(certc#)

Rejects the modifications to MovieExec that violates this constraint.

No policy needs to be specified ,because rejecting violating modifications is the default action.

MovieExec table schema :

CREATE TABLE MovieExec(

       name VARCHAR(50),

        address VARCHAR(255),

       cert# INTEGER PRIMARY KEY,

       netWorth INTEGER

);

Related Questions

Navigate

• Browse (All)
• Browse 3
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.