ITIS+1P97+Lecture+9 - Databases & Information...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Databases & Information Databases & Information Management Databases basics Bit: Smallest unit of data; binary digit (0,1) Byte: Group of bits (8) representing a single character Field: group of characters, representing an attribute or characteristic Record: group of related fields, describes an entity (e.g., person, event, place) File or table: group of related records of the same type Database: group of related files ITIS 1P97 Canbolat F2009 ITIS 2 with a single table named “Customers” Attributes (Fields) Key field Medialoft database Record 3 Before databases flat files & their problems Data redundancy: same data stored in more than one location Data inconsistency: same attribute may have different values Program­Data Dependence: changes in programs required changes to the data Lack of Flexibility: only routine scheduled reports, hard to obtain ad­hoc reports Poor security: little knowledge of who is accessing or making changes to the data Lack of data sharing: no sharing of data between different parts of the organization ITIS 1P97 Canbolat F2009 ITIS 4 Databases what? Database Collection of data organized to serve several applications efficiently A database management system (DBMS) Comprises programs to store, retrieve, and manage a database and to provide interfaces to applications Provides Backup & Recovery, Authentication & Security ITIS 1P97 Canbolat F2009 ITIS 5 Databases physical vs. logical view A DBMS separates the logical and the physical view of the data Physical view Logical View How data is organized & stored on physical storage (e.g., disk, CD, etc.) How data appears to the end user ITIS 1P97 Canbolat F2009 ITIS 6 Databases the ACID properties Atomicity, Consistency, Isolation, & Durability are the transaction features of a DBMS Atomicity: if one part of the transaction fails, the entire transaction fails (the “all or nothing” rule) Consistency: only valid data will be written to the database Isolation: multiple transactions occurring at the same time must not impact each other Durability: any transaction committed to the database will not be lost ITIS 1P97 Canbolat F2009 ITIS 7 [ Source: ] Databases relational DBMS All data in the database is represented as two­dimensional tables called relations Data stored in one table relates to data stored in another table as long as both tables share a common data element ITIS 1P97 Canbolat F2009 ITIS 8 Databases relations A database with 2 relations (tables / files): SUPPLIER & Primary PART key SUPPLIER & PART share the field “Supplier_Number” To find the name & address of the supplier of part 137: Primary Key “Part­Number” leads us to Foreign Key “Supplier­Number” which is the Primary Key in the relation SUPPLIER Foreign key The key uniquely identifies each record ITIS 1P97 Canbolat F2009 ITIS 9 Databases relations An example of a database with 3 relations: ORDER, PARTS & SUPPLIER ITIS 1P97 Canbolat F2009 ITIS 10 An Employee database with 1 table: “Employees” ITIS 1P97 Canbolat F2009 ITIS 11 with 3 tables: “Employees”, “Locations”, “Titles” An Employee database ITIS 1P97 Canbolat F2009 ITIS 12 with 3 tables: “Employees”, “Locations”, “Titles” An Employee database ITIS 1P97 Canbolat F2009 ITIS 13 Databases basic operations 3 basic operations to develop useful sets of data (1) Select Creates a subset of records that meet certain criteria e.g., Select records (rows) with part number = 137 or 150 ITIS 1P97 Canbolat F2009 ITIS 14 Databases basic operations (2) Join combines tables into one with more info e.g., join the resulting table from (1) and SUPPLIER ITIS 1P97 Canbolat F2009 ITIS 15 Databases basic operations (3) Project Creates a subset of columns e.g., from the resulting table project Part_Number, Part_Name, Supplier_Number, Supplier_Name ITIS 1P97 Canbolat F2009 ITIS 16 Databases data manipulation language A DBMS has a data manipulation language used to add, change, delete, and retrieve data in the database One such language is SQL (Structured Query Language) MS Access uses SQL behind a user­friendly interface (so that you don’t need to learn SQL in order to manipulate your data) Here is the SQL query for the previous example: ITIS 1P97 Canbolat F2009 ITIS 17 MS Access Example Datasheet view – what you see as results Design view – design input SQL view – query language used converts SQL results in Design Datasheet 18 18 Hands­on Experience with MS Access In the Next Lab: Creating a database file and tables Using direct data input Using Forms Creating primary key(s) and relationships Creating queries and generating outputs 19 19 ...
View Full Document

This note was uploaded on 10/20/2010 for the course ITIS 1P97 taught by Professor Df during the Winter '09 term at Brock University, Canada.

Ask a homework question - tutors are online