×
Don't wait for the right time for studying... It won't come.
--Your friends at LectureNotes
Close

Note for Database Management System - DBMS By ahmed elalfy

  • Database Management System - DBMS
  • Note
  • 7 Topics
  • 141 Views
  • Uploaded 1 year ago
0 User(s)
Download PDFOrder Printed Copy

Share it with your friends

Leave your Comments

Text from page-2

INTRODUCTION ................................................................................................................................................ 6 BASIC ELEMENTS .............................................................................................................................................. 8 DATA AND INFORMATION ......................................................................................................................................... 8 DATABASE ............................................................................................................................................................. 9 DATABASE MANAGEMENT SYSTEM (DBMS) .............................................................................................................. 10 Local database ............................................................................................................................................. 12 File – server architecture ............................................................................................................................. 13 Client – server architecture .......................................................................................................................... 14 Multi-Tier ..................................................................................................................................................... 17 Thin client .................................................................................................................................................... 18 BASIC STRUCTURES ................................................................................................................................................ 18 IMPROVEMENT OF DATA MODELS ................................................................................................................. 20 HIERARCHICAL ...................................................................................................................................................... 20 NETWORK DATA MODEL ........................................................................................................................................ 21 RELATIONAL ......................................................................................................................................................... 22 DATABASE PLANNING AND ITS CONTRIVANCES ............................................................................................. 28 MAIN STEPS OF DATABASE DESIGNING ....................................................................................................................... 29 NORMALISATION ................................................................................................................................................... 31 Normal Forms: ............................................................................................................................................. 31 Dependences................................................................................................................................................ 32 Relation key ................................................................................................................................................. 33 DATA MODEL MISTAKES .......................................................................................................................................... 35 STOPPING THE REDUNDANCY ................................................................................................................................... 37 Normal forms: .............................................................................................................................................. 37 Third normal form: ...................................................................................................................................... 40 Boyce/Codd normal form (BCNF) ................................................................................................................. 41 THE RELATION’S THIRD NORMAL FORM AND THE DECOMPOSITION OF THE BOYCE/CODD NORMAL FORM ................................ 42 Fourth normal form (4NF)............................................................................................................................ 42 Fifth normal form (5NF) ............................................................................................................................... 43 PHYSICAL DESIGNING.............................................................................................................................................. 43 SUPPORTING THE DESIGN WITH SOFTWARE ................................................................................................................. 44 Designing requirements: .............................................................................................................................. 44 Design of the database, the database tables and their fields: .................................................................... 44 THE MYSQL WORKBENCH ..................................................................................................................................... 45 OPERATIONS OF RELATIONAL ALGEBRA ...................................................................................................................... 50 TASKS ................................................................................................................................................................. 55 2

Text from page-3

LANGUAGE REFERENCE OF SQL ...................................................................................................................... 58 ELEMENTS OF DDL ................................................................................................................................................ 58 Create schemes, the creat ........................................................................................................................... 58 Changed the schema elements, the alter .................................................................................................... 60 .CONSTRAINT integrity constraint application ............................................................................................ 61 .ELEMENTS OF THE DML ........................................................................................................................................ 62 .New data entries, insert the command ...................................................................................................... 62 . Create table based on another table ......................................................................................................... 63 .Change data,the update ............................................................................................................................. 63 .Delete data, the delete ............................................................................................................................... 63 .RIGHTS AND USER MANAGEMENT, THE DCL .............................................................................................................. 64 .Privileges contribute ................................................................................................................................... 65 .Roles(ROLE)................................................................................................................................................. 66 QUERIES AND THE QL ............................................................................................................................................ 68 The base of the select command ................................................................................................................. 68 Counted fields and aggregate functions ...................................................................................................... 69 Filters and the where clause ........................................................................................................................ 71 Aggregation queries, the usage of group by and having, and arrangement............................................... 75 Connecting Tables........................................................................................................................................ 77 Embedded queries ....................................................................................................................................... 81 TASKS ................................................................................................................................................................. 84 VIEWS AND INDEXES .................................................................................................................................... 102 VIEW ................................................................................................................................................................ 102 Modifiable view tables............................................................................................................................... 103 Structural terms of modifiability: ............................................................................................................... 103 Deleting a view table ................................................................................................................................. 104 Consequently, let’s look through the advantages of view tables: ............................................................. 104 INDEXES ............................................................................................................................................................ 104 Sparse indexes ........................................................................................................................................... 104 Searching ................................................................................................................................................... 105 Insertion ..................................................................................................................................................... 105 Deleting ..................................................................................................................................................... 105 Modifying: ................................................................................................................................................. 106 B*-trees as multilevel sparse indexes ........................................................................................................ 106 Searching: .................................................................................................................................................. 107 Insertion: .................................................................................................................................................... 107 Delete: ....................................................................................................................................................... 107 3

Text from page-4

Modification: ............................................................................................................................................. 107 DENSE INDEXES: .................................................................................................................................................. 107 CONSTRAINTS, INTEGRITY RULES, TRIGGERS ................................................................................................ 110 Keys ............................................................................................................................................................ 110 Referential integrity constraint .................................................................................................................. 112 Constraints for attribute values ................................................................................................................. 113 Standalone assertions................................................................................................................................ 113 Modifying constraints ................................................................................................................................ 114 TRIGGERS: (ORACLE 10G) ............................................................................................................................. 116 Triggers could triggered by: ....................................................................................................................... 116 We use them in the following cases: ......................................................................................................... 116 Row-level trigger: ...................................................................................................................................... 117 Statement-level trigger .............................................................................................................................. 117 Before and After triggers: .......................................................................................................................... 117 Instead of trigger ....................................................................................................................................... 119 System triggers .......................................................................................................................................... 119 Creating Triggers ....................................................................................................................................... 119 How triggers work: .................................................................................................................................... 120 TASKS ............................................................................................................................................................... 121 BASICS OF PL/SQL ........................................................................................................................................ 125 BASIC ELEMENTS OF PL/SQL ................................................................................................................................. 125 Character set ............................................................................................................................................. 125 Lexical units ............................................................................................................................................... 125 Symbolic names ......................................................................................................................................... 126 Reserved words .......................................................................................................................................... 127 Identifiers with quotation marks: .............................................................................................................. 127 Literals ....................................................................................................................................................... 127 Label .......................................................................................................................................................... 128 Nominated constants ................................................................................................................................ 128 Variable ..................................................................................................................................................... 128 Simple and complex types ......................................................................................................................... 129 NUMBER type ............................................................................................................................................ 130 Character family ........................................................................................................................................ 131 ROWID, UROWID Types ............................................................................................................................. 132 Date/interval types .................................................................................................................................... 132 Logical type ................................................................................................................................................ 133 4

Text from page-5

Record type ................................................................................................................................................ 133 PROGRAMMING STRUCTURES ................................................................................................................................ 135 The CASE statement................................................................................................................................... 136 Loops.......................................................................................................................................................... 138 Base loop ................................................................................................................................................... 138 While loop .................................................................................................................................................. 139 FOR loop .................................................................................................................................................... 140 The EXIT statement .................................................................................................................................... 141 MIXED TASKS ............................................................................................................................................... 142 5

Lecture Notes