LIS 558: Database Management Systems

1
LIS 558 Database Management Systems

• Introduction
• Margaret Kipp
• mkipp_at_uwo.ca

Faculty of Information Studies, University of
Western Ontario
2

• Introduction to LIS 558

3
Course Material

• Course Website
• http//publish.uwo.ca/mkipp/teaching/558/
• Texts
• Available from the GRC or on-line
• Notes and Assignments
• in class or on the website

4
Course Objectives

• 1. To understand and apply the basic concepts of
  data modelling, requirements analysis and
  databases structures.
• 2. To gain exposure to industry standard concepts
  in the realm of database management systems.
• 3. To gain exposure to scripting for database
  applications and web database access concepts.

5
Course Skills Tools, Languages and Protocols

• Database Management Systems
• Microsoft Access
• MySQL
• SQL (Structured Query Language)
• ODBC (Open Database Connectivity)
• Scripting (VBA or Python)
• Entity-Relationship Modelling
• Database Design

6
Evaluation

• In Class Exercises
• practical exercises
• Assignments
• Creating an Access Database
• E-R Modelling
• Database Design from an E-R Model
• SQL Assignment
• Database Access via ODBC
• Mounting a Database on the Web

7
Course Outline
8

• Introduction to Databases

9
What is a Database?

• Definitions
• an organised body of related information
• a set of records on a particular topic
• Note that neither of these definitions implies
  the database must be electronic!
• Databases are designed for structured information
  storage.
• Database applications (DBMS) have special tools
  for information retrieval (e.g. Structured Query
  Language)

10
Examples of Databases

• phone book (paper database)
• membership directory (paper or electronic)
• library catalogue (from book catalogue to card
  catalogue to OPAC)
• article/abstract database (e.g. Dialog, INSPEC,
  Gale) - these databases have special tools for
  text retrieval
• the web (electronic)

11
Database Data Models

• A data model is a way of organising the data
  based on the relationships between the data in a
  database.
• There are a number of different database data
  models
• Flat Files (e.g. Spreadsheet)
• Hierarchical Database
• Network Database
• Object-Oriented Database
• Relational Database

12
Flat Files

• a flat file database is a single table database
  (e.g. the course outline)
• Advantages
• simple structure, all in one table
• Disadvantages
• table may be very long (and wide)
• duplication of information
• hard to handle multiple values (e.g. multiple
  authors)

13
Hierarchical Databases

• based on a classification scheme and organised
  into a tree-like structure
• widely used in computer file systems (folders and
  sub folders)
• Disadvantages
• each item can only have one parent

14
Network Database

• organises date into sets of records
• a set is a collection of items, in this case a
  collection of records
• records are connected directly by reference to
  other records
• the web is a large example of a network database

15
Object-Oriented Database

• Object-oriented programming sees software as a
  set of objects that interact with each other and
  stores these objects and interactions together in
  one place
• Object-oriented databases try to do the same
  thing
• have not really caught on, but have influenced
  relational database models

16
Relational Database

• created by E.F. Codd in 1970
• consists of multiple tables that can be related
  to each other
• each table has records (rows) consisting of
  individual fields (columns)
• tables are related to other tables by keys
• a key is a unique value which can come from the
  data (e.g. ISBN) or be assigned

17
Relational Databases (cont.)

• relational database model allows data to be
  separated into smaller sets to reduce
  duplication, but keeps relationships
• relational databases have a special query
  language (SQL)
• SQL allows database users to write queries that
  were not anticipated by the database designer
  (just like joining keywords in a keyword search)

18
Databases and Libraries

• the library card catalogue is a non-electronic
  form of database.
• cards are records, entries on a card are fields
• different sections of the card catalogue
  correspond to tables (e.g. subject section,
  author section, title section)
• libraries were early adopters of database
  technologies for card catalogues and for
  information retrieval

19
Relational Database Software (DBMS)

• Many different commercial packages for servers
  (Internet or Intranet) or clients (PCs).
  Examples
• Server
• Oracle, MySQL, PostgreSQL
• Microsoft SQL Server, DB/Textworks
• Client
• MS Access, FoxPro, dBase, DB/Textworks client
• SQLite, OpenOffice Base

20
MS Access

• Relational Database software for desktop users
• Not as powerful as big databases such as Oracle,
  but provides home users access to real relational
  database software.
• Features
• user friendly interface similar to other MS
  Office software multiple tables and
  relationships SQL support forms and reports
  scriptable (programmes can be written to access
  the DB), web capable

21
In-Class Exercise Creating an Access Database

• The In-Class Exercises will be mixed in with the
  theory. We will create a library employee
  database linking library employees and tasks.
• Step 1 Create a new database
• Open Access
• Create a new database
• Select File -- New from the menu
• Select Blank Database from the right side panel
• Name your new database (libraryemployees.mdb)

22
Key Components of a Relational Database

• Tables
• Records
• Fields
• Data Types
• Data Validation /Constraints (wk2)
• Indexes (wk2)
• Transactions (wk7)

• Keys (Primary and Foreign)
• Relationships
• Forms and Reports (wk2-3)
• Import/Export Routines
• Replication/Backups (wk3)

23
Tables

• A Table is a structured file that can store date
  of a particular type
• A Table is a collection of related items called
  records
• E.g. customer data, product data, library item
  data, employee information...
