Home > IT Answers > Database > Constraints (MySql,tables,null,PK,FK etc)
Ak1bo78
150 pts.
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
Gent01
1525 pts.
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • Bookmark and Share
I would take Carlosdl advise and read up. Here is how I would do this:


CREATE TABLE department(
departmentId INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
departmentNumber VARCHAR(16),
departmentName VARCHAR(64) NOT NULL,
isEnabled TINYINT NOT NULL
);

CREATE TABLE course(
courseId INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
departmentId INT NOT NULL,
courseNumber VARCHAR(16),
courseName VARCHAR(64) NOT NULL,
isEnabled TINYINT NOT NULL
);

CREATE TABLE budget(
budgetId INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
courseId INT NOT NULL,
budgetYear INT NOT NULL,
totalBudget DECIMAL(10,2) NOT NULL,
budgetBalance DECIMAL(10,2) NOT NULL
);

To add a department:
INSERT INTO department(departmentNumber, departmentName, isEnabled)
VALUES('1023', 'Humanities', 1);
Note: I don't IDs or numeric types for things like department numbers because they can change and could change to something non numeric.

To add a course:
INSERT INTO course(departmentId, courseNumber, courseName, isEnabled)
VALUES(1, '1026', 'English 101', 1);

To add a budget:
INSERT INTO budget(courseId, budgetYear, totalBudget, budgetBalance)
VALUES(1, 2010, 500.00, 500.00);

To deduct from their budget:
UPDATE budget SET budgetBalance = budgetBalance - 50.00 WHERE budgetId = 1;

If you don't have the budget id you could:
UPDATE budget b, course c, department d
SET b.budgetBalance = b.budgetBalance - 100.00
WHERE b.budgetYear = 2010 AND b.courseId = c.courseId AND c.departmentId = d.departmentId AND c.courseName = 'English 101' AND d.departmentName = 'Humanities';

To get a budget report:
SELECT d.departmentNumber, d.departmentName, c.courseNumber, c.courseName, b.budgetYear, b.totalBudget, b.budgetBalance
FROM budget b
INNER JOIN course c ON b.courseId = c.courseId
INNER JOIN department d ON c.departmentId = d.departmentId
ORDER BY d.departmentName, c.coursename, b.budgetYear

To get a report for a single course:

SELECT d.departmentNumber, d.departmentName, c.courseNumber, c.courseName, b.budgetYear, b.totalBudget, b.budgetBalance
FROM budget b
INNER JOIN course c ON b.courseId = c.courseId
INNER JOIN department d ON c.departmentId = d.departmentId
WHERE courseName = 'English 101'
ORDER BY d.departmentName, c.coursename, b.budgetYear

You can keep changing out that field in the where statement to be able to pull rusults by different fields.
Last Answered: Jan 23 2010  6:42 PM GMT by Gent01   1525 pts.
  •   
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



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

Carlosdl   32725 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   150 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   150 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   32725 pts.  |   Nov 4 2009  10:11PM GMT

You really need that course.

And this:

Database design tutorial

Learning requires some effort.