Creating Software For Oral Examination Timetabling Computer Science Essay

Published:

This project is as the name suggests is based on a university oral examination system. In a university where there are hundreds and thousands of students studying, it is very difficult and hectic to prepare their oral examination dates keeping in mind about the various constrain like teacher's timings, university operation timings, student availability, dates of exam availability and numerous others. Keeping all these in mind one has to develop a schedule manually, which is quit tough and time consuming task. But if a software is developed which will take all the constrains and will produce the proper oral examination time table then that would make life easier and would save great amount of time.

Here this report is trying to solve the same problem of university oral examination by creating software. This software takes all the different constraint needed and will produce a time table which will satisfy the need of the university. The scope of this project is very vided and bright as this can be used in any university, each and every university have the problem of sorting out the university examination which can be fulfilled by this software.

Lady using a tablet
Lady using a tablet

Professional

Essay Writers

Lady Using Tablet

Get your grade
or your money back

using our Essay Writing Service!

Essay Writing Service

To sum it up I would just say that I have really put in a great deal of effort in this project and able to accomplish most of them and not fully just because of the time constrains. But, still the part I have done I did make sure that it gives the best all the time. This project still has some work to do, which can make it more versatile and bigger in sense of usage, so I do recommend some future work on this project.

INTRODUCTION

This report was requested by Dr Wilton Bailey supervisor this project. The report concerns the research and development of university oral examination system. The main aim was to develop a robust and fully efficient university oral examinations system for the administrator.

This section of the report will give detail knowledge of the whole system. In order to build the software we first needed to understand fully the working of the whole system in detail. Like, what are the key contents of the system what are the different constraints given etc.

This project even demanded a storage space where all the detail and the the examination timetable should be stored so there are main two body they are as follows, of which we will talk in detail individually.

Gui part (VISUAL STUDIO.NET)

Database part (SQL SERVER)

VISUAL STUDIO.NET

Gui part:- We have used visual studio.net as the graphical user interface, as it is easy to use, code and user friendly. here in this report we will discuss something about the working of visual studio.net

It is a platform that provides tools and technologies you need to build Networked Applications as well as Distributed Web Services and Web Applications. This provides the necessary compile time and run-time foundation to build and run any language that conforms to the Common Language Specification (CLS).The main two components of .Net Framework are Common Language Runtime (CLR) and .Net Framework Class Library (FCL).

Common language runtime:-

The main function of Common Language Runtime (CLR) is to convert the Managed Code into native code and then execute the Program. The Managed Code compiled only when it needed, that is it converts the appropriate instructions when each function is called . The Common Language Runtime (CLR) 's Just In Time (JIT) compilation converts Intermediate Language (MSIL) to native code on demand at application run time.

During the execution of the program ,the Common Language Runtime (CLR) manages memory, Thread execution, Garbage Collection (GC) , Exception Handling, Common Type System (CTS), code safety verifications, and other system services.

The user written Source Code are compiled using the .Net compiler and are fed into microsoft Intermediate Language (MSIL). Microsoft Intermediate Language (MSIL) is a CPU independent set of instructions that can be converted to the native code. Metadata also created in the course of compile time with Microsoft Intermediate Language (MSIL) and stored it with the compiled code . Metadata is completely self-describing . Metadata it contains information about the members, types, references and all the other data that the Common Language Runtime (CLR) needs for execution.

Lady using a tablet
Lady using a tablet

Comprehensive

Writing Services

Lady Using Tablet

Plagiarism-free
Always on Time

Marked to Standard

Order Now

During the runtime when the Common Language Runtime (CLR)'s is executing the metadata Just In Time (JIT) compiler converts the Microsoft Intermediate Language (MSIL) code into native code to the Operating System. Which in turn finally produces the output.

.Net Framework Class Library (FCL)

The .Net Framework class library (FCL) provides the core functionality of .Net Framework architecture . The .Net Framework Class Library (FCL) includes a huge collection of reusable classes , interfaces, and value types that expedite and optimize the development process and provide access to system functionality.

The .Net Framework class library (FCL) organized in a hierarchical tree structure and it is divided into Namespaces. Namespaces is a logical grouping of types for the purpose of identification. Framework class library (FCL) provides the consistent base types that are used across all .NET enabled languages. The Classes are accessed by namespaces, which reside within Assemblies. The System Namespace is the root for types in the .NET Framework.

