An Internet store sends emails to customers and would like to use a database to keep track of which messages are sent to which customers. A message has a message id (mId), a subject (subject), and a body (body). A customer is identied by the email address (email), and customer's data includes the attributes name, sex, householdSize, and address. When an email is sent, the date (sendDate) is

recorded. You can assume any reasonable domains for these attributes.

(a) Give an E-R diagram that completely describes the entities and relationships of this plan.
(b) Translate the E-R diagram into SQL tables using SQL DDL, by specifying a table for
messages, a table for customers, and a table for which messages are sent to which customers,
in SQL DDL.
(c) How to specify that the subject of a message must not be longer than 60 characters, in
SQL?
(d) How to require that customers at the same address all have dierent names, in SQL?
(e) How to enforce the restriction that the only valid values of sex are male or female, in
SQL?
(f) How to specify that each message must be sent to no more than one customer, in SQL?
(g) How to specify that each customer must be sent one or more messages, in SQL?
(h) How to specify that each message must be sent to exactly one customer, in SQL?
(i) How to specify that each customer must be sent exactly one message, in SQL?
(j) How to specify that a message can be deleted only if no customer has been sent that
message, in SQL?
(k) Shipping department needs not be concerned with the sex and household size of the
customers. Create an element of an external schema which is a view of customers that
does not contain these attributes.

(a) The diagram looks something like below.





(b) The corresponding tables are shown below.

CREATE TABLE Message                          ( CREATE TABLE Customer (


mId INTEGER,                                           email CHAR(40),


subject CHAR(40),                                    name CHAR(40),


body CHAR(10000),                                 sex CHAR(1),


PRIMARY KEY(mId))                                 householdSize INTEGER,


                                                                address CHAR(50)


CREATE TABLE SentTo (                           PRIMARY KEY(email))


mId INTEGER,


email CHAR(40),


sendDate DATE NOT NULL,


FOREIGN KEY mId REFERENCE Message,


FOREIGN KEY email REFERENCE Customer,


PRIMARY KEY (mId, email))

(c) Change the domain of subject to: CHAR(60)



(d) Add to the table Customer: UNIQUE (name, address)



(e) Add to the table Customer:

CHECK (sex IN ('M', 'F'))

Alternatively, create the following domein

CREATE DOMAIN Sex CHAR(1)

CHECK (VALUE IN ('M', 'F'))

and change the domain of the attribute sex to: Sex



(f) Change the primary key of the Send table to mId. Since email will no longer be part of

the primary key, it needs a NOT NULL constraint.



(g)

CREATE ASSERTION NoNeglectedCustomer


CHECK (NOT EXISTS (


SELECT * FROM Customer C


WHERE NOT EXISTS (


SELECT * FROM SentTo S


WHERE C.email = S.email )))

(h) Add to the table Message: FOREIGN KEY mId REFERENCE Send.

In the table Send: make mId into the primary key and add NOT NULL to email.



(i) Make email into the primary key of Send and add NOT NULL to mId (since it is no

longer part of the primary key).

Add to the table Customer: FOREIGN KEY email REFERENCE Send.



(j) Modify the foreign key mId in the table Send by adding ON DELETE NO ACTION (this

is actually the default anyway).



(k) CREATE VIEW Shipping (email,name,address) AS

SELECT email,name,address

FROM Customer