preface
This experiment used javaswing, which has not been written for a long time, in which the use of various components took some time to review; The biggest problem I encountered was how to connect java and oracle. This problem made me work all night. At the beginning, I used eclipse, and the code was no problem. I couldn't connect with life and death. The next day, I moved the code to IDEA and then worked hard. Finally, it was OK. The experiment took a lot of effort. If there are any mistakes, you are welcome to point them out; If this article is helpful to you, please like it đź‘Ť.
Experimental overview
1. System overview
The database system is an educational administration management system, which is used to manage the basic information of students and teachers, as well as the information of teaching buildings and courses.
You can add, delete, check and modify the relevant information in the database, provide a visual interface, and have the function of error prompt. When the administrator account password is incorrect, or the addition, deletion and modification of the database do not meet the database integrity constraints or the paradigm, an error prompt will pop up.
There are four entities in this system, namely students, teachers, class places and courses. There are four relationship patterns generated, namely Student, Teacher, Place and Course.
2. Relationship model
Student (student number, name, gender, age, class)
Teacher (teacher number, teacher name, gender, age, length of service)
Class location (location number, building, classroom number)
Class schedule (teacher number, student number, course name, place number, class time, section number)
3. Relationship mode attribute
The attributes of student relationship mode are:
Student number VARCHAR(10), name VARCHAR(8), gender VARCHAR(2), age SMALLINT, class VARCHAR(10).
The attributes of teacher relationship mode include:
Teacher NUMBER VARCHAR(10), teacher name VARCHAR(8), gender VARCHAR(2), age NUMBER, length of service NUMBER.
Class location relationship mode attributes include:
Location number VARCHAR(10), building VARCHAR(12), classroom number VARCHAR(4).
The attributes of schedule relationship mode are:
Teacher number VARCHAR(10), student number VARCHAR(10), course name VARCHAR(16), place number VARCHAR(10), class time (week) VARCHAR(6), class time VARCHAR(10).
4. Inter entity linkages
The relationship between student entity and teacher entity is many to many;
There is a many to many relationship between student entity and class location entity;
There is a many to many relationship between the entity of teacher and the entity of class place;
The relationship between student entity and curriculum entity is many to many;
The relationship between teacher entity and curriculum entity is many to many;
5. Integrity conditions:
1. Student relationship model
The primary code is PRIMARY KEY, and the name cannot be empty, NOT NULL. Only 'male' or 'female' CHECK IN(SEX IN ('male ',' female ') is allowed for gender.
2. Teacher relationship model
The primary code is the PRIMARY KEY of the teacher number. The teacher name cannot be empty, NOT NULL. Only 'male' or 'female' CHECK IN(SEX IN ('male ',' female ') is allowed for gender).
3. Class location relationship mode
The primary code is the location number PRIMARY KEY. The building and classroom number cannot be empty, NOT NULL.
4. Timetable relationship mode
The main code is the PRIMARY KEY of the teacher number and student number. The location number refers to the main code of Place. The location number foreign key REFERENCES Place. The class time is between Monday and Friday. CHECK the class time IN ("Monday", "Tuesday", "Wednesday", "Thursday", "Friday",)
6. Relevant description
A student can only belong to one class and cannot belong to multiple classes at the same time. The student's name can be repeated, and the student's unique ID is the student number.
A certain course of a student is taught by only one teacher, and a certain course of a student in a class corresponds to one teacher. Class hours are from Monday to Friday.
There are many classrooms in each building. Different classroom locations in different buildings have different numbers.
Each teacher has a unique teacher ID, which allows duplicate teacher names. Each teacher teaches multiple classes every week. Each teacher can teach multiple courses, but only one course for the same class.
Create corresponding triggers to handle corresponding data changes.
7. Standardization
1. Student relationship model:
Student number is the main code, student number – > name, student number – > gender, student number – > age, student number – > class. Each non primary attribute has no partial functional dependency and transitive dependency, and all determinants contain codes. Therefore, the student relationship model belongs to BCNF.
2. Teacher relationship model:
Teacher number is the main code, teacher number – > teacher name, teacher number – > gender. Each non primary attribute has no partial functional dependency and transitive dependency, and all determinants contain codes. Therefore, the teacher relationship model belongs to BCNF.
3. Class location relationship mode:
Location number is the main code, location number – > building, location number – > classroom number. Each non primary attribute does not have partial functional and transitive dependencies, and all determinants contain codes. Therefore, the relationship mode of class location belongs to BCNF.
4. Schedule relationship mode:
Teacher number + student number is the main code, teacher number + student number – > course name, teacher number + student number – > place number, teacher number + student number – > class time, teacher number + student number – > section. Each non main attribute does not have partial functional dependency and transfer dependency, and all determinants contain codes. Therefore, the schedule relationship mode belongs to BCNF.
II Experimental conception and corresponding diagram of database
1. Business flow chart
2. Data flow diagram
3. Use case diagram
4.E-R diagram
III Experimental process
(1). Build database and create initial data
1. Create user manager and authorize
create user manager identified by 123456;
grant connect,resource,dba to manager;
2. Log in to the database using the user manager
3. Create STUDENT table STUDENT
CREATE TABLE STUDENT(
Student number VARCHAR(10) PRIMARY KEY,
full name VARCHAR(10) NOT NULL,
Gender VARCHAR(3) CHECK (Gender IN('male','female')),
Age SMALLINT,
class VARCHAR(10)
);
4. Create TEACHER table
CREATE TABLE TEACHER(
Teacher number VARCHAR(10) PRIMARY KEY,
Teacher name VARCHAR(10) NOT NULL,
Gender VARCHAR(3) CHECK (Gender IN('male','female')),
Age NUMBER,
working years NUMBER，
);
5. Create classroom table PLACE
CREATE TABLE PLACE(
Location number VARCHAR(10) PRIMARY KEY,
Building VARCHAR(12) NOT NULL,
Classroom number VARCHAR(4) NOT NULL
)
6. Create curriculum COURSE
CREATE TABLE COURSE(
Teacher number VARCHAR(10) ,
Student number VARCHAR(10) ,
Course name VARCHAR(16),
Location number VARCHAR(10),
Class time VARCHAR(10) CHECK (Class time IN('Monday','Tuesday','Wednesday','Thursday','Friday')),
Section VARCHAR(10),
FOREIGN KEY(Teacher number) REFERENCES TEACHER((teacher number),
FOREIGN KEY(Student number) REFERENCES STUDENT((student number),
FOREIGN KEY(Location number) REFERENCES Place(Location number),
PRIMARY KEY (Teacher number,Student number)
);
7. Insert data into STUDENT table STUDENT
INSERT INTO STUDENT(Student number,full name,Gender,Age,class) VALUES('1906300087','Zhang San','male',19,'Software 192');
INSERT INTO STUDENT(Student number,full name,Gender,Age,class) VALUES('1906300001','Zhou Nan','male',20,'Network 194');
INSERT INTO STUDENT(Student number,full name,Gender,Age,class) VALUES('1906300002','Heluo','female',19,'Planning section 191');
INSERT INTO STUDENT(Student number,full name,Gender,Age,class) VALUES('1906300028','Yan Xu','female',18,'Software 191');
INSERT INTO STUDENT(Student number,full name,Gender,Age,class) VALUES('1906300034','Xia Guang','male',20,'Planning section 192');
INSERT INTO STUDENT(Student number,full name,Gender,Age,class) VALUES('1906300015','Yao Chen','male',21,'Software 191');
INSERT INTO STUDENT(Student number,full name,Gender,Age,class) VALUES('1906300078','Zhai Xiao','male',22,'Software 191');
INSERT INTO STUDENT(Student number,full name,Gender,Age,class) VALUES('1906300056','Zhang Yan','female',20,'Software 192');
INSERT INTO STUDENT(Student number,full name,Gender,Age,class) VALUES('1906300045','Liu Ye','female',21,'Software 193');
INSERT INTO STUDENT(Student number,full name,Gender,Age,class) VALUES('1906300023','Ren Hao','male',21,'Software 194');
INSERT INTO STUDENT(Student number,full name,Gender,Age,class) VALUES('1906300020','Zhao Lei','male',20,'Software 194');
INSERT INTO STUDENT(Student number,full name,Gender,Age,class) VALUES('1906300030','Zhao rang','male',19,'Planning section 191');
8. Create the sequence teacher for the teacher table_ SEQUENCE
CREATE SEQUENCE TEACHER_SEQUENCE MINVALUE 1 INCREMENT BY 1 START WITH 10001;
9. Insert data into the TEACHER table
INSERT INTO TEACHER(Teacher number, teacher name, gender) VALUES(TEACHER_SEQUENCE.NEXTVAL,'Willow','female',30,5); INSERT INTO TEACHER(Teacher number, teacher name, gender) VALUES(TEACHER_SEQUENCE.NEXTVAL,'Yang Hua','male',31,6); INSERT INTO TEACHER(Teacher number, teacher name, gender) VALUES(TEACHER_SEQUENCE.NEXTVAL,'Plum tree','male',34,3); INSERT INTO TEACHER(Teacher number, teacher name, gender) VALUES(TEACHER_SEQUENCE.NEXTVAL,'Su San','male',45,6); INSERT INTO TEACHER(Teacher number, teacher name, gender) VALUES(TEACHER_SEQUENCE.NEXTVAL,'Tang Wei','male',28,4); INSERT INTO TEACHER(Teacher number, teacher name, gender) VALUES(TEACHER_SEQUENCE.NEXTVAL,'Liang Kai','male',35,7); INSERT INTO TEACHER(Teacher number, teacher name, gender) VALUES(TEACHER_SEQUENCE.NEXTVAL,'Yan Guofeng','male',30,8); INSERT INTO TEACHER(Teacher number, teacher name, gender) VALUES(TEACHER_SEQUENCE.NEXTVAL,'Gu Peng','male',47,2); INSERT INTO TEACHER(Teacher number, teacher name, gender) VALUES(TEACHER_SEQUENCE.NEXTVAL,'Wang Xianmin','male',50,4); INSERT INTO TEACHER(Teacher number, teacher name, gender) VALUES(TEACHER_SEQUENCE.NEXTVAL,'Li Junrong','female',67,7); INSERT INTO TEACHER(Teacher number, teacher name, gender) VALUES(TEACHER_SEQUENCE.NEXTVAL,'Zhang Yan','female',35,6);
10. Insert data into the classroom table PLACE
INSERT INTO PLACE(Location number,Building,Classroom number) VALUES('LN204','Linan','204');
INSERT INTO PLACE(Location number,Building,Classroom number) VALUES('LN211','Linan','211');
INSERT INTO PLACE(Location number,Building,Classroom number) VALUES('WX305','Wen Xin','305');
INSERT INTO PLACE(Location number,Building,Classroom number) VALUES('LN308','Linan','308');
INSERT INTO PLACE(Location number,Building,Classroom number) VALUES('WX619','Wen Xin','619');
INSERT INTO PLACE(Location number,Building,Classroom number) VALUES('WX503','Wen Xin','503');
INSERT INTO PLACE(Location number,Building,Classroom number) VALUES('LB304','Libei','304');
INSERT INTO PLACE(Location number,Building,Classroom number) VALUES('WJ603','Wen Jun','603');
INSERT INTO PLACE(Location number,Building,Classroom number) VALUES('LN408','Linan','408');
INSERT INTO PLACE(Location number,Building,Classroom number) VALUES('LB301','Libei','301');
INSERT INTO PLACE(Location number,Building,Classroom number) VALUES('WQ305','Wen Qing','305');
INSERT INTO PLACE(Location number,Building,Classroom number) VALUES('WY308','Wen Yi','308');
INSERT INTO PLACE(Location number,Building,Classroom number) VALUES('DZ416','Electronic building','416');
INSERT INTO PLACE(Location number,Building,Classroom number) VALUES('DZ418','Electronic building','418');
INSERT INTO PLACE(Location number,Building,Classroom number) VALUES('JSJ211','Computer building','211');
INSERT INTO PLACE(Location number,Building,Classroom number) VALUES('LX305','Science Experiment Building','305');
11. Insert data into COURSE
INSERT INTO COURSE(Teacher number,Student number,Course name,Location number,Class time,Section) VALUES('10005','1906300047','discrete mathematics ','LN204','Monday','1-2');
INSERT INTO COURSE(Teacher number,Student number,Course name,Location number,Class time,Section) VALUES('10008','1906300001','Computer composition','LN211','Monday','3-4');
INSERT INTO COURSE(Teacher number,Student number,Course name,Location number,Class time,Section) VALUES('10011','1906300002','operating system','LN408','Thursday','5-6');
INSERT INTO COURSE(Teacher number,Student number,Course name,Location number,Class time,Section) VALUES('10010','1906300028','linear algebra','DZ416','Tuesday','1-2');
INSERT INTO COURSE(Teacher number,Student number,Course name,Location number,Class time,Section) VALUES('10004','1906300034','data structure','LB304','Thursday','9-11');
INSERT INTO COURSE(Teacher number,Student number,Course name,Location number,Class time,Section) VALUES('10002','1906300015','database','LN308','Wednesday','9-11');
INSERT INTO COURSE(Teacher number,Student number,Course name,Location number,Class time,Section) VALUES('10003','1906300056','operating system','JSJ211','Tuesday','3-4');
INSERT INTO COURSE(Teacher number,Student number,Course name,Location number,Class time,Section) VALUES('10003','1906300045','operating system','WJ603','Monday','5-6');
INSERT INTO COURSE(Teacher number,Student number,Course name,Location number,Class time,Section) VALUES('10009','1906300023','data structure','WJ603','Wednesday','5-6');
INSERT INTO COURSE(Teacher number,Student number,Course name,Location number,Class time,Section) VALUES('10011','1906300034','operating system','WQ305','Wednesday','7-8');
INSERT INTO COURSE(Teacher number,Student number,Course name,Location number,Class time,Section) VALUES('10005','1906300015','discrete mathematics ','LN204','Monday','1-2');
INSERT INTO COURSE(Teacher number,Student number,Course name,Location number,Class time,Section) VALUES('10005','1906300078','discrete mathematics ','LN204','Monday','1-2');
INSERT INTO COURSE(Teacher number,Student number,Course name,Location number,Class time,Section) VALUES('10011','1906300045','operating system','LN408','Thursday','5-6');
INSERT INTO COURSE(Teacher number,Student number,Course name,Location number,Class time,Section) VALUES('10004','1906300023','data structure','LB304','Thursday','9-11');
INSERT INTO COURSE(Teacher number,Student number,Course name,Location number,Class time,Section) VALUES('10002','1906300020','database','LN308','Wednesday','9-11');
INSERT INTO COURSE(Teacher number,Student number,Course name,Location number,Class time,Section) VALUES('10009','1906300030','data structure','WJ603','Wednesday','5-6');
INSERT INTO COURSE(Teacher number,Student number,Course name,Location number,Class time,Section) VALUES('10011','1906300078','operating system','WQ305','Wednesday','7-8');
INSERT INTO COURSE(Teacher number,Student number,Course name,Location number,Class time,Section) VALUES('10003','1906300078','Assembly principle','LB304','Tuesday','1-2');
INSERT INTO COURSE(Teacher number,Student number,Course name,Location number,Class time,Section) VALUES('10006','1906300023','Assembly principle','LB301','Wednesday','1-2');
INSERT INTO COURSE(Teacher number,Student number,Course name,Location number,Class time,Section) VALUES('10003','1906300034','Computer composition','LB304','Monday','7-8');
INSERT INTO COURSE(Teacher number,Student number,Course name,Location number,Class time,Section) VALUES('10007','1906300078','Assembly principle','WJ603','Tuesday','5-6');
INSERT INTO COURSE(Teacher number,Student number,Course name,Location number,Class time,Section) VALUES('10004','1906300045','numerical analysis','WX619','Friday','1-2');
INSERT INTO COURSE(Teacher number,Student number,Course name,Location number,Class time,Section) VALUES('10007','1906300047','numerical analysis','WX619','Friday','3-4');
12. Create trigger TEACHER_TRIGGER
CREATE TRIGGER TEACHER_TRIGGER BEFORE DELETE ON TEACHER FOR EACH ROW
BEGIN
DELETE FROM COURSE WHERE Teacher number=:OLD.Teacher number;
END;
13. Create trigger STUDENT_TRIGGER
CREATE TRIGGER STUDENT_TRIGGER BEFORE DELETE ON STUDENT FOR EACH ROW
BEGIN
DELETE FROM COURSE WHERE Student number=:OLD.Student number;
END;
14. Create trigger PLACE_TRIGGER
CREATE TRIGGER PLACE_TRIGGER BEFORE DELETE ON PLACE FOR EACH ROW
BEGIN
DELETE FROM COURSE WHERE Location number=:OLD.Location number;
DELETE FROM PM WHERE Location number=:OLD.Location number;
END;
(2) Preparation of visual interface and connection with database
The visual interface is written with object-oriented language JAVA, database programming with JDBC, and visual programming with awt and swing framework;
There are many visual interface codes. The following process codes only give the key codes of database operation and the display diagram of visual interface
1. Connect to the database (key)
1. Import jar package from idea
Find the jar package under your oracle installation directory
Import complete
2. Establish database connection in the program
public static final String URL = "jdbc:oracle:thin:@localhost:1521:orcl";
public static final String USER = "manager";
public static final String PASSWORD = "123456";
//1. Load driver
Class.forName("oracle.jdbc.driver.OracleDriver");
//2. Get database connection
Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
analysis:
â‘ url = "jdbc:oracle:thin:@localhost:1521:orcl" is the created database. The database ID used by this machine is "orcl", localhost is the local host, and 1521 is the port number.
② "oracle.jdbc.driver.OracleDriver" is the jdbc driver
③ uname = "manager" is the user name created and authorized in SQLDEVELOPER
â‘Ł pwd = "123456" is the password corresponding to the user manager, and the database password is "123456"
⑤　conn = DriverManager.getConnection() connects java to the database
2. Student information management database operation
â‘ Check
String sql = new String("select * from student");
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql);
while(rs.next()) //Read data item by item
{ //Student five parameters
Vector<String> r=new Vector<String>();
r.add(rs.getString("Student number"));
r.add(rs.getString("class"));
r.add(rs.getString("Gender"));
r.add(rs.getString("Age"));
r.add(rs.getString("full name"));
data.add(r);
}
② Increase
String sql = "INSERT INTO STUDENT(Student number,class,Gender,Age,full name) "+ "VALUES(?,?,?,?,?)";
PreparedStatement st = conn.prepareStatement(sql);
st.setString(1, jt1.getText());
st.setString(2, jt2.getText());
st.setString(3, jt3.getText());
st.setInt(4, Integer.parseInt(jt4.getText()));
st.setString(5, jt5.getText());
st.executeUpdate();
③ Modification
String sql = "update STUDENT set Student number=?,class=?,Gender=?,Age=?,full name=? where Student number = ?";
PreparedStatement st = conn.prepareStatement(sql);
st.setString(1, jt1.getText());
st.setString(2, jt2.getText());
st.setString(3, jt3.getText());
st.setInt(4, Integer.parseInt(jt4.getText()));
st.setString(5, jt5.getText());
st.setString(6, jt1.getText());
st.executeUpdate();
â‘Ł Delete
String sql = "delete from STUDENT where Student number = ? ";
PreparedStatement st = conn.prepareStatement(sql);
st.setString(1, jt1.getText());
st.executeUpdate();
3. Teacher information management database operation
Similarly, it can be modified according to the mode of student information management. I'm too lazy to play
3. Classroom information management database operation
Similarly, it can be modified according to the mode of student information management. I'm too lazy to play
3. Course information management database operation
Similarly, it can be modified according to the mode of student information management. I'm too lazy to play
Effect display (taking student information as an example)
summary
This comprehensive experiment is difficult, but through this experiment, I also learned a lot of new knowledge and consolidated and reviewed some knowledge I had left behind before.
Well, as for sauce, the key codes in the article have been given. There are too many specific codes. Friends in need can leave an email in the comment area (three consecutive transactions).