The .Net Framework class library (FCL) classes are managed classes that provide access to System Services . The .Net Framework class library (FCL) classes are object oriented and easy to use in program developments. Moreover, third-party components can integrate with the classes in the .NET Framework

Declaring Variables :-

Variables are named storage areas inside computer memory where a program places data during processing. A program sets aside these storage areas by declaring variables, that is, by assigning them a name and indicating what types of data will occupy these areas during processing.

Within a Visual Basic program, variables are declared using the Dim statement whose general format is shown below

Dim variables as type

where type is one of the data types permissible in Visual Basic and variable is a programmer-supplied name for the storage area.

Naming Variables

Programmer-supplied variable names are also referred to as identifiers, and they must conform to Visual Basic naming conventionsand a variable name must

Variable names are not case sensitive, so the name expressed in upper-case characters refers to the same storage area as a name expressed in lower-case characters and however, for programming consistency, and to avoid unnecessary errors, it is best to express a variable as it is originally declared. Thus, valid variable names include Y, My_Data, MyData, My_Data_Veta, and other names using meaningful and simple identifiers.

VB.NET Access Specifies

Access Specifies describes as the scope of accessibility of an Object and its members. We can control the scope of the member object of a class using access specifiers. We are using access specifiers for providing security of our applications.

Visual Basic .Net provide five access specifiers , they are as follows :

Public, Private , Protected , Friend and ProtectedFriend .

Public :

Public is the most common access specifier. It can be access from anywhere, hat means there is no restriction on accessability. The scope of the accessibility is inside class also in outside the class.

Private :

The scope of the accessibility is limited only inside the classes in which they are decleared. The Private members cannot be accessed outside the class and it is the least permissive access level.

Protected :

The scope of accessibility is limited within the class and the classses derived (Inherited )from this class.

Friend :

The Friend access specifier can access within the program that contain its declarations and also access within the same assembly level. You can use friend instead of Dim keyword.

ProtectedFriend :

ProtectedFriend is same access lebels of both Protected and Friend. It can access anywhere in the same assebly and in the same class also the classes inherited from the same class

Data Types

When declaring a variable an indication can be given about what type of data will be stored and there are many data types permissible under Visual Basic.In computer processing,the following are common for declaring the usual types of data involved:

Short. A non-decimal (whole) number with a value in the range of the -32,768 to +32,767.

 

Integer. A non-decimal (whole) number with a value in the range of the -2,147,483,648 to +2,147,483,647.

 

Lady using a tablet
Lady using a tablet

This Essay is

a Student's Work

Lady Using Tablet

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

Examples of our work

Long. A non-decimal (whole) number with a value in the range of the -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807.

 

Single. A floating-point (decimal) positive or negative number with a value in the range of up to the 3.402823E38 (scientific notation).

 

Double A floating-point (decimal) positive or negative number with a value in the range of up to the 1.79769313486232E308.

 

Decimal. A decimal number with a value in the range of up to +79,228,162,514,264,337,593,950,335. without decimal precision and up to the +7.9228162514264337593543950335 with 28 places of decimal precision.

 

String. Any number of special, numerical,alphabetic and special characters.

 

Date Dates in the range between January 1, 0001 to December 31, 9999 and times in the range between 0:00:00 to the 23:59:59.

 

Boolean. The value of True or False.

Assigning Values to Variables

With the Visual Basic assignment statement, variables are assigned values whose general format is shown below.

Variable declarations simply set aside memory storage areas as temporary containers of data -- temporary because they are no longer accessible once the program ends and during processing, of course, these variables are occupied by data values.

Data for processing can come from many different sources and they can come from outside the program -- from external files and databases; they can come from inside the program by explicitly setting a value for a variable or by storing processing results of manipulating other variables. In any case, variables take on values by assigning values to them.

Variable = value

String Operators

 

Visual Basic supplies the concatenation operator (&) to put together individual data strings to create a longer string of those combinations and the Like operator looks for matching characters in a string.

Operator

Use

& or +

Concatenation. Appends the string value appearing on the right of the operator to the string value on the left of the operator.

Like

Determines whether a string matches a given pattern.

 

Arithmetic Operators

Much of information processing involves applying arithmetic to numeric data types and values are added, subtracted, multiplied, and divided to generate results, either final results or intermediate values that are involved in further arithmetic operations. For these purposes Visual Basic supplies a set of seven arithmetic operators shown in the following table.

Operator

Use

+

Addition. Adds the values appearing on the two sides of the operator.

-

Subtraction. Subtracts the value on the right of the operator from the value on the left of the operator.

