Paper Of Database Department Of Computer Science and Software Engineering

Let’s get this straight: Database Systems is not about “making spreadsheets bigger.” It’s the foundational discipline of turning chaotic, raw data into a reliable, consistent, and efficient source of truth for entire organizations. This past paper is your blueprint exam. It tests whether you can design a robust data architecture, protect its integrity, and retrieve complex insights with precision—all while juggling the demands of multiple users and potential system crashes.

Forget simple file storage. This is about engineering systems that can handle transactions from millions of users, guarantee that your bank balance is correct, and answer complex questions in milliseconds. It’s where theory meets industrial-scale practice.

What This Paper Actually Architects: Your Data Engineering Judgment

1. The Foundation: Modeling the Real World
The journey begins with abstraction. You must translate a messy business problem into a clean, logical model.

• Entity-Relationship (ER) Modeling: You’ll be given a narrative (e.g., “a university needs to track students, courses, and professors”) and must produce a precise ER diagram with entities, attributes, keys, and relationships (cardinalities: one-to-one, one-to-many, many-to-many). This is the crucial bridge between human understanding and machine structure.
• Relational Model: Translating your ER diagram into a set of normalized tables. You’ll define schemas, primary keys, and foreign keys to enforce relationships.

2. The Heart: Structured Query Language (SQL)
This is the lingua franca of data. You won’t just write basic queries; you’ll craft sophisticated ones that demonstrate deep understanding.

• Data Manipulation Language (DML): Mastery of SELECT statements with complex JOINs (INNER, LEFT, RIGHT, FULL), nested subqueries, and set operations (UNION, INTERSECT).
• Aggregation & Analysis: Using GROUP BY, HAVING, and aggregate functions (COUNT, SUM, AVG, MAX, MIN) to generate insights from data.
• Data Definition Language (DDL): Writing CREATE TABLE statements that enforce constraints (NOT NULL, UNIQUE, CHECK). You’ll modify schemas with ALTER TABLE.
• Advanced SQL: Window functions (ROW_NUMBER(), RANK(), LEAD/LAG) for powerful analytical queries without collapsing rows.

3. The Guardian: Ensuring Integrity and Consistency
A database that gives wrong answers is worse than useless. You’ll be tested on the mechanisms that protect data.

• Normalization: The science of eliminating redundancy and update anomalies. You’ll normalize a table to Boyce-Codd Normal Form (BCNF) or Third Normal Form (3NF), justifying each step. You’ll also know when to denormalize for performance.
• Transactions & ACID Properties: The bedrock of reliability.
  • Atomicity: All or nothing. Implemented via commit and rollback.
  • Consistency: Transactions leave the database in a valid state.
  • Isolation: Concurrent transactions don’t interfere. You’ll explain isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) and their trade-offs between consistency and performance.
  • Durability: Once committed, it’s permanent (involving write-ahead logging).
• Concurrency Control: How to manage simultaneous users. You’ll understand and apply locking protocols and identify problems like deadlock.

4. The Brain: Optimization and Efficient Access
Storing data is easy. Retrieving it quickly at scale is an art.

• Indexing: The single most important performance tool. You’ll explain B+ Trees (the workhorse of database indexes), how they speed up searches, and their cost on inserts/updates. You’ll advise which columns to index based on query patterns.
• Query Processing & Optimization: You won’t just write a query; you’ll analyze its execution plan. You’ll understand how a query optimizer chooses between a full table scan, an index scan, and different join algorithms (Nested Loop, Hash Join, Merge Join).

5. The Modern Landscape: Beyond the Relational Model
The paper will likely test your awareness of the broader ecosystem.

• NoSQL Databases: Understanding when to use key-value stores (Redis), document stores (MongoDB), wide-column stores (Cassandra), or graph databases (Neo4j) based on the data model and access patterns.
• Database Security: Basic concepts of authentication, authorization, and SQL injection attacks (and how to prevent them with parameterized queries).

The Paper’s Ultimate Challenge: The Design & Optimization Scenario
The most demanding question is a mini capstone project:
“Design a database for an online food delivery service. Provide an ER diagram and relational schema. Write a SQL query to find the top 5 most popular restaurants in each borough last month. A critical query joining the Orders, Order_Items, and Restaurants tables is running slowly. Propose two specific, actionable strategies to optimize its performance, explaining the database principle behind each.”
This tests modeling, query crafting, and deep performance tuning in one integrated problem.

How to Conquer This Past Paper:

