Topic: Entity-Relationship data model
Related Reading: Ch. 4
Due: 3:10pm, Monday 26 September 2005

Please make sure you adhere to the policies on academic integrity.

In a series of assignments during the course, we will have you performing various steps of the design and implementation of a database. In this first assignment, we want you to identify a potential database project and to develop an ER model for the data.

1. Form a group of one to three people.

2. Pick a project domain. This is not so easy. A project should have the following qualifications:

   • Each group needs a unique project.
   • Your project should NOT be taken from the book.
   • Your project can NOT be a University Database.
   • Your project can NOT be an example described in lecture.
   • Your project should contain at least five entities per person in the group and about the same number of relationships. If you have three people in the group that means at least 15 entities.
   • Your project should be realistic as possible. In fact, it would be best if you research a real life application that needs to be developed (it might be a lot easier that way). Remember that your project needs to be interesting and complex enough to explore most of the concepts that we will be learning in the course.

3. Write a few paragraph describing your chosen domain. Be as specific as possible (should be about a page or two).

4. Include a list of questions that you asked (or would have asked) your real (or imaginary) client and the responses you got. These should be questions that would have an effect on your design. For instance: Does a person have more than one office? The answer would determine the cardinality of the relationship between employee and office.

5. Create an E-R diagram for your project following the guidelines we set up in class. The diagram should include entities, attributes, keys, relations, key constraints, participation constraints, weak entities, and identifying relationships.

6. Turn your ER schema into a true relational schema by giving an SQL script for creating the tables and various constraints (primary key, foreign keys, participation constraints, null constraints, etc.) You should test these files on our PostgreSQL database on turing, and submit the files electronically.

7. Write another SQL script for populating your table with sample data. Your data may be either real or artificial, and does not need to be complete. But it should be plentiful enough to clearly illustrate the design aspects of your database. (the basic syntax for changing the contents of a table is given on page 19 of the text).

Grading Standards

Submitting your Assignment

Each group should submit one hard copy regarding parts (3,4,5) above, and electronic files regarding parts (6,7) above. (submit Details on the electronic submission process)