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

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

OriginalGriff · Answer 1 · 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:

Member 14139508 · Answer 2 · 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

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

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

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

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us