Hamid-World

Course Code : CS-06
Course Title : Database Management Systems
Assignment Number : BCA (4)/Assignment/ 2008
Maximum Marks : 25
Last Date of Submission : 30th April, 2008/30th October, 2008

Question : 1 Assume that a bookshop has database with the following scheme of six relations :

Authors (au_id, au_laname, au_frame, phone, address)
Publishers (pub_id, pub_name, city, state, country)
sales (stor_id, ord_name, stor_address, qty, title id)
stores (stor_id, stor_name, stor_address, city, state, zip)
Title author (au_id, title_id, royaltyper, au_ord)
Titles (title_id, title, type, pub_id, price, pubdate)

Write the following quries in relation algebra :

i- List the authors first and last names for the book titles ' life without fear '.
ii- List the title of the books sold at Barnum's. (Stor_name)
iii- Give the last name (au_lname) and au_ids of the authors whose have sold in the store named Barnum's. Assign an alias to your answer.
iv- Using your alias from the previous problem, give the last names and au_ids of the authors whose books have not sold in the store name Barnum's.
v- List the titles of books, which have not sold any copies at any store (no books sold at all).
vi- List titles of the books which have only sold at Barnum's. (Do not list the books which hava not sold anywhere).
vii- List the titles of the books, which lhave sold at both Barnum's and bookbeat. (Two stor_names).
viii- List the titles and store_names for each book when the book has sold and the title only (with null for the stor_name) if the book has not sold. (use an outer join).
ix- List the stor_ids of the stores which have sold all books published bu New Moon Books (pub_name).
x- List the titles of the books, which have sold at all stores.

Ans:
Query (i) : List the authors first and last names for the book titled 'life Without Fear'.

SQL .
Select au_lname, au_fname
From Authors
where au_id in (select au_id
from titleauthor
Where title_id in (select title_id
from titles
Where upper(title)='Life Without Fear'
)
)

Query (ii) : List the titles of the books sold at Barnum's. (stor_name)

SQL.
select title
from titles
Where title_id in (select title_id
from sales
where stor_id in(select stor_id
from stores
where upper(stor_name)='Barnum's'
)
)

Query (iii) : List titles of the books sold at Barnum's. (stor_name)

SQL.
select distinct au_lname "Last Name", au_id "Author ID"
from Titleauthor
where title_id in(select title_id
from sales
where stor_id in (select stor_id
from stores
where upper(stor_name)='Banum's'
)
)
Query (iv) : Using your alias from the previous problem, give the lastnames and au_ids of authors whose books have not sold in the store named Barnum's.

SQL.
(select distint au_lname"Last name, au_id"Author id"
from titleauthor
)
minus
(select distinct au_lname "last name", au_id "author id"
from titleauthor
where title_id in (select title_id
from sales
where stor_id in (select stor_id
from stores
where upper(stor_name)='Barnum's'
)
)
)

Query (v) : List the titles of books, which have not sold any copies at any store (no books sold at all).

SQL.
(select title from titles)
minus
(select title
from titles
where title_id in (select title_id
from sales
)
)

Query (vi) : List the titles of books, which have only sold at Barnum's (Do not list the books, which have sold anywhere).

SQL.
Select title
from titles T
where not exists(
(
select stor_id
from sales S
where S.title_id=T.title_id
)
minus
(
select stor_id
from stores
where upper(stor_name)='Barnum's'
)
)

Query(vii) : List the titles of the books, which have sold at both Barnum's and bookbeat (Two stor_names)

SQL.
select titles
where title_id in
(
(
select title_id
from sales
where stor_id in (select stor_id
from stores
where
upper(stor_name)='Bookbeat'
)
)
)

Query(viii) : List the titles and stor_names for each book when the book has sold and the title only (with null for the stor_name) if the book has not sold(Use an outor join)

SQL.
select title, stor_name "stor name"
from titles, stores
where titles.tiltle_id=stores.title_id(+);

Query(ix) : List the stor_ids of stores which have sold all books published by New Moon books (pub_name)

SQL.
select stor_id
from stores S
where not exists
(
(select title_id
from titles
where titles
where pub_id in (select pub_id
from publishers
where upper(pub_name)='New Moon books'
)
)
minus
(
select title_id
from stores SI
Where S.store_idSI.store_id
)
)

Query(x) : List the titles of the books, which have sold at all stores.

SQL.
select title
from title T
where not exists
(
(
select stor_id
from stores
)
minus
(
select store_id
from sales S
where S.title_id=T.Title_id)
)




Question : 2 Define the two principle integrity rules for the relational model. Discuss why it is disarable to enforce these rules?

Ans:
Intrgrity rules :-
The relational model includes two general integrity rules. These rules implicitly or explicitly define the set of consistent database states, or changes of sate, or both. Other integrity constraints can be specific, for example, in terms of dependencies during database design.

The principle integrity for the ralational model.
Entity integrity (Integrity Rule 1):-
In a relational database, entity integrity is a property that ensure that no records are duplicated and that no attributes that make up the primary key are NULL. It is one of the properties necessary to ensure the consistency of the database.
Entity Ingegrity ensures that are no duplicate records within the table and that the field that identifies each record within the table is unique and never null.

