Skip to content

Database Management System

CSE · Midterm · Spring 23

Batch 58, 59

This browser can’t show the PDF here

Open it, and your phone’s PDF viewer takes it, or download it.

Read the questions as text

Read off the PDF by AI, so it may have mistakes. The PDF is the original.

Daffodil International University Faculty of Science & Information Technology Mid Examination, Spring 2023 Course Code: CSE311: Course Title: Database management system Sections & Teachers: All Level: 3 Term: 1 Batch: 58, 59 Time: 1.5 Hrs Marks: 25 Answer ALL Questions [Optional] [The figures in the right margin indicate the full marks and corresponding course outcomes. All portions of each question must be answered sequentially.] 1. Bidyanondo Publishers has decided to store information about all their products and peers in a database. They usually publish around 20 books every year. Each book has a title, an ISBN number, author name and price. Also each book has a theme of their own, like "Novel" or "Poetry". There can be more than one authors of a single book. Information of book authors are also stored into the database. Each author has NID number, name, age, and commission amount for each book. One author can publish multiple books by the publisher. The books are sold in many libraries in all over the country. Listed libraries have their unique id, name, owner name, district and phone number. All libraries sell some books from Bidyanondo publisher, but all the books are not sold in every library. Bidyanondo publishers have their own distributers who distribute books to libraries. They are uniquely identified by their trade License number, and also their name, business area and phone numbers are listed in the database. One library takes books from only one distributor and one distributor covers all the libraries inside their business area. CO1 a) Now, design an ERD based on the above scenario. Be certain to indicate primary keys and cardinality constraints. 6 b) [Figure: An Entity-Relationship Diagram showing entities STUDENT, ADDRESS, COURSE, LECTURER, SUBJECTS, and relationships Attends, Teaches, HAS, Teaches (between LECTURER and SUBJECTS)] Convert the ERD into a relational database schema. Be certain to indicate primary keys and foreign keys. 5 2. What are relational model constraints? Explain with example. 3 CO1 3. Carefully look at the following database instance and write down the SQL queries for the following questions. CO2 id | name | category | price | quantity 1001 | Realme Buds Air 3 | Audio | 4500 | 15 1002 | Insta360 One X2 Action Camera | Camera | 43000 | 4 1003 | OnePlus 7 Pro | Mobile | 45000 | 3 1004 | Logitech z623 2.1 Home Theatre | Audio | 16500 | 0 1005 | Asus ROG G15 2022 | Laptop | 215000 | 2 1006 | Apple iPhone 14 Max Pro | Mobile | 150000 | 6 1007 | Sony ZV-e10 Mirrorless Camera | Camera | 85000 | 0 1008 | Google Pixel 6A | Mobile | 85000 | 2 1009 | Xiaomi Haylou LS02 Smart Watch | Watch | 5000 | 14 a) Write an SQL query to view the information for the most expensive mobile phone. 2 b) Write an SQL query for viewing products that are not available. 1 c) Write an SQL query for showing the average price of products in each category. 2 d) Write an SQL query to view products that costs more than 1,00,000 BDT. 1 4. Carefully look at the following database instance and write the output of the queries followed by them. CO2 id | order | customer | price | date 1001 | Chillox Burgers | Rahat | 450 | 2023-02-23 1002 | Chillox Masala Chicken | Ashik | 150 | 2023-02-15 1003 | Chillox Burgers | Fahim | 500 | 2023-01-25 1004 | Chillox Burgers | Ashik | 1000 | 2023-01-19 1005 | Chillox Pizza | Ashik | 1300 | 2023-01-16 1006 | Chillox Spicy Noodles | Rahat | 150 | 2023-01-14 a) SELECT name, MAX(price) from order; 1 b) SELECT * FROM order WHERE date BETWEEN '2023-01-15' AND '2023-02-15'; 1 c) SELECT * FROM order WHERE price > (SELECT AVG(price) FROM order); 2 d) SELECT DISTINCT(customer) FROM order; 1