Reference no: EM132221026

Database for Video Game Centre

Scenario

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 staff members, the database maintains information on staff’s Name, Social Security number, address, and birth date, a rank (e.g. assistant, associate, adjunct, research, visiting, and so on), office, office phone, and salary.

All staff members are related to the academic department(s) with which they are affiliated (a staff member can be associated with several departments). For each student, the database maintains information on student’s Name, SID, address, and birth date 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.

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. ER diagram is based on the business rules, and should clearly label all entities, entity attributes, primary and foreign keys, relationship and connectivity. The cardinality is optional.

Instruction: Use professional software (e.g., MS Office Visio) to draw the ER diagram. Crow’s Foot notation is preferable.

3. Develop relational schemas. Relational schemas should be derived using the ERD.

You should map cardinalities correctly from ERD to Relational Schema. You should clearly indicate the referential integrity constraints  (primary and foreign key relationships) using arrows. Clearly indicate datatype for each attribute.

e.g.

Emp( eid: integer, ename: string(50), address: string(100), did: number)

Dept(did: number, dname : string (15))

4. SQL commands

1. Create all tables in Deakin Oracle DBMS (about eight tables including composite tables) and Populate the tables with sample data (10  records in each table is recommended).

2. Alter the student table and add new field Date of Birth for student table. Type for date of birth should be date.

3. Increase the annual salary for all staff member by 5%.

4. List the course numbers, course names a student who is doing computer science major could enrol for.

5. Find the students with age between 18 and 21. Print their student number, name and the age with the major.

6. Create your own query. It must include a nested query. Submit the following:

i question your query is answering the SQL query

ii the mark for this question will depend on the complexity of the query.

iii higher marks will be given for queries that are more complex and/or innovative.

iv if you do not provide a description of what question the query is answering, you will get zero for this query.