Printable View
Hi all i am a newbie to oracle development. I have just created an EERD (Please see attached) and would like to know how to map my global schema into a set of normalised relations stating the origins of each relation and its role in the database schema. Also what other contraints could i have.
Any assistance would be really appreciated.
SO you want someone to tell you what tables, columns, primary key, foreign keys and indexes you will need?
Is there anything else??? :rolleyes:
Tony, I admire your cheek - that's $'000 of consulting you're asking for free!
You might want to look at the sub-typing of Client & Branch:
- is it necessary?
- a buiness-org doesn't have gender, salary, first/last name, dob etc: so why are these attributes on the super-type?
- do single and married clients have different attributes?
- do the London, New-York branches have different attributes? not really according to your diagram, so they don't model well as sub-types.
The sub-typing of Loan looks bizzare to me, but I don't know the business.
Sorry didnt mean to be cheeky just got confused and wanted some advice to make sure i have got the right erd and your questions have really helped. Actually thought i was doing ok but dont have any teachers to go to as i am doing self study. Now that i know that mapping means getting the right tables, columns, primary key, foreign keys and indexes(what do you mean by indexes know the rest but dont understand indexes?) once i get the erd right i should be able to do it my self
I am trying to create a loans information system from an exercise i am doing (trying to learn oracle as you can see i a not that good hence the stupid questions).
The company offers commercial loans and equity loans to its clients. Applicants for the loans can be single or private individuals, married couples or business organisations. The commercial loans can be secured with collateral in the form of insurance, property, stock or any bonds accepted by the company. The company has three branches London,Newyork and Johannesburg. London and Newyork are the operational branches and the hq in jburg only houses the manage staff. both the operational branches will utilise identical schemas in terms of the database system.
The subtyping for client was used because the clients applying for loans will fall under 4 different categories as shown in the erd (single/private/married/business org.
In terms of setting the attributes if i change the attibutes to the following would it help:
First name/last name changed to client name
salary change to income
dob removed
gender removed
or should i have different attributes for the different subtypes and just leave the primary key under client as the only attribute.
There is a client category as an attribute and i was going to use a check constraint to select all the different attributes.
In terms of the branch entity i have used the sub type because i am going to be distributing the loans systems as follows:
1 the commercial loans data will be stored at the london site
the home equity loan at the newyork site.
I was going to fragment it by creating a view to select all the commercial loan data where the location is for london (am i right in using subtypes and should the branched have different attributes).
Now that you know about the business can you give me advice on where i can improve my ERD.
hi all,
just a follow up to my thread. I am just looking for some pointers. just want advice on what i am doing wrong and if possible point me towards the right direction
Tony, don't go over-board with sub/super-types. Object Oriented DBMS are strong on it, weaker on relationships and vice versa for Relational DBMS (like Oracle).
I will never drive your car, you will never drive mine. That doesn't make them different sub-types; they differ in their relationship to the "driver" entity.
Like wise, I think your branches differ in their relationship to "Loan (Type)" . . . . yes? In which case there will be a table which says what kind of loan is issued from which office, and will help enforce your business rule.
BTW I'd think about whether "Loan" or "Loan Type" is a better name.
Thanks for replying.
I can see where you are coming from regarding the subtypes and supertypes (frankly after looking at it i thought it was too much hence the cry for help!!)
My questions here are:
If i was to create the table as suggested what will happen to the subtypes under the loan entity will they still remain as subtypes and is there a better way of designing the erd to remove the subtypes under commercial loan used for the collateral type.
Should i just make the make the locations under branch attributes.
How do i create the relationship. Please help
:confused:
I have made some changes to the schema based on the advice i recieved and would like to know if i am on the right track. I dont know if i have got the right name for the entity loan_type_location any suggestions will be greatly appreciated.
I have made some changes to the schema based on the advice i recieved and would like to know if i am on the right track. I dont know if i have got the right name for the entity loan_type_location any suggestions will be greatly appreciated