Lecture notes for Database Management systems

how to learn database for beginners and lecture notes on database design and management pdf free downlaod
DavidCooper Profile Pic
DavidCooper,Singapore,Researcher
Published Date:11-07-2017
Your Website URL(Optional)
Comment
Databases for beginners 02-DBBA Marek Kręglewski 1 Basics about a database? • A database is a structured collection of records. • Database Management System (DBMS) – add, remove, update records – retrieve data that match certain criteria – cross-reference data in different tables – perform complex aggregate calculation • Database consists of columns (attributes) and rows (records). • Databases versus spreadsheets – easy manipulation of data 3 Single table database ISBN Title AuID AuName AuTel PubID PubName PubTel Price 0-99-999999-9 Emma 1 Austen 111-111-1111 1 Big House 123-456-7890 20.00 zł 0-91-335678-7 Faerie Queen 7 Spenser 777-777-7777 1 Big House 123-456-7890 17.00 zł 0-91-045678-5 Hamlet 5 Shakespeare 555-555-5555 2 Alpha Press 999-999-9999 20.00 zł 0-103-45678-9 Iliad 3 Homer 333-333-3333 1 Big House 123-456-7890 25.00 zł 0-555-55555-9 Macbeth 5 Shakespeare 555-555-5555 2 Alpha Press 999-999-9999 12.00 zł 0-55-123456-9 Main Street 10 Jones 123-333-3333 3 Small House 714-000-0000 23.00 zł 0-55-123456-9 Main Street 9 Smith 123-222-2222 3 Small House 714-000-0000 23.00 zł 0-12-333433-3 On Liberty 8 Mill 888-888-8888 1 Big House 123-456-7890 25.00 zł 0-321-32132-1 Balloon 2 Sleepy 222-222-2222 3 Small House 714-000-0000 34.00 zł 0-321-32132-1 Balloon 4 Snoopy 444-444-4444 3 Small House 714-000-0000 34.00 zł 0-321-32132-1 Balloon 11 Grumpy 321-321-0000 3 Small House 714-000-0000 34.00 zł 4 Disadvantages of a single table database • Redundancy of data • Problem with complex data • Problems in updating in bulk (new phone number) • Problems in adding incomplete data (new publisher) • Problems in removing group of data (all books from the publisher) Solution: Relational Database Management System (RDBMS) • E.g. Microsoft Access 5 Relational Database • System of related tables • Minimum redundancy • Referential integrity • Database keys • The ACID model (guarantee of successful transactions): – Atomicity („all or nothing” rule) – Consistency (only valid data in) – Isolation (order of executed transactions) – Durability (committed transaction will not be lost) 6 Relations in a database example AuName Title Price ISBN AuID AuTel   Written by Authors Books  1 Is Published by 1 1 Coauthors Publisher CoauID CoauTel PubTel PubID CoauName PubName 7 Table • Unique name • Size = of rows, order = of columns • Structure of a table → T A , A , … , A 1 2 n • All rows different • Order of rows not important • Unique headers identify columns • NULL value in tables 12 Database keys • Primary key – Value unique for each record in a table – This value can not be used twice – AutoNumber guarantees uniqueness but does not carry any useful information • Foreign keys – Used to create relationships between tables – No uniqueness constraint for foreign keys • Relation between primary and foreign keys – Same format – Same values 13 Relations in a database example AuName Title Price ISBN AuID AuTel   Written by Authors Books  1 Is Published by 1 1 Coauthors Publisher CoauID CoauTel PubTel PubID CoauName PubName 14 Building relations Relation one-to-one Table S Table T A A A A A A B B B B 1 2 3 4 5 1 1 2 3 4 15 Building relations Relation one-to-many Table S Table T A A A A A A B B B B 1 2 3 4 5 1 1 2 3 4 Primary Foreign key key Values of the foreign key can not be different from the values of the primary key. 16 Building relations Relation many-to-many Table S Table S/T Table T A A A A A A B B B B B 1 2 3 4 5 1 1 1 2 3 4 17 Enforcing referential integrity • Cascade Update Related Fields – the values of foreign keys change following changes of the values of the primary key • Cascade Delete Related Records – deleting a record from the primary field in a relationship causes a deletion of all related records in the second table 18 Indexing field values • Purpose: speed up access to specific data • Used in large tables • Updating of all indexes every time a table record is updated or added • Example Index of towns Table of shops Gdańsk • 1 Plus Toruń Kraków • 2 Piotr Poznań Poznań • 3 Tesco Kraków Poznań • 4 Tesco Poznań Toruń • 5 Plus Gdańsk 19 Principles for building a database • Types of attributes: – Identification – Information – Identification+information • Example 1: PubID,PubName,PubTel,FoundYear Ident Ident+inform Inform • Example 2: ISBN Title PubID PubName 1-1111-1111-1 Macbeth 1 Big House 2-2222-2222-2 Hamlet 1 5-5555-5555-5 2 ABC Press 20 Queries • Database – data located in tables + relations • Query – primary mechanism for retrieving information from a database, consists of questions presented to the database in a predefined format – an expression stored in a database having a unique name • Answer to the query – a computed table = Dynaset • SQL – Structured Query Language • Types of queries: – Select query – Action queries (Make-Table, Append, Update, Delete) – Crosstab query 21 Creating a query in MS Access 22 Design View of a query Drug and drop principle 23 Selection criteria • Specifying criteria: – A value of an expression – Use of criteria operators: , , =, =, – BETWEEN, e.g. BETWEEN 2 AND 5, – LIKE, e.g. LIKE „b-dk0-5?” • Logical operators: OR, AND – e.g. „Smith” OR „Jones” • Mathematical operators: +, - ,, /, \, MOD, • Text operator: & • Date/Time fields – Format 2009-06-19 16:00 4:00PM – Date/time functions: Date() Day(date), Month(date), Year(date), Weekday(date) 24 Calculation on groups of records 25

Advise: Why You Wasting Money in Costly SEO Tools, Use World's Best Free SEO Tool Ubersuggest.