Questions 36 through 39 relate to the following relational schema. This relational schema is used to build a relational database to keep track of movies and their box office revenues in different countries. The domain definitions for the attributes of the relations are given in the following table. Attribute Description Data Type MovieID Unique identifier of the movie Numeric: size 10 MovieName Official title of the movie Variable Character: size 50 YearReleased 4-digit number corresponding the year in which the movie is released Numeric: size 4 SequelID MovieID of the movie to which the movie in the record is sequel Numeric: size 10 Country Country Name (e.g., USA, Canada) Variable Character: size 20 BoxOffice Total dollar amount that the movie made in the country’s movie theaters Numeric: size 12 To help you visualize the structure of the database, a few rows from each table are shown below for illustration purposes. Performance_T MovieID Country BoxOffice 1110000001 Germany 25,560,500 1110000001 USA 267,652,800 1110000001 Italy 15,030,300 1110000001 Turkey 10,340,500 2220000001 USA 1,200,000 3330000001 Italy 1,300,000 … … …. Movie_T MovieID MovieName YearReleased SequelID 1110000002 Shrek 2 2004 1110000001 1110000001 Shrek 2001   2220000001 The Usual Suspects 1995   3330000001 Kingdom of Heaven 2005   4440000001 Superman 1978   4440000002 Superman 2 1980 4440000001 … … … … Which of the following SQL code, in the given order, should be run to create the first two records in the tables?

Questions 14 through 20 relate to the following ER diagram. This ER model captures the data needs of a pharmacy, representing the associations among (i) drugs, (ii) side effects, (iii) interactions, (iv) conditions, and (v) ingredients.  Please answer the questions about this conceptual model. Side Effect arises because of

Questions 44 through 50 relate to the following relational database. The Book table (named Book_T), given below, is one of the tables in a relational database used by a bookstore. ISBN Title PubDate PubID Cost Retail Discount Category 0401140733 Revenge of Mickey 12/14/2005 1 14.2 22   Family Life 8843172113 Database Implementation 06/04/2003 3 31.4 55.95   Computer 3437212490 Cooking with Mushrooms 02/28/2004 4 12.5 19.95   Cooking 3957136468 Holy Grail of Oracle 12/31/2005 3 47.25 75.95 3.8 Computer 2491748320 Painless Child-Rearing 07/17/2004 5 48 89.95 4.5 Family Life 2147428890 Shortest Poems 05/01/2005 5 21.85 39.95   Literature The domain definitions for the attributes of the book table are given below. Attribute Description Data Type ISBN Unique identifier of the book (primary key) VARCHAR2(10) Title Title of the book VARCHAR2(30) PubDate Publication date of the book DATE PubID Publisher ID of the book NUMBER(2) Cost Cost of a copy of the book in dollars NUMBER(5,2) Retail Sale price of a copy of the book in dollars NUMBER(5,2) Discount Discount amount in dollars from retail price NUMBER(5,2) Category Category of the book VARCHAR2(15) What is the output generated after running the following SQL statements successively?  CREATE VIEW CompBook_V ASSELECT ISBN, Title, Cost, RetailFROM Book_TWHERE Category=’Computer’; UPDATE CompBook_VSET Retail=49.95WHERE Title=’Database Implementation’; SELECT *         FROM Book_TWHERE Title=’Database Implementation’;

Questions 36 through 39 relate to the following relational schema. This relational schema is used to build a relational database to keep track of movies and their box office revenues in different countries. The domain definitions for the attributes of the relations are given in the following table. Attribute Description Data Type MovieID Unique identifier of the movie Numeric: size 10 MovieName Official title of the movie Variable Character: size 50 YearReleased 4-digit number corresponding the year in which the movie is released Numeric: size 4 SequelID MovieID of the movie to which the movie in the record is sequel Numeric: size 10 Country Country Name (e.g., USA, Canada) Variable Character: size 20 BoxOffice Total dollar amount that the movie made in the country’s movie theaters Numeric: size 12 To help you visualize the structure of the database, a few rows from each table are shown below for illustration purposes. Performance_T MovieID Country BoxOffice 1110000001 Germany 25,560,500 1110000001 USA 267,652,800 1110000001 Italy 15,030,300 1110000001 Turkey 10,340,500 2220000001 USA 1,200,000 3330000001 Italy 1,300,000 … … …. Movie_T MovieID MovieName YearReleased SequelID 1110000002 Shrek 2 2004 1110000001 1110000001 Shrek 2001   2220000001 The Usual Suspects 1995   3330000001 Kingdom of Heaven 2005   4440000001 Superman 1978   4440000002 Superman 2 1980 4440000001 … … … … Which of the following SQL select queries lists the records of movies shown in Germany?

Questions 5 through 9 relate to the following scenario. Suppose that you are asked to create an ER diagram for a real estate firm that lists properties for sale. The following rules describe the data requirements for this firm. The firm has a number of sales offices in several states. Attributes of a sales office include Office Number (identifier) and Location. Each sales office is assigned one or more employees. Attributes of employee include Employee ID (identifier) and Employee Name. An employee must be assigned to only one sales office. For each sales office, there is always one employee assigned to manage that office. An employee may manage only the sales office to which he or she is assigned. The firm lists properties for sale. Attributes of property include Property ID (identifier) and Location. Components of Location include City, State, and Zip Code. Each unit of property must be listed with one (and only one) of the sales offices. A sales office may have any number properties listed or may have no property listed. Each unit of property has one or more owners. Attributes of owners are Owner ID (identifier) and Owner Name. An owner owns one or more units of property. Attribute Percent Owned represents ownership percentage of a property by an owner. Assume that you drew the draft ER diagram for this real estate firm below.  However, this solution is neither complete nor fully correct. Please answer the questions related to this draft solution. Consider the Manages relationship between EMPLOYEE and SALES OFFICE entities. Which of the following cardinalities are missing in this relationship?

Questions 29 through 31 relate to the following ER diagram. After a few iterations, we developed the following partial relational schema in a graphical notation, which is neither complete nor fully correct. Please answer the questions to complete this partial schema. Which attribute, if any, is missing in the PROPERTY relation?

Questions 44 through 50 relate to the following relational database. The Book table (named Book_T), given below, is one of the tables in a relational database used by a bookstore. ISBN Title PubDate PubID Cost Retail Discount Category 0401140733 Revenge of Mickey 12/14/2005 1 14.2 22   Family Life 8843172113 Database Implementation 06/04/2003 3 31.4 55.95   Computer 3437212490 Cooking with Mushrooms 02/28/2004 4 12.5 19.95   Cooking 3957136468 Holy Grail of Oracle 12/31/2005 3 47.25 75.95 3.8 Computer 2491748320 Painless Child-Rearing 07/17/2004 5 48 89.95 4.5 Family Life 2147428890 Shortest Poems 05/01/2005 5 21.85 39.95   Literature The domain definitions for the attributes of the book table are given below. Attribute Description Data Type ISBN Unique identifier of the book (primary key) VARCHAR2(10) Title Title of the book VARCHAR2(30) PubDate Publication date of the book DATE PubID Publisher ID of the book NUMBER(2) Cost Cost of a copy of the book in dollars NUMBER(5,2) Retail Sale price of a copy of the book in dollars NUMBER(5,2) Discount Discount amount in dollars from retail price NUMBER(5,2) Category Category of the book VARCHAR2(15) What is the output generated by the SQL statement below?               SELECT ISBN, Title, Retail  Price               FROM Book_T;

Questions 1 through 4 relate to the following ER diagram. The following ER diagram represents two entities and a relationship between them. Assume that we want to redraw the ER diagram above using an associative entity.  We come up with the following partial solution, with circled numbers in the diagram indicating the incomplete parts. Each number is referred to in a question with the same question number. Please refer to these numbers to answer the questions. What should be the relationship name on the line between the associative entity and the PRODUCT entity?

Questions 14 through 20 relate to the following ER diagram. This ER model captures the data needs of a pharmacy, representing the associations among (i) drugs, (ii) side effects, (iii) interactions, (iv) conditions, and (v) ingredients.  Please answer the questions about this conceptual model. Which entity is an optional participant in a relationship with which other entity?

Questions 32 through 35 relate to the following ER diagram. Data domain definitions for the attributes are given below. Attribute Description Domain Nurse ID Set of possible nurse IDs Fixed Character: size 5 Nurse Name Set of possible nurse names Variable Character: size 20 Nurse Birth Date Set of possible nurse birth dates Date Date Assigned Set of possible assignment dates Date Center ID Set of possible care center IDs  Number: size 4 (no decimal) Center Location Set of possible care center locations Variable Character: size 10 (This column can only take one of three values: Dallas, Plano, Allen) Based on the conceptual model and the domain definitions, we decided to create tables using SQL DDL statements for NURSE and CARE CENTER relations to build the database.  We came up with the following table definitions (based on the relational schema, which is not shown here). CREATE TABLE Nurse_T (NurseID                            A,NurseName                       B,NurseBirthDate                  C, CONSTRAINT Nurse_PK                   D );CREATE TABLE CareCenter_T (CenterID                            E,CenterLocation                    F, CONSTRAINT CareCenter_PK           G ); Which of the following SQL DDL code should be inserted in place of ‘F’?