• Table schema (description) describes what data
  may be stored (format and size), how individual
  pieces are named, and so on

24
Records and Fields

• Tables are composed of rows and columns like a
  spreadsheet
• Rows are called records columns are represented
  by fields
• A record is an entry in a database table (one
  line in a table)
• A record consists of related fields (columns in a
  spreadsheet)
• Each record has the same fields as all the other
  records in the table.

25
Fields and Data Types

• Each field has a data type, this restricts the
  kind of information that can be stored in the
  field
• Data types allow data to be formatted correctly,
  sorted and displayed
• Data Types Number, Text, Date, AutoNumber,
  Binary Large Object (programme, image, PDF, other
  document), Image File, etc.
• Text is the default data type

26
Creating Tables

• In-Class Exercise
• Step 2 Creating Tables
• Create Table By Using Wizard
• Choose Business -- Select Employees
• Select appropriate fields (Employee ID,
  Department Name, Employee Number, Last Name,
  First Name, Title, Extension, Office Location)
• Set the Table Name
• Have Access set the primary key for you
• Click Finish to create the table

27
Creating Tables (cont.)

• Create two more tables
• give the tables useful names
• allow Access to set the primary key
• click Finish after setting table name, ignore
  relationships for now
• Task Table
• Task ID, Task Description, Start Date, End Date,
  Notes
• EmployeesAndTasks Table
• Employee Task ID, Task ID, Employee ID

28
Creating Tables (cont.)

• Look at the tables in design view (View -- Design
  View)
• Notice the two columns Field Name and Data Type
• Access has auto selected appropriate data types,
  generally you should select text unless you have
  a good reason to use another data type
• Access has also set what is called a primary key,
  this field has the type AutoNumber

29
Primary Keys

• Each record in a database should have a field
  that uniquely identifies it -- this is the
  primary key
• Primary keys can be used to locate one and only
  one item in a table for modification, otherwise
  the wrong records might be modified
• Primary keys must be unique
• A field from the data can be used, but it is
  better to use the AutoNumber data type

30
Relationships

• Relational Databases have multiple related tables
• There are three types of relationships
• one to one each item in table 1 can only be
  related to one item in table 2
• one to many one item in table 1 can be related
  to many items in table 2
• many to one many items in table 1 can be related
  to one item in table 2

31
Relationships (cont.)

• In-Class Exercise
• Step 3 Creating Relationships (Joining Tables)
• Tools -- Relationships
• Relationships -- Show Table
• Add all tables
• What are the relationships in our database?
• between Employees and EmployeesAndTasks (one to
  many)
• between Tasks and EmployeesAndTasks (one to many
  or possibly one to one)

32
Relationships (cont.)

• Creating a relationship
• move the mouse over Employee ID in the Employees
  table (or Task ID in the Tasks table)
• Click and drag it over to the Employee ID entry
  in the EmployeesAndTasks table
• release the mouse over Employee ID
• OR
• Relationships -- Edit Relationships
• Select the two tables to be joined
• Select the two related fields
• Click Create

33
Foreign Keys

• a relationship between two tables joins two
  tables by a primary key
• foreign key is the name for a primary key value
  from another table
• e.g. the EmployeesAndTasks table has two foreign
  keys Employee ID and Task ID

34
Import and Export Routines

• Databases offer methods for importing data from
  other programmes or exporting data to other
  programmes.
• In-Class Exercise
• Step 4 Import Data
• File -- Get External Data -- Import Data
• Select one of the Excel files, Click Import
• Click Next
• Make sure First Row contains Column Names is
  selected, Click Next

35
Import and Export (cont.)

• Import Data (cont.)
• Select In an Existing Table
• Select the appropriate Table
• Click Next, Click Finish
• If all went well, your table should now have
  records, if not, what is the error message?
• Repeat with the other two Excel files

36
Changing the Database

• Add a Field
• Open the Employees Table
• Select View -- Design View
• Add a field to the table by entering a name and
  type
• Save
• Changing a Field
• In Design View
• Change a Field name (not the primary key)
• Click on another field
• Save

37
Changing the Database (cont.)

• Deleting a Field
• In Design View
• Right click on field name and select Delete Row
• Note, this will also delete all the data in that
  row
• Adding a Table
• Create Table in Design View
• Enter fields (Branch ID, Name, Location, Hours)
• Save Table as Branches
• Note we could then change the Office Location in
  the Employees table to a foreign key storing the
  Branch ID!

38
Recap

• Database refers to the data
• Database Management System (DBMS) refers to the
  software (e.g. Access)
• Tables contain structured data on a particular
  topic
• Tables consist of records and fields
• Fields have data types
• Tables may be related to other tables (joined by
  keys)

39
Recap (cont.)

• Relationships between tables may be one to one,
  one to many or many to one
• Tables are related by primary keys, called
  foreign keys when they are from a different table
• Primary Keys are unique values, can be from data,
  but it is better to auto generate a unique value
• Data can be imported/exported from database
  formats

40
First Assignment

• Create an Access database with a library theme
• http//publish.uwo.ca/mkipp/teaching/558/558assig
  n1.html
• Due September 28th
• Any library themed database
• favourite book database
• store books, votes and comments for books, names
  of voters, etc.
• journal acquisition or weeding database
• store lists of possible journals, votes for
  favourite journal, etc.
• etc.