Database Schema

[zishan0215]

Database Schema Design

Thursday we have our DBMS lab and we will have to show our database schema to the teachers. I have started with it and will upload a picture of the schema as soon as i finish with it. There will however be many changes to make so i request everyone to give their suggestions

[nickedes]

How will we update d attendence ? and can u post d pics here again zishan.

[zishan0215]

![Uploading db2.jpg . . .]() ![Uploading db3.jpg . . .]()

[zishan0215]

I'm working on that part.. updating the attendance..

[zishan0215]

In the teacher view, the rightmost button "feed" will be the one from where the teacher will update the attendance

[nickedes]

but if we are clear with d tables..den writing query wont be a problem...i m confused wid d 2nd pic u posted on fb :confused:

[zishan0215]

Can't you see the second pic here ? and the third ?

[zishan0215]

When the teacher clicks the view button, then that is the view the teacher will get.. that is what the second pic is about

[nickedes]

no zee ..only d 1st pic

[zishan0215]

[zishan0215]

[nickedes]

ohk..i got it now... d first page will have d login for d teacher (in pic1) n den d teacher can update info (pic2 ) :+1:

[zishan0215]

when the teacher logs in.. he goes to pic 1 when he clicks on view, he goes to pic 2 which is view only.. not updatable.

[zishan0215]

To update the attendance, he clicks on feed.. then he goes to pic 4(which i will post soon) and is more or less same as pic 2

[nickedes]

@wadhwasahil come here now :P

[wadhwasahil]

hi guys.)

[zishan0215]

hello

[nickedes]

Phew! worked welcum sahil

ok zee ..but let me try d query

[wadhwasahil]

I mean why zishan you r creating an xtra table

[nickedes]

which is d extra table?...we need 3 tables atleast

[wadhwasahil]

and btw sir said the updation is only done by fayaz and zishan sir

[wadhwasahil]

so admins are only zishan and fayyu

[wadhwasahil]

3 tables would work fine

[wadhwasahil]

1 st table = options table 2nd table = feed table and offcourse various tbles linked with them

[zishan0215]

no...updation are done by the teachers themselves... the admin decide the time frame

[wadhwasahil]

so what role does admin play ??

[nickedes]

updation can be done by subject teacher also. and admin also ..we need to keep both d options .

[zishan0215]

i dont see how can your database design work with the tables you mentioned

[wadhwasahil]

what's wrong with it??

[zishan0215]

@nkmittal4994 yes we can have both the options

[zishan0215]

it wont work.. too much redundancy... and what do you mean by various tables linked with them ?

[wadhwasahil]

lets see..you also posted the same design almost

[zishan0215]

This is the view of the feed button.. identical to the view of the view buttton with one difference of fetching the time period

[zishan0215]

@wadhwasahil If you have a design in mind then post the pics like i am doing. and show the relationship among them

[nickedes]

feed button is only for d admin? and for teacher only for dat subject ..but how will we manage dat ?

[zishan0215]

[zishan0215]

No ! The first pic is the teacher's view.. Feed as well as the view button are for the teacher Admin will come later.. Right now we are only discussing about the teachers

[nickedes]

ok..den its fine :relaxed:

[zishan0215]

Using the attendance table (in the above pic), we will be able to update the attendance when the teacher clicks the feed button. update table attendance set attendence=$attendence, period=$period where student_id = $id

[zishan0215]

and now the view section of the view button can be completed by the query select student.roll_no, student.name, attendance.attendence From attendance, student where student.semester = $semester and attendance.period = $period and attendance.subject_code = $sub_code;

[wadhwasahil]

seems fine till now I am working on design as well

[zishan0215]

okay

[nickedes]

acha zee read somehting about update anomalies... we have to take care about des things too.

[zishan0215]

okay.. will read about that

[nickedes]

and one more thing ..will we keep d record of all d updates? dat we do

[zishan0215]

yes.. everytime a teacher feeds the attendance, it will be stored in the database along with the time period for which it corresponds to

[nickedes]

ok fine

[zishan0215]

and this update anomaly is the reason why normalization is important.. That is why if you see the tables. i have only included the subject id and not subject name as well...same with student id .. and then created a separate table for student and subject so that they can be updated without any problem

[wadhwasahil]

hi all

[zishan0215]

hello

[nickedes]

yeah correct @zishanAhmad