index.md (3296B)
1 +++ 2 title = "Introduction" 3 +++ 4 5 # Introduction 6 7 database: collection of data with 8 9 - logical structure 10 - specific semantics 11 - specific group of users 12 13 Why not in files? There\'s no query language, a weak logical structure, 14 no efficient access, almost no protection from data loss, no parallel 15 access control. 16 17 ANSI SPARC architecture stores data in three levels: 18 19 1. View level: application programs hide details of data types. Hide 20 information for privacy/security 21 2. Logical level (\'conceptual schema\'): describes data and relations 22 among data 23 3. Physical level: how data is stored, in disk pages, index structures, 24 whatever else. 25 26 ## Relational databases 27 28 view and logical levels are data representations in relations/tables 29 30 a row is a tuple record. the order of the elements of the tuples 31 doesn\'t matter. 32 33 a database instance is a \'snapshot\' of a database at a certain point 34 in time. 35 36 the database schema is the structure of the database - the relations and 37 constraints. 38 39 constraints: 40 41 - primary key (would be underlined) uniquely identifies a row in a 42 table (`customer(id, name, street, city)`) 43 - a foreign key that points to a record in a different table 44 (`account(depository → customer, accountnr)`) 45 - data types, constrained data types 46 - columns constraints (e.g. unique, nullability, counter) 47 - check constraints (logical expression for a domain) 48 49 create an entity relationship model in UML: 50 51  52 53 then translate that into relations 54 55 ## Database management system 56 57 database management system (DBMS) allows: 58 59 - create, modify database 60 - query data using query language (retrieve) 61 - support persistent storage of large amounts of data 62 - allow durability and recovery 63 - control access to data by users in parallel, without unexpected 64 interactions among users (isolation) or partial actions (atomicity) 65 66 multiple users, concurrent access. transactions have ACID properties: 67 68 - Atomicity: transaction executes fully or not at all (commit/abort) 69 - Consistency: database remains in a consistent state where all 70 integrity constraints hold 71 - Isolation: multiple users can modify database at same time but will 72 not see each other\'s partial actions 73 - Durability: when a transaction is successfully committed, modified 74 data is persistent regardless of disk crashes 75 76 DBMS have data independence and duplication avoidance. 77 78 - Logical independence: logical schema doesn\'t depend on views 79 - Physical independence: changing physical schema doesn\'t break the 80 logical schema 81 82 ## SQL: Structured Query Language 83 84 SQL is declarative data manipulation language. The user says which 85 conditions the retrieved data has to fulfill. 86 87 It\'s more concise than imperative languages, thus easier to maintain 88 and cheaper to develop programs in it. 89 90 Users usually don\'t have to think about efficiency, the DBMS will 91 manage that. 92 93 Creating a table with constraints: 94 95 ```sql 96 CREATE TABLE solved ( 97 id INT AUTO_INCREMENT, 98 name VARCHAR(40) NOT NULL, 99 homework NUMERIC(2) NOT NULL, 100 points NUMERIC(2) NOT NULL CHECK (points <= 10), 101 PRIMARY KEY (id) 102 ); 103 ``` 104 105 Creating a view: 106 107 ```sql 108 CREATE VIEW solved_homework AS 109 SELECT id, name, homework FROM solved; 110 ```