University XYZ needs a database that keeps track of students, what classes they taken and the grades for each of the classes. In addition, they need to know what instructors are on staff and the courses they are approved to teach. It is necessary to track rooms for each campus including capacities as well as special facilities for each room.

Review the solution .pdf file in Doc Sharing, and Adjust your ERD in Visio to look like this one.

This project is due in Week 4 and is worth 75 points.

Now we will convert our conceptual model into a logical model. Using Microsoft Access, perform the following steps.

For each entity listed in the Solution file, create a table in Access.
Add primary keys for each table.
Set column properties for unique constraints.
Update the datatype for the attributes to enforce the domain constraints of the data.

Dates should have a date datatype.
Surrogate keys should be autonumber.
Numeric should have a numeric datatype.
Character data should have a character datatype.
Under Database Tools, create the relationships, as appropriate.
(All relationships should have referential integrity and the cascade update enabled.)
Add several rows of data for each table.
Add column constraints, as indicated.
Campus name should be ONLINE or the campus name.
Student name shall be not null.
Course credit hours must be between 1 and 4.
Course name must be unique and not null.
Instructor last name and first name should not be null.
Grade should be one of these values: A, B, C, D, F, I, W, or E (Enrolled).

If you need help, you could post the suggested table names and the data each table contains.

Remember this is an occasion to practise what you have learned in your course, and you will need this knowledge / competence to pass your exam, and eventually solve a customer's problem during your career.

To meet the requirements of University XYZ, you would need to design a relational database that includes the following tables:

1. Students: This table would store information about each student, such as their student ID, name, contact details, and any other relevant information.

2. Courses: This table would contain information about each course offered, such as the course ID, course name, description, and other details.

3. Enrollments: This table would establish the relationship between students and courses. It would store information about which students are enrolled in which courses, along with any additional information like the enrollment date.

4. Grades: This table would store the grades earned by students in each course. It would include the relevant student ID, course ID, and the grade itself.

5. Instructors: This table would store information about each instructor, including their instructor ID, name, contact details, and other relevant information.

6. Course_Instructor: This table would establish the relationship between courses and instructors. It would store which instructors are approved to teach which courses and any additional information like the approval date.

7. Campuses: This table would store information about each campus, including the campus ID, name, address, and other relevant details.

8. Rooms: This table would contain information about each room, such as the room ID, capacity, and any special facilities available in the room.

9. Room_Campus: This table would establish the relationship between rooms and campuses. It would store which rooms are located in each campus and any additional information like the room assignment date.

By structuring the database in this way, you can efficiently track students, courses, grades, instructors, campuses, and rooms, as well as establish the necessary relationships between them. This will enable efficient querying and reporting on various aspects of the university's activities.