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
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
|