www.covenantuniversity.edu.

ng

Database Management

The CST121 Team

1
 Lecture Goals
 At the end of this lecture, you should have learnt
how to:
o Create a database using Microsoft Access DBMS
o Create Tables and relationships
o Query a database
o Create Forms and Reports

2
 We Remember…
 10% of what we read
 20% of what we hear
 30% of what we see and hear
 50% of what we see someone do
while explaining it
 90% of what we do ourselves
3
 What is data?
 Facts related to any object in consideration
– Data about a student can include: Name, Matric No.,
Age, Level, Programme etc.
– A picture of the student can also be considered as data

4
 How is data stored?
 Data can be stored in flat files (paper database)

5
 How is data stored? (Cont’d)
 Data can also be stored in an electronic
(computer) database

6
 What is a database?
 A database is a systematic collection of data.
 It can consist of tables, queries, reports, and views.

7
 Basic Database Concepts
 Table
– A set of related records.
 Record
Name: Barry Harris
– A collection of data about an College: CST
individual item. Matric No.:
10AH0210
 Field
Name: Barry Harris
– A single item of data common

to all records. 8
Basic Database Concepts

9
Advantages of Computer Database over Flat files

 Massive Storage: the largest computer database

can fit on a hard disk, but a paper database might
take a roomful of file cabinets
 Fast Retrieval: Searching for a record in a
computer database is fast and easy

10
Advantages of Computer Database over Flat files

 Reporting: A report can help you make sense out of

your data, such as showing a list of customers who
earn a certain amount of money OR individuals
who come from a particular state of origin

11
 Why a Database is important?
 Data integrity: This ensures the correctness of data
especially when changes are made.
 Security: This ensures that access to the data is
restricted to those that actually have the right to
access the database

12
 Why a Database is important?
 Share ability: It makes information readily
available to other users who require it.
 Data redundancy: Using a database ensures that
there is little or no data duplication.

13
 What is a database management system (DBMS)?

 A DBMS is a software used for creating,

modifying, accessing and presenting the data in a
database.

14
 Types of DBMS
 Hierarchical DBMS
 Network DBMS
 Relational DBMS
 Object-Oriented DBMS

15
 Relational DBMS
 This is the focus of this lecture.
 Some well known Relational DBMS examples include:
– Oracle,
– MySQL,
– Microsoft SQL Server
– Microsoft Access

16
 Microsoft Access
 Microsoft Access performs the following basic
tasks.
– Creates tables and their relationships.
– Enters and modifies data
– Creates query to retrieve information.
– Creates forms and reports.

17
 Microsoft Access
 Access 2007 and above databases end with .accdb
file extension – for example, sales.accdb
 Databases made in older versions of Access have
the file extension .mdb

18
 Microsoft Access
 Tables: Tables are used for data entry and edit. In a table, each record is
displayed as a row and each field is displayed as a column.
 Queries: You use queries to extract records that meet specific selection
criteria e.g. all students in 100 level. When you run a query, the results
are arranged in columns and rows like a table.
 Forms: You can use forms to provide an alternative to tables for data
entry and viewing records.
 Report: They can be used to produce various printed outputs from data
in your database.
19
 Starting Microsoft Access
• To open a database when you start Access
– Choose the database you wish to open from
the left hand panel, or choose More… to
browse for another database

• To Create a database, click on the Blank

Database at the top of the middle panel

• You can also use the Microsoft button to

open existing databases or create new
ones.

20
Exploring the Microsoft Access Window

21 21
 Working with Tables
 A table is made up of fields and records as you know in excel.
 There are two basic methods of that can be used in creating a
table.
– Data sheet view.
– Design view.

– In Datasheet view, a table is similar in appearance to an Excel

worksheet in that data is stored in rows (records) and columns (fields).
22
 Working with Tables
 The first row contains column headers (field
names). In this format, the table is often simply
referred to as a datasheet.

