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.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Can someone please give me some ideas ???

Author  Topic 

simpson
Starting Member

11 Posts
Posted - 2005-09-28 : 18:24:57
Can some one pls help me with this ? What is the best solution ?

Design Internet Web backend based database call Email and inside of the database it has stores a list of email addresses for external users who do not want to receive email from CEB. Web and email applications may select, insert, or delete from this table using the following table (Nevermail) and queries:



Nevermail
Email Varchar(100)
NoSpamDate DateTime







-- Start Query
SELECT Email, NoSpamDate FROM NeverMail
WHERE Email = @Email


IF NOT EXISTS (
SELECT * FROM NeverMail
WHERE Email = @Email
)
BEGIN
INSERT INTO NeverMail(Email, NoSpamDate)
VALUES (@Email, Getdate())
END


DELETE Nevermail
WHERE Email = @Email

-- End Query



Your task is to redesign Nevermail to support the following features:

1) Support 3 different types of "No Contact" lists:
• No emails
• No phone calls
• No postal mail
A single email address can be added to more than one list. Don't worry about looking up the phone number or mailing address; you only need to keep track of email addresses.

2) Maintain a history of when an email address was added or removed from one of the lists. Your design should allow for an email address to be added to a list, removed, and then added again to the same list.


For output, please provide the following.
1) Create your new table(s) in the Email database. You may provide the SQL script to create table and query or you may provide with Visio diagram or any snapshot of SQL Diagram will do that work.


2) Outline and briefly describe any other database objects or queries that applications would use to insert, select or delete from the database. This could include views, stored procedures, triggers, user-defined functions, etc.

3) Provide a comprehensive solution for world peace, if possible.


Extra point>

1) Think about designing table that is flexible enough to support more than 3 different types as business requirement needs in the future.

2) Think about high performance designing structure so that this application can handle thousands of transactions if necessary.

nathans
Aged Yak Warrior

938 Posts
Posted - 2005-09-28 : 19:32:13
Homework?

Nathan Skerl
Go to Top of Page

simpson
Starting Member

11 Posts
Posted - 2005-09-28 : 19:36:00
It's a home work I need to answer otherwise I will fail. Coupd you please give me your best solution ? Thanks
Go to Top of Page

nathans
Aged Yak Warrior

938 Posts
Posted - 2005-09-28 : 20:16:10
Post what work you have done so far and we will correct your mistakes and offer suggestions.



Nathan Skerl
Go to Top of Page

simpson
Starting Member

11 Posts
Posted - 2005-09-28 : 20:53:36
Hi, Thanks for your help:

This is the current design:

NEVERMAIL
EMAIL = VARCHAR(20) PK
NOSPAMDATE=DATETIME not null

REQUIREMENTS:
1- Maintain history when emails deleted or added.
2- support 3 diff types of "NO CONTACT) by email , Tel, Mail
3- Specify the objects required.


My solution:

3 tables

1- EMAIL

email = varchar(20)
nospamdate=datetime

2- HISTORY
Email = varchar(20)
Deleted= datetime
added=datetime

3- CONTACT
email = bit NOT NULL - PK
calls =bit Not NULL
postalMail=bit NOTNULL


Do you thin this is a good desing ? I need to find answers tonight. I appreciate your help.
Go to Top of Page

simpson
Starting Member

11 Posts
Posted - 2005-09-28 : 22:57:12
Any one with DB design experience? ???????????????
Go to Top of Page

simpson
Starting Member

11 Posts
Posted - 2005-09-28 : 23:43:33
Any one can help me with this problem tonight ? Thanks
Go to Top of Page

simpson
Starting Member

11 Posts
Posted - 2005-09-29 : 01:21:16
Can some one at least tell me how many tables should I create??????
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts
Posted - 2005-09-29 : 07:43:13
Problem is that the majority of us have an aversion to "home work"....
It's expected by us (and your college/lecturer) that you will complete the task yourself (or make a very good effort to do so), and if you can't, that you would establish a rapport with the lecturer and ask for additional help on solving the problem. Failure to complete the task is not a bad thing - it can point to weaknesses in the communication of the lessons given to date.