*

Multiplication. Multiplies the values appearing on the two sides of the operator.

/

Division. Divides the value on the left of the operator (dividend) by the value on the right of the operator (divisor).

\

Integer Division. Divides the value on the left of the operator by the value on the right of the operator, giving an Integer result.

Mod

Modulo Division. Returns the remainder of the division of the value on the left of the operator by the value on the right of the operator.

^

Exponentiation. Raises the value on the left of the operator to the power on the right of the operator.

Relational Operators

Visual Basic supplies a set of six relational operators to compare the magnitudes of two data values and comparisons can be made with numeric or string values, with a Boolean value returned from the comparison.

The following table summarizes these relational operators

 

Operator

Use

=

Tests whether two values are equal.

<> 

Tests whether two values are not equal.

< 

Tests whether the first value is less than the second value.

> 

Tests whether the first value is greater than the second value.

<=

Tests whether the first value is less than or equal to the second value.

>=

Tests whether the first value is greater than or equal to the second value.

Mathematical Functions

In the following table,popular mathematical functions are summarized.Also note that certain functions do not require the Math. prefix.

Function

Use

Math.Abs()

Returns the absolute value.

Math.Abs(-10) returns 10.

Fix()

Returns the integer portion of a number.

Fix(5.3333) returns 5.

Math.Max()

Returns the larger of two numbers.

Math.Max(5,7) returns 7.

Math.Min()

Returns the smaller of two numbers.

Math.Min(5,7) returns 5.

Math.Pow()

Returns a number raised to a power.

Math.Pow(12,2) returns 144.

Math.Sqrt()

Returns the square root of a positive number.

Math.Sqrt(144) returns 12.

Date and Time Properties

When working with dates and times it is often necessary to know the current date and/or time and these values can be extracted from the server's system clock through the properties shown in the table below. These properties are used in several of the time and date functions described below.

Property

Value

DateString

Returns a String value representing the current date.

   DateString = 12-21-2006

Now

Returns a Date value containing the current date and time.

   Now = 12/21/2006 5:20:00 AM

TimeOfDay

Returns a Date value containing the current time of day (the date is set to 1/1/0001).

   TimeOfDay = 1/1/0001 5:20:00 AM

Timer

Returns a Double value representing the number of seconds elapsed since midnight.

   Timer = 19200.1183166

TimeString

Returns a String value representing the current time of day.

   TimeString = 05:20:00

Today

Returns or sets a Date value containing the current date.

   Today = 12/21/2006 12:00:00 AM

Date Functions

In the following table,date functions are summarized with functions described more fully below.

Function

Use

DateAdd()

Returns a Date value containing a date and time value to which a specified time interval has been added.

DatePart()

Returns an Integer value containing the specified component of a given Date value.

DateSerial()

Returns a Date value representing a specified year, month, and day, with the time information set to midnight (00:00:00).

DateValue()

Returns a Date value containing the date information represented by a string, with the time information set to midnight (00:00:00).

Day()

Returns an Integer value from 1 through 31 representing the day of the month.

WeekDay()

Returns an Integer value containing a number representing the day of the week.

 

 

Time Functions

Time functions are summarized in the following table with functions described more fully below and several of the date functions can be applied to time measurements.

Function

Use

DateAdd()

Returns a Date value containing a date and time value to which a specified time interval has been added.

Hour()

Returns an Integer value from 0 through 23 representing the hour of the day.

Minute()

Returns an Integer value from 0 through 59 representing the minute of the hour.

Second()

Returns an Integer value from 0 through 59 representing the second of the minute.

TimeSerial()

Returns a Date value representing a specified hour, minute, and second, with the date information set relative to January 1 of the year 1.

TimeValue()

Returns a Date value containing the time information represented by a string, with the date information set to January 1 of the year 1.

 

Formatting Dates and Times

The FormatDateTime() function returns a string expression and representing a date/time value. Its general format is as follows:

 FormatDateTime(value [, DateFormat])

where value is a time or date value and format is one of the following values: GeneralDate, LongDate, ShortDate, LongTime, or ShortTime.

Format

Output

FormatDateTime(Now)

12/21/2006 5:49:47 AM

FormatDateTime(Today)

12/21/2006

FormatDateTime(TimeOfDay)

5:49:47 AM

FormatDateTime(Today,DateFormat.LongDate)

Thursday, December 21, 2006

FormatDateTime(Today,DateFormat.ShortDate)

12/21/2006

