Skip to content

Database Management System

CSE · Midterm · Fall 23

Batch 60

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 Department of Computer Science and Engineering Faculty of Science & Information Technology Midterm Examination, Fall-2023 Course Code: CSE311, Course Title: Database Management System Level: 3 Term: 1 Batch: 60 Time: 1 Hour and 30 Minutes Marks: 25 Answer ALL Questions [The figures in the right margin indicate the full marks and corresponding course outcomes. All portions of each question must be answered sequentially.] 1. a) Suppose, you would like to develop a large-scale e-commerce website that may have five thousand concurrent visitors per hour and then you are asked to decide the type of Database architecture for such a website. What architecture you would use and why? [4] CO1 b) Consider the following Scenario: [7] In entertainment industry, television series are very popular. Each TV series distinguished by a unique name and associated network, further backed by specific production companies. These television series encompass numerous episodes, which are identified by their episode numbers, possessing individual titles and durations. Importantly, no episode can exist independently; they are always linked to a particular television series. Additionally, this expansive world of entertainment includes movies, which are primarily identified by their titles and also it has a release year, a studio. Within this dynamic landscape, actors play a pivotal role. They are identified by a full name encompassing their first name, middle initial, and last name, birthdates, and nationalities. Similarly, writers are integral to this ecosystem, identified by their names, birthdates, and different affiliation with many literary agencies. Actors can engage in various roles across the spectrum, appearing as "regulars" in television series, guest-starring in specific episodes, or performing in movies. These versatile individuals contribute to the diverse narratives of this entertainment realm. Furthermore, episodes and movies are enriched by the creative input of writers, shaping compelling storylines and dialogues. Writers are responsible for crafting the narratives of both episodes and movies, contributing their unique perspectives and talents to the industry. Now, From The scenario propose an entity-relationship diagram that captures all the information. Be certain to indicate primary keys, relationship and cardinality constraints. c) Convert this ER diagram into a relational database Schema. [5] [Figure: ER diagram showing entities like User, Reports, Manages, Staff, Login, Authentication system, Publisher, Books, Readers, Keeps track of, maintain, Publishes, and various attributes.] 2. a) Consider the following tables: WorkerInfo (WorkerId, Name, ManagerId, DateOfJoining, City) WorkerSalary (WorkerId, Project, Salary, Bonus) Answer the following questions using SQL query: [6] CO2 i. Select all the workers who either live in "Noristown" or work under a manager with ManagerId - 459219432 or name is Alex. ii. Select the workers whose name begins with any five characters, followed by a text "ghty" and ending with any other characters. iii. Select the project name and maximum salary of the workers under each project in descending order (only include projects with maximum salary more than 40,000). iv. Find the number of workers who have joined the company before 2020. b) Analyze and specify the kind of joining is required to efficiently display all the details about the car that has a registration number. write the query: [3] Vehicle | car_id | chasis_no | model_no | body_type | color C01 | BN3456A | MN007 | SUV | White C02 | BN4567B | OP001 | Sedan | Green C03 | BN9876C | BC005 | CrossOver | Black C04 | BN5678D | AH007 | Convertible | Maroon C05 | BN1012E | MN008 | Sedan | White Registration | chasis_no | registration_no | reg_expiry_date BN3456A | BRTA12001 | 31/01/2027 BN9876C | BRTA12002 | 31/01/2027 BN5641D | BRTA12003 | 31/01/2029