|
Be very sure this is a real world issue, not just an excercise in theoretical design question on how to build a bulletproof data entry system. Over many years and some fairly high volume data entry apps I have never, not once, had to implement a locking structure to secure against simultaneous editing.
I once wrote a system that kept track of the occurrence of data conflicts to assess their impact. The tracking system ran for 3 weeks and recorded 2 incidents, on a very high volume travel booking system, some twat of a manger had predicted multiple conflicts every hour. When the cost of the tracking was factored in management discarded it.
So make sure you (probably some manager somewhere) are not just wasting your time on unrealistic problems.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks in advance,
I Want to restore a SQL 2005 databse .bak file during setup while i am installing SQL Server in silent mode.
Any Suggestions.
SOFTDEV
Luck in life always exists in the form of an abstract class that cannot be instantiated directly and needs to be inherited by hard work and dedication.
|
|
|
|
|
Why not create a command script (or similar) which first calls the silent setup and after that the script to restore the database. To run the restore script, you could use the newly installed SQLCMD[^].
|
|
|
|
|
RESTORE DATABASE AdventureWorks2008R2
FROM DISK = 'd:\SQLServerBackups\sampleDB.bak'
WITH FILE=1,
NORECOVERY;
|
|
|
|
|
hi
i want to write a good app for schools to use in regeister student.
please help me to create a good database for that !
thanks for your helping ! ! !
|
|
|
|
|
|
sorry ! ! !
i want to design a database for school !
please help me !
|
|
|
|
|
You don't have to be sorry. The problem is that you haven't:
- given any usable information for/about the design
- showed anything you've already done
- pointed out a specific question
These forums are for getting help for example when you get stuck in a certain issue or don't understand a certain detail etc. People here won't hand over the code or database etc.
If you're a student it's important that you learn the tools and techniques you're given lessons in. This won't happen if someone else does the thinking for you.
I would suggest that you have a look at other questions in this forum. You'll quickly notice what is the best way to ask for help. I believe that if you explain yourself well and in detail and more importantly show that you've done some effort then you'll get quality answers.
|
|
|
|
|
ok
i'm student and i want to creat an App for my school.
i learned C# programing but i have problem in sql server .
i can't Normalization what i want !
if any body can to teach me how to create a good database for my school, I will be grateful
|
|
|
|
|
So you really ask:
PLEASE, MAKE THE COMPLETE DESING FOR ME
Look, it isn't this hard; you just have to think about relationships:
- you have *students* with Student_ID and other student related columns
- vou have *teachers* with Teacher_ID and other teacher related columns
- you have *courses* with Course_ID and other course related columns
them are your BASE tables
then you have a relation: which teacher can teach which courses
- *TeachCourse* with Teacher_ID and Course_ID and other columns (i.e Weekday/Time)
also, you have a relation: which student took what courses
- *StudCourse* with Student_ID and Course_ID, no other columns (initially) needed
Underlined columns are the primary keys for the tables
Beware this is just the basic work - you have to refine it for multiple years and so on
HTH
|
|
|
|
|
Oracle has a nice description[^] of the steps. 3NF is usually considered "normalized".
I are Troll
|
|
|
|
|
Hi
I have a column where are values regarding to time. And In my query I want to add additional column where I will have delta between each record. So e.g. between one record and another remain 34 seconds. For this one I was trying to use DateDiff function, and compare my column with times to the same column but offset by one record. And there is the problem. It looks like date DateDIFF(seconds, column, [subquery]) and I have error that subquery return more than 1 value and it its not appropriate where are following; =, != or something like that.
Do you have any ideas? how to manage this problem?
Dnorus
|
|
|
|
|
If you use a subquery to fetch the previous (or next) record you must ensure that only 1 record is fetched. So this means that you have to be able to order the rows based on some column and take only the first, for example using TOP 1 . So your query could look something like:
SELECT ...
DATEDIFF(seconds,
datecolumn1,
(SELECT TOP 1 datecolumn2
FROM YourTable a1
WHERE ...
ORDER BY datecolumn2 DESC))
FROM YourTable
WHERE ...
|
|
|
|
|
It isnt good solution. subquery returns one record for example first one. and Each record in datecolumn1 is compared to only one record from datecolumn2. I want to create some query that I will have data from datecolumn from ID 1 to 10, then create subquery to have data from the same datecolumn from ID 2 to 11, and then use datediff to obtain time delta between following records.
|
|
|
|
|
This is why I wrote WHERE ... inside the subquery. What you must do is to add necessary conditions into the subquery using correlation to the outer query. Since you didn't provide the query and the columns I'm just quessing the columns but try adding something like the following to the subquery
...
WHERE a1.datecolumn1 < a.datecolumn1
...
A1 is the alias for you table in the subquery and A is alias for your table in the main query.
|
|
|
|
|
Oh it is working;) thx.. but it is working for to long.... maybe another way?
something like:
....
Datediff(seconds, datecolumn, (Delete first row from datecolumn and add at the end null))
Is there any possibility to do that? Because now I have almost the same searching in main query and subquery, and I think this main issue for this long working.
|
|
|
|
|
If it's taking a long time, it's probably because for each row in the main query a scan is done to the table to fetch the previous/next row. If possible, can you add an index to the table for the column you're using in the subquery. If several columns are used in the subquery condition consider having more than one column in the index.
|
|
|
|
|
You can build it on this principle.
WITH Ordered AS (
Select IDColumn,DateColumn,ROW_NUMBER() OVER(ORDER BY DateColumn) as rn
FROM YourTable
)
SELECT o2.IDColumn,DateDiff(seconds,o2.DateColumn - o1.DateColumn)
FROM ordered o1
join ordered o2
on o1.rn = o2.rn - 1
|
|
|
|
|
Okey it seems working and also in a very fast way. But I have a another question.
In section With ordered as( ) I have some values declared... and now I dont know where can I put Declare and Set variable..
Ah Ok I didnt have ";".. before With or after declarations
So now it is perfect;)
modified on Monday, March 28, 2011 4:59 AM
|
|
|
|
|
I always struggle when it comes to PIVOTs (as I am pretty sure that is what is required).
I have the following query:
WITH plant As
(SELECT mp.ID, mp.MixPlant_Name
FROM MixPlant mp
WHERE mp.IsActive = 1)
SELECT DISTINCT(mp.MixDate), plant.MixPlant_Name, SUM(mp.NoMixes) As No
FROM MixProduction mp
JOIN plant
ON mp.MixPlant_ID = plant.ID
WHERE mp.MixDate BETWEEN '2/22/2011' AND '2/23/2011'
Group By plant.MixPlant_Name, mp.MixDate
Go
which produces the following result:
MixDate MixPlant_Name No
2011-02-22 00:00:00.000 MTI 88
2011-02-22 00:00:00.000 Plasmec 116
2011-02-22 00:00:00.000 Prodex 27
2011-02-23 00:00:00.000 MTI 69
2011-02-23 00:00:00.000 Plasmec 121
2011-02-23 00:00:00.000 Prodex 15
I am looking to get the following output:
MixDate MTI Plasmec Prodex
2011-02-22 00:00:00.000 88 116 27
2011-02-23 00:00:00.000 36 121 15
Any suggestions, links or gentle sarcasm appreciated.
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
Some have found this article[^] useful, I know you need just the standard 1 column pivot but anyone who can use a CTE should have no problems with this.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks, I had actually looked at that is my wanderings.
I will post my solution when complete.
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
Tada...
DECLARE @List VARCHAR(1000), @Sql VarChar(5000)
SET @List = ''
SET @Sql = ''
IF EXISTS (SELECT * FROM tempdb.sys.objects
WHERE name LIKE '#tblX%' AND type IN (N'U'))
DROP TABLE [dbo].#tblX
CREATE TABLE #tblX(MixDate DateTime, NoMixes Int, MixPlant_Name VarChar(50) )
INSERT [#tblX]
SELECT DISTINCT(mp.MixDate), SUM(mp.NoMixes) As NoMixes, Mixplant.MixPlant_Name
FROM MixProduction mp
INNER JOIN MixPlant
ON mp.MixPlant_ID = Mixplant.ID And MixPlant.IsActive =1
WHERE mp.MixDate BETWEEN '2/1/2011' AND '2/28/2011'
Group By Mixplant.MixPlant_Name, mp.MixDate
DECLARE @tblY TABLE(MixPlant_Name VarChar(50))
INSERT @tblY
SELECT DISTINCT MixPlant_Name
FROM #tblX
ORDER BY MixPlant_Name
SELECT @List = ISNULL(@List, '') +
CASE WHEN ISNULL(@List,'') = ''
THEN '[' + MixPlant_Name + ']'
ELSE ',[' + MixPlant_Name + ']' END
FROM @tblY
ORDER BY MixPlant_Name
SET @Sql = 'SELECT MixDate, ' + @List + CHAR(13)
SET @Sql = @Sql + 'FROM (SELECT MixDate, NoMixes, MixPlant_Name ' + CHAR(13)
SET @Sql = @Sql + 'FROM #tblX ) As P ' + CHAR(13)
SET @Sql = @Sql + 'PIVOT (SUM(NoMixes) FOR MixPlant_Name IN (' + @List + ')) As Pvt' + CHAR(13)
Exec (@Sql)
DROP TABLE #tblX
GO
Thanks for prompting me to persist. Now to add a couple more columns to the pivot
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
Hi guys!
I'm a software developer and not very good in database designing. One day, I was asked something like this :
"For example, there is a company with a web application. One day, the database for that application is way too big, caused performance issues and others, what is the solution for that application database?"
At first, I thought that was about using multiple database with single app. But I don't know if I was right.
I want to ask that what are the solutions? If it's "multiple database" then what should I do? Using two connection to 2 database simutaneously?
I appreciate any replies. Thanks!
...................................................
............ I have nothing to lose ...............
...................................................
|
|
|
|
|
This is a large topic but some keywords you could lookup (taken this is SQL Server):
- (better) indexing
- partitioning
- using filegroups to distribute data across disks
- using well optimized views
- transferring workload to another database using different techniques to copy the data from original db (may not be possible)
- etc etc.
- and last but not least, redesign of the database
I'd say that the key thing is to try to make such modifications which are not visible to the application (expect in response time of course) so using directly multiple db's will cause large redesigns and also typically leads to different kinds of conflict situations.
|
|
|
|