×
Everything you do now is for your future. Think About that.
--Your friends at LectureNotes
Close

Database Management System

by Ahmed ElalfyAhmed Elalfy
Type: NoteViews: 37Uploaded: 29 days ago

Share it with your friends

Suggested Materials

Leave your Comments

Contributors

Ahmed Elalfy
Ahmed Elalfy
DATABASE MANAGEMENT SYSTEMS Tibor Radványi PhD It was made with support of the TÁMOP-4.1.2-08/1/A-2009-0038 1
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
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
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

Lecture Notes