This project was started on the 9th of February, the aim was to design and build a medium-large scale database in SQL Server which stores university data and contains multiple triggers and procedures. The data used for courses was taken from a local university website. Staff and student data was randomly generated in visual studio using C#.
I designed this database in excel, the colour coding represents primary key to foreign key relationships. This is not the final design, more tables were added during the implementation process.
Creating the tables in SQL Server. There were approximately 15-16 tables in total. The Enrolment tables were difficult to implement because of their many to many relationship nature, especially when it came to writing stored procedures for them.
Adding foreign keys followed creation of tables. They outline the one to many relationships between tables. For example each department could have many courses, therefore a foreign key references the department table needed to be added to the course table, and so on.
Verifying all the required foreign keys were created afterwards, using sys.objects.
The database design diagram. It shows all the relationships between tables. It can be noted tblLog does not relate to any table. Every other table contains at least 1 one to many relationship. tblEnrolmentDetail has a many to many relationship with tblCourse, tblStaff and tblEnrolment.