FormatDateTime(Now,DateFormat.LongTime)

5:49:47 AM

FormatDateTime(TimeOfDay,DateFormat.ShortTime)

05:49

Conditional Structures & Control Flow

IF…ELSE in VB.NET

The conditional statement IF ELSE , is use for examining the conditions that we provided, and making decision based on that condition. The conditional statement examining the data using comparison operators as well as logical operators.

If [your condition here]

Your code here

Else

Your code Here

End If

If the contition is TRUE then the control goes to between IF and Else block , that is the program will execute the code between IF and ELSE statements.

If the contition is FLASE then the control goes to between ELSE and END IF block , that is the program will execute the code between ELSE and END IF statements.

If you want o check more than one condition at the same time , you can use ElseIf .

If [your condition here]

Your code here

ElseIf [your condition here]

Your code here

ElseIf [your condition here]

Your code here

Else

Your code Here

End If

FOR NEXT loop

Whenever you face a situation in programming to repeat a task for several times (more than one times ) or you have to repeat a task till you reach a condtition, in these situations you can use loop statements to achieve your desired results.

FOR NEXT Loop, FOR EACH Loop , WHILE Loop and DO WHILE Loop are the Commonly used loops in Visual Basic.NET.

The FOR NEXT Loop , execute the loop body (the source code within For ..Next code block) to a fixed number of times.

For var=[startValue] To [endValue] [Step]

[loopBody]

Next [var]

var : The counter for the loop to repeat the steps.

starValue : The starting value assign to counter variable .

endValue : When the counter variable reach end value the Loop will stop .

loopBody : The source code between loop body

While …End While loop

Whenever you face a situation in programming to repeat a task for several times (more than one times ) or you have to repeat a task till you reach a condition, in these situations you can use loop statements to achieve your desired results.

FOR NEXT Loop, FOR EACH Loop , WHILE Loop and DO WHILE Loop are the Commonly used loops in Visual Basic.NET.

While ..End While

While .. End While Loop execute the code body (the source code within While and End while statements ) until it meets the specified condition.

While [condition]

[loop body]

End While

Do …While Loop

The Do loop can be used to execute a fixed block of statements indefinite number of times. The Do loop keeps executing it's statements while or until the condition is true. Two keywords, while and until can be used with the do loop. The Do loop also supports an Exit Do statement which makes the loop to exit at any moment. The syntax of Do loop looks like this:

Do[{while | Until} condition]

[statements]

[Exit Do]

[statements]

Loop

Methods

Methods

A Method is a procedure built into the class. They are a series of statements that are executed when called. Methods allow us to handle code in a simple and organized fashion. There are two types of methods in VB .NET: those that return a value (Functions) and those that do not return a value (Sub Procedures). Both of them are discussed below.

Sub Procedures

Sub procedures are methods which do not return a value. Each time when the Sub procedure is called the statements within it are executed until the matching End Sub is encountered. Sub Main(), the starting point of the program itself is a sub procedure. When the application starts execution, control is transferred to Main Sub procedure automatically which is called by default.

Functions

Function is a method which returns a value. Functions are used to evaluate data, make calculations or to transform data. Declaring a Function is similar to declaring a Sub procedure. Functions are declared with the Function keyword. The following code is an example on Functions:

Method Variables

Variables declared within methods are called method variables. They have method scope which means that once the method is executed they are destroyed and their memory is reclaimed. For example, from the above code (Functions) the Add method declared two integer variables i, j. Those two variables are accessible only within the method and not from outside the method.

Exception Handling

Exceptions

Exceptions are the occurrence of some condition that changes the normal flow of execution. For ex: you programme run out of memory , file does not exist in the given path , network connections are dropped etc. More specifically for better understanding, we can say it as Runtime Errors.

In .NET languages, Structured Exceptions handling is a fundamental part of Common Language Runtime. It has a number of advantages over the On Error statements provided in previous versions of Visual Basic. All exceptions in the Common Language Runtime are derived from a single base class, also you can create your own custom Exception classes. You can create an Exception class that inherits from Exception class.

You can handle Exceptions using Try..Catch statement.

Try

Code

Exit from Try

Catch [Exceptions [As Type]]

Code-if the expection occurred this code will execute

Exit from Catch

Finally

The code in the finally block will execute even if there is no Exceptions. That means if you write a finally block , the code should execute after the execution of try block or catch block.

Try

code

exit from Try

Catch [Exception [As Type]]

code - if the exception occurred this code will execute

exit Catch

Finally

