Constraints (MySql,tables,null,PK,FK etc)

150 pts.
Tags:
constraints
foreign key
MySQL
NOT NULL
NULL
primary key
tables
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    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)
    68,525 pointsBadges:
    report
  • Ak1bo78
    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.
    150 pointsBadges:
    report
  • Ak1bo78
    Oh and I inserted values like this: ....('Department A',1,2,200) because Department A manages courseno 1 and 2.... :)
    150 pointsBadges:
    report
  • carlosdl
    You really need that course. And this: Database design tutorial Learning requires some effort.
    68,525 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following