Introduction to Databases - Part 1 PDF

Introduction to Databases- Part 1 Week 1 Data and Information Data is raw, unprocessed facts Information is processed data; data that has been given meaning What does 31106 mean? There are 31,106 people in a town The car has 31,106 km on the clock The...

Introduction to Databases- Part 1 Week 1 Data and Information Data is raw, unprocessed facts Information is processed data; data that has been given meaning What does 31106 mean? There are 31,106 people in a town The car has 31,106 km on the clock The access code for a door lock The cost is $311.06 or $31,106 2 What is a database? An organised collection of related data Your address book The phone book Library catalogue The list of courses available at University… A database does not have to be in a computer! 3 Database Management System (DBMS) This is the software that organizes and manages a database. It allows you to: create a database select and display sort query calculate and format report Example: Microsoft Access (part of the Office suite, along with Word, Excel etc) 4 Database organisation Holmes, S Tables - 221b Baker Street which are London made up of Records 03 123 4567 which are made up of Fields 5 Data organisation Data is in tables Fields Columns are fields Records Rows are records 6 Types of databases File processing system “Flatfile database” “Simple filing system” One table, like a spreadsheet Relational DB The most popular Many tables (or files) linked together 7 Language classes - flatfile This database contains the details of students enrolled in language classes 8 Language classes - relational The two tables are linked using "ClassID" 9 In a relational database the tables are joined Classes Students Table Table Any database should be well planned…this is done on paper, and is often called a data dictionary 10 Relational DBMS vs. File Management Avoid data redundancy (data typed in two or more places) Data integrity – maintain accuracy Saves space Flexibility – add new tables Better sharing over a network Easier backups 11 Design dictionary A plan of the fields that will be in each table A good plan is the beginning of a good database Many ways of doing it (usually by hand), e.g. Field Data Field Field Example Name Type Length Format ID Number 4 ##-## 14-64 Name Text 50 - Dona Price Currency 6 $, 2 dp $12.33 12 Data Dictionary Data Dictionary in MS Access Defines field names Specifies data type Specifies particular characteristics of field Identifies primary key field 13 The Primary Key A special field that identifies each record Unique in the table (no duplicates) Often a sequential auto number Your bank account number Your student ID number Code number for a stock item 14 Common data types Text Any combination of letters, numbers, punctuation and spaces. Limited size, so saves space and is faster to find Memo Same as text, but unlimited size. Slower. Number Digits only. Use for anything you want to do maths with e.g. total, average, +, -, *, / etc Date/Time Different formats e.g. 15-6 or 15 June etc. Can do maths e.g. find differences between dates Currency In money format, can do maths with it AutoNumber Automatic incremental numbers. Good for ID numbers Yes/No Usually a tick box 15 The Ideal Database System Preserves data integrity Data is valid (correct) Promotes data independence Is in a widely used format Avoids data redundancy Data is only entered once Ensures data security Data is protected Allows for data maintenance Adding, updating and deleting records 16 Components of a Relational Database Tables records in rows and columns where the raw data is stored Query extract information from a table according to given criteria Forms screen designs for entering and viewing of data in table or query Reports print queries or tables Macros automate parts of the database Modules small programs to carry out specific tasks – “grown-up macros” 17 Summary Databases may be in many forms DBMS is the software used to manage a database on a computer A DMBS holds data in fields, which make up records, which make up tables File processing system = one table database Relational database = 2+ tables joined by primary key Characteristics of valuable information 18