code - this code should execute , if exception occurred or not

SQL SERVER

COMPARISON BETWEEN DATABASES

In this project there was a need of data base to store all the data of students, teachers, departments, dates and time etc. so on the following findings we decided to use sql server as our database

We were confused between two option for database software they were MS Access and SQL Server. Now "Microsoft Access seems to have some great features, why would I want to use any other database package. We were trained in both kind of databases, so confused which one should be the best for us. We started making a comparison between those two database

MS Access

Access is more suited for desktop use with a small number of users accessing it simultaneously. One reason you might choose to use Access over SQL Server is for compatibility/sharing. You might need to email someone a copy of your database. People are more likely to have Access on their desktop computer than SQL Server. You'll generally only find SQL Server on developers' computers/servers or on production server machines.

Another reason you might use Access instead of SQL Server is money. You might already have installed Access as part of the Microsoft Office suite. Purchasing SQL Server would be an extra expense that may not be necessary - depending on your situation. SQL Server can also be quite expensive.

MS SQL Server

SQL Server is a more robust database management system. SQL Server was designed to have many hundreds, or even thousands of users accessing it at any point in time. Microsoft Access on the other hand, doesn't handle this type of load very well. SQl Server is a server based database. Access is used as local database.

This makes SQL Server perfectly suited for database driven websites. You should never use Access for a database driven website - unless it has a very small amount of traffic (like you and a few of your friends). Even then, you may find yourself getting errors due to multiple users trying to access the database at the same time.

SQL Server also contains some advanced database administration tools that enable organizations to schedule tasks, receive alerts, optimize databases, configure security accounts/roles, transfer data between other disparate sources, and much more

Finally the winner is SQL Server

After making a detail comparison between these two databases, I preferred SQL over MS Access. Keeping in mind the security, the large size of the database, and the robustness this software demands according to me SQL Server is the best suited.

In SQL Server a lot of the processing can take place on the computer (server) running the back end. In SQL Server the selection logic can run on the server computer and only the one selected record has to travel across the network back to the client computer.

Here in this report we will discuss some basic theory of SQL Server use in the project.

A database is a collection of data. That may sound overly simplistic but it pretty much sums up what any database is.

A database could be as simple as a text file with a list of names. Or it could be as complex as a large, relational database management system, complete with in-built tools to help you maintain the data.

A Database Management System (DBMS), is a software program that enables the creation and management of databases. Generally, these databases will be more complex than the text file/spreadsheet example in the previous lesson. In fact, most of today's database systems are referred to as a Relational Database Management System (RDBMS), because of their ability to store related data across multiple tables.

Some of the more popular relational database management systems include:

Microsoft Access

Filemaker

Microsoft SQL Server

MySQL

Oracle

MS SQL Server 2008

Introduction

Microsoft SQL Server is a Relational Database Management System (RDBMS) designed to run on platforms ranging from laptops to large multiprocessor servers. SQL Server is commonly used as the backend system for websites and corporate CRMs and can support thousands of concurrent users.

SQL Server comes with a number of tools to help you with your database administration and programming tasks.

SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) is the main administration console for SQL Server.

SSMS enables you to create database objects (such as databases, tables, views etc), view the data within your database; you can configure user accounts, transfer data between databases, and more.

Here's what SQL Server Management Studio looks like when you first open it up:

Screenshot of SQL Server Management Studio

The left pane contains the Object Explorer. The Object Explorer provides navigation to databases, server objects (such as triggers), log files, and more.

The right pane allows you to write queries against the database and view the results. In this screenshot I have opened a blank query by clicking the "New Query" button. You can also bring up other windows, such as the Properties window.

Note that I have minimized the size of the window for this screenshot. Once maximized, you have much more room to play with. You can use SQL Server Management Studio to create as many databases as you like. You can also connect to as many databases on as many servers as you like.

Most of the tasks performed with SQL Server Management Studio are initiated either from the top menu, or by right-clicking on an icon/object.

SQL Server - Create a Database

One of the first things we should look at with SQL Server/Management Studio is how to create a database. After all, most of the tasks you perform with SQL Server will evolve around one or more databases.

The following steps demonstrate how to create a database in SQL Server using SQL Server Management Studio.

Right click on the "Databases" icon on the left side panel and select "New Database...":

Creating a new database in SQL Server - step 1

Name your database as u like and then click "OK":

Creating a new database in SQL Server - step 2

SQL Server - Create a Table

