Principles of Software Development Lab Assignment #3:

ENSF 607 – Principles of Software Development
Lab Assignment #3:
The following rules apply to thislab and all other lab assignments in the future:
1. Before submitting your lab reports, take a moment to make sure that you are
handing in all the material that isrequired. If you forget to hand something in,that
is yourfault; you can't use `I forgot' as an excuse to hand in parts of the assignment
late.
2. 20% marks will be deducted from the assignments handed in up to 24 hours after
each due date. It means if your mark is X out of Y, you will only gain 0.8 times X.
There will be no credit for assignmentsturned in later than 24 hours after the due
dates; they will be returned unmarked.
Using the model for the Student Registration System
Total Mark 100 points
In this lab, you are asked to implement a student registration scenario, draw an ER diagram of how
your database will look like, install a database (PostgreSQL, or MySQL), set up and populate the
tables from the drawn diagram in the respective database using either SQL or the Admin GUI, get
the JDBC driver from the vendor and use it in java code. In this assignment, you will need to create a
database with three tables: Student, Course, and Registration.
Table name Student Course Registration
Table
parameters
1. Student Id varchar(10)
2. FirstName varchar(50)
3. LastName varchar(50)
4. Location varchar(100)
1. Course Id varchar(10)
2. Course Name varchar(50)
3. Course Title varchar(50)
1. Registration Id varchar(10)
2. Course Id varchar(10)
3. Student Id varchar(10)
You should implement all tables with primary keys and foreign keys.
You should demonstrate three queries:
• Get all students.
• Get all courses.
• Get all registrations.
Task 1: Draw ER diagram (10 Marks)
Task 2: Installing the database with JDBC driver (20 Marks)
Task 3: Populating the database (10 Marks)
Task 4: Correct connection string (10 Marks)
Task 5: Demonstrating the three queries via java code (30 Marks)
Task 6: Documentation (20 Marks)
• ER Diagram
• Screen print of the database and JDBC install.
• Commented Source code.
• Output of the three queries
Exercise 1
Building an Incident Management Dashboard
Total Mark 100 Points
In the following assignment you will build a dashboard that provides information on service tickets. Since we do not have
a file with service tickets we need to create a database with service tickets.
1. Setup a database for service tickets. (20 Points)
The service ticket database will have several tables.
Table: EventActivity
Column Definition Comment
ID Integer Primary key of activity. Should auto
increment
Activityname Varchar(20) Activity name
Possible entries could be:
Design
Construction
Test
Password Reset
Table: EventOrigin
Column Definition Comment
ID Integer Primary key of activity. Should auto
increment
Activityname Varchar(20) Activity name
Possible entries could be:
Joe S.
Bill B.
George E.
Achmed M.
Rona E.
Table: EventStatus
Column Definition Comment
ID Integer Primary key of activity. Should auto
increment
Status Varchar(20) Status Decsiption
Possible entries:
Open
Exercise 2
On Hold
In Process
Deployed
Deployed Failed
Table: EventClass
Column Definition Comment
ID Integer Primary key of activity. Should auto
increment
Class Varchar(20) Class Decsiption
Possible entries:
Change
Incident
Problem
SR for Service Request
Table: EventLog
Column Definition Comment
ID Integer Primary key of activity. Should auto
increment
Caseid Varchar(20) Unique Case Id. Prefixed with
CS_<number>
Activity Varchar(20) Actvity from EventActivity Table
Urgency Varchar(1) Urgency value from table
Impact Varchar(1) Impact from table
Priority Varchar(1) Calculated priority from urgency and
impact
StartDate date Date Ticket was created
EndDate date Ticked was closed
TicketStatus Varchar(20) Ticket status
UpdateDateTime datetime Date/Timestamp of ticket record
Duration integer Length of ticket time. Calculated between
start date and end date
Origin Varchar(20) Person /Owner of ticket
Class Varchar(20) Ticket class from the class table
2. Develop a ticket generator program (40 Points)
Since we do not have any sample ticket file you need to write a program to generate tickets. You can either write
the program in Java or Python. It should follow the following requirements. All values in the ticket will be
randomly determined based on the respective values in the database table. That is as close as we can come to
provide a good sample event log.
Input parameters.
Number of tickets to generate
Time window for tickets.
Time window start date
Time window end date.
Each ticket that the program creates should fall within the provided time window. For example if you create
10000 tickets for the first 6 month of the year your time window is 2023-01-01 to 2023-06-30
3. Develop a dashboards and visualize the ticket data (40 Points)
You can try using any dashboard software of your choice. Most vendors provide a 30 day Trial. PowerBi is the
most common one in the industry.
PowerBI
You can download it from here
https://powerbi.microsoft.com/en-ca/downloads/
Tableau
https://www.tableau.com
Spotfire
https://www.tibco.com/products/tibco-spotfire
Install the dashboard software of choice.
Connect to your incident database with the generated data.
Now create a couple of dashboards.
1. Show total number of tickets over time window as graph by class.
2. Show successful deployment over deployment failures as line chart by month
3. Show MTTR over time window. MTTR = Duration/Number of Tickets
4. Try a couple of other interesting dashboards you might come up with. Play around with the generated data.
Artifacts you need to provide for this assignment
1. The SQL scripts for your tables.
2. Your source code for the generator program
3. Your dashboards visualization in a PowerPoint with explanation
Urgency, Impact and Priority
Priority is calculated from urgency and impact using the following table
A sample dashboard using Tableau