Need help on database


Sorry, I'm a newbie out there when it comes to creating database. I currently have an ASP.Net project which requires a database. I spent the past few days thinking about how to create a proper database for the application but to no avail. I think tis thread might be out of point for tis forum, but i am really in need of help.

Here goes the storyline of my database:

The project is an online project management system, meant for students to submit their deliverables (reports, eg. Minutes, agenda, project monitor chart) Lecturers will access the web application to grade the students submission. The application must allow administrators to add in new subjects. When a new subject is added, the subject leader will set a dateline for a particular module to be submitted. Eg, minutes dateline. Once the dateline is due, the system will inform the lecturers in charge of who/which group did not submit the module. Lecturers and subject leaders can then generate warning letters to be sent via email to the students involved. Lecturers and subject leaders will be able to make announcements through the system as well.

A student can either submit his/her work as an individual or as a group.
A student will be studying more than 1 subject.
A subject will consist of more than 1 module.
Every module will have a dateline for its submission.
Students can make more than 1 submission for each module.
Students belong to different groups/classes for different subjects.
For different modules, there will be different fields to be inserted into the database.
Example, in the Minutes module, the student will have to be able to key in the objectives, the date of the meeting, the venue, its members, absent members, actual minutes, action by and meeting end time.

In the Project monitor chart module, the student will have to key in the week/date, the name of the member, task involved, as well as the status.

When a new subject is created, the subject leader will have to specify the percentage of each different module. (Eg, Exams 40%, Term test 15%, and so on.)

A subject will also be taught by more than 1 lecturer. So subject leader will specify the lecturers teaching that particular subject.


I hope someone out there can help me out on the construction of a database for the above storyline.
Posted On: Wednesday 7th of November 2012 02:31:55 PM Total Views:  188
View Complete with Replies

RELATED TOPICS OF Microsoft SQL Server PROGRAMMING LANGUAGE




Advice/Options needed

HI, I have currently been working on a script which imports a very large CVS file into a table using the BULK INSERT command. However to enable the script to woork on the hosted server, I had to adjust the Server Roles in the SQL Server, adding the database in question to the Bulk Insert Administrators members. I was wondering if this could cause any potential security issues Anny advice from people in the know would be great
VIEWS ON THIS POST

68

Posted on:

Saturday 10th November 2012
View Replies!

Opinion needed.

I have a general question and would like some feedback. Currently at work IT is consolidating all server data in the department. Our server has to stay separate from the others due to the nature of the data on the database, (SQL Server 2000). The web apps that need access to the DB are currently on the same server, but the question has come up whether to move all web apps and pages to one server and the DB to another server, or to only keep the apps that need access to the DB on that server. What are your views on having a DB on one server with everything else on another, Vs. DB and apps on the same server.
VIEWS ON THIS POST

72

Posted on:

Saturday 10th November 2012
View Replies!

Migrating database between webhosts - urgent help needed

I have a problem. A big problem. Two months ago we launched a web portal on the servers of UK company UKHost4U. Thier service soon proved to be a lot to be deed, shoddy service, downtime, endless database connectivity problems. The final straw came a few days ago when, due to them continually rebooting their servers, our database corrupted. We have decided to move to a new hosting company, but are now experiencing real problems getting a copy of our database. We have been sent a copy of our last backup, but it appears this will be of no use if we move to a new provider. Why Because they outsource thier server management to a US based company called Plesk, who sound like and behave like a villian from Star Trek. Basically Plesk's software had added unwanted extras to our database, and when trying to run it locally it asks for Plesk files. The implications are obvious, if we want our database we have to find a provide that also uses Plesk software. I am not even sure if this is legal. We are not happy with this situation, and want to know how to get a pristine copy of our database to take to a new company. I have heard that there is a process whereby I can make a backup of the databse via ASP, and then reinstall it back into SQL at the other end, but this sounds beyond my novice expertise. Can anyone shed light on this process However, I am enraged I have to take the time and effort to do what should be a (presumably) straightforward task of moving a database to a new host - purely because of inadequite service. What are your experiences of this scenario, is this a common problem If not do I have legal recourse This is reaching crisis level, any help and advice is much appreciated.
VIEWS ON THIS POST

201

Posted on:

Saturday 10th November 2012
View Replies!

