Data base designing & programming with sql

1. Data base designing & programming with sql ChandanSinha , PGT Comp. Sc. , K.V Maithon Dam

2. What is DBMS A DBMS refers to a software that is responsible for storing maintaining and utilizing databases. Tables & appl. Prog.

3. DDLC – Database Development Life Cycle • It is the set of activities that are carried out to develop and implement database • Information collection • Conceptual Data Model • Logical Data Model • Physical Model & Refinement • Database Installation

4. Information collection is all about gathering information about clients requirements. Example EXAMPLE Here is a set of information requirements: KENDRIYA VIDYALAYA SANGATHAN is autonomous body under the HRD Ministry . Manages large number of schools divided into different regions. Sangathan need to keep information about each of their schools students details , employees details and their activity details. Sangathan is divided into regions, Each region have certain number of schools. School keep information about their students enrollment , employees posted on their school, activities like examination, Department , Fees details and other . Step 1. Information Collection

5. Problem in old existing system Old System • They maintain the details of students starting from their admission till their end of school manually. • The details of students are required by class teacher, exam department and other teachers time to time . • They all are dependent upon the main custodian .some time sharing of detail create anomaly in the • student record. • At the end of the month every class teacher submit the current status of the enrollment position of the • student. Some time due to human error it create problem while creating consolidated data. • School also have to record the fee collected in every quarter it is collected by teachers and then verified by • the office staff. • Staff details are also maintained by the school like their salary, transfer detail (PIS) and most important • their activity in present school like who is the department in-charge, class teacher of any class and who is • teaching English in class VII A. these all data are scattered and if any body want these data he/she have to • do to office, time table in-charge or principal.

6. Step -2 : Conceptual Modeling Pictures can also be presented more dramatically in widescreen.

7. What is Conceptual Modeling • Models functional and informational needs of a business • Based on current needs and may reflect future needs • Deals only with business needs, does not deal with implementation problems • Called an “Entity Relationship Model” • Shown with an “Entity Relationship Diagram” Important: An entity relationship model should accurately model the organization’s information needs and support the functions of the business.

8. Entities & Attributes Entities Attributes • An entity is: • “Something” of significance to the business about which data must be known • A name for a set of similar things that you can list • Usually a noun • Examples: objects, events, people • Entities have instances. An instance is a single occurrence of an entity. • Like an entity, an attribute represents something of significance to the business. • An attribute is a specific piece of information that: • Describes an entity • Quantifies an entity • Qualifies an entity • Classifies an entity • Specifies an entity • An attribute has a single value.

9. Identification of Entities • Student • Staff • School • Exam • Subject • Analysis • Class • Admission • Fees • Department • Salary • Cast

10. Entity Relationship Model • A list of all entities and attributes as well as all relationships between the entities that are of importance • Provides background information such as entity descriptions, data types and constraints

11. Goals of ER Modeling • There are four goals of ER modeling: • Capture all required information • Ensure that information appears only once • Model no information that is derivable from other information already modeled • Locate information in a predictable, logical place

12. KVs Working Scenario • Learn the complete working scenario of the KendriyaVidyalaya . Then we will examine the completed ERD. • We started out as a student takes admission in Vidyalaya in any class details has been maintained in student entity his/her class, subjects, caste and other details can be identified from subject , class , cast, fees entities. • Staff can be of two type teaching and non teaching , they can be the in-charges of departments and may be the class teacher of any class , their salary details is also can be one of the entity. • Exam department keep the record of exam held time to time and also keep the record of results

13. ER Drawing Conventions • Entities are represented by softboxes. • Entity names go in the softboxes. • Entity names are always singular and written with all capital letters STUDENT

14. Drawing Conventions • Attributes are listed under the entity names. • Mandatory attributes are marked with an asterisk: “*” • Optional attributes are marked with a circle: “o” • Unique identifiers are marked with a hash sign: “#”

15. Entities with Attributes Student # enroll *F_name *L_name *Dob *Gender *Sub_id (FK) *Class (fk) *Cat *Cast_id(fk *Ph *Min *Musl *Satff *Single *School_id (fk) Fees #fee_id *Class_id (FK) *Month *Year *Vvn OSf OHc OLf *Comp OOther *date Staff #emp_code *F_name *L_name *Desig *Type *Basic *GP OPran OPpan OGpf *Sen_no Subject #sub_id *Sub *Class (fk) Cast #cast_id *type Exam # e_id *Type *Month *year *subid(FK) *enroll(FK) Class #class_id *Class_name *Cl_teacher (FK) *Cl_mon1 (fk) *Cl_mon2(FK) *Floor *Room _no *building Dept #dept_id *Name *Ic (fk) School #kv_code *Region (fk) *name

16. ERDish and Drawing Relationships • ERDish is the language we use to state relationships between entities in an ERD.

