PART I

Step 1 of Your PDA (Personal Database Application)

As the course progresses you will be building a substantial database application for a real-world scenario of your choosing. You will design schemas for the database, and you will create an actual database using a relational database management system. You will populate the database with sample data, write interactive queries and modifications on the database, and develop application programs that manipulate the database. 

Your first step is to identify the domain you would like to manage with your database, and to construct entity-relationship schema designs for the data. We suggest that you pick an application that you will enjoy working with, since you'll be stuck with it for the whole term! It may be a good idea to pick something you are interested in--a hobby, material from another course, a research project, etc. 

Try to pick an application that is relatively substantial, but not enormous. For example, when expressed in the entity-relationship model, you might want your design to have in the range of five or so entity sets, and a similar number of relationships. Note that this is a ballpark figure only! You should certainly include different kinds of relationships (e.g., many-one, many-many) and different kinds of data (strings, integers, etc.), but your application need not necessarily require advanced features such as weak entity sets or roles in E/R. 

(a) (20 pts.) Write a short (approximately one paragraph) description of the database application you propose to work with throughout the course. Your description should be brief and relatively informal. If there are any unique or particularly difficult aspects of your proposed application, please point them out. Your description will be graded only on suitability and conciseness. 

(b) (30 pts.) Specify an entity-relationship diagram for your proposed database. As always, don't forget to show key attributes and to include bold edges indicating participation constraints. 

Don't forget to save a copy of your PDA for future reference. 

If you are having trouble thinking of an application, or if you are unsure whether your proposed application is appropriate, please feel free to consult with the instructor or tutors. 

PART II

1.(15 points). You must design the database for a software tool which allows modeling using the E/R model (i.e. a CASE tool). For this task, you are required to use the E/R model. Your CASE tool shouldbe able to represent all the E/R features explained in class and allow different kinds of users (i.e., userswho can modify a project, users that can only view a project, and so on). A user may create, manage and/or view more than one project. Extend this exercise as much as you need.

Hint.Entity sets could be "Entities", "Relationships", etc.. 

2. (10 points) Assume systems in a company are composed by a set of application programs such that each program is executed with a certain frequency. A program can be run in more than one system, and its frequency depends on the system over which it runs. A program may access files in I, O, or I/O modes, and a file can be accessed by more than one program. Every user can run all the programs in all systems, and we want to record her activity. 

a. Use the E/R model for representing the described situation. 

b. Now, add the following modification: "each user is authorized to run only certain programs in certain systems" and repeat step (a). 

3.(25 pts.) The Internet Movie Database website (http://www.imdb.com) is a repository of information about movies, actors, etc. You are asked to design the database that underlies this web site using the E/R model. Your design should capture the essential entity and relationship sets, including movies, actors, reviews, etc. You must decide what information to leave out in order to keep the design down to a manageable size (of the order of about 10 entity sets and 20 relationship sets, say). 

- Note that this assignment is open-ended. There is no single right answer, as the site is complex and different students will choose to include/exclude different aspects of it. 

- The first step is to browse and query the web site to understand what information is provided, how it is organized, and what the constraints are. 

- The next step is to give a detailed E/R diagram describing your design. Explain any constraints that are not obvious, but otherwise keep textual explanation to a minimum. Also list the information that you decide not to include in your design.