1. Think in Sets. SQL is a declarative language that operates on sets of rows. Shift your mindset from procedural “how to loop” to declarative “what is the desired result.”
2. Master the JOIN. The vast majority of analytical power comes from correctly joining tables. Be able to visualize Venn diagrams for each JOIN type.
3. Normalize by Instinct, Denormalize by Reason. Practice spotting insertion/deletion/update anomalies. Normalize by default, but know that a well-placed redundant column can save a costly join.
4. Trace the Transaction. For concurrency questions, act out the interleaving of operations from two transactions. This reveals conflicts and deadlocks vividly.
5. Index with Purpose. Remember: indexes are a trade-off. They are not magic; they are a physical design choice based on query workload.

This past paper is your professional certification in data integrity. It proves you can be trusted to design the systems that hold a company’s most valuable asset—its data. Passing it means you’re not just a programmer; you are a data architect who can build foundations that are solid, scalable, and sane.

Database Sessional 1

Database Final Paper

Question 1                                                                                                     20

Table below consist of sample data for items and for salespersons who supply those items, the item no uniquely identifies different item and salesperson names uniquely identify salespersons

• Convert this table to a relation (named item Supplier) in third normal form. Illustrate the relation with sample data in the table
• List the functional dependencies in item Supplier and identify a candidate key
• Identify each of the following an insert anomaly a delete anomaly and a modification anomaly
• Draw the relational schema and show the functional dependencies

Question 2                                                                                                (15)

Using the below scenario construct the ER-Diagram, identify different entities and specify their attribute types. Show the relationship which exist between the entities. What type of relationships exists among different entities? Mention any necessary assumptions that you make.

TCS is leading company for shipped different types of item all across the world because tcs have the latest data on the processing and current site of each shipped item. TCS used a firm wide product tracking information system that keep them up to date about their shipped items because shipped item are the key part of the TCS product tracking information system. Shipped items can be characterized by item number (unique), weight, dimensions, insurance amount, destination, and final delivery date. Shipped items are received into the TCS system at a single retail center. Retail centers are characterized by their type, uniqueID, and address. Shipped items make their way to their destination via one or more standard TCS transportation events (i.e., flights, truck deliveries). These transportation events are characterized by a unique schedule Number, a type (e.g, flight, truck), and a delivery Route.

Question 3                                                                                                   (15)

Consider the following tables:

Order (OrderId, CustomerId , ProductId , Qty, Amount, SalRepId, Date)

SalesRep (SalRepId, SalRepName, OfficeId, Title, Age, HireDate, Manager, Quota, Sales, Target) Office (OfficeId, City, Region, Target, Sales)

Customer ( CustomerId, customerName, Company, CreditLimit, Address, salRepId) Product (ProductId, Description, Price, Manufacturer, Qty_On_Hand)

Write the SQL statements for the following queries:

• List the name and hire date of anyone with sales over 50000
• Show me the result if I raised each person’s quota by 3 percent of their year to date sales.

• List the offices whose sales fall below 80 percent of target
• List sales people whose sales are not between 80 percent and 120 percent of the
• List all the customers whose name starts with ‘A’ or ‘a’
• List orders over 2500, including the name of the sales person who took the order and the name of the customer who placed it.
• List the names of sales people and their mangers(Note: Manager field contain only ID’s not names

Mid Term Examination 2022

Subject DataBase Systems-1 Total Time Allowed: 90 min.

Q.1: Define the goals of High Level Data Model

Q.2: How E-R Model helps in designing an effective database? Q.3: Describe Unary. Binary and Ternary relationship in E-R Model. Also give suitable

examples.

PROBLEM #1

Construct an E-R diagram for a university registrar’s office then transform it into the relational model. The office maintains data about each class, including the instructor, the enrollment, and the time and place of the class meetings. For each student-class pair, a grade is recorded.

PROBLEM # 2

Construct an E-R diagram for a hospital then transform it into the relational model with a set of patients and a set of medical doctors. A log of the various conducted tests is associated with each

patient.

CLO-2 Q.5:

Bank(bankCode, name, address, city, state,zip, phone) Account (accNum, balance, accType, ssn, bankCode,branchNum)

Customer(ssn, fname, Iname, address, city,state, zip, phone) Loan (loanNum, amount, currentBalance, loan Type, ssn,bankCode, branchNum) BankBranch(bankCode, branchNum, address, city, state, zip, phone, capital)

Based on the provided schema pose the following queries in SQL. (a) Retrieve the names of all banks whose name starts with “A” and are in the city of

“Bahawalpur”. (b) Retrieve the ssn, first name, last name and account balance of all customers with accounts with negative balance.

(c) Retrieve ssn, first name, zip code, total balance on account and total current balance of loans

for all customers.

(d) Retrieve bank name, branch number and total loan original amounts for all branches with

capital less than 5 million.