Database

_Damian S_25-Nov-09 19:02

_Damian S_

25-Nov-09 19:02

Generally speaking, each data item (that is not a primary or foreign key) should only appear in one table, otherwise you will have referential integrity issues.

Course table - looks fine.

Instructor table - looks fine.

Class table - Probably doesn't need a composite key between ClassCode and CourseCode, however I assume InstrName is the Instructor name - this doesn't need to be in there, as it's in the Instructor Table.

Enrollment table - Students enrol in a class. You probably need a StudentNumber and a ClassCode, and a Grade. This of course depends on changes you make to your class table.

Add a Student table in - StudentNumber, StudentName etc etc.

Hope this points you in the right direction.

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!!

Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!!
Do something wonderful - make a donation to Camp Quality today!!

Mycroft Holmes25-Nov-09 22:32

25-Nov-09 22:32

I suggest you get your naming convention sorted first, decide whether you are going to use CourseCode, CourseNo, CourseID and stick to it religiously, your life will be much happier. As a principle I avoid composite keys, this is a personal choice, same as I wash my hands after taking a pee. I would have class with a primary key of classid and a foreign key to course unless it is a many to many in which case you need another table ClassCourseLink

Course is ok
Instructor is ok

Can a class only have 1 instructor - are you sure, what about relief instructors
Can a class be in only 1 course, then use a many to many table

Enrolment should be Student > Class no refernce to course

Jörgen Andersson26-Nov-09 1:17

Jörgen Andersson

26-Nov-09 1:17

Mycroft Holmes wrote:
As a principle I avoid composite keys

Composite indices can on the other hand under certain circumstances considerably speed up a query.

Mycroft Holmes26-Nov-09 12:17

Re: table normalization [modified]

26-Nov-09 12:17

Jörgen Andersson wrote:
Composite indices can on the other hand under certain circumstances considerably speed up a query

Ah but I was being derogetry about composite KEYS not indexes. Composite indexes are a basic tool for tuning a database, composite keys annoy me.

Never underestimate the power of human stupidity
RAH

netJP12L26-Nov-09 5:18

netJP12L

26-Nov-09 5:18

Thanks you fellows for your guidance. I have corrected alot of mistakes I had made earlier. I have revised and resposted my work again. I am not sure of the Class and Enrollement tables relationship.

Isn't whenever the Foregin Key is involved it becomes 1 to many relationship. Please correct me. Also, shouldn't the CourseStartDate and CourseExpiryDate be in the Class table and student enrolledDate in Enrollement table.

STUDENT[StudentID(PK), StudentFName]

COURSE[CourseID(PK), CourseName]

INSTRUCTOR[InstructorID(PK), InstructorName]

CLASS[ClassID(PK),CourseID(FK), InstructorID, Location, CourseStartDate,CourseExpiryDate]

ENROLLEMENT[ClassID(PK), StudentID(PK),Grade, EnrolledDate]

Relationships are as follows:
Course and Class: 1 to many
Class and Instructor: 1 to many.

modified on Thursday, November 26, 2009 11:34 AM

The Man from U.N.C.L.E.26-Nov-09 11:24

The Man from U.N.C.L.E.

26-Nov-09 11:24

Looking good, however.

It looks like your CourseStartDate and CourseExpiryDate should be on your course table. The classes probably have thier own dates within the course date range, unless they should be the ClassStartDate and ClassExpiryDate.

Personaly I would prefer to call it the EndDate rather than ExpiryDate. 'ExpiryDate' makes it sound like the class will begin to smell if you leave it in the cupboard to long. Big Grin | :-D

If you have knowledge, let others light their candles at it.
Margaret Fuller (1810 - 1850)
[My Articles] [My Website]

Mycroft Holmes26-Nov-09 12:13

Building an SQL Statement in a stored procedure

26-Nov-09 12:13

netJP12L wrote:
Isn't whenever the Foregin Key is involved it becomes 1 to many relationship

You are correct! A many to many relationship requires a link table. Your structure loks good and I have yet to run across a smelly table no matter what the fields are named.

Never underestimate the power of human stupidity
RAH

Paul McGann25-Nov-09 6:32

Paul McGann

25-Nov-09 6:32

I have the following code

SET @sql = 'SELECT *
FROM tbl_Employee
WHERE tbl_Employee.Department_ID = ' + @Department_ID + AND tbl_Employee.Surname LIKE %' + @Surname + '%'

If i remove the Like part it works fine but with it added it's casuing some problems. I think I'm concatenating the sql wrong.

If someone can help it would be much appreciated.

ASP all the way

Re: Building an SQL Statement in a stored procedure

Shameel25-Nov-09 7:59

Re: Building an SQL Statement in a stored procedure

25-Nov-09 7:59

Do this and check your SQL:

SQL

print @sql

Shameel25-Nov-09 8:01

Re: Building an SQL Statement in a stored procedure

