1 / 23

Database Administration Lecture No 1

Database Administration Lecture No 1. Muhammad Abrar muhammadabrar78@yahoo.com. Muhammad Abrar????. MS (Computer Science) Gold Medal Specialization in Database Systems Research Publication

topaz
Télécharger la présentation

Database Administration Lecture No 1

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. Database AdministrationLecture No 1 Muhammad Abrar muhammadabrar78@yahoo.com

  2. Muhammad Abrar???? • MS (Computer Science) Gold Medal • Specialization in Database Systems • Research Publication • M. Abrar, Z. Jan, Dr A.M. Mirza S. Bashir “Seasonal to Interannual Climate Prediction Using Data Mining technique KNN” Springer Verlag Communications in Computer and Information Science Series Book. 2008 • Research Areas: Data Mining , Data Ware Housing, Distributed Database • Areas of expertise : Programming (.NET (Visual Basic, Visual C++ etc), C++, JAVA) OOP Analysis, Structure Analysis and Designing

  3. Muhammad Abrar???? (Cont’d) • Experience • January 2009 – ---- (kardan Institute of Higher Education, Kabul) • Assistant Professor In Computer Science • September 2006 – Dec 2008 (IBMS/CS , Agricultural University Peshawar) • Lecturer In Computer Science, • Assistant In-charge Computer Science • September 2002- August 2006 (Govt. College of Commerce Mardan) • Computer Instructor • In-charge Computer Literacy Program. • February 2004 – August 2006 (Ansi Institute of Management Sciences) Mardan • Visiting Lecturer In Computer Science.

  4. Terms Definition • Database • Data • Information • Records • Files • Relations

  5. Entity • Relationship • One to One • One to Many • Many to Many • ERD

  6. How to implement many to many relationship is database?

  7. What we will study ? • Database design Theory • Normalization • Database Tuning • System Implementation Techniques • RDBMS Architecture • System Catalog • Query Processing • Transaction Processing concepts • Transaction properties • Concurrency Control Techniques • Locking Techniques

  8. What we will study ? (cont’d) • Database Recovery Techniques • Recovery based on differed update • Recovery based on immediate update • Backup and recovery • Database Security • Security Issues • Granting/revoking privileges • Statistical database Security • Introduction to Object Oriented Databases • Introduction to Data Warehousing • Introduction to Data Mining

  9. Design Techniques • Informal Design Guidelines for relation schema • Semantic of the attributes • Reducing redundant values in tuples • Reducing NULL values in tuples • Disallowing the possibility of generating spurious values

  10. Semantics of the attributes • Whenever group the attributes to form a relation, certain meaning should be associated with the attributes • Simply include the most important and related attribute to the relation that clarify the meaning of relation • Consider the next example

  11. Semantics of the attributes (cont’d) • Employee • Same Table in which the meaning of attribute is more realistic • Employee

  12. DEPARTMENT • The refined and simplified version of Department • DEPARTMENT • Guideline 1: Design a relation schema so that it is easy to explain its meaning. Do not include attribute from multiple entities

  13. Redundant Information What are problems in this relation?

  14. Anomalies • We may enter different same for department number • We must need to enter the department information or leave it blank if the new employee is not working in any department at the time of entry

  15. Insertion Anomalies • It is difficult to insert a new department that haven’t any employee because the ssn will be blank and the insertion will not be allowed by the DBMS • WHY???????????? • SSN is Primary Key

  16. Deletion anomalies • What about the department no 3 if we delete the employee with SSN no 7?

  17. UPDATE Anomalies • In case of updating the department information, we must update multiple records • Department no 10 will require to update 3 records

  18. Guideline No 2 • Design the relation so that no insertion , deletion and modification anomalies are present in the relation • This guideline is consistent with the first one

  19. Null values n tuples • In some relation we may include many attributes together into a ‘fat’ relation • If all attributes are not necessary for a record, then they may have null values. • Waste of memory • Semantic may lose • Create problem when joining relations

  20. Guideline No 3 • As far as possible avoid to include the attributes whose have may frequently be null. If nulls are unavoidable, make sure that they apply in exceptional case only • Example • If there are only 10% employees who has their offices then there is little justification for including ‘off-no’ in the employee relation • You can use a separate relation • EMPOFF(ESSN, Off_no)

  21. Generation of Spurious Tuples • EMP_LOC EMP_PROJ

  22. Guideline No 4 • Design the relation so that equality join is applied on the attributes that are either primary key or foreign keys

More Related