Database Info Storage and Retrieval

1
Database Info Storage and Retrieval

• Aim Understand basics of
• info storage and retrieval
• database organization
• DBMS, Query and Query Processing
• Work some simple exercises
• Readings
• SG --- Ch 13.3
• Optional
• Some experiences with MySQL, Access

2
Outline

• What is a Database
• Evolution of Databases
• Relational Database
• DB Operations
• Concurrency Issue in Database

3
What is a Database

• First attempt
• A collection of data
• Examples
• Employee database
• Jobs Database
• LINC Database
• Inventory Database
• Recipe Database
• Hotel Database
• Database of Hotels, Restaurants
• MP3 Database

4
What is a Database (2)

• Combination of Databases
• Can do more
• eg Employee Database CIA Database
• eg Inventory Database Recipe Database
• Database is
• A combination of a variety of data collections
  into a single integrated collection

5
Evolution of Databases

• From separate, indept database
• One Courses DB for each dept or faculty
• Inherent Problem
• incompatability,
• inconvenience, slow, error prone
• CF NUSs (not-so) Online Registration
• To Integrated Database
• One integrated DB or DB schema
• Serving the needs of all depts/faculty
• Better data compatability, fasters,
• CF IRAS e-filing (Online Tax Submission)

6
DBA and DBMS

• With Integrated Database, we need
• To ensure data consistency
• Provide services to all depts
• Different services to diff dept,
• Different interface
• To provide different views of the same data
• Eg CEO, CFO, Proj Mgr, Programmer
• Eg Dean, Heads, Professors, AOs, Students
• to decide how to Organize data (schemas)
• Usually organized into tables
• DBA Database Administrator
• DBMS DB Management System

7
Database (with 3 Relations or Tables)
8
Relational DB -- Definitions

• Attribute (Fields) Columns in the table
• Course, Day, Stud-ID, Grades
• Scheme Set of Attributes (Columns)
• Course, Day, Time -- SCHEDULE-DB
• Record (Tuple) A row in the table
• (USST01, Tue, 10 AM)
• Relation (or Tables) A set of records
• Database A set of Relations
• SCHEDULE-DB, GRADES-DB, STUDENTS-DB

9
Operations on a Relation (Table)

• Insert ( (CS1102, Thu, 11AM), SCHEDULE-DB)
• Delete ( (UIT2201, Tue, 11AM), SCHEDULE-DB)
• Delete ( (UIT2201, , ), SCHEDULE-DB)
• Delete ( ( , Tue, ), SCHEDULE-DB)
• Lookup ( ( , Wed, ), SCHEDULE-DB)

10
Operations on a Relation (Table)

• Insert a Record
• Deleting Records
• Delete a specific record
• Delete all records that match the given
  specification X
• Searching Records
• Look up all records that match the given
  specification X
• Can also choose to display only some of the
  attributes (also called projection)

11
In SQL (a Query Language).

• SQL is a DB Query Language
• Supported by many of the common DBMS
• Provides easier means to insert/delete records
• Quite simple to use/learn on your own
• SQL Queries (format)
• SELECT FROM databases WHERE

12
In SQL (a Query Language).

• Sample SQL Queries
• SELECT from SCHEDULE-DB where
  (DAYWed)
• SELECT DAY, HOUR FROM SCHEDULE-DB WHERE
  (COURSEUIT2201)
• SELECT Course, Hour FROM SCHEDULE-DB

13
Working with Multiple Relations

• Combine two tables
• that share common data
• Use the JOIN operator
• Eg
• Given GRADES-DB and STUDENTS-DB (abbrev GDB
  and SDB, respectively)
• NEW1 ? JOIN GDB and SDB with
  GDB.Stud-ID SDB.Stud-ID
• Given SCHEDULE-DB and VENUE-DB (abbrev SDB
  and VDB, respectively)
• NEW1 ? JOIN SDB and VDB with
  GDB.Course SDB.Course

14
Joins -- Working with Multiple Relations
SCHEDULE-DB.course VENUE-DB.course
15
JOIN is an expensive operation

• Running time is O(mn)
• May produce huge resultant tables
• (See examples in Tutorial)
• Exercise great care with JOINs

16
Why not store everything in one Table?

• Problems
• Duplication of data
• Deletion Problem
• What if Cathy Xin drops CS1101?

17
Other Issues (for reading)

• Primary Secondary Keys
• Concurrency Issues (Read Section 9.6)
• THE END