Within the scope of this project, you are expected to get used to ER/EER diagrams and relation schemas. In addition, it is expected that you will be able to design your own database.
It is a group project. A group of two to four students (ideally, three) should do the project. You are free to choose your own project members. In this project, you should write a report for all tasks (A, B, C). Your reports should be legible and understandable.
You must submit the project as a single file in the “StudentNumber1_StudentNumber2_…pdf” format. The file name should include student numbers of all group members. It is ok if only one of the group members submits. Each group is expected to do it themself. In projects that are taken from each other or that are understood to be duplicates, all projects sent by the student will not be evaluated and the student’s overall lab grade will be considered “0”.
Task A: Define Your Own Database
Before starting, you need to decide your database concept. You can see a few sample databases below. These databases are given as an example to give you an idea. While creating your own database, you can prepare it completely independently of the samples. Use your imagination.
- Books Database: This domain is a popular one. You could model entities such as books, their authors, topics. You may also model various attributes of the authors and the institutions they belong to. You can support a service for buying and selling used books or books used in specific university courses. Your system can build a personal profile of people and the books they like. Your database application could form the basis for a “recommender system”, such as those supported by the commercial sites.
- Movies (or Television Shows) Database: There are several excellent movie resources on the web. You could model entities such as movies, their actors, directors, genres, playing times, and reviews. There are several sources on the web from which you could get data to populate such a database. You can support various queries such as finding specific playing times, or finding movies directed by a given director. You can also support updates to the reviews section of the database. Another functionality is to provide personal profiles of people and then try to recommend movies to them based on profiles of viewers with similar tastes.
- Research Literature: This domain involves modeling research publications. You need to identify the title of the publication, the forum it was published in, the authors, topics, keywords and related subtopic areas. For example, the ACM or IEEE provides a beautiful searchable index of nearly all of the publications . If you use this domain, then
there are a lot of available resources for you to use. The classification system provides a convenient hierarchial meta-index that you can use to organize your class hierarchy. You could identify papers written by a particular person at a particular place or ones in a narrowly defined area.
Others: There are a whole host of other ideas such as personal photo collections, bank accounts, student records, World Cup data, election results, car rentals, auto insurance, consumer products, silly statistics, “match-making services” and so on.
After decide your database, you should write a short report on what this database will be used for and what information it contains (not exceeding 1 page). It should include the name of the database which is given by group and full names, student numbers and email addresses of the students in the group.
Task B: Desing an ER/EER Diagram!
Now, after deciding your database, design an ER/EER diagram for your application. Your model should provide:
- 4 to 7 entity sets, and
- a similar number of relationships, and
- at least one example of a non-binary relationship (ternary, etc), and
- at least one example of specialization / generalization, and
- at least one example of weak entity sets, and
- at least one example of superclass and subclass, and
- at least two multi-valued attributes, key attributes and
- must include integrity constraints (cardinality and participation)
Your design must satisfy the criterias. Your relationships must also have a variety of multiplicities (one-to-one, one-to-many, many-to-many).Your design MUST be “rich” in all these goodies. Don’t forget to underline key attributes, to specify integrity constraints (cardinality and participation), specify any domain-specific constraints, and to thick-border any weak sets and their connections.
You should draw your ER/EER diagram clearly and properly. Also you should write one or two sentences for each entity set and each relationship, explaining what it represents or models. Discuss and identify any constraints and restrictions that your domain poses and at least one constraint that could not be captured in your ER diagram (The report (independent of ER diagram) should be 1 or 2 pages.).
Task C: Convert Your ER/EER Diagram to Relations
Now, you are expected to write down relation schemas. While doing this, you should pay attention to pick suitable data types for each attribute and not forget to specify at least one key for each relation.