asp sql select/display help needed

Website is using asp and is connected to a SQL Database. There are a whole bunch of articles in the DB and I want to be able to display a preview of the article. Eg. the first 50 words or 200 characters (or whatever). Below is the current code that displays the full article. I need to retain this code for displaying the full article but on another section of the site I want to display a preview (50 words/200 characters ish) Any help would be much appreciated. I can be emailed on it@scc.edu.au.
VIEWS ON THIS POST

113

Posted on:

Saturday 10th November 2012
View Replies!

Newbie needs help

I have set up SQL Server on a Windows 2003 Server, also running Sharepoint Team Services. However, I need to tell SQL server to do full text searching within file extensions other than the default. I really hope someone can tell me what I need to do and how, as I can not find any relevant help topics on the web. Many
VIEWS ON THIS POST

118

Posted on:

Saturday 10th November 2012
View Replies!

I need help completing my homework [Writing a statement]

all: I have a few of these to write for school and will appreciate some guideance on this one as a starting point. See the following: Write an SQL statement to display the SKU, SKU_Description and Warehouse for products having QuantityOnHand equal to 0. Sort the results in descending order by Warehouse and in ascending order by QuantityOnHand. Please help, Thank you...
VIEWS ON THIS POST

206

Posted on:

Sunday 11th November 2012
View Replies!

Hi guys im just new to mssql & i need assistance

i've been using ms access as my db, and i think i should upgrade /move on to mssql so then i installed mssql 05 then migrated all the db and dbtables from my old ms access db. and now i wonder how to upload or transfer this db and tables to my hostserver, i have a medium plan where it has mssql but i dont know how to upload this db and tables to my hosting. is there anyway or ideas how should i do this one
VIEWS ON THIS POST

93

Posted on:

Sunday 11th November 2012
View Replies!

Which SQL Server licence will i need?

Hi I am currently in the process of planing to migrate an Access database system across to a more stable and robust SQL Server platform. I am a little unsure to which SQL Server licence to i will need. The office consists of 5 client PC,s that will be used to access the Intranet from which the SQL Server database will drive. One of these 5 PCs only will be used for development of the database. In the future a seperate SQL Server Database will also be written for a new database driven website that will be hosted using a web hosting provider. Can anyone please help me with this one
VIEWS ON THIS POST

72

Posted on:

Sunday 11th November 2012
View Replies!

I need help with a complex query and subqueries

On this page, http://www.specialopspaintball.com/b...ber_finder.asp, when doing a search by RANK, I am having a hard time writing a SQL query that will work. My problem is that RANK is based on a members total POINTS. I need a query that can SUM the points, lookup what RANK the points fall betweeen (based on min and max column values) My test query: SELECT TOP 100 *, (SELECT COUNT(LogID) FROM PlayLog WHERE MemberID = Members.MemberID) AS Logs, (SELECT SUM(Points) FROM Points WHERE MemberID = Members.MemberID) AS Points, (SELECT Rank FROM Ranks WHERE MIN = (SELECT SUM(Points) FROM Points WHERE MemberID = Members.MemberID)) AS Rank FROM Members WHERE Rank = 'Private' The part that is not working: WHERE Rank = 'Private' Why can I not use where on a column that I defined with a subquery, and what's the workaround
VIEWS ON THIS POST

104

Posted on:

Sunday 11th November 2012
View Replies!

Beginner needs advice on sql server with XP

Hi im starting uni in september. i will be studying computing and thought i would get a jump start on the course. I obtained a version of MS SQL server 2000 but when i installed it, the install wizard will only allow me to install the client tools and not an instance of the server. I do not as yet have access to an SQL Server 2000. It does state however, that with operating systems other then XP i can install an instance of the server. I have XP Home edition. If i upgrade to professional then will this resolve the problem and allow me to install an instance of the server, or is their something else I need to do. Any assistance with this matter would be much appreciated. I'm finding it hard enough learning all this stuff without having problems like this!
VIEWS ON THIS POST

97

Posted on:

Sunday 11th November 2012
View Replies!

I need help return all data with the latest Orbitimport ID

