BSc.-Database Systems-Mumbai-April 2019
MUMBAI UNIVERSITY
Subject: Database System
Semester: 1
Total Marks: 75]
Total Time: 2 1/2 hours
N.B: 1) All questions are compulsory
2) Figures to the right indicate marks.
3) MIssing of sub-questions is not allowed.
4) Assume suitable data if necessary and state it clearly.
____________________________________________________________________________________________________________________________
Q. 1 Attempt All. (15M)
a Multiple Choice Questions.
1) __________________ defines the structure of a relation which consists of a fiexd set of attribute-domain pairs.
a) Instance b) Schema
c) Program c) Super Key
2) Dates must be specified in the format of ________________
a) mm/dd/yy b) yyyy/mm/dd
c) dd/mm/yy d)yy/dd/mm
3) Column HEader refers to __________
a) Table b) Domain
c) Attribute d) field
4) In SQL, which command is used to SELECT only one copy of each set of each set of duplicate rows?
a) SELECT DISTINCT b) SELECT UNIQUE
c) SELECT DIFFERENT d) All of the above
5) You can delete view with ______________ command.
a) DROP VIEW b) DELETE VIEW
d) REMOVE VIEW d) TRUNCATE VIEW
b) Fill in the blanks. [ Use following pool to answer questions.]
( Data Control Language, double rectangle, foreign Key, domain, select )
1) Key to represent relationship between tables is _______________.
2) A set of possible data values are called ______________.
3) In E-R diagram Weak Entities are represented by ______________.
4) _______________ clause is m andatorily used in Subqueries.
5) DCL stands for ________________.
Q. 2) Attempt the following ( Any THREE ) (15M)
a) What are the advantages of DBMS?
b) What do you mean by Weak Entity Sets? Explain with example.
c) Explain the distinction between total and partial participation constraint,
d) what do you mean by Primary Key Constraint and Foreign Key Constraint? Discuss with suitable examples.
e) Write short note on Client/Server Architecture for DBMS.
f) Construct on ER Diagram for a car insurance company whose customers owns one or more cars each. Each car has associated with it zero or any number of accidents.
Q. 3) Attempt the following. (Any THREE ) (15M)
a) Explain 2 NF with suitable example.
b) Explain UNION, INTERSECTION and MINUS operations with suitable algebraic query example.
c) Consider following tables. Underline fields are key fields.
cystiner ( custno, aname, city )
Solve following queries using MySQL-
i) Create above table with custno as Primary Key.
ii) Insert 2 records in it.
iii) Write a query to count number of customers in each city.
iv) Find out the customers whose name starts with ‘S’ and third letter as ‘I’.
d) Write short note on Aggegate functions used in MySQL.
e) Explain Lossless-join decomposition.
f) Explain SQl. commands for following.
i) Create Dateabse
ii) Show Database
iii) Truncate Table
iv) Drop Table
v) Use Dateabse.
Q. 4) Attempt the following. ( Any THREE ) (15M)
a) Explain any 5 Date functions used in MySQL with example.
b) What do you mean by a subquery? Give query example of subqueries with ANY clause and ALL clauses.
c) What security mechanism is used to secure database ?
d) What are the roles of DBA?
e) What is a process of creating and dropping a user in MySQL?
f) Consider following tables-
Book ( bookid, title, author, pubisher, category, price )
Distributor ( distid, bookid, dname, city )
Orders (orderno, bookid, distid,qty)
Solve following queries –
i) Display the details of book whose order is placed having the author Henry Korth’ and publisher ‘TMG’
ii) Find out minimum or ordered quantity maximum of ordered quantity, total number of orders placed by each distributor.
iii) Create a view to display total number of books from each category.
Q. 5) Attempt the following ( Any THREE ) (15M)
a) Write short note on database users.
b) Write short note on – Network Model.
c) Explain ALTER TABLE command with proper example.
d) Explain any 5 Math functions used in MySQL with example.
e) What do you meant by Join? Explain Full Outer Join with suitable example.