CSC 630 Principles of Data Base System Design
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