Assignment 2 ECI5266 – Advanced Database systems

ECI5266-TMA2 2014-15.txt

THE OPEN UNIVERSITY OF SRI LANKA
DEPARTMENT OF ELECTRICAL AND COMPUTER ENGINEERING


Assignment 2
ECI5266 – Advanced Database systems
The answers to the assignment should be sent under registered post on or before the due date to the following address or should be placed in the relevant box provided at block No. 12 of Colombo Regional Centre.
The Course Coordinator – ECI5266
Department of Electrical and Computer Engineering
Faculty of Engineering Technology
The Open University of Sri Lanka
PO Box 21Nawala, Nugegoda 10250

Due Date: 21.03.2015

Question 1.
This question requires you to use the inheritance features of Oracle object relational system.

Consider a type named student_type that has attributes, (sid: char(8), sname: varchar(15), phone: char(10)).
Let ug_type be a subtype of student_type with attributes, (gpa: real, deptid: char(6), course: varchar(10)).

(a) Write Oracle object SQL statements to create these two types.

-----------------------------
create type aug_type as object (
	gpa real, 				//or ? NUMBER(),
	deptid CHAR(6),
	course VARCHAR2(10)	
);

Create type  student_type as object (
	sid CHAR(5),
	sname VARCHAR2(15),
	phone CHAR(10),
	ug_type ug_type
);

-------------------------------------------------------------------

3 marks)
(b) Assuming that a table named students of student_type has been created, insert aug_type tuple into it withattribute values of sid: 12354326, sname: Janet Paeres, phone: null, gpa: 3.2, deptid: CS01, and course:InfoTech.

–---------------------------------------------------------------

create table students of student_type;

insert into students values(12354326,'Janet Paeres',null, ug_type(3.2,'CS01','InfoTech'));


------------------------------------------
(2 marks)
(c) Assuming there may be data of ug_type and others in the table students of student_type, write an Oracleobject SQL statement to retrieve the sid and sname of only students with deptid of CS01.
(4 marks)
------------------------------------------------------------

select sid,sname from students where value(deptid) is of (ug_type) = 'CS01'

-----------------------------------------
Question 2.
Consider the following object relational database schema for a database of insurance policies for cars:

Object types:
Customer_t (cid: char(6), name: varchar(15), birthdate: date, phone: char(10), address: varchar(50))
Car_t (regno: char(9), make: varchar(12), model: varchar(10), mdate: date, owner: ref customer_t, value: number(8,2))
Claim_t (claimno: char(12), cdate: date, amount: number(8,2), claimant: ref customer)
Claim_ntab table of claim_t
Policy_t (pid: char(7), sdate: date, edate: date, inscar: ref car_t, premium: number(6,2), claims: claim_ntab)

Tables:
Customers of Customer _t (cid primary key)
Cars of car_t (regno primary key, owner references Customers)
Policies of policy_t (pid primary key, inscar references Cars)
Nested table claims store as claims_ntable

-The tables named Customers, Cars, and Policies contain tuples for all customers, cars and policies
respectively. Their attributes are indicated by the corresponding types.
-Attributes of Customers are customer id (cid), name, date of birth, phone and address. Attributes of Cars areregistration number (regno), make, model, date of manufacture, owner of the car, and insured value of thecar.
-Attributes of Policies policy id (pid), starting date, ending date, insured car, annual premium and the claimsmade.
-Attributes of the nested table of claims are claim number (claimno), claim date (cdate), the amount of claimand the claimant.
-The attribute types are specified in the type descriptions above, as also are the primary keys and referentialconstraints in the table schema.

(a) Write Oracle OBJECT SQL statements to answer the following queries (use columns of REF type insteadof joins to link tables):
------------------------------------------------------------

select avg(premium) 
	from 
Policies p, Cars
where  p.

(i) Find the average insurance premium on cars owned by customers aged between 20 and 25 years.
Sysdate contains current date and the function month_between(d1,d2) gives the number of months
in decimals between the dates d1 and d2 where d1>d2.
-----------------------------------------------------




 
(4 marks)
(ii) For each make and model of car, find the total claim amount on policies that expire between 
1 Jan2004 and 31 Dec 2004.
(5 marks)
(b) Write Oracle object SQL to insert a claim against an existing policy that has a pid of SL12354, given thefollowing claim details: claim number: 001, claim date: 12 July 2004, claim amount: 2000, and customer idof claimant: S25431. Assume that the claimant is already present as a customer in the database.
(5 marks)
(c) Write Oracle object SQL to calculate the renewal premium for a given policy in the database using thefollowing logic:
If the policy had no claims or the total claim is less than 1000, then the new premium is the same as thecurrent premium. If the total claim on a policy is greater than or equal to 1000, then the new premium isto be 20% more than the current.
(12 marks)
(d) Using the method defined above in (c), get the renewal premium for the policy of a car with registrationnumber SLA984.
(3 marks)

Where do i get answer of this above questions??

Where do i get answer of this above questions??

sorry i don't have those answers now

do u have the answers??