CS403

CS403 Assignment Solution:
CREATE TABLE IF NOT EXISTS ‘Customers’ (

‘ Customer_No’

int (8) unsigned NOT NULL AUTO_INCREMENT,

‘ First_Name ‘ varchar (32) NOT NULL,

‘ Last_Name’

varchar (32 ) NOT NULL,

‘ Address’

varchar (100 ) NOT NULL,

PRIMARY KEY ( ‘ Customer_No ‘ )

);

CREATE TABLE IF NOT EXISTS ‘Order ‘ (
‘ Order_No ‘ int (11) unsigned NOT NULL AUTO_INCREMENT,
‘Customer_No ‘ int (8 ) NOT NULL,
‘ OrderDate ‘ date NOT NULL,
‘PromisedDate ‘ date NOT NULL,
PRIMARY KEY (‘order_No ‘)
KEY `CustNo`

(`Customer_NO`)

) ;




CREATE VIEW `CustOrder` As
SELECT

`Customer`. ` Customer_No ` As ` Customer No`,

`Order`.`Order_No` As

` Order No` ,

`Customer`.` First_Name` As

` First Name` ,

`Customers`.` Last_Name` As ` Last Name`,
FROM (` Customer` JOIN `Order` ON

((` Customer` . ` Customer_NO` = ` Order `. ` Customer_NO ` ) )
);


Constraints for table `order` to enforce uniqueness and referential integrity.




ALTER

TABLE ` Order `

ADD CONSTRAINT `CustNo ` FOREIGN KEY ( ` Customer_No ` ) REFERENCES
`Customer ` (` Customer_No `)

ON UPDATE CASCADE;




CREATE VIEW `CustOrder` As
SELECT

`Customer`. ` Customer_No ` As ` Customer No`,

`Order`.`Order_No` As

` Order No` ,

`Customer`.` First_Name` As

` First Name` ,

`Customers`.` Last_Name` As ` Last Name`,
FROM (` Customer` JOIN `Order` ON

((` Customer` . ` Customer_NO` = ` Order `. ` Customer_NO ` ) )
);