Database Management System

Database Management System

Sample Final

2013

Data modeling

Micro loans are small loans, which is beginning to gain popularity especially among borrowers in developing countries. The idea is to bring venture lenders together using information technology. Typically, the loans will be used to finance startup or development of the borrower’s company, so that there is a realistic chance for repayment. The money in a loan can, unlike traditional loans, come from many lenders. In this problem, you must create an E-R model that describes the information necessary to manage micro loans. The following information form the basis for creating the model:

·  Each borrower and lender must be registered with information about name and address.

·  A loan starts with a loan request, which contains information about when the loan should at latest be granted, The total amount being discussed (US-dollars), and how long the payback period is. Also, a description is included of how the money will be used. The rent on the payment is calculated in the loan amount, which is to say, the full amount is not paid .

·  Lenders can commit to an optional portion of the total amount of a loan request.

·  When the commitments for the loan request covers the requested amount, the request is converted to a loan. If not enough commitments can be reached, the loan request is cancelled. A borrower can have more than one request, and more than one loan at a time, but can at most make one request per day.

·  The loan is paid through an “intermediary”, typically a local department of a charity, who has a name and an address.

·  The borrower chooses when he or she will make a payment. Every payment must be registered in the database with an amount and a date (at most one payment per loan per day). The lenders share the repayment based on how large a part of the loan they are responsible for.

·  If the loan is not repaid before the agreed upon deadline, a new date is agreed. The database must not delete the old deadline, but save the history (the deadline can be overridden multiple times).

·  Each lender can for each burrower save a “trust”, which is a number between 0 and 100 that determines the lender’s evaluation of the risk of lending money to that person. The number must only be saved for the borrowers, for whom there has been made such an evaluation.

Make an E-R model for the data described above. If you make any assumptions about data that doesn’t show from the problem, they must be described. Use the E-R notation from KBL. Put an emphasis on having the model express as many properties about the data as possible, for instance participation constraints.

Example answer:

The following relation schema can be used to register information on the repayments on micro loans (see the text in the problem 1 for the explanation on micro loans, and the example on data about micro loans in problem 1).

Repayment(borrower_id,name,address,loanamount,requestdate,repayment_date,request_amount)

A borrower is identified with an unique borrower_id, and has only one address. Borrowers can have multiple simultaneous loans, but they always have different request dates. The borrower can make multiple repayments on the same day, but not more than one repayment per loan per day.

a) State a key (candidate key) for Repayment.

Example answer: {borrower_id,requestdate,repayment_date}

b) Make the normalization to 3NF. State for every step in the normalization, which functional dependency that causes it.

Example answer: Functional dependencies:

·  borrower_id → name address

·  borrower_id requestdato → loanamount

3NF:

·  Repayment1(borrower_id,name,address)

·  Repayment2(borrower_id,requestdate,loanamount)

·  Repayment3(borrower_id,requestdate,repayment_date,repayment_amount)

Design an Entity-Relationship schema for a database of research projects. The database should contain the information about:

− projects: name, manager, budget, duration (in years), funding agency;

− employees: SSN, name, projects, salary;

− funding agencies: name, address.

Each project is funded by a single agency. Project names are unique within an agency. An employee can be associated with several projects. Managers are employees. You can make any other additional assumptions that make sense in the real world.

Solution.

The E-R schema:

− entity types:

·  Project (weak): attributes: Name (partial key), Budget, Duration, borrowed key Aname from Agency;

·  Employee: SSN (key), Name, Salary;

·  Manager;

·  Agency: Aname (key), Address.

− relationship types:

·  Manages(Manager,Project): 1:N;

·  Funding(Agency,Project): 1:N;

·  Employ(Project,Employee): N:M.

− isa relationships:

·  Manager isa Employee.

Produce a relational schema in 4NF from the E-R schema obtained in Problem 1. If your approach

is guaranteed to produce a schema in 4NF, then you do not have to check whether that condition

holds. Identify the keys and foreign keys. Eliminate redundancies.

Solution.

The relational schema:

·  PROJECT(Aname,Pname,Budget,Duration), foreign key Aname references AGENCY(Aname);

·  EMPLOYEE(SSN,Ename,Salary);

·  MANAGER(SSN), foreign key SSN references EMPLOYEE(SSN);

·  AGENCY(Aname,Address);

·  MANAGES(SSN,Aname,Pname); foreign key (Aname,Pname) references PROJECT(Aname,Pname);

·  EMPLOY(Aname,Pname,SSN), foreign keys: (Aname,Pname) references PROJECT(Aname,Pname) and SSN references EMPLOYEE(SSN).

PROJECT and MANAGES can be merged to yield PROJECT(Aname,Pname,Budget,Duration,Manager).

In Figure A, which attribute is multivalued?

A) Years_Employed

B) Employee_ID

C) Skill *

D) Address

In Figure A, which attribute is derived?

A) Years_Employed *

B) Employee_ID

C) Skill

D) Address

The total quiz points for a student for an entire semester is a(n) _____ attribute.

A) derived *

B) mixed

C) stored

D) addressed

Which of the following criteria should be considered when selecting an identifier?

A) Choose an identifier that is stable.

B) Choose an identifier that will not be null.

C) Choose an identifier that doesn’t have large composite attributes.

D) All of the above. *

An attribute that uniquely identifies an entity, and consists of a composite attribute is called a(n):

A) composite attribute.

B) composite identifier. *

C) identifying attribute.

D) relationship identifier.

