BSc.-Database Systems-Mumbai-April 2018
MUMBAI UNIVERSITY
Subject: Database Systems
Semester: 1
(Time: 2 ½ Hours)
[Total Marks: 75]
N.B. 1) All questions are compulsory.
2) Figures to the right indicate marks.
3) Illustrations, in-depth answers and diagrams will be appreciated.
4) Mixing of sub-questions is not allowed.
5) Assume suitable data if necessary and state it clearly.
____________________________________________________________________________________________________________________________
Q. 1) Attempt All. [15M]
A) Multiple Choice Questions.
1) Select __________ from instructor where dept name=‘ Comp.
Sci. ‘; Which of the following should be used to find the mean of the salary?
a) mean (salary)
b) min(salary)
c) avg (salary)
d) average (salary)
2) Which of the following is the syntax for views where v is view name?
a) Create view v as ‘query name’,
b) Create ‘query expression’ as view,’
c )Create view v as ‘query expression’,
d) Create view “query expression”,
3) Cartesian product in relational algebra is
a) a Unary operator.
b) a Binary operator
c) a Ternary operator
d) not defined.
4) Architecture of the database can be viewed as
a) two levels.
b) four levels.
c) Three levels.
d) one levels.
5) Grant and revoke are __________________ statements.
a) DDL
b) DML
c) DCL
d) TCL
B) Fill in the blanks. [ Use following answer pool to fill the correct answer. ]
[row, column, unique, single, committed, length, DDL, DML]
1) In Mathematical term _________ is called as tuple.
2) Primary key should be ___________.
3) A transactions completes its execution is called as _____________.
4) ____________ is one of the string handling function in mysql.
5) CREATE command is ____________.
c) Answer in one line.
a) Define DBMSj.
C) Write syntax for inserting a row in a table.
c) Why security is needed in DBMS?
d) Define subquery.
e) Give one example of multivalued attributes.
Q. 2) Attempt the following. [15M]
a) What are the Levels of abstraction?
b) Explain Record-based Logical Modal.
c) what do you mean by binary Relationship and Ternary Relationship? Explain with suitable examples.
d) What are the characteristics of Relations?
e) Define following terms.
a) Value Set
b) Composite Attribute
c) Multivalued Attribute
d) Simple
f) Construct an ER Doagra, for Railway Reservation Systems,
[Assume suitable data and mapping cardinalities exist.]
Q. 3) Attempt the following ) ( Any THREE ) [15M]
a) What do you mean by functional dependency?
b) Explain following operations of Relational Algebra with algebraic query example.
i) Selection
ii) Cross Product
c) How do your Backup and Restore Database in MySQL?
d) Explain Group BY and Having Clause of MySQL with suitable query example.
e) Explain all Aggregate functions used in MySQL.
f) Consider following table. Underline fields are key fields.
book ( booked, title, author, publisher, category, price.,)
Solve following Qucries using MySQL-
i) Crate above tale with bookid as Primary Key.
ii) Find out Book titles starts with ‘D’.
iii) Add a column ‘Year’ with data type INT in Book table.
iv)Find out Books with prices in the range of Rs. 500 to Rs. 1000.
Q. 4 Attempt the following (Any THREE) [15M]
a) Explain any 5 String functions used in MySQL with example.
b) What do you mean by Join? Explain Left outer join and Right outer join with suitable query example.
c) what are the different threats to the databases?
d) What do you mean by privileges with respect to databases? How to grant and revoke privileges?
e) Write short note on correlated subqueries. Give example of it.
f) Consider following tables:
Supplier(suppno, same, city)
Orders(orderno, orderdt, qty, amt suppno)
Solve following queries using MySQL.
i) Display details of suppliers who have taken the orders.
ii) Create a view showing the details of suppliers living in each city.
iii) Get the details of suppliers who have not taken any orders. ( Solve with subquery).
Q. 5) Attempt the following (Any THREE) [15M]
a) Write s short note on Aggregation. Explain with example.
b) What are the drawbacks of traditional file processing systems?
c) What is the use of keywords ‘Distinct’ and ‘all’ in MySQL? Explain with query example.
d) How to create and drop a user in MySQL?
e) What do you mean by views? What are its types?