Hi, Please i need to develop or actually to propose a Database plan for a housing project, and i need some input from experienced as well as well wishers the tables an d fields to use. Find below a short sumary of the project: 1. We are consultants for a company who intend to build like 2000 housing units in like 30 geographical locations for sale to the public as well as for its staffs. 2. The houses are in form of 2/3 bedroom detached and semidetached bungalows. 3. As consultants, we require estate developers to register with us to bid for the houses they wish to construct. They will bid by submitting their prefered location of intrest as well as category. i.e. 25- 50 units, 50 - 100, 100 -500, 500 and above. As of now, i know i would need a COMPANY TABLE FOR THE BIDDERS, HOUSE TYPES TABLES AS WELL AS CATEGORY TABLE. Please any help will be appreciated. Stay blessed.
What have you come up with so far? I'm happy to suggest ways you can improve what you've got. If your company's not going through a hard time then why not hire a database professional who can do the job properly? (It's strange that it reads like a homework assignment. Also strange that it's such a simple set of requirements for something needed in a professional environment. Are you sure this isn't homework/project work?)
Yes you were right, its actually an assignment but i was trying to use that case study wish i find very confusing. Although i came up with a new idea this mornin and its like a Police Station type Database which will have. OFFICERS TABLE, CRIMINALS TABLES, DEPARTMENT, CRIMES TABLE, COURT TABLE. Please any further help will be appreciated.
Well, I'll try to help (here in the forum, that's how it works, not via PM), but I'm not going to do it for you. Have a look at the sample tables that are installed with the database. I'll assume Oracle, because that's what I use on a regular basis. Have a look at the EMP table (SCOTT schema, which you can specify to be installed during the installation), DESC EMP and/or SELECT * FROM EMP. (SQL*Plus requires queries to be terminated with a ; otherwise it prompts you for another line.) Also have a look at the other tables in SCOTT; the data dictionary query SELECT TABLE_NAME FROM USER_TABLES is quite helpful. You need to decide what information you want stored in each table, and what data type that information needs to be stored in. Text data is usually stored in VARCHAR2(n) where n is the maximum length of the column. Numeric data is stored in NUMBER type, or NUMBER(n) if you want to limit the number of decimal columns. (NUMBER is the same as NUMBER(38).) Dates are stored as DATE, timestamps as TIMESTAMP and there are others; read the SQL manual for a full list of types. Don't worry too much about getting the syntax right at this stage (although if you can that'd be great.) Just list what information you want in a maximum of two tables and, if possible, have a guess at the data type you think might be appropriate. Also you'll need to let us know what database software you're using. They're not all the same, so if you're not using Oracle then I'm probably limited in what I can do. However it is possible to download Oracle without paying licencing fees; I'm not qualified to advise on the precise terms but if you read the licencing agreement before downloading (and you will have to) then you'll be able to determine if it's appropriate to your needs. (I believe it's possible to use Oracle for free, in production, without paying, provided that the database doesn't exceed 1GB, and I think there are other limitations as well. That may or may not be Oracle Express; there are a variety of options to suit all budgets.) Look here for database downloads: http://www.oracle.com/technology/software/products/database/index.html and of course the documentation: http://www.oracle.com/pls/db111/homepage
By the way, thanks for coming clean. It's very common for people to post homework and expect it to be done for them, and when you've seen enough of them you get to know the style. I don't mind helping people who are honest.
Thank you so much for your response. But i could hardly understand ur first paragraph. I have even discarded my first plan and came up wit a better plan. At this stage all i need to do is to put up an UML model to submit. and my prospect is im looking at a Police database e.g a small county that has OFFICERS TABLE, UNITS, CRIMAINALS, CRIMES, COURT TABLE. It would be like each officer belongs to a unit, i.e bike patrol, traffic warden, narcotics etc. Each criminal will have a procecuting officer, crime ID, etc, COURT will have juristiction, etc, Hope you understand what i mean. Now i need a complete table with attributes as well as their relationships. Hope its much clearer now. Cheers.
Yes and its Oracle i would implement it with, Bu thats at a latter stage after my tutpr has approved the proposal and i get all the relationships and stuffs. She emphazized that i use UML modelling.
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.
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.
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"?)
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.
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 relates to CRIMES, 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.