An entity that associates the instances of one or more entity types and contains attributes specific to the relationships is called a(n):

A) associative entity. *

B) connecting entity.

C) intersectional entity.

D) all of the above.

The number of entity types that participate in a relationship is called the:

A) number.

B) identifying characteristic.

C) degree. *

D) counter.

A relationship where the minimum and maximum cardinality are both one is a(n) _____ relationship.

A) optional

B) unidirectional

C) mandatory link

D) mandatory one *

The ______rule specifies that each entity instance of the supertype must be a member of some subtype in the relationship.

A)  semi-specialization

B)  total specialization *

C)  partial specialization

D)  total convergence

The _____ rule specifies that an entity instance of a supertype is allowed not to belong to any subtype.

A) semi-specialization

B) total specialization

C) partial specialization *

D) disjointedness

The _____ rule specifies that an entity can be a member of only one subtype at a time.

A) exclusion

B) disjoint *

C) removal

D) inclusion

What will be returned when the following SQL statement is executed?

Select driver_no,count(*) as num_deliveries

from deliveries

group by driver_no;

A)  A listing of all drivers, sorted by driver number

B)  A listing of each driver as well as the number of deliveries that he or she has made *

C)  A count of all of the deliveries made by all drivers

D)  None of the above

What will be returned when the following SQL query is executed?

Select driver_no, count(*) as num_deliveries

from deliveries

group by driver_no

having count(*) > 2;

A)  A listing of all drivers who made more than 2 deliveries as well as a count of the number of deliveries *

B)  A listing of all drivers

C)  A listing of the number of deliveries greater than 2

D)  A listing of all drivers who made more than 2 deliveries

Which of the following have affected the way businesses use computing systems to meet the demand of the competitive marketplace?

A)  GUI evolutions

B)  Networking advances

C)  Communication changes

D)  All of the above. *

A PC configured to handle user interface with little or no local storage is called a:

A)  server.

B)  fat client.

C)  thin client. *

D)  workstation.

Which of the following is true about three-tier architectures?

A)  Less scalable than two-tier

B)  Increased risk

C)  More technological flexibility *

D)  All of the above

In the following diagram, which is true?

A)  It depicts a unary relationship.

B)  It depicts a many-to-many relationship.

C)  There is an associative entity.

D)  All of the above. *

A mutually exclusive relationship is one in which:

A) an entity instance can participate in many different relationships.

B) an entity instance can participate in only one of several alternative relationships. *

C) an entity instance can not participate in a relationship with another entity instance.

D) none of the above.

The _____ is the structure that contains descriptions of objects such as tables and views created by users.

A)  SQL

B)  schema *

C)  catalog

D)  master view

Figure B

In Figure B, which of the following are subtypes of patient?

A) Outpatient *

B) Physician

C) Bed

D) All of the above

In a file server architecture, which of the following is performed by a client?

A)  Provides significant LAN traffic

B)  File storage

C)  User interface processing *

D)  All of the above.

A join operation:

A)  brings together data from two different fields.

B)  causes two tables with a common domain to be combined into a single table or view. *

C)  causes two disparate tables to be combined into a single table or view.

D)  is used to combine indexing operations.

What is the English meaning of the following SQL.

select P.profno, P.profname

from Professor P, Section S, Registration R, Registration R1, Section S1, Professor P1

where P.profno = S.profno and S.sectno = R.sectno and R.studno = R1.studno and

R1.sectno = S1.sectno and S1.profno = P1.profno and R1.grade = ’A’ and

P1.profname = ’Jones’

Find professors (profno and profname) who have taught a student who has received an A from a professor named Jones.

The following query finds a weighted average of seat counts by course, in which a given room’s seatCount enters multiple times in the computation, depending on how many times a section of the course has been taught in the room. Modify the query to give a simple average, in which each room’s seatCount enters just once in the computation, even though the room may have hosted several sections of the course.

select C.courseno, C.title, avg(R.seatCount) as AvgSeats

from Course C, Section S, Room R

where C.courseno = S.courseno and S.roomno = R.roomno

group by C.courseno, C.title

Solution:

select C.courseno, C.title, avg(R.seatCount) as AvgSeats

from from Course C, Room R

where exists (select *

from Section S

where C.courseno = S.courseno and S.roomno = R.roomno)

group by C.courseno, C.title

The following query finds a weighted average of seat counts by course, in which a given room’s seatCount enters multiple times in the computation, depending on how many times a section of the course has been taught in the room. Modify the query to give a simple average, in which each room’s seatCount enters just once in the computation, even though the room may have hosted several sections of the course.

select C.courseno, C.title, avg(R.seatCount) as AvgSeats

from Course C, Section S, Room R

where C.courseno = S.courseno and S.roomno = R.roomno

group by C.courseno, C.title

Solution:

select C.courseno, C.title, avg(R.seatCount) as AvgSeats

from from Course C, Room R

where exists (select *

from Section S

where C.courseno = S.courseno and S.roomno = R.roomno)

group by C.courseno, C.title

List six major steps that you would take in setting up a database for a particular enterprise.

1.  Define the high level requirements of the enterprise (this step generates a document known as the system requirements specification.)

2.  Define a model containing all appropriate types of data and data relationships.

3.  Define the integrity constraints on the data.

4.  Define the physical level.

5.  For each known problem to be solved on a regular basis (e.g., tasks to be carried out by clerks or Web users) define a user interface to carry out the task, and write the necessary application programs to implement the user interface.

6.  Create/initialize the database.