0
xpi0t0s's Avatar, Join Date: Aug 2004
Mentor
I double checked my first paragraph and it seems clear enough. When you get to this stage, install Oracle and make sure you install the sample schemas when you are prompted. Then try the queries I suggested; maybe it'll make more sense then. I don't mind individual sentences being picked on, "what does this mean" sort of thing, but I can't really clarify further if you just say the whole paragraph is gibberish.

UML modelling and database design are quite different beasts. Unfortunately I know nothing about UML. However the net effect is the same; you still have to define what you want in each table, and in the UML you will also decide on the relationships between tables and entries in tables (one/many to one/many - 4 possibilities, and "is a" (inheritance) and "contains" (aggregation) relationships). Data types probably come later in the implementation of the model.

Do you have an example table with attributes and relationships (if there are relationships then presumably there will need to be at least two tables) from an earlier worked example or project? It seems unlikely you'd be dropped straight into this kind of project without any clues at all. We can then look at how to modify what you already have to make what you want.
0
saddik's Avatar, Join Date: Oct 2008
Light Poster
Tnx for your response. Let me break it down, I suppose u understand my proposed POLICE database that im trying to design.

Find attached a simple model i came up with. I would need your professional assistance to help me identify the attributes as well as relationships.

Cheers.
Attached Images
File Type: jpg Sample Model.jpg (45.4 KB, 3 views)
0
xpi0t0s's Avatar, Join Date: Aug 2004
Mentor
OK, so the next step is to work out what information you want in each table. You'll need to determine (it isn't automatically obvious) whether the references flow in the same direction as the arrows or in the opposite directions. For example look at Officers->Unit and Officers->Suspects; you would want a reference to the Unit in the Officers table (this mirrors the use of DEPTNO in the Oracle SCOTT sample schema EMP and DEPT tables) (spanner in the works: or would you? Could you have multiple rows in DEPT, one for each officer?), but would you want a reference to the Officer in the Suspects table or the other way round? If you're not sure you can try it one way and see how well it works; if it doesn't then you swap it over.

