Database I all previous/ past question papers

-Database I –Computer Science all subject past paper  – Database I   with source code – Database I  Course – Database I  idea -Database I MCQ – Database I Interview Question  –Computer Science all courses -Technology –University Past Paper -Programming language –Question paper –old paper

Sessional 1

DB-I S1

Final

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

item no	Item Description	Salesperson name	address	Unit cost
1234	Logic chip	Ali	Peoples colony	10
		Hamza	Madina Town	8
5678	Memory chip	Nasir	Peoples colony	3
		Asad	College town	2
		Anjum	Muslim Town	5

• 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.

#Database I #Computer Science all subject past paper  #Database I # Database I Course  #Database I  idea -Database I  MCQ #Database I  Interview Question  #Computer Science all courses #Technology –University Past Paper #Programming language #Question paper #old paper