SQL Server :: Getting An ERD From A Statement ?
Jan 15, 2011
At University it was suggested to us that the best way to learn about designing and implementing databases is to practice the art. So I chose the following statement to implement and refine my skills:
Harpers Hauliers deliver shipments on contract for customers. Each contract is identified by a unique contract number. The contract may require a single or many shipments. Each shipment must be allocated resources of a vehicle (regisatration number, type, volume, weight), which is suitable for the shipment and a driver (name, address, telephone number) who is licensed to drive that type of vehicle.
As soon as a customer (companyname, address, telephone number) makes an enquiry, a pending contract is set up. The contract is then negotiated on price between the customer and the haulier. Not all pending contracts are successfully negotiated. Either the customer or the haulier may back out at this stage. If the haulier has previously carried for this customer and financial settlements were not made to his satisfaction, he may reject the contract. He may also reject the contract on the basis that he thinks the workload being undertaken is too big a risk for his company with this customer that is the contract is worth more than he is willing to risk for this customer.
An agreed contract requires the haulier to deliver the listed shipments between a start and an end date, at an agreed price. The haulier always takes out an insurance policy on the contract, in case he cannot allocate enough resources to the contract to get it delivered on time.For each shipment in the delivery there is an origin and destination. The shipment has a description, a weight and a volume. The haulier has a fleet of vehicles of different types and a staff of drivers with different license types, to match the vehicle types. During the contract, the haulier tries to allocate vehicle, driver and time-slot resources to each ship-ment. The vehicle must have a suitable volume and weight capacity and the driver must have a suitable license to drive that vehicle.
The time must be when the driver and vehicle are free and before the contract end date. As each shipment is delivered, it is marked off as delivered by the driver, giving the date and time of delivery. If not all shipments are delivered by the contract end date, the con-tract becomes overdue, incurring a penalty cost on the haulier. When all shipments are delivered, the haulier invoices the customer for the required amount. Each invoice is has a date and is identified by a unique invoice number . Payment from the customer may be in full or may be partial. The contract is kept active until all payments have been made against it.
And here is the ERD that I have come up with. I have created the ERD in Visio 2010.
[IMG]http://i56.tinypic.com/6s43t4.jpg[/IMG]
Can someone who's an expert in Database design kindly check my ERD and see if it perfectly matches the Problem Statement.
View 16 Replies
Similar Messages:
Sep 25, 2010
have a very important issue,i have three Stored Procedures Sp1,Sp2 and Sp3 .the first one (Sp1) will execute the second one (Sp2) and save returned data into @tempTB1 and the Second one will execute the third one (Sp3) and save data into @tempTB2.if I execute the Sp2 it will works and it will returned me all my data from the Sp3 ,but the problem is in the Sp1, when i execute it it will display this Error:INSERT EXEC statement cannot be nested I tried to change the place of execute Sp2 and it display me another error:Cannot use the ROLLBACK statement within an INSERT-EXEC statement.
View 4 Replies
Feb 27, 2011
The following stored procedure updates the value in LeaveTransaction table.
[code].,...
My Question here:
I have written the above trigger,which would update the value for the corresponding records in another table ie) LeaveCumulative table. The trigger is not updating the value in the designated rows in the leavecumulative table. Whereas when I excute the same update command separately in the Query Editor window the trigger is working fine.
View 1 Replies
Mar 9, 2011
I have a requirement in my app to find out if a certain row exists in a table and then if it does get some fields from another row
I have two parameters an EventId and a UserId.
This is the normal statement I would use to get that row
[Code]....
Then I would check if the result > 0. If it is greater than 0 this is the statement I would use to get the other details
[Code]....
So how can I join this into one statement? This is what I have tried
[Code]....
View 3 Replies
Dec 27, 2010
I've build a query, but the where statement is doing strange things. First my query:
select * from
(
SELECT 1 as Tag, null as Parent, row_number() OVER (ORDER BY tblOccasion.ID DESC) AS [tblOccasion!1!rownum], FROM tblOccasionwhere tblOccasion.UserID = @UserName and ((tblOccasion.MerkDesc like '%' + @search + '%')or(tblOccasion.UitvoeringDesc like '%' + @search + '%')or(tblOccasion.UitvoeringDesc like '%' + @search + '%'))
) AS A where a.[tblOccasion!1!rownum] BETWEEN (@startRowIndex) AND (@startRowIndex+@pageSize)
order by a.[tblOccasion!1!MerkDesc!Element],a.[tblOccasion!1!TypeDesc!Element],a.[tblOccasion!1!UitvoeringDesc!Element]
FOR XML EXPLICIT;
But the result should be ordered by Merkdesc, then TypeDesc, and then as last the uitvoeringDesc.
So for testing i only used the MerkDesc. The strange thing is that i get the result:
BMW
BMW
BMW
Opel
Volvo
But this was page 1, on page 2 (paging) you got BMW again... so he takes the first 5 rows ore something and then the order. But i like that he first orders it by MerkDesc and then picks the first 5. How can i change that, i can't add a Order by in the inline sql statement i get this error: Msg 1033, Level 15, State 1, Procedure MijnAdvertenties, Line 47 De ORDER BY-component is ongeldig in weergaven, in line functies, afgeleide tabellen en algemene tabelexpressies, tenzij ook TOP of FOR XML is opgegeven.
View 1 Replies
Nov 9, 2010
I'm trying to pull a list of clients that have both of two codes in the code table. Here is what I have tried and it does not return any rows. I know that there are clients that have both codes.
[Code]....
View 7 Replies
Sep 27, 2010
I have to pull up a list of people by their branch number which could vary, and give a detailed report on there performance using a select queries. I have the select queries but how can i create a script to look similar to this one. Ive got the branch drop downlist already created just need to know how i can go about pulling all the users in this group and doing this select to all of them. someone told me I might need to use cursors to loop thru a select with every person in the list but im not sure how to go about that.
select fldusername from tbluser where fldio='o' and
flduserbranch=@branchid
is going to give me the names of the people i need to query. but how can i run a query on each of them seperate?
View 3 Replies
Mar 25, 2011
I want to create a program that allows me to enter the name of a table, a field along with a value. The program should concatenate the values to form a valid SQL select statement. I can also modify the program to include multiple field names and values. Example: -Select "FieldName" from "TableName" where "FieldName"= "Value" I have created 3 textboxes called and 3 labels named Select, From and Where This is my code here, i would need an assistance on the right way to achieve this, maybe mine is wrong.
[Code]....
View 1 Replies
Jan 29, 2011
Here is the deal. I need to create a Stored Procedure that has 2 SELECT statements. Now in ASP.NET, I know that a DataSet can have multiple tables and the DataAdapter can do just that. I am stuck with a scenario where I need to get a value from the first SELECT statement which will later be used in the second SELECT statement. I am creating a SP that passes one parameter (@AnswerID). Which should give me 2 resultsets. See below SELECT statements.
[Code]....
So how do I get the QuestionID from the first SELECT statement?
Basically I am creating a Questionnaire that has Question Dependacies. So from the above SQL statements I would like to display the Question Title with it's relevant answer options but depending on the previous Answer selected hence the @AnswerID parameter.
The reason why I want to use 2 SELECT statements is because I don't want to have 2 roundtrips to th server. So in my code the DataAdapter should return 2 resultsets and fill the DataSet.
If there is any another solution that can prevent 2 roundtrips to the server, I would most definitely like to know how to do it.
View 10 Replies
Mar 30, 2011
n my button_click event, I have three data connections being opened and closed... I'm wondering... should I have one try/catch statement with the three connections and executions in the single try/catch... or should I have a try/catch statement for each connection/execution?
View 9 Replies
Jul 21, 2010
Here's my SQL Statement
[Code]....
Unfortunately, I get the following error message:Message="The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."
View 3 Replies
Feb 14, 2011
I have a select statement:
cmd = new SqlCommand("select lab_key from tblorderwhere", connection);
I would like to add a where statement that pass variable to the select statement. How I can do this?
Where lab_key =ChargeFine( that is my variable)
View 2 Replies
Jan 17, 2011
I am using join to get values from three tables. I want the first column to be Sl.No which autoincrements by 1.What should i write in the select for the first column.
View 4 Replies
Jan 3, 2011
Is it possible to sum to clolumns in one select statement?
[Code]....
[Code]....
ID sUM(aMOUNTpAID) sUM(aMOUNTdUE)
1234 506.64 4340.00
The actual rows look like this:
1234 10.00 5.00
1234 200.00 100.00
1234 130.00 130.00
1234 166.64 1000.00
totals 506.64 1235.00
View 7 Replies
Jul 29, 2010
i have the table like
TId DG rating DGMake DGCapacity
1 1 4 DG1 1X1
1 2 5 DG2 2X2
1 3 3 DG3 3X3
i need the output like this.
TId DG1 DG2 DG3 DG1Rating DG2Rating DG3Rating DG1Make DG2Make DG3Make DG1Capacity DG2Capaciy DG3Capacity
1 1 2 3 4 5 3 DG1 DG2 DG3 1X1 2X2 3X3
View 1 Replies
Feb 12, 2011
I am using sql server 2005 on my local PC. I have the same copy of the database on a shared hosting environment.I need to transfer daily transaction on some tables in my local database to the remote database. My remote database ison the shared hosting environment so i don't have much control on it.
I want to capture all the DML statement on tables and then at the end of day will execute those DML statement on theremote database.Kindly suggest how can i trace DML statement on database tables.
View 1 Replies
Sep 13, 2010
i trying following query Select
View 4 Replies
Dec 15, 2010
I have been working on a stored procedure to calculate likert scales for course evaluations. I have the stored procedure done but I ran into an interesting but frustrating situation.
I used a case statement along with a select query to count the number of responses of a given value. Likert scales are usually 5 point scales 5 being the highest and 1 being the lowest. The value that gave me the trouble was null values. In my evaluation page the instert query puts a null value in the field instead of leaving the response blank. These are the two queries I used both are syntactically correct but one works and the other doesn't. #1 Null query that works
[Code]....
Can anyone explain the differences and why one works but the other doesn't? Can it be as simple as switching the WHEN and the column name and if it is would it be advisable the other ones around?
View 4 Replies
Sep 1, 2010
I have the following code to For each Item in the file uploader, Insert Into database.... but It doesnt work in the For Each Statement but if i take it out of the For Each statement it works.
For Each item As AttachmentItem In Attachments1.Items
Dim objConn2 As New SqlConnection("Data Source=XXX")
objConn2.Open()
Dim objCmd2 As New SqlCommand("INSERT INTO clientport (name, event, date1, username, password, path)" & "VALUES (@name, @event, @date1, @username, @password, @path)", objConn2)
objCmd2.Parameters.AddWithValue("@name", clientname.Text)
objCmd2.Parameters.AddWithValue("@event", [event].Text)
objCmd2.Parameters.AddWithValue("@date1", [date].Text)
objCmd2.Parameters.AddWithValue("@username", username.Text)
objCmd2.Parameters.AddWithValue("@password", password.Text)
objCmd2.Parameters.AddWithValue("@path", item.FileName)
objCmd2.ExecuteNonQuery()
objConn2.Close()
View 1 Replies
Feb 18, 2010
this might be an easy one, but I just can't get it.I am creating a page which will query a table with many columns and most items are not unique. I need to be able to get a list of records that match as many of the (up to 4) search criteria as possible.Example:am user searching for the following items, I enter at least one and up to 4 of the items below in a text box:Name, age, gender, weight (user may or may not fill in all of them).If he just enters "F" for gender, then he will get a list of thousands of females with their name, age, gender and weight.However if he enters "F" for gender and "300" for weight, he will get a much smaller list of returned records.I need to be able to create a sql statement that can perform that search with that functionality.
View 6 Replies
Jan 24, 2011
I need to excute one insert query basend on two different if conditins. If one of them conditions is true then excute block of code.
For Example,
if exists( select * from tablename where docid = @docid and adv_feed <> 'sent' ) or
if not exists (select * from usaa_audit_fields_log where docid = @docid)
begin
insert statement
end
I have tried using OR statement between them but it doesn't work.
View 2 Replies
Dec 30, 2010
i am having the following problem:-
1. i have the following stored procedure
ALTER PROCEDURE dbo.UpdateCountint
@itemid
@date
AS
update items set type = "Approved" where item_ID = @aitemid and createdDate > @date
and i want that in case there is a row upd(the update statment was succsfull) to perform antoher update, else to return a value from the procedure.and another thing i want to do is to call this procedure from my MVC web application when the user clicks on a link.
View 5 Replies
Mar 21, 2011
how can i use Distinct in my sql statement like that
SELECT
Distinct * from my_table ?? ? ? ?? ? ? ?
View 2 Replies
Feb 24, 2011
here is a sql select statement,
Select
ColA = ID
,ColB = Name
,ColC = invtotamt + (SELECT SUM(TableA.camount) FROM TableA WHERE TableA.csucceed = 1 AND TableA.cID = InventoryTotal.invID GROUP BY TableA.cID)
,ColD = ((SELECT SUM(TableA.camount) FROM TableA WHERE TableA.csucceed = 1 AND TableA.cID = InventoryTotal.invID GROUP BY TableA.cID)
,ColE = (SELECT SUM(TableB.camount) FROM TableB WHERE TableB.csucceed = 1 AND TableB.cID = InventoryTotal.invID GROUP BY TableB.cID)
FROM TABLE
it look complicated, and long statement, actually it's easy, it is like: (it's a sample, not a really one)
Select
ColA = ID
,ColB = Name
,ColC = ColD + ColE
,ColD = ColE + ColC
,ColE = ColC + ColD/100
FROM TABLE
it looks much easier with ALIAS. But, it seems i cannot directly use alias in select stetement like that.
if i cannot use alise, i have to copy paste lots of same column definition. how can i use the alias in select statement?? this bothers me a lots of times!
View 5 Replies
Sep 4, 2010
I used an function in select statement. the select return 3 rows but the function return same value in this 3 rows.
[Code]....
View 3 Replies
