MCS-023 Introduction to Database Management Systems Solved Assignment 2018-19
Question 1: List and describe briefly all the possible applications of a database management system for a University. (15 Marks)
Question 2: Identify all the associated entities for a University Management System, their corresponding attributes, relationships and cardinality and design an EntityRelationship (ER) diagram for it. (20 Marks)
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. (20 Marks)
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. (15X1=15 Marks)
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: (10 Marks)
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.
👇Click Below to Download Solution👇
Question 2: Identify all the associated entities for a University Management System, their corresponding attributes, relationships and cardinality and design an EntityRelationship (ER) diagram for it. (20 Marks)
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. (20 Marks)
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. (15X1=15 Marks)
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: (10 Marks)
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.
👇Click Below to Download Solution👇
Comments
Post a Comment