University of Mumbai 2006 B.Sc Information Technology SQL ( Structured Query Language ) TY-IT- - Question Paper

Q one is cumplosory
Solve four ques. from Q2 to Q 7
marks 100
Duration three hours

O JU . i.O't'O ' ch J c \J

CON/4431-06.    O Q L. 2-    XE4525-

( 3 Hours )    [ Total Marks : 100

(q f\kv, o6-

Note: (1) Question No. 1 is compulsory

(2) From Question No. 2 to Question No. 7 attempt any four questions.

Q. 1. Answer the following:

State the features of SQL which have made it successful in the market.

Explain the relational database model.

If a view is updated, is the original table updated? Uncer what conditions can a view not be updated?

Explain the concept of NULL values.

Write SQL Queries for the following:

Attribute Data Type Constraint Name Up to 20 characters long Unique and NOT NULL Length Up to 4 digits Values between 100 and 4160 Outflow Up to 20 characters long Required. ii. Add a new column called MaxDepth to the Rivers table. Make sure that the

values of this column range from 100 to 250 fee..

Q. 2. Answer the following:

a)    State and Explain the Codds 12 Rules.

b)    State and explain the built-in functions available in SQL2 standard

c)    Write SQL queries for the following:

i. Create a table CITY wit	h the following fields:
Field	Data Type / Size		Constraint
ID	Integer		Primary Key
Name	Upto 20 characters
State	2 Characters
Latitude	Number
Longitude	Number
ii. Create a table CITY wit	ri the following fields:
Field	Data Type / Size	Constraint
ID	Integer	Foreign Key matching CITY ID
Month	Integer	Mut.-t be between 1 and 12
Temp_.F	Number	Must be between -80 and 150
Rain I	Number	Must be .between 0 and 100
(ID + Month)		Primary Key

iii.    List the temperatures for July from table DATA, lowest temperatures first, picking up city name and latitude by joining with table CITY.

iv.    List (using subquery) the cities with year-round average temperature above 50 degrees.

Answer the following:

a)    Explain the column check constraint in SQL2. How can it be applied to many different columns in the database?

b)    Explain the problems created by referential cycles on referential integrity.

c)     What is a deadlock? How can it be avoided? Explain various techniques used to

overcome deadlocks.

Answer the following:

a)    Explain the syntax of the SELECT statement with various clauses that can be attached to it.

b)    Enumerate the problems that can corrupt referential integrity of the parent / child relationships in a database.

c)    Write SQL statements for the following based on the tables CUSTOMER, MOVIE and INVOICE given behind (after Q 7):

i.    Find the movies that cost more than 159 and also find the new cost as original cost* 15

ii.    Print the names and types of all the movie except horror movies

iii.    List the various movie types available.

iv.    List the mvno, title, type of movies whose stars begin with letter M\

v.    Determine the maximum and minimum of price. Rename the title as max_price and min_price respectively.

Q. 5. Answer the following:

a)    What is a trigger? How are triggers used to implement referential integrity?

b)    What is a transaction? Why is it necessary that a transaction either be completed or completely aborted? Explain the two transaction processing statements in SQL.

c)    Write SQL statements for the following based on the tables CUSTOMER, MOVIE and INVOICE given (after Q 7):

i.    Print the information of invoice table in the following format for all records The Invoice No. of Customer Id. {Custjd} is {Invno} and Movie No. is {Mvno}.

ii.    Select thelitle, Custid, ftfvno for all the movies that are issued

iii.    Find out which customers have been issued movie number(Mvno) 9.

iv.    Display the month (in alphabets) in which customers are supposed to return the movies

Q. 6. Answer the following:

a)    Explain the concept of Domain in SQL 2 standard.

b)    Explain the different types of joins in SQL. Give an example for each type.

c)    Write SQL statements for the following based on the tables CUSTOMER, MOVIE and INVOICE given (after Q 7):

i.    Delete all the records having return date before 10th July '05

ii.    Find out if th movie starring Tom Cruise' is issued to any customer and list the Custid to whom it is issued

iii.    Find the names of customers who have been issued movie of type 'drama'.

iv.    Find out the title of the movies that have been issued to the customer whose Fname is 'Mary'.

Q. 7. Answer the following:

a)    Explain the different database architectures.

b)    What are aliases? How can he / be created and dropped?

c)    Write SQL statements for th? f allowing based on the tables CUSTOMER, MOVIE and INVOICE given below:

i.    Add a column Remark o.^: type Varchar and size 25 to the Invoice table.

ii.    Find the names of all customers having 'a' in the second letter in their fname

iii.    Find out the movie number which has been issued to customer whose first name is 'Ivan'.

iv.    Display thelftle, Lname.fname for-customers having movie number greater than or equal to three, in the following format The movie taken by {Fname} {Lname} is {Title}.

Reference Tables for Q. 4, Q. 5, Q. 6 and Q. 7 Table: CUSTOMER

Attribute Data type Constraints Custid Varchar(3) Primary key Lname Varchar(15) Fname Varchar(15) Area Varchar(2) Phone. Number(8)

Table: MOVIE

Attribute	Data type	Constraints
Mvno	Number(2)	Primary key
Title	Varchar(25)
Type	Varchar(10)
Star	Varchar(25)
Price	Number(8,2)

Table: INVOICE

Attribute	Data type	Constraints
Invno	Varchar(3)	Primary key
Mvno	Number(2)	Foreign Key Movie (Mvno)
Custid	Varchar(3)	Foreign Key Customer(Custid)
Issuedate	Date
Returndate	Date

Attachment: university-of-mumbai-2006-b-sc-information-technology-51565-1.pdf

Earning:   Approval pending.