Dbms Database Management System Assignment 01

Only available on StudyMode
  • Download(s) : 954
  • Published : August 7, 2012
Open Document
Text Preview
ACKNOWLEDGEMENT

First of all I would like to thank a lot to my teacher who is an excellent lecturer. Under his guidance, I learnt a lot about DBMS concepts. And as a result, I become able to complete this assignment with confidence. I am also very thankful to God, my husband, my brother and also some of my classmates who helped me a lot in completing my assignment.

(1) HOLDER
HolderID
Name
Telephone
DateOfBirth
OrgName
AgentID(F.K)
ER- DIAGRAM :-
AGENT
AgentID
Name
Street
City
State
Postcode
Telephone

may sell
supervises
buys
occupied by

EMPLOYEE
StaffID
EmpName
ClaimNum (F.K)
belong toassigned to
MAIN ADDRESS
HouseNum
Street
City
State
Postcode
RENEWAL
ReNum
StartDate
EndDate
NumOfDays
PayOption
PreAmount
PayAmount
RenewNotice
PolicyNum (F.K)
POLICY
PolicyNum
Make
Model
Year
RegNum
MarketValue
ExcessValue
AgentID (F.K)
StaffID (F.K)
occupies
sold by underwritten by
OTHER ADDRESS
PostalAddress
DeliveryAddress
HolderID (F.K)
HouseNum (F.K)
may have sells
underwrites

CLAIM
ClaimNum
Event
ReceiveDate
EventDriver
EventDate
EventDescription
SettleAmount
PolicyNum (F.K)
StaffID (F.K)

received by may receive

COVER TYPE
CoverNum
Description
PreAmount
PolicyNum (F.K)

renews

be renewed
be covered by exam-
ines
EMP_CLAIM
StaffID (F.K)
ClaimNum (F.K)
may cover

(2) ASSUMPTIONS :-

1. The main address can belong to only one policy holder. 2. The other address must belong to only one holder.
3. The entity “MAINADDRESS” is allocated “HouseNum” (House Number) as a Primary key for unique identification. 4. The entity “RENEWAL” is allocated “ReNum” (Renewal Number) as a Primary key for unique identification. 5. There must be renewal of each policy.

6. The main address may relate to one or many other addresses. 7. The other address can relate to only one main address. 8. Each cover type can be covered by only one policy.
9. Every claim may be received by one policy.
10. The entity “AGENT” is provided “AgentID” (Agent Identification number) as a primary key. 11. The entity “CLAIM” is allocated “ClaimNum” (Claim number) as a primary key to uniquely define the values. 12. The entity “COVER TYPE” is allocated “CoverNum” (Cover number) as a primary key.

(3) RELATIONAL DATA STRUCTURE :-

HOLDER(HolderID,Name,Telephone,DateOfBirth,OrgName,AgentID)

AGENT(AgentID,Name,Street,City,State,Postcode,Telephone)

MAINADDRESS(HouseNum,Street,City,State,Postcode)

OTHERADDRESS(PostalAddress,DeliveryAddress,HolderID,HouseNum)

POLICY(PolicyNum,Make,Model,Year,RegNum,MarketValue,ExcessValue,AgentID, StaffID)

EMPLOYEE(StaffID,EmpName,ClaimNum)

RENEWAL(ReNum,StartDate,EndDate,NumOfDays,PayOption,PreAmount,PayAmount, RenewNotice,PolicyNum)

COVERTYPE(CoverNum,Description,PreAmount,PolicyNum)

CLAIM(ClaimNum,Event,ReceiveDate,EventDriver,EventDate,EventDescription,SettleAmount, PolicyNum,StaffID)

EMP_CLAIM(StaffID,ClaimNum)
Note :- The relational database structure is in 3NF.

(4) RELATIONAL DATABASE SCHEMA :-

Table Name Field dataType Description

HOLDER holderID Number(10) Primary Key
Name Text(30)
Telephone Number(20)...
tracking img