In a base table no column of a primary key can be null.

Intigrity rules are implicity or explicity define the set of consistent database states or changes of states or both.

Referential Integrity (Integrity Rule 2):-
Referential integrity in a relational database is consistency between coupled tables. Referential integrity usually enforced by the combination of a primary key or candidate key (alternate key) and a foreign key.

If a foreign key exists in a table, either the foreign key value must match wholly null.




Question : 3 What is a view? Discuss the difference between a view and a base relation. Explain what happens when a user accesses a batabase through a view.

Ans:
View: A relation that is a part of the physical databse, (e.g. A virtual relation) is made available to the users, is called a view.

Difference between a view and a base relation :-

Views	Base relation
1. This is one type of ralation which is not part of the physical database.	1. A base relation is a relation that is not a derived relation.
2. It has no direct or physical relation with the database.	2. While it can manipulate the conceptual or physical relations stored in the data.
3. Views can be used to provide security mechanism.	3. It doesn't provide security.
4. Modification through a view. (e.g. insert, update, delete) generally not permitted.	4. Modification may be done with a base relation.

What happens when a user access a data base through a view.

Any opration on the view are automatically translated into operations on the relations from which it is derived, views are dynamic, meaning that changes made to the base relations that effect view attributes are immediately reflected in the view. When users make permitted changes to the view. Those changes are made to the undrlying relations.



Question : 4 Discuss the advantages and disadvantages of hierarchical database management system in comparison with RDBMS. Discuss types of applications suitable for hierarchical DBMS and RDBMS.

