Relational database help oracle?

Question

I am trying to learn the process of creating a relational database using Oracle SQL and form builder.

I have details stored about a person, including all the generic thing such as first name, surname, DOB etc, however details of the persons school, college, university, doctor and employer, will also be stored

The person will be inputting the information themselves, and the data will go into the corresponding tables.

I am having some confusion about what to do. In order to make the tables relational, do I include the attributes of school, college, doctor, etc in the table Person, as well as storing these details as their own tables, e.g. College, Doctor, or am I getting this completely wrong?

Here is my SQL code so far, which currently doesn't work.

CREATE TABLE person (personID int NOT NULL PRIMARY KEY, firstname char(20) NOT NULL, surname char(30) NOT NULL, DOB number(7) NOT NULL, gender char(6) NOT NULL, NInumber varchar(9) NOT NULL, city char(30) NOT NULL, emailAddress varchar(50) NOT NULL, schoolName char(100) REFERENCES school(schoolName) NOT NULL, surgeryName char(50) REFERENCES doctor (surgeryName), educationName char(50) REFERENCES college_uni (educationName), employerName char(30), cityed char(30), contacted number(12), emailAddressed varchar(50), addressline1 varchar(30), addressline2 char(30), postcode varchar(7), convictions char(3) NOT NULL);

Any help appreciated as I am getting very confused.

Answer 1

unless you are creating that table as SYS which is not advisable as SYS should only be used by Oracle scripts during updates aso., you need to give your user the proper privileges;

have a look at this link:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7002.htm#i2235043



in the Index (link top right of that web-page) you find entries for CREATE USER and ALTER USER (to give an existing user privileges)



providing the error message you get would help.



if you have created the database yourself and are managing it yourself you may want to familiarize yourself w/ Enterprise Manager

http://download.oracle.com/docs/cd/B19306_01/server.102/b14196/em_manage.htm#sthref126



if someone else created the database & your user (and you don't have the password to become system administrator) contact that person and ask him / her to give you those privileges

Answer 2

Designing a database is not a simple task, as there are a lot of things to consider. Probably the most important is the degree of normalization the tables should have...this impacts the amount of data redundancy (same data stored different places), ease of data access, individual table size and other such things. In your case, an important thing to consider - does a given person always have exactly one school, employer and doctor associated with them? (Probably not, especially over time...a given person could have several or even possibly none.)



What you want are one set of tables to hold details about various entities and another for relationships between entities (and details about the relationship). For example:



Entities

Person (id, name, DOB, gender, ...)

School (id, name, address, ...)

Doctor (id, name, specialty, ...)

Employer (id, name, address, ...)



Relationships

Person_School (personID, schoolID, enrollDate, major, ...)

Person_Employer (personID, employerID, hireDate, ...)

Person_Doctor (personID, doctorID, dateSeen, ...)



This is just a crude example...some of these (especially relationship tables) may further break down into smaller tables. For instance, a person's relationship with a school can easily span many years, not necessarily contiguous. Their major can change as well or a person can have two or even more majors at the same time. The best thing to do is develop what's called a Entity-Relationship diagram...this helps determine all the entities, at least some of the major attributes of each and define the relationships between them. Withough at least a rough idea of that, any database design is likely to be (at best) inefficient, unwieldy and a problem to maintain.

Answer 3

While the question you ask is probably the best question I have reas on Yahoo Answers, it is very open ended and guess what - there is no right answer. I would suggest taking a look at this web site. It has a ton of models that you can look at to get an idea of what you want. Likewise, I would encourage you to get a modeling tool so you can create your model before you actually create a physical database - there are many free ones out there.



http://www.databaseanswers.org



I am an experienced data modeler - If you get in a jam - I have some models that I have no problem sharing with you that would fit the bill for what you are trying to accomplish. Feel free to email me off my profile if you have anything specific to ask regarding data modeling. I can tell you from what I see there are a few problems with the data types you are using.