SQL SERVER PROJECT: UNIVERSITY DATABASE (PART 1) – DESIGN

SUMMARY

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#.

DESIGN

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.

2017-02-09 12_35_17-Microsoft Excel - New Microsoft Excel Worksheet.xlsx.png

IMPLEMENTATION

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.

2017-02-09 11_52_51-SQLQuery1.sql - DANIEL.UniProject (daniel-sa (59))_ - Microsoft SQL Server Manag.png

2017-02-09 11_53_01-SQLQuery1.sql - DANIEL.UniProject (daniel-sa (59))_ - Microsoft SQL Server Manag.png

2017-02-09 11_53_10-SQLQuery1.sql - DANIEL.UniProject (daniel-sa (59))_ - Microsoft SQL Server Manag.png

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.

2017-02-09 12_37_32-uniTables.sql - DANIEL.UniProject (daniel-sa (59))_ - Microsoft SQL Server Manag.png

Verifying all the required foreign keys were created afterwards, using sys.objects.

2017-02-09 12_13_53-SQLQuery2.sql - DANIEL.UniProject (daniel-sa (54))_ - Microsoft SQL Server Manag.png

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.

2017-02-12 17_23_17-DANIEL.UniProject - Diagram_0_ - Microsoft SQL Server Management Studio (Adminis.png

 

2 thoughts on “SQL SERVER PROJECT: UNIVERSITY DATABASE (PART 1) – DESIGN

Add yours

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Website Built with WordPress.com.

Up ↑

%d bloggers like this: