Database Management System
CSE · Midterm · Fall 23
Batch 60
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