Constraints (MySql,tables,null,PK,FK etc)
Home > IT Answers > Database > Constraints (MySql,tables,null,PK,FK etc)
Ak1bo78
30 pts.
0
Q:
Constraints (MySql,tables,null,PK,FK etc)
MySQL, tables, constraints, NULL, NOT NULL, primary key, foreign key
Hi all(waves) noob here ( :) ) ( yes you might look down upon me) ( :( ) never the less....:)
 Quick question:


Tables made so far:
department values (depno,dname)
course values values (courseno)

How would I create a table eg "Table budgets"...with values of two budgets (£100 and £200) for courses.

1.Courses are managed by departments.
2.Department A manages course 1 and 2
3.Department B manages course 3 and 4
4.Department C manages course 5,6,7,8
5.Department D manages course 9 and 10
6.Department E manages no course
7.Courses 1-5 each have a budget of £200
8.Courses 6-10 each have a budget of £300

I know the table needs primary key and foreign keys referencing two tables:department and course.

I have tried the following so far:

CREATE TABLE budgets (depno INTEGER NOT NULL,courseno INTEGER NOT NULL,dname (char)NOT NULL,budgetA INTEGER(200),budgetB INTEGER (300),PRIMARY KEY (depno) REFERENCES department (depno),FOREIGN KEY (courseno) REFERENCES course (courseno));

I have also tried:
CREATE TABLE budgets (depno INTEGER NOT NULL,courseno INTEGER NOT NULL,dname (char)NOT NULL,budgetID INTEGER(1,2,3,4,5,6,7,8,9,10),PRIMARY KEY (budgetID) REFERENCES department (depno),FOREIGN KEY (courseno) REFERENCES course (courseno));

When mysl'd:
Select budgetA from budgets where dname="Department A";

Answer should be:budgetA £200

When mysl'd:
Select budgetID(1),courseno(5) from budgets,course where dname="Department C";

Answer should be:budgetA £200 or £300
depending if its courseno 5 or higher.

All help MUCH appreciated. Yes I know its a short question.Not!! lol well it might be simple for all you SQL pros out there.Ive only used 4months now...:)

Thanks,
Akibo


Software/Hardware used:
MySql, unix etc
ASKED: Nov 4 2009  7:52 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Ak1bo78
30 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
Last Answered: Nov 4 2009  7:52 PM GMT by Ak1bo78   30 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Carlosdl   29795 pts.  |   Nov 4 2009  8:16PM GMT

So, the question is about database design, syntax of the CREATE TABLE statement, the SELECT statements that would get your answers, or the complete solution ?

I would recommend:

SQL Tutorial (MySql)

 

Ak1bo78   30 pts.  |   Nov 4 2009  8:34PM GMT

Thanks…Well design. Because I know once the design is perfect then the querys will be too…Im trying this now:
creat table budget (dname char (30) not null,courseno int null,budget float);

It works!! I used the budget float because Department C manages courseno 5,6,7,8 and courseno 5 is on £200 budget and the rest £300….

BUT when im inserting values as so: insert into budget values (’Department A’,1,2,200) it doesnt work :|… :(….

Thanks for the link but I dont think i can learn that fast and there too much info…its not specific etc…

I appreciate much you help though.

 

Ak1bo78   30 pts.  |   Nov 4 2009  8:36PM GMT

Oh and I inserted values like this: ….(’Department A’,1,2,200) because Department A manages courseno 1 and 2…. :)

 

Carlosdl   29795 pts.  |   Nov 4 2009  10:11PM GMT

You really need that course.

And this:

Database design tutorial

Learning requires some effort.

 
0