Ineed Help plzzz - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Ineed Help plzzz

Author

Topic

sal800us
Starting Member

3 Posts

Posted - 2010-05-11 : 08:31:28

(a) Create a function called “higher-loaded” that takes as input the ids of two students and returns the name of the student with a higher work load. Test the function by invoking it for the students „s01? and s09?.
(5 marks) (b) Createaprocedureincludingacursordeclarationtomoderatecourseresults
In order to pass a course, a student needs to receive at least 20/50 marks in the midterm assessment (MTA), 20/50 marks in the Final, and 50/100 marks in the total score (MTA+Final). Create a procedure (which includes a cursor) that will receive as input an integer parameter (margin) and will amend the scores of marginal students into a new table, called treated_marginals. Marginal students are defined as those students who could pass the course if up to margin marks are added to their scores. Marks are amended according to the following table:
i. Write a compound statement to create the following tables:
course_marks (a_sid, a_mta, a_final) moderated_marks (s_id, mta_before, mta_after,
final_before, final_after, total_before, total_after). and to populate the course marks table with the following test
case
treatment
1. failed
MTA? 20
mta only
2. failed
Final ? 20
final only
3.failed
MTA ? (50-Total)
total only
4.failed
MTA?MTA + (Total-50)
mta and
total
5.failed
Final ? Final+(Total-50)
final and
total
cases:
course_marks
6 of 10
a_sid
a_mta
a_final
1
22
32
2
19
50
3
2
50
4
19
19
5
2
20
6
18
20
7
20
20
8
24
24
9
18
30
10
30
18
11
50
19
12
19
29
13
29
19
(3 marks)
ii. Write 5 SQL select statements to identify each of the 5 groups of marginal students as indicated in the table above. Assume margin = 2.
(10 marks) iii. Develop a procedure named moderate(margin) that takes as input
the desired allowable margin (an integer), and using a cursor to moderate the marks by adding small offset marks to allow marginal students to pass the course. The procedure should take its input from the table:
course_marks (a_sid, a_mta, a_final)
and for every identified marginal student, should add a tuple to the table moderated_marks, which contains all old scores of marginal students and their new scores:
moderated_marks (s_id, mta_before, mta_after, final_before, final_after, total_before, total_after).
(15 marks)
7 of 10
(c) In this exercise, you will develop a database trigger for the University database to limit class size with any one given teacher to 3 students only!. Whenever an insert command is issued against the Tutors table which will result in the number of students per tutor class to exceed this limit, a new tuple will be inserted in the waiting list table:
(d)
Waiting_list (student_id, course_code, staff_number) i. Write a compound SQL statement to
1. CreatetheWaiting_listtable,then 2. Createthetriggerintheuniversitydatabase. Test the trigger as follows:
3. 4.
5. 6. 7.
ii.
iii.
Insert the following tuple to the student table: (S58, Nancy, 10 downing street, nancy@downing.com, 2010-8-30, 1) Enroll this student in course c7 (Pragmatics) by inserting an appropriate record in the enrolment table for the course c7 at a date later than the date of registration in order to avoid violating existing constraints.
Then assign tutor Lai (staff_number 4324) to teach Nancy the course c7 by inserting an appropriate record into the tutors table Then show the contents of the waiting list table. Nancy?s new record should be found there.
Finally, show also Nancy?s record in the tutors table. It should still be there.
(10 marks) After you finish, write a compound statement to clean up everything
you have done and restore the University database to the state before you worked on part (c) of this problem.
(2 marks) Modify the last trigger so that the new student will not be inserted in
the tutors table, but only in the waiting_list table.
(5 marks)
Explain what is meant by the “lost update problem” and explain its harmful effect. Give an example showing such harmful effects. You may need to consult your e-library to answer this question.
(3 marks) Explain what is meant by isolation levels, and name, with reason, one concurrency problem that is disallowed by all isolation levels.
(2 marks)

i need help in this how to solve it and explain to me im totally died

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-05-11 : 08:38:17

I'm sorry but it seems you're gonna die then. Noone here will do your homework for you.

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

sal800us
Starting Member

3 Posts

Posted - 2010-05-11 : 08:40:55

im not asking to do home work im asking to explain cuz our teacher is really baaaad

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-05-11 : 08:52:38

Then ask precise questions that can be answered in a quick and simple manner, and give the impression that you have tried to some extent yourself. Noone in this site gets paid to post here and you're showing great disrespect when you just cut and paste some assignment and expect us to do all your work for you.

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

sal800us
Starting Member

3 Posts

Posted - 2010-05-11 : 09:14:04

Sorry

i did not mean to disrespect any one and plz as u read i asked gently i need help if u dont want help up to u and i put all together cuz its one Q if u read it and im sorry i dont think this the place where ppl can give help thank u Mr manner

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-11 : 11:26:49

If I do your homework, do I get the qualification?

We're not going to do it all for you. You learn nothing from that. Show what you've tried. Show that you've made some effort. Ask specific questions on the points where you're stuck.

--
Gail Shaw
SQL Server MVP

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-05-11 : 12:07:53

quote:

If I do your homework, do I get the qualification?

LOL. Indeed

sal800us: You sound like you do need some help but you aren't going to get the answers direct from us. Here's some advice though.

!) Don't post garbage like your first post. It's not even readable. If you can't be bothered to format or check your post why would we waste any of our time trying to read it

2) Break the problems down into component parts. For instance for your question a) Create a function called “higher-loaded” First work out how to get the information required. (the work load) for a student and go from there.

3) Try something. You do have your own copy of the database and the tools?

4) Look up examples on whatever help resource your university provides.

5) TALK TO YOUR TUTOR. Please -- you obviously are struggling and your tutors are there to help you -- they don't want you to fail. If for no other reason than it looks bad for them. You should be asking for help from the people you know rather than a bunch of strangers in the first place.

The majority of the posters here are database professionals who post because they like to help people but we won't do the work for you. What's the point if you don't learn anything? It's no fun for us to answer these questions -- we'd probably be more inclined to take issue with the requirements "Create a procedure (which includes a cursor)" - GAH.

Good luck

If you do try something and start to make a stab at the questions feel free to post. You'll get replies.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources