Reference no: EM132154736

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.

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.