Here is my SQL Query. The Table where the data is coming from has 19,000 rows of data however, this only returns 2550 rows of data. In the where statment I am call all the select tables, with the OrbitimportId that is most recent. But it only returns 2500. I need help retirenving the entire liste with the most recent OrbitimportId. Help! SELECT TOP (100) PERCENT dbo.AssetType.Description, dbo.AssetAttribute.AssetDescription, dbo.Asset.Barcode, dbo.Asset.SKU, dbo.ESNTracking.ReportTime, dbo.ESNTracking.CurrLocStreet, dbo.ESNTracking.CurrLocCity, dbo.ESNTracking.CurrLocState, dbo.ESNTracking.CurrLocZip, dbo.ESNTracking.CurrLocCounty, dbo.ESN.EsnNumber, dbo.ESNTracking.DistanceMiles, dbo.ESNTracking.MapUrl, dbo.InventoryOrigin.WarehouseDescription FROM dbo.AssetType INNER JOIN dbo.Asset ON dbo.AssetType.AssetTypeId = dbo.Asset.AssetTypeId INNER JOIN dbo.InventoryOrigin ON dbo.Asset.WarehouseId = dbo.InventoryOrigin.WarehouseId INNER JOIN dbo.AssetAttribute ON dbo.Asset.AssetAttributeId = dbo.AssetAttribute.AssetAttributeId INNER JOIN dbo.EsnAsset ON dbo.Asset.AssetId = dbo.EsnAsset.AssetId INNER JOIN dbo.ESN ON dbo.EsnAsset.EsnId = dbo.ESN.EsnId LEFT OUTER JOIN dbo.ESNTracking ON dbo.EsnAsset.EsnId = dbo.ESNTracking.EsnId LEFT OUTER JOIN dbo.AssetVehicle ON dbo.EsnAsset.AssetId = dbo.AssetVehicle.AssetId LEFT OUTER JOIN dbo.AssetCustomAttribute ON dbo.EsnAsset.AssetId = dbo.AssetCustomAttribute.AssetId LEFT OUTER JOIN dbo.AssetCustomAttributeDef ON dbo.AssetCustomAttribute.AssetTypeId = dbo.AssetCustomAttributeDef.AssetTypeId LEFT OUTER JOIN dbo.OrbitImport ON dbo.ESNTracking.EsnId = dbo.OrbitImport.OrbitImportId WHERE (dbo.ESNTracking.OrbitImportId = (SELECT MAX(OrbitImportId) AS Expr1 FROM dbo.ESNTracking AS ESNTracking_1)) ORDER BY dbo.AssetType.Description
VIEWS ON THIS POST

109

Posted on:

Thursday 15th November 2012
View Replies!

T-SQl help needed

Hi pals, Need small help. There is a test data with aggregated values from which i need to generated the below report. I got stuck over here\t ( whether to use PIVOT / UNPIVOT / anything else). Any help would be greatly appreciated.
VIEWS ON THIS POST

89

Posted on:

Monday 19th November 2012
View Replies!

Sql group by - need help

, I have a query like this: ID Date Email 2 5/19/2011 1@aol.com 2 5/19/2011 2@aol.com 3 5/19/2011 3@aol.com 4 5/19/2011 4@aol.com 4 5/19/2011 5@aol.com How do I select the combined email(s) with any record that is the same ID and Date. I tried this: Select ID, Date, Email From qryName Group by ID, Date, Email I does not work. The output I like to see is: ID Date Email 2 5/19/2011 1@aol.com; 2@aol.com 3 5/19/2011 3@aol.com 4 5/19/2011 4@aol.com; 5@aol.com
VIEWS ON THIS POST

87

Posted on:

Tuesday 20th November 2012
View Replies!

SQL update statement needed

hey all. can anyone provide an SQL example for an update statement that is, i am attempting to write an [event procedure] in MS Access that updates [Assisgnment Count] to a table (tbl_Employee_LifeCycle), along with the other data from a report. Overall i needed to obtian a running Sum for a group in my query results. The only way i found to do this was in a report. therefore, I now all of the deed data (from the query) and the Assignment Count associated by Employee (from the running sum function) in a MS Access report. I would like to now update all of this info to a table. there are two fields in the report, the Assignment Count field, called lbl_RecordCount in the report and Employee Name (lbl_Employee).
VIEWS ON THIS POST

113

Posted on:

Tuesday 20th November 2012
View Replies!

Help needed for query