Ans:
One of the main advantages of using a database system is that the organisation can exert, via the DBA, centralised menagement and control over the data.
The following are important advantages of DBMS.
ADVANTAGES :
Reduction of Redundancies
Centralised control of data by the DBA avoids unnacessary duplication of data and effectively reduces the total amount of data storage required. It also eleminates the extra processing necessary to trace the required data in a large mass of data.
Sharing data
A database allows the sharing of data under its control by any number of application programs or users.
Data Integrity
Centralised control can also ensure that adequate checks are incorporated in the DBMS to provide data integrity. Data integrity means that the data contained in the database is both accurate and consistent.
Data Security
Data is of vital importance to an organisation and may be confidetial. Such confidential data must not be accessed by unauthorised persons. The DBA who has the ultimate responsibility for the data in the DBMS can ensure that proper access procedures are followed, including proper authentication shemas for access to the DBMS and additional checks before permitting access to sensitive data. Different levels of security could be implemented for various types of data and operations. The enforcement of security could be data value dependent (e.g. a manager has access to the salary details of employees in his or her department only) as well as data type dependent (but the manager can't access the medical history of any employees, including those in his or her department).
Conflict Resolution
Since the database is under the control of the DBA, she or he should resolve the conflicting requirements of various users and applications. In essence the DBA chooses the best file structure and access method to get optimal performance for the response critical applications, while permitting less critical applications to continue to use the database albeit with a relating slower response.
Data Independence
Data Independence is usually considered from two points of view : physical data independence and logical data independence. Physical data independence allows changes in the physical storage devices or organisation of the files to be made without requiring changes in the conceptual view or any of the external views and hence in the application programs using the database. Logical data independence implies chat application programs need not be changed if field are added to an existing record; nor do they have to be changed if fields not used by applicatiojn programs are deleted. Logical data independence indicates that the conceptual schema can be changed without affecting the existing external shemas. Data independece is advantageous in the database environment since it allows for changes at one level of the database without affecitng other levels. These changes are absorbed by the mapping between the levels.

DISADVANTAGES
A significant disadvantage of the DBMS system is cost. In addition to the cost of purchasing or developing the software, the hardware has to be upgraded to allow for the extensive programs and the work spaces required for their execution and storage. The procassing overhead introduced by the DBMS to implement security, integrity, and sharing of the data causes a degradation of the response and through-put times. An additional cost is that of migration from a traditionally separate application environment to an integrated one.
While centralisation reduces duplications, the lack of duplication requires that the database be adequately backeup so that in the case of failure the data can be recovered. Backup and recovery perations are fairly complex in a DBMS environment, and this is exacerbated in concurrent mutiuser database system. Furthermore, a databse system requires a certain amount of controlled redundancies and duplication to enable access to related data items.
Centralisation also means that the data is accessible from a single source namely the database. This increases the potential severity of security breaches and disruption of the operation of the organisation because of downtimes and failures. The replacement of a monolithic centralised database by a federation of independent and cooperating distributed databases resolves some of the problems resulting from failuares and downtimes.




Question : 5 Produce an E-R diagram, which documents the entities, attributes, relationships and cardinality involved in the life insurance Management system. Create a relational schema to hold the necessary information. Identify the tables and perform normalization to the tables to the required normal forms.
NOTE : Assumptions can be made. Whenever necessary.

Ans:
ER : Life Insurance Management.
This system has repository of various policy and their details like pokicy code, name of policy, year of introduce, type of policy (med claim, Health plus, money back, accident, Life Insurance, Vehicle)

Max customer (to how many customer it can be issued)

Risk coverage detail, status (active/close)

Here, policy code is unique for each policy so, policy is a strong entity with key attribute Policy-code.



Fig 1. http://i44.photobucket.com/albums/f5/jonss3456/Assignments/CS-06fig1.jpg


Policy Entity store different detail for different type of policy.
Like, for Corporate Policy, in addition to general policy detail, it keep track of no_of_emp-limit.

For Vehicle Policy, party type (self/third/both),
For mediclaim, store bonus_rate on renewal.
For monetback_policy, it record money back_period, moneyback_return in addition to general details.

Entity specialization concept is applied on policy entity as follow.



Fig 2.http://i44.photobucket.com/albums/f5/jonss3456/Assignments/CS-06fig2.jpg


This system records policy holder details like personal detail, and policy details : personal details like
policy_number (Unique for each policy_holder relationship)
holder_name,
holder_address,
holder_datebirth
sum_assured_amt.
policy_duration

And policy specific details depends on type of policy holder.
for example,
for corporate_holder
it store company_name
No_of_employee,
Employee_share etc.

For Vehicle_Plicy_type,
self coverage_amt, party_coverage_amt. etc.

Policy holder can claim insurance on valid event so for each policy hold, it also maintains claim details like, claim_date, cloaim_event, claim_amount, for vehicle_claime, accedent_place, tird_party, accident_date, are additional information need to maintain.



Fig 3.http://i44.photobucket.com/albums/f5/jonss3456/Assignments/CS-06fig3.jpg


Claime_no is unique for each claim of each policy not in general so it is prime attribute.



Fig 4.http://i44.photobucket.com/albums/f5/jonss3456/Assignments/CS-06fig4.jpg


System also keep track of various policy-premium details as shown in following ER diagram.



Fig 5.http://i44.photobucket.com/albums/f5/jonss3456/Assignments/CS-06fig5.jpg


System also keep track of preminum paid for each polich



Fig 6.http://i44.photobucket.com/albums/f5/jonss3456/Assignments/CS-06fig6.jpg


For Life Insurance management is shown below.



Fig 7.http://i44.photobucket.com/albums/f5/jonss3456/Assignments/CS-06fig7.jpg


Relation schema for this ER
Relation 1: Policy (PCode, PName, Ptype, Status, yr-introduce, max customer, age-limit)
Primary key=Pcode
Unique = PName

Table 2 : Policy Premium (Pcode, Term, Age, Premium, Pay-mod)
Table 3 : Corporate_Policy (Pcode, emp-limit)
Primary key=Pcode
Foreign key=Pcode references Policy (Pcode)

Table 4 : mediclaim_Policy (PCode, cashless-mode, bonus, rate-renewal)
Primary key=Pcode
Foreign key=Pcode refrences Policy (Pcode)

Table 5 : Vahicle_Policy (Pcode, self-coverage-amt)
primary key=Pcode
Foreign key=Pcode references Policy (Pcode)

Table 6 : Moneyback_policy (Pcode, back-amt, ask-period)
Primary key=Pcode
Foreign key=Pcode references Policy (Pcode)

Table 7 : Holder (Policy-no, holder-name, sum-assure-amt, hoid-add, hld-phone, hld-dob, Policy-dur, hld-ssn, hld-type, payment-mode)
Primary key=Policy-no

Table 8 : Corporate-holder (policy-no, comp-name, no-of-emp, emp-share)
Primary key= policy-no
Foreign key=policy-no references holder (policy)

Table 9 : Mediclaim=Hod (Policy-no, bonus-earned)
Primary key=policy-no
foreign key=policy-no references holder (policy no.)

Table 10 : Policy-hld-prem (policy-no, prem-date, prem-amt) fine)
primary key=policy-no
foreign key=policy-no references holder(policy no.)

Table 11 : policy-claim (policy-no, claim-no, claim-dt, claim-amt)
primary key=(policy-no, claim-no)
foreign key=policy-no references holder(policy-no.)

Table 12 : Vehicle-claim (policy-no, claim-no, Accident-place, third-party-ssn, accident-date)
primary key=(policy-no, claim-no)
foreign key=(policy-no, claim-no) references policy-claim (policy-no, claim-no)

Table 13 : Mediclaim (policy-no, claim-no, doctor-id, hospital-name, medical-dtl)
Primary key=Policy-no, claim-no
Foreign key=(policy-no, claim-no)references policy-claim

Normalization

All table excet holder table are normalized holder table is in 2 NF, but not in 3 NF because
Policy-no --> hld-ssn
hod-ssn --> hod-name, hlod-add, hod-phone, hod-dob.
(policy-no), hod-type, payment-mode,
To put holder in 3NF
Decompose it to policy-holder (hod-ssn, hls-nm, hld-add, hld-phone, hls-dob)

And holder(policy-no, hld-ssn, hld-type, payment-mode)