23
 Identifying fields in a Table
 Key fields and Table relationship

 Primary Key: This is a field that helps to identify each row

uniquely in a table. It ensures that there are no duplicate rows.
 Foreign Key: This is the primary key of one table appearing on
another table for the purpose of creating a relationship.
 Composite Key: A composite key is a primary key consisting of
more than one key.
24
Example of Primary and Foreign Key
Publisher ID Publisher Name Address
PUBLISHER TABLE
P001 Evans Ikeja
P002 Light House surulere
Primary Key P003 Dominion Ota
P004 Longman Mushin Foreign Key

Book Code Title Author Publisher ID

BOOK TABLE
BC001 Piercing the Jack Rere P001
darkness
BC002 Stand Alone Johnson James P002
BC003 Almighty Jonah Janah P003
BC004 Love Sam Akin P004

25 25
 Example of Composite Key
Customer Date Item Id Qty Total Cost
ID
C01 22/05/04 I03 4 N4000

C12 01/03/04 I01 2 N40

C06 30/06/03 I05 10 N3000

Composite Key
Considering the transaction table above, customer ID and item ID
together identify a row uniquely
26 26
 Creating a Table in Datasheet View
 Click the Create tab on the Ribbon
 In the Tables group, click the Table button
 Accept the default ID primary key field with the AutoNumber
data type, or rename the field and change its data type, if
necessary
 Double-click the Add New Field column heading, and then type
the name for the field you are adding to the table
 Press the Tab key or the Enter key
27
Creating a Table in Datasheet View

28 28
 Saving a Table
 Click the Save button on the Quick Access Toolbar.
The Save As dialog box opens
 In the Table Name text box, type the name for the
table
 Click the OK button

29
 Creating Tables in Design View
 Creating a table in Design view, gives one more control over
the database design.
 First create the new table’s structure in Design view, and then
switch to Datasheet view to enter data.
 In Design view, the Object window consists of two panes.
 The Field Entry pane, located at the top of the window, is
used to enter each field’s name, the data type and description.

30
 Creating Tables in Design View
 The Field Properties pane, located at the bottom of the
window, is used to specify the field’s properties.

31
 Creating Tables in Design View
 Access Data Types
Data Type Usage
Short Text Text is the default data type. Text fields accept either text or numeric
data, including delimited lists of items (up to 255 characters).
Long Text large amounts of text and numeric data can be entered into this type of
field (up to 65,538 characters).
Also, if the field is set to support rich text formatting, you can apply the
types of formatting that you normally find in word processing programs
such as Word.

32
 Creating Tables in Design View
 Access Data Types
Data Type Usage
Number Only numbers can be entered in this type of field, and calculations can be
performed on the values in a Number field.
Date/Time Only dates and times can be entered in this type of field.
Currency Only currency values can be entered in this type of field. You do not have
to manually enter a currency symbol.
By default, Access applies the currency symbol ($, ¥, £, and so on)
specified in your Windows regional settings.

33
 Creating Tables in Design View
 Access Data Types
Data Type Usage
AutoNumber Data cannot be enter or changed in this type of field. Access increments
the values in an AutoNumber field whenever you add a new record to a
table.
Yes/No When a field is set to this data type, Access displays either a check box or
a drop-­‐down list, depending on how you format the field.
If you format the field to show a list, you can select either Yes or No, True
or False, or On or Off from the list, again depending on the format applied
to the field.
You cannot enter values in the list or change the values in the list directly
from a form or table.
34
 Creating a Table (Example)
 Create a table in Access (named StudentInfo table)
Let’s assume the following:
 The school has offer the following programs:
Name Field  Accounting (ACC)
Matric No Field  Mass Communication (MAC)
Department Field
 Petroleum Engineering (PET)
Program Field
 Estate Management (ESM)
 Biochemistry (BCH)
 Computer Science (CSC)

35
 Using Validation Property
 With a validation, Access tests the incoming data to make
