VOIP Project: Database implementation - Part 3

After creating the database model as shown in the previos post VOIP Project: Database modeling - Part 2, we will cover the implementation of that model in this part. On this project will be used a MySQL database. During this lesson we will demostrate the implementation on a MySQL database, which you can obtain by following the lesson Installing MySQL database, or you can create an account at db4free.net in order to have a MySQL database online.

First step after logging into the phpMyAdmin management site, as shown in VOIP Project: Database modeling - Part 2, will be the creation of the database. In the databases tab in the menu of the phpMyAdmin management site is selected the name of the database. During the development of the project the name of the database is set to voip_school_network. After the database is created, the next step that follows is the creation of the tables.

As seen in figure 1 of the lesson VOIP Project: Database modeling - Part 2, in order to supply all the data in a proper structure, this database will contain six tables. In order to properly organize the retrieving process of the data form the database and to pass the workload to the database, there is created a view named cit_view_consultation, a view containing information from three tables, cit_schedule, cit_subject and cit_consultation.

In order to create the tables in the phpMyAdmin management site, after clicking in the name of the database, in the create table session, must be provided the name of the table and the number of columns for each table that will be created. After clicking the go button a page where can be defined the name, type and other attributes for each column is opened. After filling the required information for each table as shown in the figure 1 of the database ERD in VOIP Project: Database modeling - Part 2 lesson, the table is created into the database.

In order to create the view, the proper query of the view must be executed in the SQL tab. In the session “Run SQL query/queries on database voip_school_network:” where a text area is provided must be written the following query:

Example code:
SELECT con.UserName, con.Subject_Id, sub.Name, sch.UserName as ProfessorUserName, con.Periodicity, con.Day, con.StartTime, con.EndTime from cit_consultation con join cit_subject sub on sub.Id = con.Subject_Id join cit_schedule sch on con.Subject_Id = sch.Subject_Id join cit_user usr on sch.UserName = usr.UserName where usr.Position = 'Professor';

and then the go button must be clicked. After the query execution, in the page that apears, in the query results options, create view option must be clicked. After the create view is clicked, in the popup window that appears, the name of the view must be defined, during the development of the project the given name of the view was cit_view_consultation and in the and go button must be clicked in order to create the view.

As it can be seen from the query shown in the previews paragraph, the view in the UserName column will contain the information from the cit_consultation table, and will be the username of the user for whom the consultation is registered.

The next column that the view will have, Subject_Id will have the id of the subject from the same table as the column UserName mentioned above. The Subject_Id will hold the value of the subject where a consultation will be held.

The username of the professor will be stored in the column ProfessorUserName of the view, which will take it values from the column UserName of the cit_schedule table. This column will hold the value of the UserName that teaches the subject with the specific given id.

The other three columns of the view, Day, StartTime and EndTime take their values from cit_consultation table. These three columns define the time when a consultation is held.

< Part 2: Database modeling Part 4: Understanding VOIP >

(please report broken link in the comment)

Comments

Post a Comment

Popular posts from this blog

Free host and domain

Image
In order to be able to have a website online are needed a server which is used to host the website and a domain which is linked with the IP of the server where is hosted the website. In order to get a host and a domain the programmers must pay, but at 000webhost.com can be taken free subdomain and host. At 000webhost.com after sign up must be confirmed the email address entered during sign up. Figure 1 shows an example of filling the information during the sign up that we'll use in this lesson. Figure 1: Sign up at 000webhost.com The information that are used in figure 1 must be replaced as required. First is the email, after email is the password field and the third input field is the field where is entered the name of the website and bellow is updated the domain of the website which will be created. After the creation of the account the email address must be confirmed. After confirming the email address, the created website is displayed as seen in figure 2 bellow. Fig
Read more

C++ Beginner: Lesson 3 - Simple calculator

Image
After we got familiar with Qt Creator environment in the lesson 2 we will build a simple calculator in order to get familiar with some data types which come with Qt Creator framework like QString and other C++ primitive data types. First of all we need to create a new project as shown in the lesson 1 . During the creation, instead of mainwindow class we have used calculator class. After creating the project, in design view of qt creator we create the layout of our application. Initially we get a LineEdit element from the toolbox panel as shown in the figure 1. Figure 1: Add LineEdit on window After we add the line edit on window we fix the size according to our needs, in this case we fill the window horizontally by leaving some margin right and left. After we add the LineEdit element on window we need to add the buttons for the numbers and operations as well as shown in the figure 2. Figure 2: Add PushButton on window After we add the buttons as shown in the figure above we
Read more

C++ Beginner: Lesson 4 - Tic Tac Toe game

Image
In this lesson we will cover if condition which we saw in the previous lesson as well, and we will cover the switch control statement too. Other elements that we will cover in this lesson are one and two dimensional arrays and we will see the for loop as well. During this lesson we will develop the tic tac toe game. In order to do so, we need to create a new project as we have seen in lesson 1 , but in this case we will call the application TicTacToe and the class will be named TicTacToe as well as shown in the figure 1. Figure 1: TicTacToe game The first step to create the TicTacToe application is to prepare the layout of the window. From the toolbox panel we take the Grid Layout element and drag it into window and than select the window and press the Lay Out in a Grid button as shown in the figure 2. Figure 2: Grid layout After the setup of the layout we start dragging and dropping into the window 9 QPushButton elements as shown in the figure 3 bellow. Figure 3: Add butt
Read more