Sample Paper

Q1. A nonprofit organization depends on a number of different types of persons for its successful operation. The organization is interested in the following attributes for all of these persons: SSN,
Name, Address, City and Telephone. Three types of persons are of greatest interest: employees, volunteers and donors. Employees have only a DateHired attribute and volunteers have only a skill attribute. Donors only have a relationship (named Donates) with an Item entity type. A donor must have donated one or more items and an item may have no donors, or one or more donors. There are persons other than employees, volunteers and donors who are of interest to the organization, so that a person need not belong to any of these three groups. On the other hand, at a given time a person may belong to two or more these groups, e.g. employee and donor. Include a subclass discriminator
(named PersonType) in your diagram.

a) Identify the main entity types [7 marks]
b) Identify the main relationship types and specify the multiplicity for each relationship. State any assumptions you make about the data. [3 marks]
c) Identify the main attributes associated with each entity/relationship type. [5 marks]
d) Using your answers to (a), (b), and (c), draw a single ER diagram, using Crows Foot notation, to represent the data requirements of water utility. To simplify the diagram, it is only necessary to show entities, relationships, multiplicities and the primary key attributes.
[5 marks]
e) Translate the following ER diagram into a relational database schema.

Q2. Consider the relational database with the following schema. employee(person_name, street, city) works(person_name, company_name, salary) company(company_name, city) manages(employee_name, manager_name)

a) Find the names and cities of residence of all employees who work for First Bank Corporation.

b) Find the company that has the most employees.

c) Find those companies whose employees earn a higher salary, on average, than the average salary at First Bank Corporation.

d) Find the names of all employees who work for First Bank Corporation.

e) Find the names, street address, and cities of residence of all employees who work for First
Bank Corporation and earn more than $10,000.

f) Find all employees in the database who live in the same cities as the companies for which they work.

g) Find all employees in the database who do not work for First Bank Corporation. People may have jobs with more than one company.

h) Find all employees in the database who earn more than every employee of Small Bank
Corporation.

I) Find all employees who earn more than the average salary of all employees of their company. j) Find the company that has the smallest payroll.

Q3 (a) What is the difference between Primary and Secondary indexes? (b) Given the following relation schema: EMPLOYEE (eid, ename, age, salary) WORKS-ON (eid, did, pct_time) DEPARTMENT (did, dname, budget, manager_eid)
•What primary indexes would you create for the above database? Show how you would create them.
* What secondary indexes would you create? Show how you would create them. [5 marks]
(c) Explain the difference between Hash indexes and B+-tree indexes.

Q4 You are given the following information so that you may design a database for a customer who trains dogs:
The company wishes to set up a database dealing with puppies, kennels, and tricks performed by the puppies. The following table contains information, which is included as unnormalized Data
Items for Puppies. In this original list of data, each puppy's description is followed by a list of tricks the puppy has learned. Some might know ten tricks, some might not know any. To answer the question, "Can Fifi roll over?” we need first to find Fifi's puppy record, then scan the list of tricks associated with the record. As you can guess this is awkward, inefficient, and extremely untidy. Your job is to design the Puppy Database for the Company.

Unnormalized Data Item Comment
Puppy Number Every puppy gets a unique number.
Puppy Name
Kennel Code
Kennel Name
Kennel Location
Trick ID 1...n
Trick Name 1...n We'll add a row for every trick
Trick Where Learned 1...n Learned by every puppy
Skill Level 1...n

a) List out all the functional dependencies and identify the primary key
b) You are required to take the above unnormalized data and normalize it to 3NF.