17. Entity relationship • Student • # enroll • *F_name • *L_name • *Dob • *Gender • *Sub_id (FK) • *Class (fk) • *Cat • *Cast_id(fk • *Ph • *Min • *Musl • *Satff • *Single • *School_id (fk) Each Student must be enrolled in only one school Each School may be Study centre for one or more student School #kv_code *Region (fk) *name Enrolled in Study center for

18. ER continued ….. • Student • # enroll • *F_name • *L_name • *Dob • *Gender • *Sub_id (FK) • *Class (fk) • *Cat • *Cast_id(fk • *Ph • *Min • *Musl • *Satff • *Single • *School_id (fk) • Subject • #sub_id • *Sub • *Class (fk) Each Student must be Optedmany subjectid Each Subject may be given to one or more student Opt Given to

19. Student • # enroll • *F_name • *L_name • *Dob • *Gender • *Sub_id (FK) • *Class (fk) • *Cat • *Cast_id(fk • *Ph • *Min • *Musl • *Satff • *Single • *School_id (fk) • Class • #class_id • *Class_name • *Cl_teacher (FK) • *Cl_mon1 (fk) • *Cl_mon2(FK) • *Floor • *Room _no • *building Each Student must be studied only one class Each class may have one or more student Studies in have

20. Student • # enroll • *F_name • *L_name • *Dob • *Gender • *Sub_id (FK) • *Class (fk) • *Cat • *Cast_id(fk • *Ph • *Min • *Musl • *Satff • *Single • *School_id (fk) • Exam • # e_id • *Type • *Month • *year • *subid(FK) • *enroll(FK) Each Student may give one exam Each exam may taken by one or more student give Taken by

21. Student • # enroll • *F_name • *L_name • *Dob • *Gender • *Sub_id (FK) • *Class (fk) • *Cat • *Cast_id(fk • *Ph • *Min • *Musl • *Satff • *Single • *School_id (fk) • Cast • # cast_id • *Type Each Student must have only one cast Each cast must belongs one or more student have Belongs to

22. Student • # enroll • *F_name • *L_name • *Dob • *Gender • *Sub_id (FK) • *Class (fk) • *Cat • *Cast_id(fk • *Ph • *Min • *Musl • *Satff • *Single • *School_id (fk) • Fees • #fee_id • *Class_id (FK) • *Month • *Year • *Vvn • OSf • OHc • OLf • *Comp • OOther • *date Student must deposit only one fees fees may be paid by one and only one student deposit Paid by

23. Super type and sub type • Evaluating Entities • Often some instances of an entity have attributes and/or relationships which other instances do not have. • Imagine a Staff entity which there are two types of staffs in KVs teaching and non teaching. • All teaching staff must have some subjects for teaching in different classes so they need some extra attribute which is not necessary for not teaching staff. • Some attributes are common for both teaching and non teaching staff.

24. Super type and sub type • Subdivide an Entity • Sometimes it makes sense to subdivide an entity into subtypes. This may be the case when a group of instances has special properties, such as attributes or relationships that exist only for that group. In this case, the entity is called a "supertype" and each group is called a subtype. • A subtype: • •inherits all attributes of the supertype • •inherits all relationships of the supertype • •usually has its own attributes or relationships • •is drawn within the supertype • •never exists alone • •may have subtypes of its own • •is also known as a "subentity“ • Invertebrates are animals with no backbone (such as earth worms). Vertebrates have backbone and can be subdivided into birds, mammals, and reptiles etc. Staff Teaching Non teaching

25. Staff • #emp_code • *F_name • *L_name • *Desig • *Type • *Basic • *GP • OPran • OPpan • OGpf • *Sen_no works Class #class_id *Class_name *Cl_teacher (FK) *Cl_mon1 (fk) *Cl_mon2(FK) *Floor *Room _no *building School #kv_code *Region (fk) *name has Taught by Teach in Dept #dept_id *Name *Ic (fk) Teacher Subject department Associated with has

26. each Subject may be devaluated by many exam Many exams may be taken for one and more subject Subject #sub_id *Sub *Class (fk) Exam # e_id *Type *Month *year *subid(FK) *enroll(FK) conduct Taken for

27. Student subject cast fees Class department school staff exam

28. Matrix Diagram

29. Step 3: Logical Data Modeling • Once the conceptual data model is ready , we can apply various refinement techniques, so that the logical data model is ready. The Logical Data Model represent the relational schema. A schema is collection of database objects of a User. Schema objects are the logical structures that directly refer to the database’s data. Normalization is the process of converting a data model into a relation Schema.

30. Step 4: Physical Refinement • Once we have a relational schema, there are still of RDBMS-specific refinements that need to be considered such as : de-normalization, access control, adding constraints etc.

31. Step 5: Database Installation • Once the physical database schema is ready , it is converted in the form of database object such as table , clusters, indexes etc. and the database is actually installed on the system . After this , the database is ready for any further type of processing or queries as per the needs and requirements of the organization.

32. Thank You End 4x3 16x9