CMPS 277 Final, Fall 2002

MATHEMATICS 277 Time: 2 Hrs. Final Examination Fall 2002-2003 Family Name First Name Id. Num Write down your answers on the question sheet. You can use the reverse side for scratch work. DO NOT CUT OFF ANY PAGE. Problem Grade ] Problem 1 (10 Points) The relational database design schema given below is for Zakaria movie theater. ‘ MOVIE (TITLE, YEAR, STUDIONAME, PRODUCERC#, INCOLOR) STAR (STARNAME, SEX, BIRTHYEAR) “STARS _IN (TITLE, STARNAME) EXECUTIVE (CERT#, NAME, ADDRESS) STUDIO (STUDIONAME, CITY, COUNTRY, PRESIDENTC#) The database is supposed to keep track of the different movies, stars, studios that produce these movies, and the executives who either direct these movies, or are presidents of A movie is assumed to be identified by its title, and-for each movie in the database is kept its year of production, its director, the studio it was filmed at, and whether it is in color or not (INCOLOR being “yes” or “no”). A star is identified by the name, and the database maintains information about the gender and the year of birth. An executive here is either a studio president or a movie director or others. Every executive goes through a certification procedure mun by some high authority. Once certified, the executive gets a certificate number, CERT#, which is assumed to identify the executive. For a studio, the database keeps track of its name, which identifies the studio, the city and country, and the presidents certificate number. The primary key for STARS_IN is the combination of its two attributes. a. Give an appropriate ER schema diagram for the given relational schema, including cardinality and participation constraints. Page 2/13 Problem 2. (20 Points) a. List the set of referential integrity constraints that are present in the given relational design. b. Give appropriate SQL statements that create the 3 relations MOVIE, STAR, and STARS IN as given. Include key and referential integrity constraints. Page 3/13 c. Based on the referential integrity constraints, what should a database management system do if a INSERT INTO STARS _IN statement is to be executed. Briefly explain. d. Based on the referential integrity constraints, what should a database management system do ifa DELETE STUDIO statement is to be executed. Briefly explain. Page 4/13 Problem 3.(20 Points) This problem also refers to the relational design of Problem 1. a. Suppose that there are no colored movies prior to year 1945, and Zakaria wants to enforce this “business rule” (integrity constraint) in the database. Suggest a scheme that can be implemented in SQL to enforce this constraint. Give the appropriate SQL statements. b. Can such a business rule be expressed in the ER diagram that you gave earlier? Page 5/13 c. Suppose that every movie must have a female star and a male star. Can such a constraint be enforced in SQL? How ? Give the appropriate SQL statements. , d. Can the constraint in (c) be modeled in the ER diagram? Give a briefly justified answer. Page 6/13 Problem 4. (15 Points) ‘ With reference to the relational design of Problem1, write the following in SQL: a. Find all titles of movies that were developed at Universal Studios during 1995. b. Find all names and birth-years of those stars who have starred in all movies filmed at universal studios during the year 1995 c. Find the average age of the stars in the movie “Thunderstorm” Page 7/13 Problem 5. ( 15 Points) a. Suppose that r is a relation on the schema R(A, B, C). Write an SQL query to test whether the functional dependency 4 —-> C holds on r. Assume that no null values are present Briefly explain your strategy. For the rest of the problem, consider the scheme STUDIUO (STUDIONAME, CITY, COUNTRY, PRESIDENTC#) b. Suppose that in the scheme for STUDIO above, the following functional dependency hold : CITY > COUNTRY. Is the scheme BCNF ? Why ? ec. Suggest a decomposition of the scheme that will result in a BCNF schema. d. Is your decomposition lossless join? Why ? e. Is your decomposition dependency preserving ? Why ? Page 9/13 Problem 6. ( 10 Points) Suppose that the data contained in the relation ‘ MOVIE (TITLE, YEAR, STUDIONAME, PRODUCERC#, INCOLOR) of the relational design of Problem 1 above is to be stored as follows: all records corresponding to movies of the same year will be stored in a bucket. (By a record here is meant a tuple of MOVIE) Suppose that there are at most 100 movies per year. Also assume the following storage requirements: 30-bytes for TITLE, 2-bytes for YEAR, 30-bytes for STUDIONAME, 4-bytes for PRODUCER3#, and 2-byte for INCOLOR. Assume that the block size is 4096 bytes. No spanning is allowed (i.e. one record must be stored in one block, and cannot cross over two blocks). Answer the following showing your computations: a. How many blocks of data at most would a bucket require? b. What will be the total number of blocks needed to store the relation MOVIE? Page 10/13