I have a database table in which the columns are custid and custgroup. custid custgroup ------ ---------- 1 ABC 2 ABC 3 ABC-1 4 ABC-1 5 EF 6 EF-2 7 MN-1 8 MN-1 9 MN-2 10 XYZ Now I want to get the result like this. ABC EF MN XYZ ---- --- --- ----- 4 2 3 1 How can I do this Please help.
VIEWS ON THIS POST

91

Posted on:

Tuesday 20th November 2012
View Replies!

Do we need such tables....

My table for USER information has following three fields country How you reached this site marital status I have used tinybit in all of these fields instead of varchar to reduce the size of each record. Now should I also make corresponding tables to denote the names assigned to each one of these numerical values. For example 1 for married, 2 for unmarried, 3 for divorced etc. Is there any harm in making such tables; even for Gender which would have no more than 2 records. Shadow wizard told me that modern DBMS has no problems with the number of tables. What matters is normalization and indexing. Too many tables don't slow down the performance. CB
VIEWS ON THIS POST

108

Posted on:

Tuesday 20th November 2012
View Replies!

SQL need halp.....

if i do not want to use MAX(score) what else can i use to look for max score SELECT Name, MAX(Score) FROM MyClass
VIEWS ON THIS POST

76

Posted on:

Tuesday 20th November 2012
View Replies!

SPACE and REPLICATE help needed

, I am building a bill of material where based on the level each part is in the bill of material I want to add a number of leading spaces to the output. I can't seem to be able to add the number of spaces as the output comes out without the leading spaces. The REPLICATE function works with the hypen but not the space. HELP!!! SELECT REPLICATE("-",(levelNo-1)*5) + customer_number FROM #tree, pd_parts_master WHERE #tree.id = pd_parts_master.entry_id ORDER BY part_sequence The above code produces this: A 251 620 01 87 -----A 251 620 01 86 ----------A 251 610 01 76 ----------A 251 610 02 76 ----------A 251 610 04 10 -----A 251 625 01 86 -----A 251 626 40 16 Changing to SELECT REPLICATE(" ",(levelNo-1)*5) + customer_number produces this: A 251 620 01 87 A 251 620 01 86 A 251 610 01 76 A 251 610 02 76 A 251 610 04 10 A 251 625 01 86 A 251 626 40 16 Changing to SELECT SPACE((levelNo-1)*5) + customer_number produces this: A 251 620 01 87 A 251 620 01 86 A 251 610 01 76 A 251 610 02 76 A 251 610 04 10 A 251 625 01 86 A 251 626 40 16 Thank you very much for your assistance. Robert Giannone
VIEWS ON THIS POST

93

Posted on:

Tuesday 20th November 2012
View Replies!

Sql inner join need help

, I am trying to do the student evaluation and stuck in this problem. Each student evaluates himself and all his friends in the same class. I have a sample table below. On the query, I tried to join the table to itself to get a new column calls "StudentID-Change" to list a student himself and his class mates. But I can't get the right result. SELECT Table1.StudentID, Table1_1.StudentID, Table1.DepartmentID FROM Table1 LEFT JOIN Table1 AS Table1_1 ON Table1.StudentID = Table1_1.StudentID; ID StudentID ClassID 1 22 100 2 33 100 3 44 100 Here is the result I want to see in query. StudentID StudentID-Change ClassID 22 22 100 22 33 100 22 44 100 33 22 100 33 33 100 33 44 100 44 22 100 44 33 100 44 44 100 Can anyone please help What did I do wrong Why cant I get the right result using the that join
VIEWS ON THIS POST

111

Posted on:

Tuesday 20th November 2012
View Replies!

Storing numeric values-need literal

Guys and Gals, I have run into a problem with the values pulled from SQL and written to an Excel sheet. Since we deal with lab results, this can be a real problem. When the upper spec limit (8.7) is written to the Excel sheet and you click on the cell, the value shown is 8.6999036 or something like that. I need the 8.7 value. I've been reading and it's my understanding that using Real or Float can lead to this problem. It looks like the solution is to store the all numeric values as a decimal data type. Since I'm a newbie, I need to know if this is truly the solution. Your two cents, please.
VIEWS ON THIS POST

104

Posted on:

Tuesday 20th November 2012
View Replies!