Database/Query Design Help

I am designing an ASP.NET app that can be used to keep track ofattendance at office hours for a class. The purpose of this isthat we need to know if a student is attending office hours bydifferent people (so that we can flag them as "in trouble"). Idon't know if I have chosen the best database design, and I'm lost asto how to accomplish a query I need.
I have a table HoursAttendance that has the following design.
Column_Name Data_Type Length Allow_Nulls
TA char 4 n
Date smalldatetime 4 n
Start smallint 2 n
End smallint 2 n
Student1 bit 1 y
Student2 bit 1 y
Student3 bit 1 y
Student4 bit 1 y
Student5 bit 1 y
I chose to have the students as columns because the students don'tchange, and then you add rows of office hours. If students arethe rows, then you would be adding columns as the semester continuedwhich I thought was odder...? I'm completely open to suggestionson Database Design, because I really wasn't sure.
Ok, so now I need useful queries. The one that I am stuck on (andalso the first one besides select * from HoursAttendance) is that Iwant the names of Students who have attended more than x officehours. So I need something like
select <column name> from HoursAttendance where count(sum(<column name>)) > x
Granted a better table design could help with this. I'mrelatively new to design, so constructive criticism is desired please

You can't run a query like that if the students are columns. My suggestions:

Students
---
StudentID int
StudentLastName varchar(25)
StudentFirstName varchar(25)
HoursAttendance
------
HoursAttendanceID int
StudentID int
TA char(4)
AttendanceDate smalldatetime
Hours tinyint
Select StudentID, Sum(Hours)
From HoursAttendance
Group By StudentID
Having Sum(Hours) > x
Hope that helps!
Marcie

|||This is why I didn't go with something like what you suggested: It seems with the HoursAttendance table set up like that that it should be broken into an Hours table and an Attendance table.
This seems like way too many tables lol!!
Oh well, I guess.|||Could be -- what would you put in the Hours table vs. the Attendance table? For the most part, trying to cram everything into one table leads to a poor database design (and is not normalized).

This seems like way too many tables lol!!

Scoff...at work I work on a system with several hundred tables, this is nothing!
Marcie|||

There are 9 TA's that hold weekly office hours. I have a TA table and a DefaultHours table. The DefaultHours table has when the TA normally holds hours (I use this to set selectedValue in time drop down lists. 5-20 students attend each office hour. So that means there are 5-20 rows that have TA, Date, Start, End in common. Which is where I was referring to moving that to a separate table. *shrug*

|||Sure, you could put that in a separate table then:
OfficeHours
----
OfficeHourID int
TA char(4)
Date smalldatetime
Start int
End int
Attendance
----
OfficeHourID
StudentID
Marcie|||I'm just not sure as to what would be the "best" (most efficient in terms of performance and space)
|||Having the extra tables *saves* space because data isn't repeated in any one table. (Normalization) The performance difference between having one HoursAttendance table and splitting them into two would not even be measurable.
Marcie

Database.Create()

I'm looking at using SQL Server SMO to create databases dynamically from a code generator that I'm writing that will use UML class diagrams of business entities as the input metadata.

Is there a sample for using the Microsoft.SqlServer.Management.Smo.Database class, and specifically the Create() method.

When I try a simple call to Create() after merely assigning the database a name, I recieve an inner exception stating:

"You cannot perform operation Create on an object in state Pending."

Any help would be greatly appreciated.

- Doug

There are two ways doing this:

Server svr = new Server(m_yukon);

Database db1, db2;

if ((db1 = svr.Databases["db1"]) != null)
db1.Drop();
if ((db2 = svr.Databases["db2"]) != null)
db2.Drop();

db1 = new Database(svr, "db1");
db1.Create();

db2 = new Database();
db2.Name = "db2";
db2.Parent = svr;
db2.Create();

Database work

Hello!I'm new here on the forum!
I have a work to do on a class which consists on creating a database and this's a work by checkpoints,which means,I have a limit date to give away part of the work I've done until the next checkpoint.The prob lem is,I don't know where to start!The first thing I'm asked to do is to propose a theme(Managing a bar was what I had in mind) and have its preliminary specification.What do they want to say with 'preliminary specification'?Next I'm supposed to give away the inicial specification and the applicational funcionalities that uses this database...Again I'm not sure what they mean by that...Any help is extremely appreciated!Which school is it? Full name and address, please.

Why? Perhaps someone (among Forum members) goes to that school, or even works there. Then it will be easier to answer such a question. Because, if YOU, who attend those classes (or, at least, you are supposed to) don't have a clue what your teacher wants, how do you expect US to read his mind?

Whatever we think "preliminary specificatons" or "functionalities" are, it is your teacher who EXACTLY knows what he wants you to do. Therefore, I'd suggest you to take a walk, knock on his door and get all required information there.

Then return back and show what you've already done, explain problems you have and someone will surely help you. But, it is a little bit impolite to expect Forum members to do your school assignment from the beginning. Gosh, you don't even know how to start!

"Managing a bar" could be OK if you know to manage a bar (it is not just "a barman pours a drink into my glass"), i.e. know business rules. If not, you'll have to spend some time to get into the business - you can't design something you don't know at all. Therefore, choose a theme you are familiar with.|||To make things easier for you, select a data model here (http://www.databaseanswers.org/). ;)

PS: You may need to mention the site/author in the 'bibliography' or 'references' section of your paper.|||At least you can explain all the time you spend in the bar as "research".

It could have been worse. The assignment could have been for a sewage treatment plant.|||Well we are freely to choose a theme we like...|||After giving some more thought to the "Bar management" theme I've decided to change themes,not only because I'm on a tight schedule and I need to send the theme by mail to the teacher until Monday, but also because it would involve lots of research(and field research) and it's not one of the most easiest subjects to deal with.Also the teacher values originality and a database about a bar is something overused this days(at least I see it that way).
After someone suggested to speak with the teacher about what he really wanted to say with "preliminar specification" ,I mailed my teacher with the question and got a response:

"A preliminar specification could be,for example,

'Caf Management Application' will allow:

- do the stock management of all products(inserting shops and sales,deliveries,invoices,etc.)
- staff management(personal data,contracts,prizes,remunerations)

'Caf Management Application' should have:

- an application of POS(cash register) to be used by the waiter
- a backoffice application to be used by the cafe manager"

My new theme deals with 'Dog's house management'.This's what I've got so far concerning my preliminar specification:

'Management application of a dog's house' will allow:

- when the owners are away the dog's house would be their new home.
- register the dogs' breed and type
- manage the the type of treatments and medical care needed
- register information about the dogs(name,age,color,gender,etc.)
- manage the dogs' family details(name,adress,city,state,contact,etc.)
- manage the vacancies and reservations

Does this preliminar specification looks ok?Should I include something more?Also, what does the term "backoffice" means?
Any help on this is greatly appreciated!Thanks in advance!:)|||Any help,please?