Comprehensive experiment of database system design
Experimental Report Download
There are all three experimental reports
Link: link
About experiment
It is suggested to watch the video sent by the teacher first (the teacher said it is inconvenient to send it out, so it won't be sent here). Go to a station to search c# student information management system and learn about the basic operation
An important part of the source code is found in the experimental report and articles. Now it has been changed beyond recognition by me
Experimental software:
vs 2019
sql server
Oracle 11g
Visio (draw flow chart and E-R diagram)
Navicat Premium 15 (automatically generate inter table diagram)
Experimental requirements
C#& WinForm student information management system
It is assumed that there are three entities: "teacher", "student" and "course". The basic information of teachers includes: job number, name, professional title and salary. The basic information of courses includes: course number, course name and academic score. The basic information of students includes: student number, name, gender and age. The system must meet the following requirements:
- Only one teacher can teach a course, and one teacher can take multiple courses;
- A student can take multiple courses, and a course can be taken by multiple students, recording the results of different students taking different courses;
- The system includes two types of users, namely administrators and students. After logging in to the system, administrators have the following functions:
① Maintain (add, delete, modify and other basic tasks) basic information of students, teachers and courses;
② The administrator can reset the password for students;
③ When a student takes a course, the course score is entered by the administrator;
④ Be able to count the number of teachers with different professional titles, the average salary of teachers with different professional titles, and the average score, maximum score and minimum score of each course; Count the total credits of each student's elective courses;
⑤ Modify password and basic personal information. - After students log in the system with student ID and password, they have the following functions:
① Modify password and basic personal information;
② Elective courses and withdrawn courses;
③ Query the results of elective courses;
④ Count the total credits of all elective courses and completed elective courses (i.e. more than 60 points).
According to the above description, answer the following questions:
- Design and draw E-R diagram, and mark connected words (i.e. connection type);
- The E-R diagram is transformed into a relational model, and the main code and external code of each relationship are pointed out;
- Select a database management system in MySql, SQL Server and Oracle, and design the physical structure of the database;
- Choose a familiar object-oriented programming language to complete the system development.
Note: the minimum requirements of the test report must include the following contents:
(1) E-R diagram;
(2) The relational model obtained by transforming it into relational model, that is, logical structure design;
(3) SQL statements of related tables, including statements of creating tables, statements of creating related views, statements of creating related indexes, etc.
For the students who have completed the development, the experimental report should include the function test of the main interface (screenshots are required) and the relevant implementation code.
Content of experiment report
1. System requirements analysis stage
1.1 demand analysis
This system development selects student information management as the theme. Student information plays a very important role and value in school management and scheduling. The design and development of the system mainly provides information-based methods for the unified management of students.
In addition, the student information management system records the basic information of students and related courses. Students can easily edit, browse and query the basic information of students and teachers through the student information management system. Schools can also conveniently manage students' data information through this system.
1.2 function design
(here are the functional requirements in the title, and then I drew a flow chart)
The system function flow chart is as follows:
There are three types of users in the system. After logging in, users first judge their identity,
If you are an administrator, you can maintain (add, delete, modify and other basic tasks) the basic information of students, teachers and courses, reset the password, enter the course score, count the number of teachers with different professional titles, the average salary of teachers with different professional titles, count the average score of each course, the highest score, the lowest score Statistics of the total credits of each student's elective courses and other functions;
If you are a student, you can use the functions of changing your password and basic personal information, elective courses, withdrawing courses, querying results, and counting the total credits of all elective courses and completed elective courses (i.e. more than 60 points);
If you are a teacher, you can use functions such as viewing personal information,
Exit the system after the user's operation, and the process stops
1.3 data dictionary
1.3.1 data items
2. Conceptual structure design
2.1 entity and its attributes
2.2 global E-R model
The entities in the model include: students, teachers and courses
Inter entity linkages include:
- A course can only be taught by one teacher, one teacher can take multiple courses, and there is a one to many connection between the course and the teacher;
- A student can take multiple courses, a course can be taken by multiple students, and there is a many to many connection between the course and students.
3. Logical structure design
3.1 relationship analysis between tables
Here, after the tables are built with the database, connect to Navicat Premium 15 to automatically generate the relationship diagram between the tables
3.2 relationship model
3.3 relationship user sub model
4. Physical structure design
4.1 database storage structure
There are different types of data files in Orale database. The data file is real. The database allocates logical database space for all data in the database. The units of database space allocation are data block, segment and segment. It is composed of many blocks, many regions, and many segments. Oracle database consists of at least two logical storage units called tablespaces, which jointly store all data of the database. You must have SYSTEM and SYSAUX tablespaces. A tablespace consists of one or more data files named datafiles, which are the physical structure files on the operating SYSTEM where the Oracle database runs. Data is stored in the data file of each table space that makes up the database.
4.2 evaluation of physical structure
freespace free space can be managed automatically or manually. Free space is automatically managed in the database segment. Free / used space within segments is tracked using bitmaps instead of free lists. Automatic segmented space management provides better space utilization, especially for objects with large differences in row size; Better runtime adjustment for changes in concurrent access (Concurrency: a table can be used by multiple users)
Better multi instance behavior in terms of performance / space utilization (specify automatic segmented space management when creating locally managed tablespaces. The specification then applies to all segments subsequently created in this tablespace).
5. Database implementation
5.1 database software
The relevant software used in this experiment is:
Oracle 11g，Visual studio 2019，SQL server
5.2 related SQL statements
5.2.1 statement for creating table
create table student
(
sno varchar2(10) primary key,
sname varchar2(20) not null unique,
ssex char(3) check(ssex in('male','female')),
sage smallint not null,
pwd varchar(20) not null
);
create table teacher
(
tno varchar(7) primary key,
tname varchar(20) not null unique,
tposition varchar(20) not null,
tsalary smallint not null,
pwd varchar(20) not null
);
create table course
(
cno varchar(4) primary key,
cname varchar(40) not null unique,
ccredit smallint not null,
tno varchar(7) not null,
foreign key (tno) references teacher(tno)
);
create table sc
(
sno varchar(10) not null,
cno varchar(4) not null,
grade smallint not null,
primary key(sno,cno),
foreign key (sno) references student(sno),
foreign key (cno) references course(cno)
);
5.2.2 insert data
insert into student(sname,ssex,sno, sage, pwd) values('Haramuji','male','1906300158',20,'123456');
insert into student(sname,ssex,sno, sage, pwd) values('Zhao Si','male','1906300020',19,'123456');
insert into student(sname,ssex,sno, sage, pwd) values('lily','female','1906300195',18,'123456');
insert into student(sname,ssex,sno, sage, pwd) values('Weiwei','male','1906300140',19,'123456');
insert into student(sname,ssex,sno, sage, pwd) values('Xinxinzi','female','1906300152',19,'123456');
insert into student(sname,ssex,sno, sage, pwd) values('shy','male','1906300121',20,'123456');
insert into student(sname,ssex,sno, sage, pwd) values('Moo moo','female','1906300151',19,'123456');
insert into teacher(tno,tname, tposition, tsalary, pwd) values('t001','Teacher Liang','professor',20000,'123456');
insert into teacher(tno,tname, tposition, tsalary, pwd) values('t002','Tan Qiao','associate professor',15000,'123456');
insert into teacher(tno,tname, tposition, tsalary, pwd) values('t003','Ming Shiyin','professor',35000,'123456');
insert into teacher(tno,tname, tposition, tsalary, pwd) values('t004','Treetop','associate professor',38000,'123456');
insert into teacher(tno,tname, tposition, tsalary, pwd) values('t005','Zhou Yu','associate professor',42000,'123456');
insert into teacher(tno,tname, tposition, tsalary, pwd) values('t006','Little Joe','professor',60000,'123456');
insert into course values('6','numerical analysis',2,'t001');
insert into course values('2','Advanced mathematics',4,'t002');
insert into course values('7','C++language',4,'t003');
insert into course values('5','data structure',4,'t004');
insert into course values('1','database',4,'t003');
insert into course values('3','assembly language',3,'t006');
insert into course values('4','operating system',3,'t005');
insert into course values('8','The road to Erxian Bridge',3,'t002');
insert into course values('9','b Which is more fun, station or microblog',4,'t003');
insert into sc values('1906300158','1',92);
insert into sc values('1906300158','2',85);
insert into sc values('1906300158','3',88);
insert into sc values('1906300020','2',90);
insert into sc values('1906300020','3',81);
insert into sc values('1906300195','4',60);
insert into sc values('1906300195','5',98);
insert into sc values('1906300140','5',60);
insert into sc values('1906300140','7',90);
insert into sc values('1906300152','8',95);
insert into sc values('1906300152','2',87);
select * from student;
select * from course;
select * from teacher;
select * from sc;
5.2.3 creating related views
create view courseinfo as select course.cno Course number,course.cname Course name,teacher.tname Teacher,course.ccredit course credit,countnum Number of electives, avg_grade average,max_grade Highest score,min_grade Lowest score from teacher, course left outer join (select cname, count(*) countnum,avg(grade) avg_grade,max(grade) max_grade,min(grade) min_grade from sc,course where course.cno=sc.cno group by cname)a1 on (course.cname=a1.cname) where teacher.tno=course.tno; create view studentinfo as select student.sno Student number,student.sname full name,(select sum(grade) from sc where sno=student.sno) Total score , (select sum(ccredit) from course where cno in (select cno from sc where sno=student.sno)) Total credits , (select max(grade) from sc where sc.sno in (student.sno)) Highest score,(select min(grade) from sc where sc.sno in (student.sno)) Lowest score from student; create view teacherinfo as select tposition position,count(tno) Number of persons in office,avg(tsalary) average wage from teacher group by tposition; select * from studentinfo; select * from courseinfo; select * from teacherinfo1; select * from teacherinfo2; commit;
5.3 implementation source code and operation screenshot
Sign in:
//FrmLogin.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Oracle.ManagedDataAccess.Client;
namespace db_yj
{
public partial class FrmLogin : Form
{
public static string loginName = null;
public FrmLogin()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string username = tbUsername.Text;
string pwd = tbPwd.Text;
//string sql = string.Format("select * from student where sno='{0}' and pwd='{1}'", this.tbUsername.Text, this.tbPwd.Text);
//Eliminate the influence of sql statement annotation symbols
string sql = "select sno,sname from student where sno=:sno and pwd=:pwd";
OracleParameter[] para = new OracleParameter[]
{
new OracleParameter(":sno",OracleDbType.Varchar2,10),
new OracleParameter(":pwd",OracleDbType.Varchar2,20)
};
para[0].Value = username;
para[1].Value = pwd;
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
cmd.Parameters.AddRange(para);
OracleDataReader odr = cmd.ExecuteReader();
if (odr.Read())
{
loginName = odr.GetString(0);
DialogResult = DialogResult.OK;
MessageBox.Show("Login succeeded!");
this.Close();
}
else
MessageBox.Show("Login failed, account or password error!");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
private void radioButton1_CheckedChanged(object sender, EventArgs e)
{
}
private void radioButton2_CheckedChanged(object sender, EventArgs e)
{
}
}
}
(it's quite tender. Don't spray it if you don't like it)
Student information query and deletion
//FrmStuQuery.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Oracle.ManagedDataAccess.Client;
namespace db_yj
{
public partial class FrmStuQuery : Form
{
public FrmStuQuery()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string sql = string.Format("select sno,sname,sage,ssex,pwd from student where sname like '{0}%'", textBox1.Text);
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
OracleDataReader odr = cmd.ExecuteReader();
if (odr.HasRows)
{
BindingSource bs = new BindingSource();
bs.DataSource = odr;
dataGridView1.DataSource = bs;
}
else dataGridView1.DataSource = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
private void button2_Click(object sender, EventArgs e)
{
if (dataGridView1.SelectedRows.Count == 0) return;
if (MessageBox.Show("Delete data?", "Please confirm the information", MessageBoxButtons.OKCancel) == DialogResult.Cancel)
return;
DataGridViewRow row = dataGridView1.SelectedRows[0];
string sno = row.Cells[0].Value.ToString();
string sql = string.Format("delete from student where sno='{0}'", sno);
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
if (cmd.ExecuteNonQuery() == 1)
{
MessageBox.Show("Delete succeeded!");
dataGridView1.Rows.Remove(row);
}
else
MessageBox.Show("No corresponding student found!");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
private void dataGridView1_CellContentDoubleClick(object sender, DataGridViewCellEventArgs e)
{
if (dataGridView1.SelectedRows.Count == 0) return;
FrmStuUpdate frm = new FrmStuUpdate();
DataGridViewRow row = dataGridView1.SelectedRows[0];
frm.tbSno.Text = row.Cells[0].Value.ToString();
frm.tbSname.Text = row.Cells[1].Value.ToString();
frm.tbSage.Text = row.Cells[2].Value.ToString();
frm.tbSsex.Text = row.Cells[3].Value.ToString();
frm.tbpwd.Text = row.Cells[4].Value.ToString();
frm.ShowDialog(this);
}
private void label1_Click(object sender, EventArgs e)
{
}
private void textBox1_TextChanged(object sender, EventArgs e)
{
}
}
}
Student information modification
//FrmStuUpdate.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Oracle.ManagedDataAccess.Client;
namespace db_yj
{
public partial class FrmStuUpdate : Form
{
public FrmStuUpdate()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string sql = string.Format("update student set sname='{0}',ssex='{1}',sage={2},pwd='{3}' where sno='{4}'", tbSname.Text, tbSsex.Text, tbSage.Text,tbpwd.Text,tbSno.Text);
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
if (cmd.ExecuteNonQuery() == 1)
{
MessageBox.Show("Modification succeeded!");
this.Close();
}
else
MessageBox.Show("No corresponding student found!");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
}
}
Student information insertion
//FrmStuInsert.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Oracle.ManagedDataAccess.Client;
namespace db_yj
{
public partial class FrmStuInsert : Form
{
public FrmStuInsert()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string sql = string.Format("Insert into student(sno,sname,ssex,sage,pwd) values('{0}','{1}','{2}','{3}','{4}')", tbSno.Text, tbSname.Text, tbSsex.Text, tbSage.Text, tbpwd.Text);
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
if (cmd.ExecuteNonQuery() == 1)
{
MessageBox.Show("Insert successful!");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
}
}
Query and delete course information
//FrmCourseQuery.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace db_yj
{
public partial class FrmCourseQuery : Form
{
public FrmCourseQuery()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
try
{
this.dataGridView1.DataSource = Course.SelectCourse(this.textBox1.Text);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex < 0) return;
DataGridViewRow row = dataGridView1.Rows[e.RowIndex];
Course c = (Course)row.DataBoundItem;
try
{
if (e.ColumnIndex == 4)
{
if (MessageBox.Show("Confirm deletion?", "Please confirm the information", MessageBoxButtons.OKCancel) == DialogResult.Cancel) return;
if (Course.DeleteCourse(c.Cno) == 1)
MessageBox.Show("Delete succeeded!");
else MessageBox.Show("No corresponding course found!");
}
else if (e.ColumnIndex == 5)
{
FrmCourseUpdate frm = new FrmCourseUpdate();
frm.tbCno.Text = c.Cno;
frm.tbCname.Text = c.Cname;
frm.tbCcredit.Text = c.Ccredit.ToString();
frm.tbTno.Text = c.tno;
frm.ShowDialog(this);
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
}
Course information modification
//FrmCourseUpdate.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace db_yj
{
public partial class FrmCourseUpdate : Form
{
public FrmCourseUpdate()
{
InitializeComponent();
}
private void label1_Click(object sender, EventArgs e)
{
}
private void button1_Click(object sender, EventArgs e)
{
Course c = new Course();
c.Cno = tbCno.Text;
c.Cname = tbCname.Text;
c.Ccredit = Convert.ToInt32(tbCcredit.Text);
c.tno = tbTno.Text;
try
{
if (Course.UpdateCourse(c) == 1)
{
MessageBox.Show("Modified successfully!");
this.Close();
}
else
{
MessageBox.Show("No corresponding course found!");
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
}
Course information insertion
//FrmCourseInsert.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace db_yj
{
public partial class FrmCourseInsert : Form
{
public FrmCourseInsert()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
Course c = new Course();
c.Cno = tbCno.Text;
c.Cname = tbCname.Text;
c.Ccredit = Convert.ToInt32(tbCcredit.Text);
c.tno = tbTno.Text;
try
{
Course.InertCourse(c);
MessageBox.Show("Successfully inserted!");
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
}
Count the total score, highest score, lowest score and total credits of each student
Count the number of electives, the highest score, the lowest score and the average score of each course
Count the total number of students taught by each teacher
Count the number of teachers with different professional titles and the average salary of teachers with different professional titles
//FrmStatistics.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Oracle.ManagedDataAccess.Client;
namespace db_yj
{
public partial class FrmStatistics : Form
{
public FrmStatistics()
{
InitializeComponent();
}
private void button2_Click(object sender, EventArgs e)
{
string sql = "select * from studentinfo";
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
OracleDataReader odr = cmd.ExecuteReader();
if (odr.HasRows)
{
BindingSource bs = new BindingSource();
bs.DataSource = odr;
dataGridView1.DataSource = bs;
}
else dataGridView1.DataSource = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
private void button1_Click(object sender, EventArgs e)
{
string sql = "select * from teacherinfo1";
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
OracleDataReader odr = cmd.ExecuteReader();
if (odr.HasRows)
{
BindingSource bs = new BindingSource();
bs.DataSource = odr;
dataGridView1.DataSource = bs;
}
else dataGridView1.DataSource = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
private void button3_Click(object sender, EventArgs e)
{
string sql = "select * from Courseinfo";
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
OracleDataReader odr = cmd.ExecuteReader();
if (odr.HasRows)
{
BindingSource bs = new BindingSource();
bs.DataSource = odr;
dataGridView1.DataSource = bs;
}
else dataGridView1.DataSource = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
}
}
}
Welcome message displayed in the window:
//mainfrm.cs
private void mainfrm_Load(object sender, EventArgs e)
{
this.Text = "Hello," + FrmLogin.loginName + "，Welcome to the information management system!";
}
Attachment:
//mainfrm.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Oracle.ManagedDataAccess.Client;
namespace db_yj
{
public partial class mainfrm : Form
{
public mainfrm()
{
InitializeComponent();
}
private void Query students ToolStripMenuItem_Click(object sender, EventArgs e)
{
FrmStuQuery frm = new FrmStuQuery();
frm.MdiParent = this;
frm.Show();
}
private void Add student ToolStripMenuItem_Click(object sender, EventArgs e)
{
FrmStuInsert frm = new FrmStuInsert();
frm.MdiParent = this;
frm.Show();
}
private void course management ToolStripMenuItem_Click(object sender, EventArgs e)
{
}
private void Query course ToolStripMenuItem1_Click(object sender, EventArgs e)
{
FrmCourseQuery frm = new FrmCourseQuery();
frm.MdiParent = this;
frm.Show();
}
private void Add course ToolStripMenuItem_Click(object sender, EventArgs e)
{
FrmCourseInsert frm = new FrmCourseInsert();
frm.MdiParent = this;
frm.Show();
}
private void statistical information ToolStripMenuItem_Click(object sender, EventArgs e)
{
}
private void Basic statistical information ToolStripMenuItem_Click(object sender, EventArgs e)
{
FrmStatistics frm = new FrmStatistics();
frm.MdiParent = this;
frm.Show();
}
private void mainfrm_Load(object sender, EventArgs e)
{
this.Text = "Hello," + FrmLogin.loginName + "，Welcome to the information management system!";
}
/*
xxx.visible = false;
*/
private void Query teacher ToolStripMenuItem_Click(object sender, EventArgs e)
{
FrmTeacherQuery frm = new FrmTeacherQuery();
frm.MdiParent = this;
frm.Show();
}
}
}
//course.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.ManagedDataAccess.Client;
namespace db_yj
{
public class Course
{
public string Cno { get; set; }
public string Cname { get; set; }
public int Ccredit { get; set; }
public string tno { get; set; }
public static List<Course> SelectCourse(string cname)
{
string sql = "select cno,cname,ccredit,tno from Course where cname like:cname";
OracleParameter[] para = new OracleParameter[] { new OracleParameter(":cname", OracleDbType.Varchar2, 40) };
para[0].Value = cname + "%";
List<Course> list = new List<Course>();
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
cmd.Parameters.AddRange(para);
OracleDataReader odr = cmd.ExecuteReader();
while (odr.Read())
{
Course c = new Course();
c.Cno = odr.GetString(0);
c.Cname = odr.GetString(1);
c.Ccredit = odr.GetInt32(2);
c.tno = odr.GetString(3);
list.Add(c);
}
}
finally
{
con.Close();
}
return list;
}
public static int DeleteCourse(string cno)
{
int result = 0;
string sql = "delete from course where cno=:cno";
OracleParameter[] para = new OracleParameter[] { new OracleParameter(":cno", OracleDbType.Varchar2, 4) };
para[0].Value = cno;
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
cmd.Parameters.AddRange(para);
result = cmd.ExecuteNonQuery();
}
finally
{
con.Close();
}
return result;
}
public static int UpdateCourse(Course c)
{
int result = 0;
string sql = "update course set cno=:cno,Cname=:Cname,Ccredit=:Ccredit,tno=:tno where cno=:cno";
OracleParameter[] para = new OracleParameter[]
{
new OracleParameter(":Cno", OracleDbType.Varchar2, 4),
new OracleParameter(":Cname", OracleDbType.Varchar2, 40),
new OracleParameter(":Ccredit", OracleDbType.Int32),
new OracleParameter(":tno", OracleDbType.Varchar2, 7)
};
para[0].Value = c.Cno;
para[1].Value = c.Cname;
para[2].Value = c.Ccredit;
para[3].Value = c.tno;
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
cmd.Parameters.AddRange(para);
result = cmd.ExecuteNonQuery();
}
finally
{
con.Close();
}
return result;
}
public static int InertCourse(Course c)
{
int result = 0;
string sql = "insert into course(Cno,Cname,Ccredit,tno) values(:Cno,:Cname,:Ccredit,:tno)";
OracleParameter[] para = new OracleParameter[]
{
new OracleParameter(":Cno", OracleDbType.Varchar2, 4),
new OracleParameter(":Cname", OracleDbType.Varchar2, 40),
new OracleParameter(":Ccredit", OracleDbType.Int32, 38),
new OracleParameter(":tno", OracleDbType.Varchar2, 7)
};
para[0].Value = c.Cno;
para[1].Value = c.Cname;
para[2].Value = c.Ccredit;
para[3].Value = c.tno;
OracleConnection con = new OracleConnection(Program.strCon);
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
cmd.Parameters.AddRange(para);
result = cmd.ExecuteNonQuery();
}
finally
{
con.Close();
}
return result;
}
}
}
6. Operation and maintenance of database
6.1 database dump and recovery
The dump and recovery of database is one of the most important maintenance work after the formal operation of the system.
6.2 database security and integrity control
During the operation of the database, due to the change of the application environment, the requirements for security will also change, and the security level of users in the system will also change. The database administrator needs to constantly modify it to meet the requirements of users.
6.3 supervision, analysis and transformation of database performance
During the operation of the database, supervise the operation of the system and analyze the monitoring data.
6.4 re organization and reconstruction of database
After the database runs for a period of time, due to the continuous addition, deletion and modification of records, the physical storage of the database will deteriorate, reduce the access efficiency of data and reduce the performance of the database. At this time, the database administrator will reorganize or partially reorganize the database (only reorganize the frequently added and deleted tables). Relational database management system generally provides the utility program of data re organization. In the process of re organization, it rearranges the storage location, recycles garbage and reduces pointer chain according to the original design requirements to improve the system performance.
7. Experimental summary
There are still many unsolved problems in the experiment. In the process of statistics, if the class is not defined and the content of the view is directly output, the conversion will be invalid. The view is displayed normally in sql and cannot be output normally in dataGridView1. All of them are converted into classes and then imported into dataGridView1 before they can be output. Moreover, when reading from the database, You still need to judge whether it is null; Since the administrator class is not created separately, the administrator can only make special judgments when logging in; It is easy to make mistakes without considering that the input data is null; The student class is not modified and created, which is easy to make mistakes when executing sql statements; The function of student course selection is not realized.
Through this experiment, we understand the basic methods of conceptual structure design, logical structure design and physical structure design, and have a preliminary understanding of database design. With the help of the teacher's teaching video, a student information management database system is successfully written in C# language under the compilation environment of VS2019. The system will connect to the database when querying, adding, modifying and deleting information, which improves the efficiency of data storage and query. After completing the basic requirements of the experimental report, in order to make the interface of the management system friendly, the functions of control management and personal center are added. In the process of completing this comprehensive experiment, the experiment has gradually taken shape from helpless at the beginning. Thank you for your serious guidance and instruction,
I hope it can be further improved when there is plenty of time.
If you have any questions, please give me more advice~