25-Nov-09 8:01

The single quote before the AND keyword is missing, it should be:

SQL

SET @sql = 'SELECT * FROM tbl_Employee
WHERE tbl_Employee.Department_ID = ' + @Department_ID + ' AND tbl_Employee.Surname LIKE %' + @Surname + '%'

Mycroft Holmes25-Nov-09 13:21

Re: Building an SQL Statement in a stored procedure

25-Nov-09 13:21

I presume there is a good reason why you are using dynamic SQL, the select statement does not require it!

This will work without dynamic SQL

Set @Surname = @Surname + '%'

SELECT *
FROM tbl_Employee
WHERE tbl_Employee.Department_ID = @Department_ID 
	AND tbl_Employee.Surname LIKE @Surname

As Shameel suggested print the @SQL, copy the result back and try to run it, then fix it so it runs and make the changes to your dynamic sql construct.

Paul McGann25-Nov-09 21:43

Paul McGann

25-Nov-09 21:43

Thanks guys. The reason for dynamic sql is because this is part of a much larger section of code for custom paging. Again thanks

ASP all the way

Start records from Specific Number

Gandalf_TheWhite25-Nov-09 4:04

Gandalf_TheWhite

25-Nov-09 4:04

Hi all,

I want to start to enter my records from specific number.

let's say I would like to start entry in database table from 1000 instead of 1.

means right now my records are entered in database from 1.
Instead of 1 I would like it to start from 1001. Is it possible? if yes then how?

Believe Yourself™

Abhijit Jana25-Nov-09 4:36

Abhijit Jana

25-Nov-09 4:36

Try This :

DBCC CHECKIDENT (tableName, RESEED, 1000)

Abhijit Jana | Codeproject MVP
Web Site : abhijitjana.net
Don't forget to click "Good Answer" on the post(s) that helped you.

Gandalf_TheWhite25-Nov-09 21:53

Gandalf_TheWhite

25-Nov-09 21:53

That worked!!
Thanks Abhijit Thumbs Up | :thumbsup:

Believe Yourself™

Abhijit Jana25-Nov-09 22:03

Abhijit Jana

25-Nov-09 22:03

Great !

Abhijit Jana | Codeproject MVP
Web Site : abhijitjana.net
Don't forget to click "Good Answer" on the post(s) that helped you.

Shameel25-Nov-09 4:55

25-Nov-09 4:55

Make your question as clear as possible, so others can understand it properly. What you are referring to is probably start an identity column in a table.

When creating the column, you can specify the seed and the increment.

SQL

CREATE TABLE Tab1
(
   Col1 int IDENTITY (1000, 1)
)

If you want to change the seed of an existing column that may already have data, use this:

SQL

DBCC CHECKIDENT ("Tab1", RESEED, 1000);

Gandalf_TheWhite25-Nov-09 21:56

Gandalf_TheWhite

25-Nov-09 21:56

Both worked. Thanks. Thumbs Up | :thumbsup:

Actually I am going to start it from 300000.

Does this huge starting number could create any issue when the records increases??
If there would be any limitation for increasing the amount of the records in future then also please mention. Thumbs Up | :thumbsup:

Believe Yourself™

Shameel26-Nov-09 4:58

Re: INSERTING Data From SELECT

26-Nov-09 4:58

There is no known problem with using IDENTITY columns. It works well. If you want to really have 'big' numbers, use bigint datatype instead of int. The syntax remains the same.

SSIS Script Task

David Muir24-Nov-09 23:34

David Muir

24-Nov-09 23:34

Hey All,

I find myself here asking a question around SSIS which is somewhat out my comfort zone of C# but I have been tasked with some work, and I have came across a problem which I can't seem to over come.

I have an SSIS Package which I am calling from the command line so its in the file system. Its encrypted with password etc. When I am logged in under my own domain user name the script executes perfectly. However; there is a network service user our support department has set up to run this job. When I run this package under this new user, the pacakge fails.

My package has several tasks, however; the task which appears not to run in the Script task. Again, it runs under my own logon, just no with the service user logon.

As a test, I added the service user to the administrator group on the computer the package is running, and the script task worked perfectly. (I think this might be important).

I'm guessing I have a permissions issue with my new user. My thoughts are that it cannot execute vb scripts which I believe are internal to SSIS?

If anyone has any thoughts, could you please help.

Thanks in advance
TF

Re: SSIS Script Task

Vimalsoft(Pty) Ltd25-Nov-09 2:28

Vimalsoft(Pty) Ltd

25-Nov-09 2:28

You need a Configuration File to store the credentials to the Server. To make sure if what you are saying 9is what am assuming. can you please send us the error ?

Vuyiswa Maseko,

Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

[Message Deleted]

mobius11100124-Nov-09 5:24

mobius111001

24-Nov-09 5:24

[Message Deleted]

Shameel24-Nov-09 7:54