How do I implement the following relational schema for a super market in SQL server.

Question

1.00/5 (1 vote)

See more:

Ensure that,
 quantity of a product is always greater than 0
 default date for a sale is the system date

What I have tried:

Product (prodId, prodName, qty, unitPrice, ROL, supplier)
Customer (custId, custName, address, phone, loyalityPoints)
Staff (staffId , staffName, phone)
Sales (prodId, custId, staffId, salesDate)
Supplier (supId, contactName, phone, address)

but I cannot implement the sql code.

Posted 8-Sep-21 20:56pm

Member 15351941

Updated 8-Sep-21 22:09pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 14139508 · Answer 1 · 2021-09-08T22:09:00

Solution 2

OriginalGriff is right to point you in the direction of CONSTRAINTS
Here is more detail.

SQL

-- to add a default
ALTER TABLE Sales 
  ADD CONSTRAINT DF_Sales_SalesDate DEFAULT GETDATE() FOR SalesDate

-- to remove the default
ALTER TABLE Sales
  DROP CONSTRAINT IF EXISTS DF_Sales_SalesDate

Posted 8-Sep-21 22:09pm

Member 14139508

OriginalGriff · Answer 2 · 2021-09-08T21:22:00

Solution 1

Look at CONSTRAINTS.
Specifically: SQL CHECK Constraint[^] and SQL DEFAULT Constraint[^] along with the SQL Server GETDATE() Function[^] or SQL Server GETUTCDATE() Function[^]

Posted 8-Sep-21 21:22pm

OriginalGriff

Comments

Jörgen Andersson 9-Sep-21 5:02am

Or SYSDATETIME() or SYSUTCDATETIME() if you want to be a bit more up to date. :)

OriginalGriff 9-Sep-21 5:19am

I live in Wales, we're lucky to be up to date with the 80's ... :D

Jörgen Andersson 9-Sep-21 5:24am

Still on Emmerdale and Eastenders then?
Or does it have to be Pobol y Cwm?

OriginalGriff 9-Sep-21 6:09am

:shudder: