MCS-023 Solved Assignment 2018-2019 For IGNOU BCA 3rd Semester and MCA 2nd Semester. Introduction to Database Management Systems Assignment Solution In PDF Format. This solution is valid for July 2018 and January 2019 Session. Click on the button below to open Download Page.
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 voice. 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:
List and describe briefly all the possible applications of a database management system for a University.
Question 2:
Identify all the associated entities for a University Management System, their corresponding attributes, relationships and cardinality and design an Entity-Relationship (ER) diagram for it.
Question 3:
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:
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.
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:
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.
Download MCA Second Semester Solved Assignments For July 2018 and January 2019 Session In PDF Format