This lesson demonstrates how to create a table in a SQL Server database using SQL Server Management Studio (SSMS).

Ensuring you have the right database expanded, right click on the "Tables" icon and select "New Table...":

While you have this screen open, do the following:

Using the values in the screenshot, complete the details in the "Column Name" column, the "Data Type" column, "Length" column, and "Allow Nulls" column.

Make the IndividualId column an "identity column", by setting "Is Identity" to "Yes". This column is going to be an auto-number column - it will contain an incrementing number for each record that is created.

Set the "Default Value" of the DateCreated column to (getdate()). (This will automatically insert the current date into that field for each new record).

Creating a table in SQL Server - step 1

Creating a table in SQL Server - step 2

What we are doing at this stage, is creating the column names, specifying the type of data that can be entered into them, and setting default values. Restricting the data type for each column is very important and helps maintain data integrity. For example, it can prevent us from accidentally entering an email address into a field for storing the current date.

Save the table by selecting File > Save Table_1:

When prompted, name your table:

Creating a table in SQL Server - step 3

SQL Server - Adding Data

In the previous lesson, we created a new table in our database. We now have a database table, columns and all, but with no data.

Editing tables rows

We can use the "Edit Top 200 Rows" option to add data to our table.

To use this option, right click on the table you wish to open, and select "Edit Top 200 Rows":

Screenshot of editing the top 200 rows of a table in SSMS

SQL Server - SQL Scripts

In the previous lesson, we added data to our database table using the "Edit Top 200 Rows" option. In this lesson, we will look at how to write SQL scripts to update and run queries against our database.

Create a New Query

Before we generate our SQL script, we need somewhere to enter it into. This part is easy. Just click the "New Query" button:

Screenshot of the New Query button

A blank, white sheet should now appear on the right pane.

Write/Run Your SQL Script

You are now ready to write SQL queries against your database. You can use this interface to create database objects (such as databases, tables, views etc), insert data into a database table, select data, update data, delete data.

To run an SQL query:

Type your query into the workspace on the right pane

Click "Execute" (you can also press F5)

The following screenshot shows an example of using a SQL 'select' statement to select data from a database:

Screenshot SQL query results

As you can see, the results of the query are displayed in the bottom pane.

As a shortcut, you can click on a table in the left pane and drag it to the right pane when building your query. This can save you time - especially if you have many tables to add to your query.

The above 'select' statement is an example of a SQL query.

SQL Server - Query Designer

In this lesson, we will look at how to write SQL scripts using the graphical query designer.

About The Query Designer

The graphical query designer is a graphical user interface that allows you to build queries to run against your SQL Server database. This can be particularly useful when building complex queries that involves many tables, views etc.

The query designer can also be beneficial for those who are learning how to write SQL. This is because you don't need to remember the SQL syntax in order to write queries against your database - the query designer generates the SQL for you.

Building Your Queries

To build a query with the query designer:

Select Query > Design Query in Editor...:

Accessing the Query Designer

Add the tables you want to run the query against. In this case, we only have one table to choose from.

Accessing the Query Designer

Select the column/s you want to display in your query:

Query Designer

Click "OK"

Once you've clicked OK, you will find the query has been added to your workspace. You can then run it as you would any other query.

SQL Server - User Logins

SQL Server allows for the creation of user logins. Each individual who needs access to SQL Server can be given their own user account.

When the administrator configures these user logins, he/she can assign them to any number of roles and schemas, depending on the access that the individual is entitled to. In this lesson, we will walk through the steps in creating a user login.

To Create a New User Login

Using SQL Server Management Studio, expand the "Security" option and right click on "Logins"

Click on "New Login"

Creating a new login in SQL Server

Complete the login properties in the "General" tab by providing a name for the login, choosing the Authentication method (providing a password if you choose "SQL Server authentication"), and selecting the database to use as a default.

Distributed Queries

Once you have configured your linked server, you will be able to run queries etc against it. When you run a query against a linked server, it is referred to as a distributed query.

When you execute a distributed query against a linked server, you must include a fully qualified, four-part table name for each data source to query. This four-part name should be in the form linked_server_name.catalog.schema.object_name.

Structured Query Language (SQL)

_________________________________________________________________________

About SQL

SQL stands for Structured Query Language.

SQL is a language that enables you to work with a database. Using SQL, you can insert records, update records, and delete records. You can also create new database objects such as databases and tables. And you can drop (delete) them.

More advanced features include creating stored procedures (self contained scripts), views (pre-made queries), and setting permissions on database objects (such as tables, stored procedures, and views).

