CS 4604 Project Assignment 3

PART A

Assigned on September 25, 2008. Hardcopy due at the start of class on Thursday, October 9, 2008.

(8*5=40 points) Write five different queries on your database, using the SELECT/FROM/WHERE constructs provided in SQL. Your queries should illustrate several different aspects of database querying, such as:
- Queries over more than one relation (by listing more than one relation in the FROM clause)
- Queries involving aggregate functions, such as SUM, COUNT, and AVG
- Queries involving complicated selects and joins, and/or subqueries
- Queries involving GROUP BY, HAVING or other similar functions.
- Queries that require the use of the DISTINCT or ALL keyword.
Caution: Do not cookup query problems to cover each and everyone of the above aspects! You do not have to illustrate all the above aspects, just the ones that occur naturally in your application. Try to infuse some reality into your project and think of some reasonable queries that people would want to make with your application. For example, in a movie application, writing a query such as "Find all actors whose ages are three times more than their street number" sounds ridiculous! You will not lose points if you do not cover all the above aspects. But do NOT give queries that use ONLY SELECT/FROM/WHERE constructs !
(10 points) Read Sections 8.3 and 8.4 of your textbook, which deal with indices. If you were to create indices on your tables to speed up query processing, which attributes would you place these on? Provide a rationale for your decision. You can choose different sets of indices for different queries. Notice that this is a theoretical question; you do not have to experiment with creating indices on your recommended attributes.

What to turn in for part a:
A paper copy that details the following:

The name of your project.
A list of your defined SQL schemas; these schemas are to remind us about your project. If you made changes to your schemas based on new knowledge, list these changes clearly.
A list of the SQL queries that you tried out, a description in English of what the queries are supposed to find, and the results you obtained. If an answer is long (and runs to several pages), please include just enough of the answer in your solution so that we know what is going on. Do not forget to mention that you truncated the final output. Please do not include pages and pages of answers.
Your answer to question 2 above.

PART B

(100 points) Starting from the two-page writeup that you turned in for Step 1 of the project, design an E/R diagram for your application. Your model should provide
1. 4-6 entity sets,
2. a similar number of relationships,
3. one example of a multi-way relationship, and
4. one example of inheritance, and
5. (preferably) one example of weak entity sets.
Your design must satisfy the first two criteria. You may need to expand the scope of your project in order to satisfy these criteria. If you do so, please hand in the two-page writeup from Step 1, with the additional scope clearly explained and demarcated.

Satisfying the last three criteria is optional. However, for each of the last three criteria that your design does not satisfy, you must state in your response why you think it is "unnatural" for your application (i.e., why multi-way relationships, inheritance, and/or weak entity sets do not make sense for your application). In other words, you should not cook up these elements in your model, just to satisfy the requirements. Your relationships should also have a variety of multiplicities. In short, your design should be "rich" in all these goodies we discussed in class! Do not forget to underline key attributes, to specify referential integrity constraints, specify any domain-specific constraints, and to double-border any weak sets and their connections.
Strange though it may seem, it is possible that you make your design more complicated than necessary! If you have more than 6 entity sets, you should probably prune them. Do not try to be over-ambitious. Future steps of the project will be much more complicated.

What to turn in for part b:
Hard-copies of the E/R design. Identify your group by your project title and the team members. In a section titled "Explanation", for each entity set and relationship, write a short description in plain English of what it represents or models. One or two sentences per entity set and relationship is enough. These descriptions are primarily to help us understand what you are modelling. 30% of the grade will be for the explanation. Discuss and identify any constraints and restrictions that your domain poses. For constraints that E/R diagrams cannot model, write in plain English what these constraints in a section titled "Notes".

Common Mistakes in Design:

Unfaithfulness to the domain being modelled. I expect that you will use some real-world assumptions when doing your project. Some common mistakes from earlier years were assuming that one person can be in two places at the same time, one team can play both basketball and football, not recognizing the multipicity of relationships (whether it is one-one, many-one etc.), modelling a single number by a set (why on earth?) instead of just by an integer, etc.
Modelling a database administrator explicitly in your E/R diagram. The DBMS usually has its own internal representation for administrators.

Missing arrows in a many-one and/or a one-one relationship.
Missing arrows from a weak set to the set(s) that provide its key attribute(s).
Using inheritance when there is no "ISA" connection between two sets.
Forgetting that when entity set B inherits from entity set A, B inherits everything that A has. In addition, B can define its own attributes of its own. Therefore, there is no need to repeat all the attributes/relationships that A has again for B.
"Cooking up" multi-way relationships.
"Cooking up" examples of weak sets.
"Cooking up" examples of inheritance.
Forgetting to underline key attributes in the E/R model.
Repeating (reusing) names for different entity sets or for different relationships within the same entity set, i.e., using the same name to denote two different things.
When converting a multiway relationship to many two-way relationships using a connecting entity set, forgetting to introduce many-one relationships!