| Home | Sign up! | Projects | Seminars | Research Notes | Today's Lecture | About |

Prof. Ashay Dharwadker

Institute of Mathematics
H-501 Palam Vihar
District Gurgaon
Haryana 122017
India

ashay@dharwadker.org

About My Students Database

My Students Database demonstrates the design and implementation of an online relational database management system (RDBMS) in Boyce-Codd normal form (BCNF). It provides a temporal data warehouse for storing information about my students' projects, seminars and research work. My Students Database also provides a search engine for performing elementary data mining tasks.

The E/R Model

The semantic structure of the database can be understood from its Entity/Relationship (E/R) model, as follows.

Entities:

  • Students
  • Projects
  • Seminars
  • Research Notes
Relationships:
  • SP: Students and their Projects
  • SS: Students and their Seminars
  • SN: Students and their Notes
There is also a Clock that keeps track of temporal updates and a Counter that counts the SQL queries. Here is the E/R diagram:

SQL Tables Creation

The actual tables are created using SQL (Structured Query Language) syntax:
  • Create table students(id smallint unsigned not null auto_increment, fname varchar(20), lname varchar(20), email varchar(40), profile text, password varchar(20), primary key (id))
  • Create table projects(pid smallint unsigned not null auto_increment, ptopic text, pdes text, primary key (pid))
  • Create table sp(id smallint not null, pid smallint not null, course text, primary key (id,pid), foreign key (id) references students, foreign key (pid) references projects)
  • Create table seminars(sid smallint unsigned not null auto_increment, stopic text, sdes text, primary key (sid))
  • Create table ss(id smallint not null, sid smallint not null, course text, primary key (id,sid), foreign key (id) references students, foreign key (sid) references seminars)
  • Create table notes(nid smallint unsigned not null auto_increment, ntopic text, ndes text, primary key (nid))
  • Create table sn(id smallint not null, nid smallint not null, course text, primary key (id,nid), foreign key (id) references students, foreign key (nid) references notes)
  • Create table clock(id smallint not null, time timestamp)
  • Create table counter(num bigint unsigned not null auto_increment, primary key (num))

Database Administrator's View

Instances of actual tables in the database at a given point of time are shown below:
 

students

ID Name Password
14 Jane Doe jane1
15 John Smith john1

projects & sp

ID Name Topic PID
14 Jane Doe First Project 13
15 John Smith First Project 14
15 John Smith Second Project 15

seminars & ss

ID Name Topic SID
14 Jane Doe First Seminar 9
15 John Smith First Seminar 10

notes & sn

ID Name Topic NID
14 Jane Doe First Note 4
14 Jane Doe Second Note 5
15 John Smith First Note 6

clock

ID Time
15 2003-10-19 18:08:48
14 2003-10-19 18:11:38

counter

Reading
212

Search Engine

The search engine for My Students Database is designed using SQL pattern matching. We can perform data mining tasks on the database. For example, to find all material related to the Database Systems course, try the following search:

References

  • C.J. Date, An introduction to Database Systems, 7th ed., Addison-Wesley, 2000.
  • MySQL Reference Manual for version 4.1.0-alpha, 2003.

System Status

My Students Database is online since 19th October 2003, 08:00:00 PM:
  • Members: 57
  • Number of queries performed on the database: 355262
  • Database last updated on Wednesday, 26th March 2014, 04:23:01 PM

Prof. Ashay Dharwadker