DBMS - Lab project - Database Development - Solved

Objective:

Enable the student to design and develop a database. In this project, the student are required to show their abilities of:

§  Analyzing the System Requirement

§  Represent the requirement into logical design using Entity Relationship (ER) or/and Enhanced Entity Relationship (EER) model

§  Mapping the designed model into relational schemas

§  Transform the relational schemas into normalized tables

§  Writing SQL statements to creates the tables including all applied integrity constraints

§  Writing SQL statements to populate the initial records of each table

§  Front End Development of Forms/ Reports

Case Brief

HAMAREY BACHCHEY

1. Introduction

Hamarey Bachchey is a NGO with the goal to reform education for children. They offer weekly classes for children below the age of 15. They wish to develop a database system.

The NGO is organized in the following ways.

After signing up, each student has to register for a course, and can only be registered to one course at a time. Once the course is completed, the student can register for a new course. After registration, the student is split into different classes based on their age (eg 3-4 years to class 1, 4-6 years to class 2, etc). Each class will be taught the same subjects, in a manner best suited to their age group. Each class can have multiple sections, depending on the need. Classes can also be further separated into groups based on students’ gender. Classes can also be given a custom title.

Each students’ personal information will be saved, including photos which may need to be updated every year. The most crucial information will be that of the parents/guardians. The Email, phone number, address and CNIC of both parents need to be recorded for each student. In the event of any incident where the parents will be unavailable, a guardian also needs to be available, in which case their details and relation to the students also need to be recorded. A history needs to be maintained for whenever base information of students/parents/guardians is changed.

For the earlier classes with very young students, the mother needs to be present with the child. If for whatever reason the mother cannot come, a female guardian must be present instead. In such cases, the NGO needs to be informed ahead of time of the individual accompanying the child, and other such information, including whether the individual is pregnant and/or needs assistance in any way.

The fee each student has to pay is different, depending on their class. If a parent admits more than 3 children, they may be offered a discount. Moreover, if the parents can’t pay for legitimate reasons, they will be offered a discount. There will be no fee for a child of a Hamarey Bachchey staff member. Payment of fee is made before admission to a third-party which provides the parents a challan or voucher number, which the parents then enter during admission process.

2.         The following manual records are kept:

The following are samples of forms the NGO currently maintain. These forms need to be developed using web. Data should be enter through these forms. Forms/attributes can be added if required.

The student admission form is filled by every parent/guardian for every child’s admission.

Form: Student Admission

HAMAREY BACHCHEY

STUDENT ADMISSION FORM

Photo
Students Information:

Name  : Date of Birth   :

                                Gender            :

Parents Information:

  Mother Name     :
Father Name
:
  Mother Contact
: 
Father Contact
:
  Mother CNIC
: 
Father CNIC
:
  Mother Email
: 
Father Email
:
  Mother Address
: 
Father Address
:
Guardian Information:

  Guardian Name   :   Guardian Contact:   Guardian CNIC     :

  Guardian Gender :

  Relation        :
 
 
For Staff Only

  Fee Amount :   Discount :

  Final Amount    :

    Fee Fully Paid :           YesNo

  

  Challan #       :
In the event a guardian must accompany a child for a class, they have to fill the following form ahead of time.

Form: Student Accompanying Form

 

Some forms are used by the admins for internal management, such as assigning/reallocation students to different classes.

Form: Class Assignment

 

Note: Other required forms/attributes can be created.

The following is a sample of how the NGO wants their student management GUI to look like: Students per class (A list of students grouped by classes)

HAMAREY BACHCHEY

STUDENTS PER CLASS FORM

Name   ▼
 
ADD +
SEARCH  

Class 1A [MQM] (12 total):

ID: 0001
Name: Hammad
Age(yrs): 3.2
Gender: M          
ID: 0002
Name: Haniya
Age(yrs): 3.6 .

.

.
Gender: F          
ID: 0011
Name: Huda
Age(yrs): 3.5
Gender: F          
ID: 0012
Name: Zayneb
Age(yrs): 3.4
Gender: F          
Class 1B [MQ
J] (13 total):
 
 
ID: 0101
Name: Ahmed
Age(yrs): 3.1
Gender: M          
ID: 0101
Name: Fatima
Age(yrs): 3.3 .

.

.
Gender: F          
ID: 0112
Name: Saqib
Age(yrs): 3.2
Gender: M          
ID: 0113
Name: Huda
Age(yrs): 3.2
Gender: F          
Class 2A [RT
-girls] (15 total)
:
 
ID: 0201
Name: Husna
Age(yrs): 4.1
Gender: F          
ID: 0202
Name: Kanwal
Age(yrs): 4.5 .

.

.
Gender: F          
ID: 0214
Name: Asma
Age(yrs): 4.4
Gender: F          
ID: 0215
Name: Nur
Age(yrs): 4.7
Gender: F          
Class 2B [RT
-boys] (9 total):
 
 
ID: 0301
Name: Hassan
Age(yrs): 4.2
Gender: M          
ID: 0302
Name: Ismael
Age(yrs): 4.8 .

.

.
Gender: M          
ID: 0308
Name: Usman
Age(yrs): 4.0
Gender: M          
ID: 0309    Name: Ibrahim     Age(yrs): 4.9     Gender: M          
.

.
. 

The following reports / queries are needed:

1.      A list of all students

2.      A list of all mothers and their spouses

3.      A list of all guardians, grouped by relation to students (show students as well)

4.      A list of parents and their children

5.      A list of all students with siblings taking classes, grouped by class

6.      A list of all students who have been assigned to a new class in the given time span (use check for including or ignoring new admissions)

7.      A list of all new students in given time span grouped by class

8.      A list of all new parents in given time span (include children info)

9.      A list of all parents who are early introducers (enroll their children into courses as soon as the children are old enough)

10.  Class change history of given student

11.  A report of a list of students grouped by classes along with add, search, delete, and edit student features. Search can be performed using student name or id.

12.  A report of a list of classes with number of students per class and student count per gender. Search can be performed using class name.

13.  A report of a list of all students who have been dormant for given number of months/years. Search can be performed using months or years.

14.  A report of all info on a given student (parents, guardians, siblings, class history). Search can be performed using student name or id.

15.  A report of all info on a given parent (all children, classes of each child, designated guardian). Search can be performed using parent name or id.

Note: [1-10] are stored procedures (only SQL queries required), while [11-15] are reports. The reports should be displayed on front end using web. All GUI reports with lists, should contain search functionality. Also include SQL queries in document which are used for generating reports. 

Document inclusive of following items in appropriate format

§  ER and/or EER diagram

§  Relational schemas/ Mapping

§  Description of all tables § Datasets

§  SQL statements used to create tables.

§  SQL statements used to populate tables (insert statements).

§  SQL statements as the answer to the needed 15 queries

§  Other

Special Instructions:

•      Use at least 1 stored procedure (productively)

•      Use at least 1 trigger )productively)

 


Each member needs to develop at least one form and one report OR two forms/reports. S/he should be responsible for all of its development activities (Table design, DDL, Queries, PHP, and HTML). During VIVA each member will be evaluated on basis of his/her own work and also general project working (ERD, Table design, Dataset etc.).

The group will be allowed to use different technologies for their projects development, provided they meet the following conditions:

•      They commit to a set of technologies (after discussing with their instructor) and only work within that set

•      No other technologies (other than those committed at the start of the project) will be allowed

•      Unless allowed by the instructor, the group may not change their technologies during their project

•      You must use a RDBMS