Create the following tables:
Customer
KNr (primary key)
Name (at most 15 characters)
City (at most 10 characters)
Country (at most 10 characters)
Balance (Type FLOAT)
Discount (Type FLOAT)
Products
PNr (greater than 1 and primary key)
Descr (not NULL, at most 10 characters and unique)
Weight (Type FLOAT)
Think about the integrity constraints for the columns Price, StorageLocation and Stock.
Orders
OrdNr (Type INTEGER, greater than 0 and primary key)
Mon (Type INTEGER, not NULL and between 1 and 12)
Day (Type INTEGER, not NULL and between 1 and 31)
PNr (Foreign Key)
KNr (Foreign Key)
The attributes Month, Day, Pnr and Knr must together be unique. Think about the integrity constraints for the columns Quantity, Sum and Status.
I have done the following :
For 1 :
CREATE TABLE Customer
(
KNr PRIMARY KEY,
Name CHAR(15),
City CHAR(10)
Country CHAR(10)
Balance FLOAT
Discount FLOAT
);
Is that correct?
For 2 :
CREATE TABLE Products
(
PNr PRIMARY KEY CHECK (PNr > 1) ,
Descr NOT NULL CHAR(10) UNIQUE.
Weight FLOAT
Price FLOAT CHECK (Price > 0) // Is checking if it is positive an integrity constraint?
StorageLocation CHAR(15) // What integrity constraint do we use here? If it is not Null for example?
Stock INTEGER // What integrity constraint do we use here? If it is not negative for example?
);
Is that correct?
For 3 :
CREATE TABLE Orders
(
BestNr INTEGER PRIMARY KEY CHECK (BestNr > 0) ,
Mon INTEGER NOT NULL CHECK(Mon >= 1 and Mon <=12)
Day INTEGER NOT NULL CHECK(Day >= 1 and Day <=31)
FOREIGN KEY (PNr) REFERENCES Customer (PNr),
FOREIGN KEY (KNr) REFERENCES Products (KNr)
Quantity INTEGER CHECK(Quantity >0) // It is the ordered quantity, or not? What integrity constraints can we consider?
Sum FLOAT // Is this the sum of invoices? Or what is this meant? What integrity constraints can we consider?
Status CHAR(20) // It is meant if is paid, delivered, etc? So this contains words, right? What integrity constraints can we consider?
UNIQUE (Mon, Day, Pnr, Knr)
);
Do we write that as in the last line that the attributes Month, Day, Pnr and Knr must together be unique ?
oracle, ormysql, orpostgresql, orsqlite, orsql-server- each has different "flavors" of SQL and constraints and things .... please add a relevant tag!VARCHAR()instead ofCHAR()for the ones that can have up to num of char, asCHAR()will add unnecessary extra spaces.