Although SQL is an ANSI (American National Standards Institute) standard, there are many different versions of SQL. Different database vendors have their own variations of the language.

Having said this, to be in compliance with the ANSI standard, they need to at least support the major commands such as DELETE, INSERT, UPDATE, WHERE etc. Also, you will find that many vendors have their own extensions to the language - features that are only supported in their database system.

Furthermore, transact-SQL is an extension to the ANSI standard and provides extra functionality.

Using SQL

If you need to build a website with a database providing the content, you will generally need the following:

A server side scripting language (i.e. ColdFusion, PHP, ASP/.NET)

A database query language (i.e. SQL)

A client side markup language and style sheets (i.e. HTML/CSS)

Although SQL can be quite involved, you can achieve a lot with a handful of SQL statements. When using SQL on a website, you will often find yourself either selecting a record, inserting a record, updating a record, or deleting a record. Fortunately, SQL has commands for performing each of these actions.

SQL Syntax

The SQL syntax is quite an easy one to grasp. Most of the actions you need to perform are done with a SQL statement.

Generally, a SQL statement begins by stating what to do (for example, "SELECT"), then states which object to do it to (for example, using the "FROM" clause).

SELECT * FROM Individual

It may also have a condition added to the end (for example, with a WHERE clause).

SELECT * FROM Individual

WHERE FirstName = 'Homer'

SQL is not case sensitive - the above examples could just have easily used all lowercase or all uppercase. Different programmers have their own preferences. For readability purposes, many SQL programmers prefer to use uppercase for SQL commands and lowercase for everything else.

DML & DDL

SQL is divided into two main categories; Data Manipulation Language (DML), and Data Definition Language (DDL). An explanation follows.

Data Manipulation Language (DML)

DML enables you to work with the data that goes into the database. DML is used to insert, select, update, and delete records in the database. Many of your SQL statements will begin with one of the following commands:

SELECT - Retrieves data from the database

INSERT - Inserts new data into the database

UPDATE - Updates existing data in the database

DELETE - Deletes existing data from the database

Data Definition Language (DDL)

You may also occasionally need to create or drop a table or other datbase object. SQL enables you to do this programatically using DDL.

Examples of DDL commands:

CREATE DATABASE - Creates a new database

ALTER DATABASE - Modifies the database

DROP DATABASE - Drops (deletes) a database

CREATE TABLE - Creates a new table

ALTER TABLE - Modifies the table

DROP TABLE - Drops (deletes) a table

The SELECT statement is probably the most commonly used in SQL. It simply retrieves data from the database.

Lets have a look at a simple SELECT statement:

SELECT * FROM Individual

SQL Select

This SQL SELECT statement is attempting to retrieve all columns from a table called Individual.

How do we know it is trying to select all columns? Because it is using an asterisk (*). This is a quick way of selecting all columns - it's much easier than writing out the names of all columns (especially if there are a lot of columns).

Of course, this SQL SELECT statement assumes that there is a table called Individual. If there wasn't, an error would be generated.

Lets have a look at the table the statement is trying to select data from:

IndividualId

FirstName

LastName

UserName

1

Fred

Flinstone

freddo

2

Homer

Simpson

homey

3

Homer

Brown

notsofamous

4

Ozzy

Ozzbourne

sabbath

5

Homer

Gain

noplacelike

Select from Multiple Tables

You can select from more than one table at a time. To do this, simply separate each table with a comma. You should also qualify any references to columns by placing the table name in front, separated by a dot.

SQL Where

In the previous lesson, we used a SQL SELECT statement to retrieve all records from a database table. This is fine if we want to see every record, but what if we were only interested in some records? For example, what if we were only interested in individuals whose first name is "Homer"?

We could use the WHERE clause.

Using the WHERE clause, you can filter out only those records that satisfy a given condition.

SQL WHERE Syntax

SELECT * FROM table_name

WHERE column_name = 'criteria'

Multiple Conditions

You can filter records based on more than one condition using operators. Two common operators are the AND and OR operators.

AND Operator

The AND operator filters the query to only those records that satisfy both the first condition and the second condition.

SELECT * FROM Individual

WHERE FirstName = 'Homer'

AND LastName = 'Brown'

OR Operator

The OR operator filters the query to only those records that satisfy either one or the other condition.

SELECT * FROM Individual

WHERE FirstName = 'Homer'

OR LastName = 'Ozzbourne'

SQL In

