ERD Database Management System Assignment

In ERD database management system assignment sample solution-ERD is made in Visio programming tool and implementation is done using MS SQL Server

Introduction

This assignment has been made to understand the concepts of database management system. Database has been designed by applying various features as studied during lectures. The tables are created with required constraints and on the basis of physical schema, ERD is drawn. Query implementation done through MS SQL Server via query language. Complex concepts like views and stored procedures are also implemented.

1 To make an ERD using crow’s foot notation

In this task an ERD has been made corresponding to Infosys database .MS Visio tool is used to draw the entity relationship diagram. Followings things are defined in the below ERD: [Also Read: – IT Programming Database Assignment Help ]

  • Defined Primary Key
  • Defined Secondary key
  • The diagram has defined relationships among entities.
  • Attributes are defined corresponds to each entity.

Assumptions

  • In this ERD there is a one to many relationships between student_record and Club_details.
  • Club_details table are related with event_details table.
  • The related_club_id is a foreign key in event_details table and primary key in club_details table.
  • Student_cl­_sc is a foreign key in student_record table.
  • Each student is associated with courses.
  • MTU_SU_staff table is related to course table.
  • Each student is associated with with one or more clubs/society.

database management assignment

image 1

Figure 1 ERD

2 With the help of ERD we have designed the database schema as per the requirements

We have designed five tables for Infosys database with required primary key and foreign key and other constraints. The yellow key symbol denotes the primary key feature. As you can see we have made Infosys database with tables such as: [Also Read: – Cipher Algorithm Networking Assignment Help]

  • Club_details
  • Student_record
  • Event_details
  • MTU_SU_staff
  • Courses

database management assignment

image 2

The structure of each table is as follows:

ERD Database Management System

image 3

Read more about Online Assignment Help

Figure 3club_details

ERD Database Management

image 4

Figure 4student_record

Top Grade Assignment Help

image 5

Figure 5 Courses

ERD Database Management

image 6

Figure 6event_details

ERD Database Management

image 7

Figure 7MTU_SU_staff

Populated data with data values

ERD Database Management

image 8

Figure 8club_details table

Top Grade Assignment Help

image 9

Figure 9 courses table

Top Grade Assignment Help

image 10

Figure 10event_details table

Top Grade Assignment

image 11

Figure 11MTU_SU_staff table

Technical Assignment Help

image 12

Query Implementation

To produce a list of all class representatives (CR)

Here to select all the representatives we need to fetch the records of CRs and for that we first select the student_record table. In this task where clause is applied in which the query is fired to fetch only those students who are CRs. [Also Read: – Big Mike Web Programming Assignment]

select * from student_record where extra_activity = ‘CR’

Database Assignment Help

image 13

Figure 13 output

Identify any courses without a class representatives

This is a very simple query and all we have to do is to select those courses that don’t have CRs. As you can see in the screenshot the all those tuples are retrieved which has ‘0’ as a value for course CR. [Also Read: – Staff Payroll System Database Assignment Help]

select * from Courses where courseCR = ‘0’

Database Assignment Help

image 14

Figure 14output

To produce a monthly calendar of events for students

The join query is applied on this problem as the data is fetched from three tables that is student_record, club_details and Event_details.

select [InfoSys].[dbo].[student_record].Student_Name,[InfoSys].[dbo].[club_details].clubtype,[InfoSys].[dbo].[EVENT_DETAILS].eventname from [InfoSys].[dbo].[student_record]

inner join [InfoSys].[dbo].[club_details] on [InfoSys].[dbo].[student_record].student_cl_sc =[InfoSys].[dbo].[club_details].Club_id

inner join [InfoSys].[dbo].[EVENT_DETAILS] on [InfoSys].[dbo].[club_details].Club_id = [InfoSys].[dbo].[EVENT_DETAILS].relatedclubid

image 15

Figure 15 output

Display the numbers of students signed up to each club

The join query is applied on this problem as the data is fetched from three tables that are student_record, club_details and Event_details. The count operator is used which displays the total number of records of an attribute on which it is applied.

select count([InfoSys].[dbo].[club_details].Club_id),[InfoSys].[dbo].[club_details].clubname from [InfoSysdbo].[student_record]

inner join [InfoSys].[dbo].[club_details] on [InfoSys].[dbo].[student_record].student_cl_sc =[InfoSys].[dbo].[club_details].Club_id

inner join [InfoSys].[dbo].[EVENT_DETAILS] on [InfoSys].[dbo].[club_details].Club_id = [InfoSys].[dbo].[EVENT_DETAILS].relatedclubid

GROUP BY [InfoSys].[dbo].[club_details].Club_id,[InfoSys].[dbo].[club_details].clubname;

Database Assignment Help

image 16

Figure 16 output

Details of all the staff of the MTU SU

The details of all the staff will be displayed through this query.

select * from MTU_SU_staff

Database Assignment Help

image 17

Figure 17 output

Details of all the students who are not class representatives

select * from student_record where extra_activity = ‘n’

Database Assignment Help

image 18

Figure 18 output

Views

A view can be defined as a virtual table that stores specific information of real table. It has similar attributes of the existing table for which view is created.

SELECT TOP 1000 [Club_id]

,[clubname]

,[clubstart_date]

,[clubtiming]

,[clubduration]

FROM [InfoSys].[dbo].[club_details_View_1]

Database Assignment Help

image 19

Figure 19 view procedure

Assignment Help Australia

image 20

Figure 20 view results

Assignment Help Australia

image 21

Figure 21values in view table

Stored procedures

Stored procedures are nothing but the functions or subroutines used to access relationship database system.

CREATE PROCEDURE spgetrecords

as

begin

select

student_record.Student_ID,student_record.Student_Name,student

_record.course from student_record

end

EXEC spgetrecords

USE [MS_SQL ASSIGNMENT]

GO

DECLARE         @return_value int

EXEC    @return_value = [dbo].[spgetrecords]

SELECT ‘Return Value’ = @return_value

GO

Assignment Help Australia

image 22

Figure 22 stored procedure making

Assignment Help Australia

image 23

Figure 23 query result of stored procedure

Read more about Assignment Help Australia 

Conclusion

This database designing assignment helped a lot to improve the database concepts. It will give an idea to implement query languages and other database concepts. The ERD is made in Visio programming tool and implementation is done using MS SQL Server.

References

Atlasindia.com, (2016).A Tutorial on Database Concepts, SQL using MySQL.. [online] Available at: http://www.atlasindia.com/sql.htm.

YouTube, (2016).Stored procedures in sql server Part 18. [online] Available at: https://www.youtube.com/watch?v=Qu3E-oncF3g.

Top Grade Assignment Help provide technical assignment writing service based on case study requirements in affordable prices and we are providing most flexible online assignment writing help, so book your Assignment with us, order now 

top grade assignment

About the Author

Alice