Blog

Questions 40 through 43 relate to the following relational database. Given the relational schema and sample data for the registration table, answer the questions below. Please note that the registration table may not have a value for the grade column. REGISTRATION (StudentID, StudentName, CourseID, SectionID, Semester, Grade)              Registration_T StudentID StudentName CourseID SectionID Semester Grade 38214 Letersky ISM 4212 501 Spring 2012 A 54907 Altvater ISM 4212 502 Spring 2013   54907 Altvater ISM 4930 101 Fall 2012 B 66234 Aiken BA 3300 503 Summer 2012 B Find all distinct students who do not have a value for the grade column and list them alphabetically by student name. 

Questions 21 through 25 are NOT related to any scenario. Please choose the one alternative that best completes the statement or answers the question.  One disadvantage of file processing systems is: 

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. ER diagram includes examples of all kinds of relationships EXCEPT?

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 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) Suppose that Book_T contains a NOT NULL constraint on the PubID column. The following SQL statement creates a view named Profit_V. CREATE VIEW Profit_V ASSELECT ISBN, Title, Cost, Retail, Retail-Cost ProfitFROM Book_T; Which of the following INSERT statements works via the Profit_V?

Questions 21 through 25 are NOT related to any scenario. Please choose the one alternative that best completes the statement or answers the question. The property by which subtype entities possess the values of all attributes of the supertype is called:

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 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 … … … … When a record for a movie is deleted in Movie_T, all related records in Performance_T are also deleted by the DBMS automatically. Which of the following clauses must have been included in the foreign key constraint and in which table definition?

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 actions will NOT be allowed by the DBMS to protect the referential integrity enforced by the REFERENCES clause? (i) Updating the title of the movie from Shrek to Shrek 1 in Movie_T (ii) Updating the movieID of the movie titled Shrek from 1110000001 to 1110000005 in Movie_T (iii) Inserting a new record to Performance_T to capture the box office revenue for the movie titled Shrek 2 in Canada.

Questions 40 through 43 relate to the following relational database. Given the relational schema and sample data for the registration table, answer the questions below. Please note that the registration table may not have a value for the grade column. REGISTRATION (StudentID, StudentName, CourseID, SectionID, Semester, Grade)              Registration_T StudentID StudentName CourseID SectionID Semester Grade 38214 Letersky ISM 4212 501 Spring 2012 A 54907 Altvater ISM 4212 502 Spring 2013   54907 Altvater ISM 4930 101 Fall 2012 B 66234 Aiken BA 3300 503 Summer 2012 B What is the purpose of the following query? SELECT CourseID, Semester, Count(StudentID) FROM Registration_T WHERE Grade IS NULL GROUP BY CourseID, Semester;