sure that it’s what you want in the table. If the data isn’t right,
the validation displays an error message.

36
 Steps in Validating a Field
 Open the table for which you want to validate
records.
 Click on the Fields tab, navigate to Field
Validation Rule under Validation, type in the rule
and then click OK.
 Use the Expression Builder to create the rule.
37
 Validation Property (Example)
 Add two new fields (Age and Sex), making sure the
following validation rules are enforces for each field.
– Age should be between 15 and 26
– The entry for sex must be “M” or “F”

38
 Create Calculated Fields
 Calculated fields let you perform calculations with the data in your tables.
Calculated fields perform calculations using data within one record.
 Select the Fields tab, locate the “More Fields” drop-­‐down command.

39
 Creating Relationships in Tables
 A Relationship describes the type of link between two
tables as supported by a specific join property.

40
 Relationship Types in Tables
 One-to-many: Implies that a record in one table is related to many
records in a second table.

 One-to-one: Implies that a record in one table is related to just one

record in the second table. e.g. one man married to only one wife.

 Many-to-many: Implies that many records in one table are related

to many records in the second table.
41
 Creating a Simple Query
A query is a question you ask about the data stored in a database
The Simple Query Wizard allows you to select records and fields
quickly

42
Creating a Simple Query

43
 Creating a Query (Example)
 On the StudentInfo table, let’s do the following:
– List the names and matric numbers of the students
– List the students showing the name, dept. and sex fields
– Show all the details except that of students more than 20 years of
age
– Display only the male students
– Display only the female students
– Display only the girls in accounting
44
 Creating a Simple Form
 A form is an object you use to enter, edit, and view
records in a database
 You can design your own forms, use the Form
Wizard, or use the Form tool to create a simple
form with one mouse click

45
Creating a Simple Form

46 46
 Form Views
 In a form, there are three views:
– Form View
– Layout View
– Design View
 You can switch between these three views. The form view enables
users to edit current data in a table.
 Users can easily between the three views by navigating through
Form Layout Tools -> Design -> View on the tool bar (Access 2013).

47
 Working with append queries
 Append queries are used to copy data from one or
more columns in a table to another table.
 The destination table can be in the same database
or not.
 They are just like normal queries but only that they
are used to copy data from a table to another.
48
Steps in creating append queries

49 49
1- Create a normal query and choose the required fields

50
 2 – Switch to the design view of the query

 Use the criteria filter if it is required for the query

51
3 - On the Design tab, under Query Tools, click Append.

 Select the destination table name from the drop-

down based on the database chosen.

52
4 – Select the destination field for each source field

53
 5 – Click “run” to run the query
 Before clicking run, you can switch to the
datasheet view of the query to see the preview
(potential result).
 Click “run” if you are satisfied with the preview.

54
 Creating a Table
 Class Work
 Create three tables in Access (named StudentInfo
table and StudentScores table, from scratch.

55
 Creating a Table

StudentInfo Table StudentScores Table

Name Field Name Field

Matric No Field Matric No Field
Department Field MidSemester Field
Program Field Exam Field
Sex Field Total Field
Age Field

56
 Creating a Simple Report
 A report is a formatted printout (or screen display)
of the contents of one or more tables in a database
 The Report tool places all the fields from a
selected table (or query) on a report, making it the
quickest way to create a report

57
Creating a Simple Report

58 58
Class Project

59 59
 Class Project
 Inventory Control Management Database Project
 Student Record Keeping System Database Project
 Online Retail Application Database Project
 College Database Project
 Railway System Database Project
 Hospital Management System Database Project
 Library Management System Database Project
 Payroll Management System Database Project
 Health Care Organization Database Project
60
 Class Project
 Restaurant Management Database Project
 Blood Donation System Database Project
 Art Gallery Management Database Project
 Hotel Management System Database Project
 School Management System Database Project
 Wholesale Management System Database Project
 Salary Management System Database Project
 ATM Management System Database Project
61