....it's part of the accepted behaviour of college life
....(that and drinking!!!)
Go to Top of Page

simpson
Starting Member

11 Posts
Posted - 2005-09-29 : 08:21:13
So , Do you think that you can help me now in stead of lecturing me ? :)... I need your help now.... Thanks
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts
Posted - 2005-09-29 : 10:30:53
3) Provide a comprehensive solution for world peace, if possible.
Make me dictator. I will have peace in one month.

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2005-09-29 : 10:49:36
How would we be helping you by doing your homework? The object of taking a class is to learn, and if we do it for you, what will you learn? How to cheat on your homework?


quote:
Originally posted by simpson

So , Do you think that you can help me now in stead of lecturing me ? :)... I need your help now.... Thanks


CODO ERGO SUM
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts
Posted - 2005-09-29 : 11:17:45
I will repeat it.....
"Problem is that the majority of us have an aversion to "home work"...."

And if you're not ready to take and apply the advice "kindly" given, then maybe you need to reexamine the spirit of these forums and find some other fool elsewhere to ensure you stay a fool.

Remember...."Give a man a fish, feed him for day.....teach a man to fish, feed him for life"
Go to Top of Page

nathans
Aged Yak Warrior

938 Posts
Posted - 2005-09-29 : 11:24:30
Andrew, youve got it all wrong.

Give a man a fish and he will eat for a day.

Teach a man to fish and he will sit in a boat and drink beer all day.



Nathan Skerl
Go to Top of Page

simpson
Starting Member

11 Posts
Posted - 2005-09-29 : 15:05:25
OK thanks for your fishing and drinking advices. Any how this is what I have come up with , So can you assist me in finding the right solution? How come no one is helpful here ?

EMAIL
emailID int 4
EmailAddress Varchar(100)
UserName varchar(50)
DateAdded datetime
AcceptCalls bit 1
AcceptEmail bit 1
AcceptMail bit 1

EmailHistory:
UserID int 4
EmailID int 4
DateDeleted datetime 8
DateAdded datetime 8

User
UserID int 4
EmailID int 4
UserName varchar (50)
Address Varchar(50)
TelNo Char(10)
Go to Top of Page

simpson
Starting Member

11 Posts
Posted - 2005-09-29 : 22:51:39
How come no one helps here ???????
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts
Posted - 2005-09-30 : 08:16:07
Once more. BECAUSE WE DONT DO HOMEWORK FOR YOU. Giving us Table Defs and no SELECT statements to show that you even tried tells us that you want us to do it all for you. I speak for me, and no one else when I say GO POUND SAND.

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-09-30 : 08:42:51
"How come no one helps here"

Ah but we do.

In your case I think you have been given the best advice possible.

Kristen
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts
Posted - 2005-09-30 : 10:48:37
"How come no one helps here ???????"

Oh we do....Have a look at the number of posts made by the topranking members....they weren't all about "Test", "yak", "beer" or "women on bondi beach"...except most of those by kristen, merkin, rob, graz.......
Go to Top of Page

simpson
Starting Member

11 Posts
Posted - 2005-10-02 : 00:51:22
None of the posts really helped me and resolved my problem did they ? The question is on the top yet no real answers !
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-10-02 : 05:56:32
"None of the posts really helped me"

I don;t agree with that, that just suggests to me that you haven't read from them the clear message they gave. However, you are still here continuing the thread and you could just as easily have buzzed off.

"and resolved my problem did they"

Nope, they certainly didn't do that.

I'm pretty sure you don't understand the point of view which has been expressed here, and I have no idea how to explain it to you better so that you might be able to.

Here's an idea:

A) submit a printout of this thread
with
B) an explanation of what you have learnt from it
and
C) no actual answer.

If you were my student I would give you a good grade for that (provided you did a good job of (B) - but, for me, you would need to then follow it up in subsequent tests with solid hard work, study and good answers.

Kristen
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site