CSCI 5931A Advanced Database Development, Homework #2

CSCI 5931A Advanced Database Development Fall 1998 Homework #2

Due: October 6, 1998 (Tuesday)

Using the suggested solution for homework #1, implement the voting system relations in Oracle by writing a script to create the tables and populate them with some rows.

For ease of reference, you should precede your tables and sequences with H1. For example, VotingProblem should be created as H1_VotingProblem. Make changes in attribute names only when it is necessary.

For all ID, use sequences that starts at 100, incrementing 1 at a time. Here is a description of the data type sizes and precision. All ID are numbers. Sizes of the IDs are:

PersonID	7
VoteID	8
CommentID	7
VotingProblemID	5
ChoiceID	6
KeywordID	4
PostTimeID	5

Other string data types have the following maximum sizes:

FirstName	30
LastName	30
EMailAddress	60
AccountName	15
Password	60
IPAddress	30
Choice.Description	100
Question	400
Comment.Description	100
Name	40
Keyword.Description	200

Create 8 persons, the first three are administrators.

Create three questions:

Q1. Who should be the president of the United States?
Choices:

Bill Clinton
Hillary Clinton
Al Gore
Newt Gingrich
None of the above

Keywords:

Politics
Government
Presidency
Congress

Q2. Which parties do better in the Congress?
Choices:

Republican
Democratic
Don't know

Keywords:

Presidency
Congress
Animal

Q3. What is your most favorite pet?
Choices:

Dogs
Cats
Fishes
Rattle Snakes
None of the above.

No keyword.

The three questions, choices and keywords should be stored exactly as above.

Make Q3 the current question being posted. Include three post time rows, one for each question. The current question should have NULL as value of EndTime in its most recent PostTime row.

Include at least 10 votes and 5 comments.

Save your script file as 'h2sol.sql' in your 5931/hw directory in your account.

Turn in a hard copy of your script and a listing of all tables created.