1 / 32

Data base designing & programming with sql

Data base designing & programming with sql. Chandan Sinha , PGT Comp. Sc. , K.V Maithon Dam . What is DBMS. A DBMS refers to a software that is responsible for storing maintaining and utilizing databases. . Tables & appl. Prog . DDLC – Database Development Life Cycle.

darryl
Download Presentation

Data base designing & programming with sql

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related