The SQL IN operator assists you in providing multiple values in your WHERE clause. This can provide very useful when you need to compare your value to a list of values. Often this list could be the result of a query from another table.

SQL Syntax

SELECT * FROM table_name

WHERE column_name IN (value1,value2,...)

SQL Inner Join

As discussed in the previous lesson, you should use the SQL INNER JOIN when you only want to return records where there is at least one row in both tables that match the join condition.

Example SQL statement

SELECT * FROM Individual

INNER JOIN Publisher

ON Individual.IndividualId = Publisher.IndividualId

WHERE Individual.IndividualId = '2'

Source Tables

Left Table

Id

FirstName

LastName

UserName

1

Fred

Flinstone

freddo

2

Homer

Simpson

homey

3

Homer

Brown

notsofamous

4

Ozzy

Ozzbourne

sabbath

5

Homer

Gain

noplacelike

Right Table

IndividualId

AccessLevel

1

Administrator

2

Contributor

3

Contributor

4

Contributor

10

Administrator

Result

IndividualId

FirstName

LastName

UserName

IndividualId

AccessLevel

2

Homer

Simpson

homey

2

Contributor

SQL Outer Join

This lesson covers both the left outer join, the right outer join, and the full outer join, and explains the differences between them. There are some occasions where you would need to use a left outer join or a right outer join, and others where you would need a full outer join. The join type you use will depend on the situation and what data you need to return.

Left Outer Join

Use this when you only want to return rows that have matching data in the left table, even if there's no matching rows in the right table.

Example SQL statement

SELECT * FROM Individual AS Ind

LEFT JOIN Publisher AS Pub

ON Ind.IndividualId = Pub.IndividualId

Source Tables

Left Table

Id

FirstName

LastName

UserName

1

Fred

Flinstone

freddo

2

Homer

Simpson

homey

3

Homer

Brown

notsofamous

4

Ozzy

Ozzbourne

sabbath

5

Homer

Gain

noplacelike

SQL Insert

Up until now, this book has covered the SELECT statement and variations on it. We are now about to learn a new statement - the INSERT statement.

The SQL INSERT command allows you to insert a record into a table in your database. As with the SELECT syntax, the INSERT syntax is quite straight forward.

SQL statement

INSERT INTO Individual

VALUES ( '6', 'Benny', 'Hill', 'hillbenny' )

SQL Update

The SQL UPDATE statement allows you to update an existing record in the database.

The UPDATE command uses a WHERE clause. If you don't use a WHERE clause, all rows will be updated. In fact, the syntax for a basic UPDATE statement is very similar to a SELECT statement.

SQL statement

UPDATE Individual

SET UserName = 'funnyman'

WHERE IndividualId = '6'

Updating Multiple Fields

To update multiple fields, separate each field assignment with a comma.

SQL statement

UPDATE Individual

SET UserName = 'getserious', FirstName = 'Onetree'

WHERE IndividualId = '6'

SQL Delete

The SQL DELETE statement allows you to delete a record from the database.

The DELETE command uses a WHERE clause. If you don't use a WHERE clause, all rows in the table will be deleted. Again, as with the UPDATE statement, the syntax for a basic DELETE statement is similar to a SELECT statement.

SQL statement

DELETE

FROM Individual

WHERE IndividualId = '6'

SQL Create

Most database systems provide a way for you to create database objects via a WYSIWYG interface. For example, Microsoft's SQL Server has Enterprise Manager. The Enterprise Manager gives you a kind of graphical representation of your database system. You can browse through your databases, view the tables and their contents etc.

Despite having a tool like Enterprise Manager to make these tasks easier, there can be good reasons for wanting to perform some of these tasks programatically. Possible reasons could include:

Your application allows users to create objects on the fly.

You have multiple environments (for example development, staging, production). It's much easier, and less error prone, to run a script against 3 environments than to open up Enterprise Manager and repeat the steps in 3 environments - especially if you have lots of changes.

You don't have access to Enterprise Manager (or your database system's equivalent).

Fortunately, SQL has a number of CREATE commands that enable you to programatically create database objects including the database, its tables and more.

Here are the CREATE commands supported by SQL Server:

CREATE ACTION

CREATE CACHE

CREATE CELL CALCULATION

CREATE CUBE

CREATE DATABASE

CREATE DEFAULT

CREATE FUNCTION

METHODOLOGY

In this part of the report we will discuss in detail about the codes of the software and how they were developed. References of which are given later in the report.

There are 5 main section in this project