Also have a look at the one to one, one to many, many to one and many to many relationships. Officer->Unit is obviously 1->1 (unless Officers can work in "virtual teams" for multiple divisions but that's probably an unnecessary complication). Are there any other 1->1's? Is Suspects->Crimes 1->1 or 1->many? Is Officer->Suspect 1->1? What if multiple officers are involved in the arrest? What if the same officer arrests multiple suspects? What if the same suspect is arrested on multiple occasions by different officers? Does the design suggest a new table, not shown in the diagram, of individual suspects? There are six arrows, so you'll need six relationships defining.

I can't give you a lot of help with the attributes. There will be those that relate to the relationships of course, but apart from that I can't tell you what you should have. The Officers table probably needs an ID field, the officer's name, but what else? Is there more to a Unit than an ID number and a name? Suspects is probably your most complicated table. You'll probably need an ID and a name. The arrows suggest other relational attributes; is there an "arresting officer" reference? a Crimes reference? What if the suspect is charged with multiple offences? You could use a VARRAY to store a list of offence IDs, but then what if they are charged with some and let off others; you might need a list of flags that show the status of the corresponding offences. Or should a suspect charged with multiple offences result in multiple rows in the Suspects table?

There isn't necessarily a single "correct" answer to any of these questions. You might be happy for example to use the officer's name as the ID, but what if you get two officers with the same name? What if one of the officers gets married and changes her name; would you want to update all the corresponding rows in any tables that might refer to the officer? Do you use data as the key or a separate ID? Your tutor may already have suggested a preferred solution to this. (On the occasions where I have designed a database and used data as the ID I've frequently had to add a new column for the key.)

(I've used ID and key interchangeably. Sorry if that's caused confusion.)

To some extent the question of attributes can be translated into "how much work do you want to make for yourself?". It's good to do a thorough job, but how much over the bare minimum do you want to do? Should the Officer table include an ID and a name? (Yes, fairly obviously). What about the officer's address? You could argue HR would need this info (but then again, would they have their own database? Ideally no, but HR departments tend to get very protective about their data and won't share with anyone, often citing Data Protection.) Do you want to store whether the officer is married or not, has any dependents, what are their names, their friend's names, do any of them have a dog, what is the dog's name, what has the dog been immunised against? I'm not extracting the urine; this is the kind of thing you need to think about; if this has gone too far, why?

A good place to start is to determine the bare minimum you need for the whole thing to work.

Also have a think about the the queries you want to run on the tables as this may suggest other relationships that need defining. For example what if different crimes belong to different divisions, and an officer in one division arrests someone on suspicion of a crime belonging to a different division? Should a traffic officer arrest someone on suspicion of supplying drugs? Should the database prevent such entries being made? Are you ever likely to want to query the database for suspects and officers with the same first name? Are there any relationships that make certain officer/suspect combinations unsafe, for example should an officer be allowed to arrest his own brother? Does a suspect need a "status" attribute that determines where they are in the process (under investigation, arrested, charged, convicted, released, others?), and does this suggest another table? You may want to query the database for all suspects who are still under investigation. Do you want to store significant dates, for example arrest date is probably fairly important; should charge date and conviction date be kept? You may want to monitor the time it takes for suspects to move from arrested to charged, for example for officers' job performance monitoring.

Lots of stuff for you to think about there. Some questions you can reject without much thought; I would think for example listing the dog immunisations of officer's children's friends could be rejected fairly quickly. Others will take some time to answer and you may not be sure if the answer is "correct"; you may need just to pick one of the possible answers and run with it for a bit and see what happens; I would certainly need to do this with some of the questions I've asked, and I asked them! As I said before, there is no single correct answer to many of these questions and determining the "best" answer will depend on many factors.

(Heh: "quick reply". Shabbir, how about a new button, "Post extensive rambling"?)
0
saddik's Avatar, Join Date: Oct 2008
Light Poster
Oh my God, u just gave me millions of ideas that i didnt think of. Excellent.

Actually,i would nt want to include most of the attributes that would make the Database complex for me as u know this is just an assignment and not a real project,(mind you im a novice not a guru). Practically i would like just to insert attributes that would make all the tables to have relationships.

And from the logical point of view, some points you made like 'i dont want multiple rows ' i would prefer the arrays for the multiple crime. I am considering a DOG TABLE to link to the OFFICERS, (I HOPE THAT WOULD NOT ADD SOME CONFUSION)

Officers address IS A MUST, although some attributes in the OFFICERS table would be accessible by HR,(but im not including a table for HR).

Based on the DIVISION TABLE (look at it this way, we would have a total of like 10 divisions) cos the POLICE DATABASE is for a small State, and not a national Database. Assume we are on a pilot scheme to test how it would benefit the police.

Im on my way to skool now, as you know i need to sit and ponder more on the good questions you brought up, when i get back i would work on it. and respond.

Finally what do you mean by a new button, "Post extensive rambling"?

Tnx a million for the input.
0
xpi0t0s's Avatar, Join Date: Aug 2004
Mentor
I just thought it was ironic that the button I had to press to post that answer, which is far from a quick reply, is called Post Quick Reply.

One problem with using arrays for multiple crimes is that you're going to have to make all other fields that relate to the crimes into arrays. So there could be just one arrest date, but if the suspect is charged with multiple crimes they may have to face multiple courts (I'm not a CJ expert, maybe that's baloney) and there could be multiple outcomes. As I said though this kind of stuff drops out of the analysis of what you're going to do with the database; for your purposes it may be sufficient to charge one suspect with just one crime at a time, with a mention in the project writeup that some way of handling multiple crimes would need to be considered for a real-world system. That you've thought of and mentioned a fairly obvious hole in the design is often enough to avoid you getting docked marks for apparently having missed that hole.

Another problem with using arrays is that it can make queries difficult to write. If, say, CHARGED is an array where CHARGED[i] relates to CRIMES[i], then listing all suspects that have been charged isn't just a case of saying SELECT * FROM SUSPECTS WHERE CHARGED=1. If you have multiple rows for a suspect charged with multiple crimes then the query (say on the name) is simply SELECT DISTINCT(NAME) FROM SUSPECTS WHERE CHARGED=1.