Project 1: Database Design and Data Modelling Solution

In the programming project of this course, you will develop and build a database application for a real-world domain. Step by step, you will design a schema, create a database using DB2/PostgreSQL, populate your database with data, maintain, query and update your data, develop application programs, and implement a user-friendly in-terface. The interface can be very simple so no requirement for web-programming, etc. You will only use a standard programming language in the last project deliverable. The course itself will cover Java but you can use another programming language (with approval from the instructor and the TAs). Each team turns in one solution for each project deliverable.

Submission Format: Your submission will contain two separate documents:

    1. The ER diagram of your project. This can be a PDF le or PNG. Make sure that the ER diagram you submit is readable (such as font size, etc). You may use a page size larger than letter for your ER diagram page if that ensures it is readable. Your group number should also be included in the ER diagram (top right side).


    2. Rest of your project document (requirements, descriptions, relational model, etc.). This should have at least 3 pages and should not exceed 5 pages and can be submitted as a PDF or DOC format. Do not use a \title page" just to include the project heading and your group members. You can include the project title, group number, and members in the rst page of your document and continue the project description on the same page.

    • Assignment

In this rst assignment you have to choose an application domain and design your database. Below are several possibilities. But you can choose any other application that can be typically found on the Internet. If you choose an application not listed below consult with me to see whether it is ok. You have to perform the requirement analysis for your application, design the entity-relationship schema (ER) for the data described in the data analysis, and translate it into the relational model. Choose an application you are interested in; then you will have more motivation doing this project!

The application should be substantial but not too big. Consider a range of 8 to 12 entity sets, and a similar number of relationship sets. The model should include di erent kinds of relationships and di erent data types. Do not force features such as weak entity-sets or is-a relationships if they are not appropriate. The total number of entity sets + relationships should be at least 16 and NOT to exceed 25. Expand/Reduce your requirements so that you can adjust your ER model. DO NOT skip adding entity sets and relationships in the ER Model to keep the total number below 25. You will loose points if requirements are not mapped into the model. Instead, adjust your requirements so that you can have a smaller model. Remove the less essential requirements, for example if your application is a store selling something, it is important to keep track of the product inventory, sales info (who bought what for how much, etc.), but on the other hand you need not bother about supporting product returns or changing prices of products with time etc.

DO NOT design a \star model", i.e., where all the relationships in the model are from one particular entity set. As a rule of thumb, if you have n relationships in your model, see to it that no single entity set is participating in more than n 3 relationships. This is to ensure that your ER model is reasonably sophisticated so that you can build interesting features in the succeeding project deliverables.



1
You have to turn in the following.


    1. (35 Points) A requirement analysis of the application. This is a half-formal speci cation. It should list in a coherent way all data that needs to be stored in the database (data requirements), and the operations that need to be executed on the data (functional requirements). The ER schema developed in the next step should not contain data that is not described here in the speci cation. If there are any unique or di cult aspects, point them out. Be precise about the real-life concepts that you want to model, their relationships etc. Also consider constraints, restrictions or special requirements that your application might have. We also discussed examples in class when we looked at Minerva and other examples. Your description is expected to be very detailed.

    2. (40 Points) An ER schema/diagram including your data requirements. Be careful not to forget to underline key attributes, indicate the types of relationship sets etc. If there are any constraints within the application that you cannot depict in the ER diagram, point them out.

    3. (15 Points) Use the method for translating an ER diagram to relations described in class and depict each resulting relation in the form Relationname(attr1, attr2, attr3,...) underlining the key attributes (e.g. Students(sid, name, age, gpa)). Indicate when attributes are foreign keys to other relations by writing something like \attr3 foreign key referencing relation X" beside the relation. Are there opportunities to com-bine relations without introducing redundancy? If so, indicate which, and if not, tell us there are none.

Note: You do not yet need to give the SQL create statements or decide on the data types.

    4. (10 Points) For creativity and complexity of your application/design. This is to ensure that you do not turn in a bland, simple, \ at" application that does not explore some of the more sophisticated areas of ER modeling. Here is an example of things I am looking for to spice up things.

How your application domain stands out among the other project applications. Three or more instances of using inheritance, weak entities

Four or more instances of key constraints, ternary relationships

Please do not \Force" these into your ER model, if it does not t your requirements. If doing so, you introduce errors into the model, you will not get the creativity points and instead may loose points for the errors. This is especially the case where many groups in the past have abused the inheritance feature where they are not meant to be .Even if you are a little short in any of the above features, but end up with a very good model and relational translation, I may decide to give you points for the good work you put in.

Write a very short description of what features in your project stands out for the above creativity part.

5. (0) Points. Indicate one or two web-sites that inspired your design.

    • Project Topics

Below are several, pretty widely de ned topics that your application could be chosen from. Of course, you have to decide on a more speci c domain/area/application/enterprise within the topic and do research on what are the speci c characteristics of the application.

The data you want to store should be realistic in the sense that for the chosen application domain, this is really the information that is relevant and should be maintained.


A store/company/enterprise/organization that sells something to customers. Choose a speci c enterprise of your choice, e.g., a bookstore selling books to clients, maintaining its stock, etc. A music center selling concert tickets, an online music store, etc. There are no limits. There are two minimum conditions: (i) The process of a customer buying something (i.e., a purchase) must be re ected in the schema. (ii) A purchase should allow the inclusion of more than one product item (you can buy more than one book in one purchase). You may want to go through one of the online stores and see what information is all needed to perform such a purchase

2
(but do not nally submit your reservation!) Look also what is the other functionality provided by such an online store.

A car rental company, a hotel, a spa, a travel agency or any other type of enterprise that includes a reservation system. The process of making a reservation by a customer must be re ected in the schema. Try do be realistic. You may want to go through one of the systems online and see what information is all needed to perform such a reservation (but do not nally submit your reservation !).

A social networking site.

If the application you would like to develop does not t in the three topics listed above, please talk to me to check whether it is ok.



I DO NOT ACCEPT


A university database An airline company

A general purpose enterprise consisting of employees, projects, products etc. Although your application item might include any of these entity sets, you should choose a more speci c enterprise (what kind of item products, projects etc.)

A database that resembles the example database project that is provided from a previous year. The running text book example and the project description in Assignment 1.






































3