MCS-023 Introduction to Database Management Systems Solved Assignment 2018-2019

Course Code : MCS-023
Course Title : Introduction to Database Management
Systems
Assignment Number : MCA (3)/023/Assignment/2018-19
Maximum Marks : 100
Weightage : 25%
Last Date of Submission : 15th October, 2018 (for July, 2018 batch)
15th April, 2019 (for January, 2019 batch)

This assignment has five questions which carries 80 marks. Answer all questions.
Rest 20 marks are for viva voce. You may use illustrations and diagrams to
enhance explanations. Please go through the guidelines regarding assignments
given in the Programme Guide for the format of presentation.

Question 1: (15 Marks)
List and describe briefly all the possible applications of a database management
system for a University.
Question 2: (20 Marks)
Identify all the associated entities for a University Management System, their
corresponding attributes, relationships and cardinality and design an EntityRelationship
(ER) diagram for it.
Question 3: (20 Marks)
Consider the E-R diagram of Question 2 and design the relational schema and the
tables. Perform and show the Normalization till the required normal form. Implement
the database using MS-Access and submit the screenshots along with your assignment
response for this question.
Question 4: (15X1=15 Marks)
Consider the following relations:
Supplier(S#,sname,status,city)
Parts(P#,pname,color,weight,city)
SP(S#,P#,quantity)
Answer the following simple queries in SQL.
a) Find name of supplier for city = “MUMBAI”.
b) Find suppliers whose name start with “AD”
c) Find all suppliers whose status is 10, 20 or 30.
d) Find total number of city of all suppliers.
e) Find s# of supplier who supplies ‘BLUE’ part.
f) Count number of supplier who supplies ‘BLUE’ part.
g) Sort the supplier table by sname.
h) Delete records in supplier table whose status is 40.
5
i) Find name of parts whose color is ‘red’
j) Find parts name whose weight less than 10 kg.
k) Find all parts whose weight from 10 to 20 kg.
l) Find average weight of all parts.
m) Find S# of supplier who supply part ‘p2’
n) Find name of supplier who supply maximum parts.
o) Sort the parts table by pname.
Question 5: (10 Marks)
Consider a toy-store database has the following schema:
Product(pid: integer, name: varchar(20), min_age: integer)
Manufacturer(mid: integer, name: varchar(20), address: varchar(50))
Supplier(sid: integer, name: varchar(20), address: varchar(50))
Inventory(pid:integer, stock: integer)
Manufactures(mid:integer, pid: integer)
Supplies(sid: integer, pid: integer)
Write and run the following SQL queries on the tables:
a) Find all the product_id’s and names whose manufacturer is LEO company.
b) Find all the Supplier details who supplies police_car toy.
c) Write a SQL statement to insert a new product with pid=-1, name=’my
product’, and min_age=3 into the Product table.
d) List the ids and names of all products whose inventory is below 10.
e) List the ids and names of all suppliers for products manufactured by “TRIKA”.
The id and name of each supplier should appear only once.
f) List the ids, names, and number in stock of all products in inventory. Order the
list by decreasing number in stock and decreasing product ids.
g) List the ids and names of all products for whom there is only one supplier.
h) Find the ids and names of the products with the lowest inventory. Do NOT
assume these are always products with an inventory of zero.
i) List the id and name of each supplier along with the total number of products
it supplies.
j) Find the id and name of the manufacturer who produces toys on average for
the youngest children.
MCS-023, MCS-23, MCS 023, MCS 23, MCS023, MCS23, MCS