Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Mohamed Faisal
Yak Posting Veteran
51 Posts |
 Posted - 2014-10-09 : 11:26:13
|
| Hi,Would need you help. Kindly help me if i would need to type the codes in the parent table or child table:C1 Cardinality between Car and Rental is 1:N, M:O with delete to VehNo being disallowed and update is cascaded.C2 Cardinality between Customer and Rental is 1:N, M:O with delete to CustNo being disallowed and update is cascaded.C3 Cardinality between Rental and Payment is 1:1, M:O with delete and update to RentalNo disallowed.Thanks. |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-10-10 : 08:55:19
|
| If you can't delete a VehNo, you'll need an instead-of trigger to stop that., Same thing with CustNo and rentalNo |
 |
|
|
Mohamed Faisal
Yak Posting Veteran
51 Posts |
Posted - 2014-10-11 : 06:07:41
|
| Hi Thanks for helping,Could you check if the following meets the requirement:CREATE TABLE Car([VehNo] Varchar(8) NOT NULL,[Brand] Varchar(64) NOT NULL,[Model] Varchar(64) NOT NULL,[DateRegistered] Varchar(50) NOT NULL,[PurchasePrice] decimal(8,2) NOT NULL,[RentalCost] money NOT NULL,[Contition] char(20) NOT NULL DEFAULT 'excellent',CONSTRAINT VehNo_PK PRIMARY KEY([VehNo]),CONSTRAINT VehNoValues CHECK([VehNo] like '[A-Z][A-Z][A-Z][0-9][0-9][0-9][0-9][A-Z]'), CONSTRAINT RentalCostValues CHECK([RentalCost] BETWEEN '120' AND '500'),CONSTRAINT ContitionValues CHECK([Contition] LIKE 'excellent' OR[Contition] LIKE'above average' OR [Contition] LIKE'average' OR[Contition] LIKE'needs repair'));CREATE TABLE CUSTOMER( [CUSTNO] varchar(5) NOT NULL, [ID] CHAR(9) NOT NULL, [NAME] VARCHAR(128) NOT NULL, [ADDRESS] VARCHAR(128) NOT NULL, [DATEOFBIRTH] DATE NOT NULL, CONSTRAINT CUSTNOPK PRIMARY KEY([CUSTNO]), CONSTRAINT CUSTNOVALUES CHECK(CUSTNO LIKE '*[A-Z][0-9][0-9][0-9][0-9]'), CONSTRAINT DATEOFBIRTHVALUES CHECK ( getdate() - DATEOFBIRTH=21), );CREATE TABLE RENTAL([RENTALNO] INT IDENTITY (1000,1)NOT NULL,[VEHNO] VARCHAR(8) NOT NULL,[CUSTNO]varchar(5) NOT NULL, [DATERENTED] DATE NULL,[DATERETURNED] DATE NOT NULL,[CONDITION] CHAR(20) DEFAULT 'EXCELLENT', CONSTRAINT RENTALNOPK PRIMARY KEY(RENTALNO),CONSTRAINT VEHNOFK FOREIGN KEY(VEHNO)REFERENCES Car(VEHNO)ON DELETE NO ACTION ON UPDATE CASCADE, CONSTRAINT CUSTNOFK FOREIGN KEY(CUSTNO)REFERENCES CUSTOMER(CUSTNO)ON DELETE NO ACTION ON UPDATE CASCADE CONSTRAINT DATERETURNVALUE CHECK(DATERETURNED > DATERENTED || DATERETURNED <>DATERENTED), CONSTRAINT CONDITIONVALUES CHECK(CONDITION LIKE ('[EXCELLENT]' || '[ABOVE AVERAGE]'||'[AVERAGE]'|| '[NEEDS REPAIR]')) ) ;CREATE TABLE Payment([RentalNo] INT IDENTITY (1000,1)NOT NULL,[CreditCardNo] varchar(12) NOT NULL,[Amount] money NOT NULL,[PaymentDate] DATE NOT NULL,[Status] CHAR(20) NOT NULL,CONSTRAINT RentalNoPK PRIMARY KEY([RentalNo]),CONSTRAINT RentalNoFK FOREIGN KEY([RentalNo])REFERENCES Payment(RentalNo)ON DELETE NO ACTION ON UPDATE NO ACTION,CONSTRAINT AmountValue CHECK([Amount] BETWEEN '120' AND '10000'),CONSTRAINT StatusValues CHECK([Status] LIKE 'processed' OR[Status] LIKE'billed' OR [Status] LIKE'received' OR[Status] LIKE'error'OR [Status] LIKE'default')); |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-10-11 : 09:33:47
|
| I don't see your triggers |
|
|
|
|
|
|
|
|
Cardinality