Database II all previous/ past question papers
-Database II-Computer Science all subject past paper – Database II with source code – Database II Course – Database II idea -Database II MCQ – Database II Interview Question –Computer Science all courses -Technology –University Past Paper -Programming language –Question paper –old paper
Q.1: Write the four goals of High Level Data Model. [2]
Q.2: How E-R Model helps in designing an effective database? [2]
Q.3: Describe Unary, Binary and Ternary relationship in E-R Model. Also give suitable [3]
examples.
Q.4: [4+4]
PROBLEM # 1
Construct an E-R diagram for a university registrar’s office. 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
Consider the following schema of a movie database with the following schemas:
Theaters (name, city, state, zip, phone)
Movies (title, rating, length)
Shownat (theaters.name, movies.title)
On the schema provided, answer the following questions (SQL Queries).
- a) List all theaters playing the movie “ABC”.
- b) List all the movies playing in theaters in “Vehari” city.
- c) Find the number of theaters in each city.
Database II Sessional 2
Q.1: [6]
A university enrollment database is a distributed database, which utilizes a common data model with three tables as follows:
Students (student#, studentName, Campus, GPA)
Courses(course#, courseName, capacity), where course name is nine digits number and first digit is a 0,1,2,3 or 4.
Enroll(student#,course#)
The university has three campuses, Islamabad, Lahore and Sahiwal. A distributed database implementation of the university enrollment database consists of three sites, one for each campus. Each site consists of information regarding the local students to courses. The tuples partition of the courses relation is such that all courses whose first number is 0, 3, or 4 reside in the Islamabad database, all courses whose first number is 2, 3 or 4 reside in the Lahore database, and all courses whose first number is 1, 2, or 3 reside in the Sahiwal database.
Provide the database schema for each of three database, Islamabad, Lahore, and Sahiwal. Each database schema should be:
- Complete, i.e., it should consist of all of the data regarding students, courses and enrollment within the constraints of the data distribution as given above.
- Minimal e., it should not consist of redundant information. Note that this requirement is specific to each database, although the distributed database consists of repetitions.
Q.2: Differentiate between unilingual and multilingual MDBMS. [4]
Q.3 What are the components of Distributed DBMS? Briefly describe with diagram. [6]
Q.4: Briefly define the terms flexibility, interoperability, and scalability. How does distributed
processing support interoperability, flexibility, and scalability? [6]
Q.5: Consider the working schema presented below, answer the following
questions. [8]
- List name of films that were rented before Oct 2008.
- List the name of customers who rented horror film OR drama.
- Which customers live in Paris and did NOT rent F1?
- List all pairs of customers who live in the same city?
Customer
| C_ID | F_ID | Date |
| C1 | F1 | 1/3/10 |
| C1 | F2 | 10/2/09 |
| C1 | F3 | 9/4/09 |
| C1 | F4 | 1/2/08 |
| C1 | F5 | 1/10/07 |
| C1 | F6 | 8/12/08 |
| C2 | F1 | 1/11/06 |
| C2 | F2 | 1/4/05 |
| C3 | F2 | 12/9/04 |
| C4 | F2 | 18/8/02 |
| C4 | F4 | 25/7/01 |
| C4 | F5 | 15/4/01 |
Rent
| C_ID | C_name | city |
| C1 | Abid | London |
| C2 | Sajid | Paris |
| C3 | Kashif | Paris |
| C4 | Yasir | London |
| C5 | Saud | Athens |
Film
| F_ID | F_name | Price | Type |
| F1 | Jurassic Park III | 2.99 | Action |
| F2 | The Others | 3.55 | Horror |
| F3 | Senseless | 5.99 | Comedy |
| F4 | Dragon Heart | 6.00 | Drama |
| F5 | 007 | 5.00 | Action |
| F6 | America Sweetheart | 0.59 | Comedy |
Database II Final Paper
Q.1: [8]
A multinational engineering company has decided to distribute its project management information at the regional level in Pakistan. The current centralized relational schema is as follows:
Employee (NIN, fname, lname, address, DOB, sex, salary, taxCode, deptNo)
Department (deptNo, deptName, managerNIN, businessAreaNo, regionNo)
Project (projNo, projName, contractPrice, projectManagerNIN, deptNo)
WorksOn (NIN, projNo, hoursWorked)
Business (businessAreaNo, businessAreaName)
Region (regionNo, regionName)
Where
Employee contains employee details and national insurance number NIN is the key.
Department contains details and deptNo is the key. managerNIN identifies the employee who is the manager of the department. There is only one manager for each department.
Project contains details of the projects in the company and the key is projNo. The project manager is identified by the projectManagerNIN, and the department responsible for the project by deptNo.
WorksOn contains details of the hours worked by employees on each project and (NIN,projNo) forms the key.
Business contains names of the business areas and the key is businessAreaNo.
Region contains names of the regions and the key is regionNo.
Departments are grouped regionally as follows:
Region 1: Punjab Region 2: Sindh Region 3: Balochistan
Information is required by business area, which covers: Software engineering, Mechanical Engineering, and Electrical Engineering. There is no Software Engineering in Sindh and all Electrical Engineering departments are in Balochistan. Projects are staffed by local department offices.
As well as distributing the data regionally, there is an additional requirement to access the employee data either by personal information (by Personnel) or by work related information (by Payroll).
Produce a distributed database design for this system, and include:
- A suitable fragmentation schema for the system
- In the case of primary horizontal fragmentation, a minimal set of predicates
- The reconstruction of global relations from fragments
State any assumptions necessary to support your design
Q.2: [2+2+3+3]
Bank(bankCode, name, address, city, state,zip, phone)
Account (accNum, balance, accType, ssn, bankCode,branchNum)
Customer(ssn, fname, lname, address, city,state, zip, phone)
Loan (loanNum, amount, currentBalance, loanType, 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.
Q.3: [6]
Provide an ER Diagram that describes the following requirements for a Online Auction System:
- There are two types of users: Bidders and Sellers
- Both types of users exist as users(identified by a username) that represent individuals.
- One individual can exist as both bidder and seller.
- Items to be auctioned are categorized
- Auctions can have a reserve price and an item can be auctioned more than once either by a different user or if the reserve price in previous auctions was not met.
- Regular bids are entered by bidders before auction close time.
- Proxy bids are automatically incrementing bids that define a maximum amount, a starting amount and an increment over the max bid so that the amount can automatically be updated whenever a higher bid is entered. Every time a higher bid is entered the system parses through the proxy bids and enters regular bids depending on the proxy bids found for that item.
- Besides the standard contact information(ph#, email), individuals have addresses
for billing, shipping and residence information, each of which might be different from each other.
- Bidders and sellers can provide reviews of other individuals only if they have been related through any auction. Reviews include a score(min 0, max 5) and detailed comments from the individual that inputs the review plus a response from the individual who is targeted by the review.
Q.4: [6]
Consider the following schema:
SALE (CUST_NAME, ITEM_NAME, WHEN)
CUST (CUST_NAME, INDUSTRY)
ITEM (ITEM_NAME, CATEGORY, PRICE)
Write RELATIONAL ALGEBRA expressions that will answer the following questions.
(1) Which customers bought something on 02-10-2020?
(2) Which customers from the medicine industry bought something on 02-10-2020?
(3) Which customers have bought something from a category other than “transportation”?
(4) Which customers bought something costing more than 5,000 that was not in the category “spare part”?
GOOD LUCK!
# Database II #Computer Science all subject past paper #Database I # Database I Course #Database II idea -Database I MCQ #Database II Interview Question #Computer Science all courses #Technology –University Past Paper #Programming language #Question paper #old paper
