Description
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_ 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