In this project option, you are asked to design and implement a database for a real mini world environment based on the given database requirements written in natural languages.
You are given a set of requirements for a university database. Based on the set of requirements for the university database, you are asked to do the following:
1. Partition the sentences (database requirement description in natural language) into homogeneous groups.
2. Design the conceptual schema of the university database by using ER diagram and CSDL. Your conceptual design of the database should include the followings but not limit to:
a. Entities
b. Relationships
c. Keys
d. Structural constraints (Cardinality ratio and participation constraints)
3. Transform the ER schema of database you get from step 2 into the corresponding relational database schema.
a. Specify all the key attributes of relations and any referential integrity constraints.
b. Specify the data item format for each attribute in each relation schema.
c. Specify all the functional dependencies you could infer from the requirements.
4. List all the join paths existing in the relational database schema you get from the transformation in step 3.
5. Normalize relation schema in the database design that you get from step 4 into either 3NF or BCNF if it is necessary.
6. Implement the relational database you get in step 5, via ORACLE SQL*PLUS, this includes creating the database, creating the corresponding relation schemas, data preparation and loading data into the database.
7. Implement the given queries in ORACLE SQL*PLUS.
8. Write a detailed and comprehensive report the database design and implementation.
Queries:
1. For each department, list the numbers of major students and minor students.
2. For each department, list all the instructors along with the number of courses he/she teaches.
3. For each department, list all the courses which it offers.
4. For each course, list all the prerequisites of that course.
5. For each department, list the total number of professors and average teaching load.
6. For each department, list the total numbers of students, total number of credit hours taken by these students, and the average credit hour per student.
7. For each instructor, list the number of all students who register in the sections that the instructor teaches.
8. For each instructor, list all the departments which offer the courses that the instructor teaches.
9. For each department, list all the professors who teach more than two courses, and make the salaries less than the average salary of the professors in their department.
10. Show how many students that each professor advises.
11. Find the departments which have more students than the average students per department.
12. Find the departments whose total salary is greater than the average salary per department.
13. For each department, list the professors who have the number of Ph.D. students he/she advises more than the average number of Ph.D. students these professors advise in their department.
14. List the students who have completed all the prerequisite courses for their major.
15. List the students who have taken all the courses offered by Professor Smith.
16. List the students who have only taken the courses taught by Professor Smith.
17. List the students who have taken all the courses that the student Franklin has taken.
18. List the students who passed all the exams required by their respective study plan.
19. List the students who had taken the courses required by their study plan.
The requirements for a university database:
In a university, we represent data about both students and employees, The university keeps track of each student's name, student number, social security number, address, phone, birth date, sex, class (freshman, sophomore,..., graduate), major department, minor department (if any), and degree program (B.A., B.S., M.A., M.S., ,„ Ph.D,), Some user applications need to access the city, state, and zip code of the student's address and the student last name. Both social security number and student number have unique values for each student. Each student has a study plan that shows list of required courses to be taken.
Each department is described by a name, department number, office number, office phone, and college, Both department name and department number have unique values for each department Each department has a Chairperson or a Dean in charge of that department. Each course has a course name, course number, number of semester hours (credit), and offering department Some courses have prerequisites (please pay attention here). Each course has the day, meeting time, place where the class is held. Each section has an instructor, semester, year, course, and section number. The section number distinguishes different sections of the same course that is taught during the same semester/year (may be at the same time), its values are 1, 2, 3, up to the number of sections taught during each semester. Employees are classified into faculty and staff, both of them have dependents, the database stores the information of employees' dependents for the insurance and benefit purposes.
Faculty could be full-time or part-time employees, Professors have ranks (Lecturer, Assistant Professor, Associate Professor, Full Professor) and salaries, Faculties (Professors) may hold different degree (highest degree is only considered here), Each professor belongs to at least one department Professors may have joint appointments from other department(s).
Staff are secretaries, program coordinators, assistant directors, directors, deans, vice presidents, and president.
A grade report for a course has student names, section number, and grades. Students may have a transcript for all the courses they have taken. For graduate students, the student's advisor should be included in the database.
Hint:
1. For any unspecified requirements, add the appropriate assumptions to make the specification complete.
2. You may want to identify multi-value attributes, composite attribute, and multi-valued composite attributes.