|
I refer to the use case as per Swagger documentation
Swagger UI[^]
I need help to know the following:
1) whether the table pets, table users and table orders below is correct
Create table pets (
petId INTEGER IDENTITY PRIMARY KEY,
NAME VARCHAR(30) NOT NULL,
PHOTOURL VARCHAR(30) NOT NULL,
User userId,
TAGS VARACHR(20) NOT NULL,
STATUS ENUM(20)
PRIMARY KEY (petId),
FOREIGN KEY (userId) REFERENCES Users(userId)
);
CREATE TABLE Orders (
OrderID int PRIIMARY KEY,
OrderNumber int NOT NULL,
petId int,
quantity int NOT NULL,
shipDate DATE NOT NULL,
STATUS VARCHAR(30) NOT NULL,
PRIMARY KEY (OrderID),
FOREIGN KEY (petId) REFERENCES Pets(petId)
Not sure to include User based on the Swagger document
);
CREATE TABLE Users(
UserID int PRIMARY KEY,
UserName VARCHAR(30) NOT NULL,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
EMAIL VARCHAR(30) NOT NULL,
PASSWORD VARCHAR(30) NOT NULL,
PHONE VARCHAR(30) NOT NULL,
UserStatus VARCHAR(30) NOT NULL,
2) whether store entity is needed ?
Please help me on above as I am not good with DB and have problem in understanding swagger documentation.
3) If I were to use JPA data in Spring, do I use Custom query for querying the status ?
Tks
|
|
|
|
|
Hi guys,
I need to build a windows app for documents archiving based on SQL server. I'm not sure if it better to store the files inside the database or as regular files. There will be thousands of files, and each file can reach upto 10MB so the total size could be around couple of TB's.
|
|
|
|
|
It's usually preferable to store the files in the file system, and store the paths in the database. But you might want to consider using file tables or filestream access instead, if they're available on your server.
Binary Large Object (Blob) Data (SQL Server) - SQL Server | Microsoft Docs[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I HAVE TO MANAGE INVENTORY
1)PARENTS (1 PK)
2)CHILD (10 PK)
CHILD NEVER SHOW MORE THEN 9 TO 0
IF I UPDATE INVENTORY FORM PARENTS OR CHILD UPDATE AUTOMATICALLY IN A PARENTS
EXAMPLE
X=2 PARENTS
Y=9 CHILD
ID I ADD INVENTORY ON A CHILD 15 WE CAN GET RESULT LIKE THAT
X=4
Y=4
THEY SHOW LIKE THAT
|
|
|
|
|
1. Don't type in all CAPS. It makes it look like you are yelling and is considered rude.
2. You have not asked a question.
3. All you have to do is write code to do this. When you update inventory then update the other fields too.
Social Media - A platform that makes it easier for the crazies to find each other.
Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.
|
|
|
|
|
I am looking for methodologies or strategy or process that makes database version control possible. Kindly share what you follow in your projects
|
|
|
|
|
One way to approach this is to have a create a baseline set of scripts which created all of your DB objects: Tables, Indexes, Triggers, Stored Proc, etc. Check those scripts into a version control system () along with your source code. (Make a folder called "SQL" or something)
As you revise your application, create ALTER scripts for the DB objects (Tables, Indexes, Triggers, Stored Proc, etc) which will take the database from State A to State B.
Use some type of naming convention which will show the progress from one version to another.
"ALTER_tables_1.0.0_to_1.0.1"
When you get to a major release of the software, such as v2.0.0, then Re-Create the CREATE DB object scripts which will include all of your ALTER scripts; this way you don't have to keep rolling up schema changes.
ALSO: Take into consideration that you may need Data Manipulation scripts to "seed" some of your tables with basic data. Handle those scripts with something like ALTER_Data_1.0.0_to_1.0.1
Just giving you some ideas, not completely thought out, but I remember doing something like on previous projects.
BTW: I use Subversion / SVN / Tortoise as version control for myself.
|
|
|
|
|
There are lots of 3rd party products that can source control your database. This is one of them that I have used, SQL source control | ApexSQL[^]
Social Media - A platform that makes it easier for the crazies to find each other.
Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.
|
|
|
|
|
Please can someone help me develop a simple database using Microsoft access? I am a beginner but willing to learn Microsoft access with help from someone.
I am using excel based database but now I really want to a access based database.
Thank you
|
|
|
|
|
|
Take a look through these and see if there is a structure that meets your needs. http://www.databaseanswers.org/data_models/[^]
Take note of the primary (PK) and foreign keys (FK) used to normalise the data.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
Hi,
I have been trying to add some data to a table we get which is sometimes incomplete
This is the SQL command that is used to fill in a "shipment_date"
UPDATE IGNORE myTable
SET Shipment_Date = CONCAT(LEFT(Sales_Order_Ship_Date,4), "-",
MID(Sales_Order_Ship_Date, 2, 2), "-",
RIGHT(Sales_Order_Ship_Date, 2) );
In this case Shipment_date is an actual DATE data type whereas the Sales_Order_Ship_Date is a VARCHAR(42) data type.
It works really well on the condition that the original string data represents a date of at least 2010-01-01. The string data is formatted as follows: YYYYMMDD so 2010-01-01 is 20100101 in the string version. It converts perfectly to 2010-01-01 and the same is true for later dates. Any string representing a date before 2010-01-01 though comes out as 0000-00-00.
Does anyone have any ideas as to what is causing this?
|
|
|
|
|
Well, MID('20091231',2,2) is '00'
MID is used like: MID( string, start_position, length) where start position is one based, not zero based.
So try using MID(Sales_Order_Ship_Date,5,2) instead.
Wrong is evil and must be defeated. - Jeff Ello
Never stop dreaming - Freddie Kruger
|
|
|
|
|
Your answer was right on the mark, thanks a lot for that. 
I first tried it on MySQL Tryit Editor v1.0[^] and it worked perfectly there.
Then I corrected my colleague's original code as you described and sure enough it now works exactly as expected.
Why it ended up producing 0000-00-00 is still a mystery but once the dissection of the string is done properly it now accepts any date
|
|
|
|
|
Just use the CONVERT function,
SELECT CONVERT(Sales_Order_Ship_Date , Date);
Social Media - A platform that makes it easier for the crazies to find each other.
Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.
|
|
|
|
|
I'm trying to add both text and a picture filename to an Access database. The actual .jpg file is stored separately in another folder. I want to use a File control in classic ASP to do this. If I create a page for the text fields and a separate page for the File control, everything works fine but the users are confused by having to go to two pages.
Ideally, they would use the Browse button of the File control to select their picture then the picture's filename would be passed down to the "ImageLink" field in the database Insert SQL command. Any ideas?
Here's my two sections of code:
<form action="marketplaceentryadd.asp" method="post" >
<!--
<table border=1 cellpadding=3 cellspacing=1>
<tr><td size="110"><font color="#FFFFFF">Country:<br />
</font></td><td><input name="txtCountry" type="text" size="50" maxlength="50" /></td></tr>
<tr><td><font color="#FFFFFF">Description:<br />(255 char. max)
</font></td><td><input type="text" name="txtDescription" textmode="multiline" maxlength="255" size="50"/></td></tr>
<tr><td><font color="#FFFFFF">Price:<br/>
Numbers only with optional decimal point
</font></td><td><input name="txtPrice" type="text" size="50" maxlength="50" /></td></tr>
<tr><td><font color="#FFFFFF">Your Name:<br />
</font></td><td><input name="txtSeller" type="text" size="50" maxlength="50" /></td></tr>
<tr><td><font color="#FFFFFF">Contact Info:<br />
(255 char. max)
</font></td><td><input name="txtContactInfo" type="text" size="50" maxlength="50" /></td></tr>
<tr><td><font color="#FFFFFF">More Info:<br />
(255 char. max)
</font></td><td><input name="txtMoreInfo" type="text" size="50" maxlength="255" /></td></tr>
<tr><td><font color="#FFFFFF">Picture Filename:<br/>Do not include spaces in the filename<br/>Be sure to include extension (.jpg)</font></td><td><input name="txtImagelink" type="text" size="50" maxlength="50" /></td></tr>
<tr>
<td><input type="submit" value="Add Item" />
</tr>
</table>
</form>
Second page code:
Please select picture to be saved for this item:<br/><br/>
<FORM method="post" encType="multipart/form-data" action="ToFileSystem.asp">
<INPUT type="File" name="File1"><br/><br/>
<INPUT type="Submit" value="Upload Picture">
</FORM>
|
|
|
|
|
Why are you asking the user for a filename when all you need is the upload selector? Once the image is uploaded to the server you have the filename which you can then add to your database.
|
|
|
|
|
Consider the relation R(A,B,C,D) with FDs AB -> C, C -> D, D ->A. What is the 3NF status of R.
1.
R is in BCNF already.
2.
R is not in BCNF. It must be divided into R1(A,C,E) with FDs A -> C, C -> E, and R2(B,D,F) with FDs B -> D, D -> F.
3.
R is not in BCNF. It must be divided into R1(C,E) with FDs C -> E, R2(D,F) with FDs D -> F and R3(A,B).
4.
R is not in BCNF. It must be divided into R1(A,C,E) with FDs A -> C, C -> E, R2(B,D,F) with FDs B -> D, D -> F, and R
|
|
|
|
|
We are more than willing to help those that are stuck: but that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for us to do it all for you.
So we need you to do the work, and we will help you when you get stuck. That doesn't mean we will give you a step by step solution you can hand in!
Start by explaining where you are at the moment, and what the next step in the process is. Then tell us what you have tried to get that next step working, and what happened when you did.
Consider this: just posting your homework question and hoping somebody will give you the answer so you can just tick a multiple choice box is futile. You have no idea if the answer you're given is right or wildly wrong ... and unless you actually learn this stuff you will have no idea at all!
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
| 42
Social Media - A platform that makes it easier for the crazies to find each other.
Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.
|
|
|
|
|
Now I feel sorry for missing this question. Normalization is one of the things you have to learn, and not just for databases. It will give you more insight into data. Let me stress that again, you want to master this, it is what makes a master.
Can you give me a real life analogy for your abstract question? Otherwise, I'd just have to reply with the rules which you already know.
And BCNF comes after 3NF; if it in 3NF, it not guaranteed to be BCNF, so answer one falls. Leaves you with three. Also, 3NF and BCNF rather close - if you can answer it, you master it.
Bastard Programmer from Hell 
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
select I."ItemOCode" as "Id", I."ItemNameA" as "Item",
S."ACCNAME" as "SalesMan", sum(Decode(T."TransKind", 504, T."QtyIO")|| Decode(T."TransKind", 506, T."QtyIO")) * -1 as "Sales",
sum(Decode(T."TransKind", 504, T."BonusIO")|| Decode(T."TransKind", 506, T."BonusIO")) * -1 as "Sales Bonus",
sum(Decode(T."TransKind", 504, T."NetTL")||Decode(T."TransKind", 506, T."NetTL" ) * -1) as "Total Sales",
round((sum(Decode(T."TransKind", 504, T."BonusIO")||Decode(T."TransKind", 506, T."BonusIO")) * -1) / (sum(Decode(T."TransKind", 504, T."QtyIO")||Decode(T."TransKind", 506, T."QtyIO")) * -1) * 100,0) as "Bonus Rate",
H."VHFNo" as "Invoice", H."AccCode", H."AccName" from "TransActn_S" T, "Item_Card" I, "Header_S" H , "SALESMEN" S
where I."ItemOCode" = T."ItemOCode" and
H."TransNo" = T."TransNo" and
H."VHFNo" = T."VHFNo" and
H."TransKind" = T."TransKind" AND
(H."TransKind" = 504 OR H."TransKind" = 506) AND
H."SalesManNo" in (00016,00034,00036,00119,00108,01000,00007) AND
S."SALESNO" = TO_CHAR(H."SalesManNo") AND H."VHFDate" between '01/08/2020' and
'24/08/2020' AND H."Stock_Code" = 1 group by I."ItemOCode", I."ItemNameA",S."ACCNAME",H."VHFNo" ,H."AccCode" ,H."AccName"
order by I."ItemOCode"
|
|
|
|
|
ora 01722 - Google Search[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hi,
I've install SQL Server Express 2014 named instance with mixed mode authentication.
one more SQL Server 2005 version installed with default instance and TCP port has been assigned 1433.
following error occurred
Server Name: .\sqlexpress
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536 -I want to connect locally (with dot) but not able to connect, I also want to connect from outside network
-with static IP still getting the same problem.
following changes I've done
-remove dynamic TCP port from configuration manager ipall
-instead of dynamic port I've place static tcp port 1438 and 1440 both are listening
-try 1434 TCP port but SQL service not able start so I've change into 1438 and 1440.
-and same has been configured from firewall inbound and outbound rule.
--also tried through dbeaver to make connection but following error occurred
SQLEXPRESS,1438 failed. Error: "java.net.UnknownHostException: 103.87.24.38:3390: invalid IPv6 address". Verify the server and instance names and check that no firewall is blocking UDP traffic to port 1434. For SQL Server 2005 or later, verify that the SQL Server Browser Service is running on the host.
modified 14-Aug-20 7:40am.
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
