rZSqqRQY

1//TO TEST TOMORROW IN THE MEETING
2package systemdesign.dbinteractions;
3import java.sql.*;
4
5public class CreateDB {
6	public static void createUsers() throws Exception {
7		Statement stmt = null;
8		DBconnect dbcon = new DBconnect();
9		Connection con = dbcon.getConnection();
10		//not sure if this work or not yet
11		try {
12			stmt = con.createStatement();
13			String sql_users = "CREATE TABLE IF NOT EXISTS users " + "(id INTEGER not NULL AUTO_INCREMENT, "
14					+ " username VARCHAR(255), " + " password VARCHAR(255), " + " privilege VARCHAR(255), " + " PRIMARY KEY (id))";
15			String sql_departments = "CREATE TABLE IF NOT EXISTS departments " + "(department_name NAVCHAR(30) , "
16					+ "abbreviation NAVCHAR(3), " + " PRIMARY KEY (department_name)" ;
17			String sql_degrees = "CREATE TABLE IF NOT EXISTS deegrees " + "(deegree_name NAVCHAR(30), " + "abbreviation NAVCHAR(7), " + "department_name NAVCHAR(30) , "
18					+ "FOREIGN_KEY (department_name) REFERENCES departments(department_name) " + "PRIMARY KEY (deegree_name))  ";
19            String sql_module_grades = "CREATE TABLE IF NOT EXISTS modulegrades " + "(module_name NAVCHAR(50), "+ "grades INTEGER, "
20					+ "PRIMARY KEY (module_name)) ";
21			String sql_modules = "CREATE TABLE IF NOT EXISTS modules " + "(module_name NAVCHAR(50), " + "lecturer NAVCHAR(30),"+ "abbreviation NAVCHAR(7), " + "number_of_credits INTEGER, "
22					+ "period_of_teaching NAVCHAR(30)," + " grades INTEGER " + "FOREIGN_KEY (grades) REFERENCES module_grades(grade)  "  + " PRIMARY KEY (module_name) )" ; //grade is missing, i think we have to reconsider this approach
23			String sql_periods_of_study = "CREATE TABLE IF NOT EXISTS periods_of_study " + "(period_of_study NAVCHAR(30) , " + "start_date DATE, " + "end_date DATE,"
24					 + "registration_number INTEGER, "+ " PRIMARY KEY (period_of_study)" + "FOREIGN KEY (grades) REFERENCES module_grades(grades) )" ;
25			String sql_students = "CREATE TABLE IF NOT EXISTS students " + "(registration_number INTEGER , " + "title NAVCHAR(10), " + "family_name NAVCHAR(30),"
26			        + "forename NAVCHAR(30)," + "deegree_name NAVCHAR(30)," + "email_address NAVCHAR(30),"+ "personal_tutor NAVCHAR(30)," + "period_of_study NAVCHAR(30)" 
27					+ " PRIMARY KEY (registration_number ) " +  " FOREIGN KEY (deegree_name) REFERENCES deegrees(deegree_name) " 
28			        + " FOREIGN KEY (period_of_study) REFERENCES periods_of_study(period_of_study)  )" ;
29            String sql_degree_modules = "CREATE TABLE IF NOT EXISTS deegree_modules " + " (deegree_name NAVCHAR(50) , " + "core_modules NAVCHAR(50)," + "optional_modules NAVCHAR(50)" + " "
30            //SOMEHOW ADD 3 FOREIGN KEYS WITHOUT A PRIMARY KEY, LOOK AT THIS            
31			//in order to enchance security against sql_injection, we have to change the name of the tables (before we start calling the functions) to some "codenames"
32			//for example, everything could be a pizza.  1.users=margarherita  2.departments=marinara 3.deegrees=romana
33			//4.modules=siciliana 5.periods_of_study=capricciosa 6.students=pepperoni
34			//This is experimental code, i suggest you do not run it as of yet
35			stmt.executeUpdate(sql_users);
36			//stmt.executeUpdate(sql_departments);
37			//stmt.executeUpdate(sql_degrees);
38            //stmt.executeUpdate(sql_module_grades);
39            //stmt.executeUpdate(sql_modules);
40            //stmt.executeUpdate(sql_periods_of_study);
41			//stmt.executeUpdate(sql_students);
42            //stmt.executeUpdate(sql_degree_modules);
43            //TOTAL OF 8 TABLES
44            
45		} catch (SQLException ex) {
46			ex.printStackTrace();
47		} finally {
48			if (stmt != null)
49				stmt.close();
50			dbcon.close();
51		}
52	}
53	public static void main(String[] args) throws Exception {		
54		createUsers();
55	}
56}