Database and Information Retrieval Assignment: Database for University of Vermont

University of Vermont need to track of students and their majors, transcripts, and registration as well as of the university's course offerings. The university database also keeps track of the sponsored research projects of faculty and graduate students. The requirements are listed as follows.

For each person, the database maintains information on the person's Name, Social Security number, address, sex, and birth date. The staff members of the faculty are given a rank (e.g. assistant, associate, adjunct, research, visiting, and so on), office, office phone, and salary. All faculty members are related to the academic department(s) with which they are affiliated (a faculty member can be associated with several departments). A student has a class as such as freshman=1, sophomore=2, graduate student=3, etc). Each student is also related to his or her major and minor departments to the course sections he or she is currently attending and registered. And if a student completed a course a transcript should be given. Each transcript instance includes the grade the student received in a section of a course.

Graduate student is a subclass of student, with the defining predicate Class = 5. For each graduate student, we keep a list of previous degrees as a multivalued attribute. We also relate the graduate student to a faculty advisor and to a thesis committee, if one exists.

An academic department has the attributes name, telephone and office number and is related to the faculty member who is its chairperson and to the college to which it belongs. Each college has attributes college name, office number, and the name of its dean.

A course has attributes course number, course name, and course description. Several sections of each course are offered, with each section having the attributes section number and the year and quarter in which the year section was offered and quarter number used to identify the section.

Finally, the entity type grant keeps track of research grants and contracts awarded to the university. Each grant has attributes grant title, grant number, the awarding agency, and the starting date. A grant is related to one principal investigator and to all researchers it supports. Each instance of support has as attributes the starting date of support, the ending date of the support.

Use the description provided above about University of Vermont. You can make your own assumptions based on the requirements given. However, the assumptions should be clearly indicated / defined.

Answer following questions. Provide the following:

1. Identify business rules. Business rules are important to define relevant constraint in the organisations. Some organisations have specific rules only practice by the organisation. (e.g. A business rule for a car sales company may be "for each car separate invoice should be issued."). You can make any appropriate assumptions and mention them in your answer.

2. Design the database using Extended Entity Relationship (EERD) Diagram. Clearly indicate entities, attributes, relationships, cardinalities, primary keys and other special types of attributes relationships (e.g. multivalued attributes/ ISA relationships, etc.).

Note: Use correct symbols. If the symbols are incorrect your diagram cannot be considered as correct. You could use either crow's foot or Chen's EERD rules. However, do not mix both.

3. Develop relational schemas. Relational schemas should be derived using the EERD. You should map cardinalities correctly from EERD to Relational Schema. You should clearly indicate the referential integrity constraints (primary and foreign key relationships) using arrows. Clearly indicate datatype for each attribute.

4. Perform Normalization (including 3rd Normal form). In each normal form mention what you would consider and clearly show how would you remove redundancies. Should clearly indicate how relational schemas are changing and how would you adjust referential integrity of the schemas.

5. Write 5 queries with where clause that would think used by the University. You are required to clearly indicate the functionality of each query. You will not be given marks, if you have not explained the functionality of each query.

Submission: Submit a copy of your solution - a single document (pdf format - 5 marks of penalty will be applied for other format of submissions), electronically on the course portal, on or before the due date.

You must submit your Report with a

• Cover page (with student number, name and a signed statement that mentioned all the work is your own work.)- Failing of one of the items will applied for 5 marks of penalty form the final mark.

• Answers for above mentioned questions. The design should be very clearly documented. And answers should be written in the order of the questions. If not 5 marks of penalty will be applied.