Reference no: EM132738259

KCS 207 Introduction to database management system

Part A:
Question One (Compulsory)
a. Define database management system
b. List three elements of a database management system.
c. Giving examples state three main types of data models.
d. Name four goals of data modeling.
e. With illustration show the four types of mapping cardinalities.
f. With the help of an example illustrate the difference between specialization and aggregation
g. List and explain five basic operators of relational algebra
h. Use examples to show the three types of degree of relationship

Part B

Question Two:
With the use of a diagram discuss the database life cycle

Question Three
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 forms 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.
a. Make an E-R model for the data above. Describe any assumptions about data not shown from the problem. Use Clear ER notations and express as many properties as possible about the data including the participation constraints.
b. Make a relational data model for the micro loans that describe at least two relations using SQL data definition language. (Be sure to make reasonable assumptions about the data types).

Question Four
I. 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
Repayment(borrower_id,name,address,loanamount,requestdate,repayment_date,request_amount)
A borrower is identified with a 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.

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

II. The problem below is about writing an SQL relation from the Repayment problem above. Repayment(borrower_id,name,address,loanamount,requestdate,repayment_date,request_amount) use information provided in three (I) above.
a. Write an SQL request that returns all the tuples with information on repayments from the borrower with Id equal to 42, and where the loan amount exceeds 1000 USD.
b. Write an SQL command, which deletes all information on ended loans, which is to say loans where the total repaid amount equals the lend amount.

Question Five
a. Consider the following relations for a database that keeps track of automobile sales in a car dealership (OPTION refers to some optional equipment installed on an automobile):
CAR (Serial no, Model, Manufacturer, Price)
OPTION(Serial no, Option name, Price)
SALE(Salesperson id, serial no, date, sale price)
SALESPERSON (Salesperson id, Name, Phone)
Specify the foreign keys in this schema stating any assumptions made.
b. With the help of an EER diagram, differentiate between predicate defined and attribute defined subclasses.
c. Differentiate between primary and clustering indexing.
d. List and explain three main types of constraints in relational database.