CSC 456Database Management Systems I

University Database Design Project

Due by Wednesday, December 14, 2016

No Late Submission Will Be Accepted !!!

1. Introduction

For this homework, we move up to the role of a free-lance database expert to design and

implement a user database. We’ve been hired to design and implement a database for

Kutztown University of Pennsylvania because their current system violates all known rules for

good database design and implementation. In fact, it’s a stretch to even call the existing

system a “database”.

Our university database will keep track of departments, professor, courses, students, loans,

research projects, and grants.

2. Database Objects

These are the main “objects” in our database.

1. Departments. Each department has a unique id, a name, a location, a telephone number, and

a secretary. Departments employ professors and offer courses.

2. Professors. Each professor has a unique id, a name, a rank, and a salary. All professors with

the same rank have the same salary. Professors teach courses, apply for grants, lead research

projects, participate in research projects, and advise students.

3. Students. Each student has a unique id, a name, a class level, and a gpa. Studentsenroll in

courses and can be employed by research projects.

4. Courses. Each course has a course number and a section number that together are unique

within a given semester and year. Courses also have a meeting day, time, and room.

5. Projects. Each research project has a unique project id, a principal investigator, and a budget.

Projects can employ students and can be worked on by other professors. All projects are

funded by some grant.

6. Grants. Each grant has a unique id, a principal investigator, a status (applied, funded,

denied), and a budget. Grants are used to fund research projects.

7. Loans. Students can take out a loan to help with their expenses. Each loan has a unique loan

id, a loan amount, and the anticipated starting date for the payments.

3. Database Requirements

The following rules summarize the interactions among our objects.

1. Departments can employ multiple professors, and each professor can work for multiple

departments. There is a percent time associated with each department a professor works in.

Departments also offer multiple courses each semester.

2. Professors can teach multiple courses in each semester, and the same course in different

semesters. Professors advise students and can have multiple advisees. Professors apply for

grants to fund research projects and can apply for multiplegrants. A funded grant means

that the professor will lead a new research project, and professors can lead several research

projects.

3. Students enroll in several courses for each semester. For each course, a studentreceives a

grade. Students are advised by a single professor. Students may apply for multiple loans.

Students may be employed by a single research project. Students can mentor other students.

4. A single course can only be taught once within a given semester, but can be repeated in any

other semester. Courses are taught by a single professor and are taken by multiple students.

5. Each research project is funded by a single grant, and the leader of the project is the

principal investigator of the grant. Projects can also have other professors working on them

besides the PI. Projects can also employ multiple students.

6. Grants are applied for by professors and used to fund research projects. Each grant can have

only one professor as a principal investigator.

7. Loans are applied for by students.

4. Sample Queries

The following is a list of sample queries that a user might make against our new

database.

1. List all professors who work at least 50% of the time in the Mathematics Department.

2. List all professors who work at least 50% of the time in the COSC Department.

3. List all of the courses being offered for the Fall semester, 1996.

4. List all of the courses being offered for the Spring semester, 1997.

5. List all of the courses that a professor Baker will be teaching in the Fall of 1996.

6. List all of the courses that a professor Baker will be teaching in the Spring of 1997.

7. List all of the courses that a student Kellee will be taking in the Fall of 1996.

8. List all of the courses that a student Kellee will be taking in the Spring of 1997.

9. List all of the students enrolled in COSC 1010-01-963.

10. List all of the students enrolled in COSC 4820-01-971.

11. List all of the students who received a B or better in COSC 3020-01-963.

12. List all of the students who are mentors.

13. List all of the students who are being mentored.

14. List the students who student #33 mentors.

15. List all of the advisees for professor Baker.

16. Who advises student Ted?

17. List all grants that have been denied.

18. List all grants from professor Charlie.

19. List all students who are employed by any research project.

20. List all faculty who are participants in a research project (not including the principal

investigator).

21. List the project that are led by professor Able.

22. List all of the courses offered by COSC.

23. List the departments that professor Charlie works in.

24. What grade did student Ted receive in COSC 2390-01-963?

25. Which student have loans?

As you can see, the number of interesting questions we can ask to this database are very

numerous, and this is only a fraction.

5. What to Do

The first thing you need to do is to construct an ER diagram that models this data. Use

connectivity and cardinality (use (min, max) notation) where possible. Then transform your

ER diagram into a set of relations (tables).

Next, you need to make sure your schemas are in 3NF; that is there are no redundancies. This

will result in a new set of relations.

Next, you need to implement these new relations using the Oracle DBMS. You must use

the following data set to populate your database.

Next, execute the above list of queriesfor test.

6. What to Turn In

You need to hand in a hard-copy of the ER diagram, a set of relations (i.e. tables) after normalization with actual records in them, the query statement and result foreach of the 25queries.

1