dbms
DESCRIPTION
DBMS with Access 2007TRANSCRIPT
Database Management Systems(DBMS)
Anil [email protected]
Dept. of Computer Science, University of Ruhunahttp:// www.ruh.ac.lk / dcs /
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Learning Outcomes
• Understand the need for database management
• Understand the Database Concepts
• Plan, design and use Microsoft Access databases
• Learn to use SQL for data definition and data manipulation.
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Introduction to Database Concepts
• Why need database management ?
?
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Branch BranchMobile
ATM users
ATM users
Bank
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
File Processing System
• Individual applications maintain own private data files.
• Leads to data duplication (redundancy).
• Data is in different formats• Inflexible • Management is very difficult
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
What is a Database?
A database is an organized collection of related data stored in a common pool that can be accessed by many users for many purposes.
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Student database
• Course name file• Student name file• Marks file• …
A database is a group of related files
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
What is a Database?
• Data collection may be . . .
Electronically Collected Manually Collected
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Key Features of a database
The data is
• input (stored) only once• Organized in some fashion• Accessible effectively and efficiently
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
What is a Database Management System (DBMS)?
• A software system that provides an efficient and convenient environment for
• Storing• manipulating (Change, Delete …)
• accessing data in the database
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Overview of a DBMS
DBMS Data-bases
Data-bases
Modify /Retrieval
(Command)
Results
Users
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Thousands of commercial and non commercial DBMS available in the world
Eg:• MS Access• Oracle• MS SQL Server• MySQL• PostgreSQL• …
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Advantages of using DBMS
• Data duplication and storage space wasting can be avoided
• Data can be shared• Unauthorized access can be
restricted• Recovery from failures
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Characteristics of Modern DBMS
• Query processing• Transaction management• Concurrency control• Database recovery• Database security and authorization• Distributed databases
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Disadvantages of using DBMS
• cost of extra hardware • cost of entering data • cost of training people to use
DBMS • cost of maintaining DBMS• complex
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Controlling & Maintaining a Database
Database Administrator (DBA)
Database
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Duties of the Database Administrator
• Installing and upgrades of database software• Performance tuning• Backup and recovery strategies • Start the database & shutdown the database• Monitoring the database• Give and drop database access to the users• Consultation with developers
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
What is a relational database? • Stores data inside tables• Tables can be joined by a common
field
relational database
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Database Building Blocks
• FieldField represents an attribute, or a characteristic or a piece of information
• Record A record is a group of related fields.
• File/TableA collection of related records
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
What is a Table?
a collection of records about a specific topic.
Eg: a table about collection of Addresses and Contac Numbers
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
What is a Record?
A row in a table
A record (a row)
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
What is a Fields?
A column in a table
A Field (a column)
Microsoft Access
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
What is Access ?• Database management system (DBMS)
• Desktop software that defines a database,
stores the data, supports a query language,
produces reports, and creates data entry
screens.
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Object Types of MS Access database• Table
• Query
• Form
• Report
• Macro
• Module
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
What is a Table in MS Access?A Table allows the user to store a collection of data about a specific topic. This is the fundamental structure in the Access Fields (Columns)
Records (Rows)
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
What is a From ?A form allows a user to add , edit or delete data within the corresponding table.
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
What is a Report ?A report is an efficient way to output your data in a printed format in the way you want it.
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
What is a Query ?A query allows the user to view, change, and analyze data in different ways like combining data from multiple related tables to create a user’s own view.
Select
Who are in Matara
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
What is a Macro ?
A macro is a set of instruction that automates a task you need to do often. When you run the macro, Access carries out the actions in the macro.
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
What is a Module ?
In the rare cases where programming is necessary, you can write modules, like macros, modules allow you to automate and customize Access.
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Starting MS Access
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Creating a Database
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Opening an Existing Database
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Creating Tables
• Using Wizard • Using Design View
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
What is a Wizard?A Microsoft Access tool that asks you questions and creates an object according to your answers.
Wizard presents a series of dialog boxes with group of options, which you can choose .
For example, you can create tables, queries, forms, reports, or data access pages by using wizards.
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
What is a Design View?
• Allow you to create your own. • It can be more flexible and
versatile than wizard-generated
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Creating a Table Using Wizard
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Entering Data in a table
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Exercise
1 to
2 questions in
Assignmen
t one
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
What is a table structure?
A data structure is a specialized format for organizing and storing data.
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
What is a table structure?
App_No Reg_No Name Address
145 CCCT/06/03/001 Abayagunawardana W.H.S. Kottagoda
105 CCCT/06/03/004 Dayananda P.M.M.N. Weligama
120 CCCT/06/03/005 Dhammika Upul P. Habaraduwa
129 CCCT/06/03/006 Gunarathna K.P.S.T. Galle
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Data Types of Microsoft Access
Data type of a field determines the kind of data the field can store. Every field must have a defined data type .
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Data Types of Microsoft Access
• Text• Memo• Number• Date/Time• Currency• Auto Number• Yes/No • OLE objects
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Data Types of Microsoft Access
Text : This field stores text and numbers and any combination of characters. Can contain up to 255 characters
Eg: Name : Text(25)
Memo : A passage of information text or notes that can contain 64000 characters
Eg : Qualification : Memo
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Data Types of Microsoft Access
Number : This can contain integer or decimal numbers
Eg : Salary : Number
Date/Time : Used for date or time values
Eg: BithDate : Date/Time
Currency : Used for monetary values
Eg : Salary : Currency
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Data Types of Microsoft Access
Yes/No : A Yes/No values , represent a logical true or false state
Eg : Permanent : Yes/No
Auto Number : A unique record number, automatically inserted begins with 1
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Data Types of Microsoft Access
OLE Object : Use this type to link pictures, audio, video, or other large objects
Eg : Picture :OLE Object
Hyperlink : Use this type to store the location of a file on your computer, a local network, or the world
wide web
Eg : NetAddress : Hyperlink
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Field Name Field Type Field Size
App_No
Reg_No
Name
Tel_No
App_No
Reg_No Name Tel_No
145 CCCT/06/03/001 Abayagunawardana W.H.S. 041-2259154
105 CCCT/06/03/004 Dayananda P.M.M.N. 041-2250622
120 CCCT/06/03/005 Dhammika Upul P. 041-2223584
129 CCCT/06/03/006 Gunarathna K.P.S.T. 091-2246786
Number
Text
Text
Text
14
25
11
Exercise
Define a
suita
ble
table str
ucture
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Field Properties of a Table
• Formats• ValidationRule and ValidationText• Default Value• Input Mask• Caption• Indexed
Formats
• You can use the Format property to customize the way numbers, dates, times, and text are displayed and printed.
Example– Medium Date : 3-Apr-93.– Long Date : Saturday, April 3, 1993.
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
ValidationRule
• You can use the ValidationRule property to specify requirements for data entered into a record, field, or control. When data is entered that violates the ValidationRule setting, you can use the ValidationText property to specify the message to be displayed to the user.
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Default Value
• Sets or returns the value of an object
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Caption
Specify a label other than field name for Forms and Reports
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Indexed
Speeds up searches
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Input Mask
• You can use the InputMask property to make data entry easier and to control the values users can enter
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Input mask syntax
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Examples
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Exercise
3 to
4 questions in
Assignmen
t one
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Field Name Data Type Length Other Properties
Name
Address
Tel No
Work Place
Designation
Married
Date MakeOut
Field Name Data Type
Length
Other Properties
Name Text 25 Required --> Yes
Address Text 50
Tel No Text 11 Input Mask --> 000-0000000
Work Place Text
Designation Text 25 Input Mask --> >L<???????????????????????
Married Yes/No Default Value --> FALSE
Date MakeOut
Date/Time Format --> Long Date
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Creating Tables Using Design View
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Importing Data to Your Database
Import data from the Employee_Personal_Division Excel file and create a table called Personal Division in your database.
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Link Data from another data Source
Link your database with the Employee_Personal_Division Excel file and name it as “Linked table”
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
• Open your Linked table and enter following data to end of your table
Emp_No
Name Date_Joined Pay_tax
OT_Hours
Des_Code
E044 Gunasekara K. 02/05/2000 No MGR
• Open your Employee_Personal_Division Excel file and observe.
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Setting a Primary Key
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Entering Data in a table 2 The table opens in Datasheet view. Type your data under the relevant fields
1 Double-click the table in the database window
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
What is a Primary Key ?
An attribute that uniquely identifies each row in a table
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
What is a Primary Key ?
One and only E002 value in the Emp_no field
Primary Key field
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Setting a Primary Key
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
What is a Relationship ?
Association between entities (Tables)
=
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Referential integrity
Referential integrity is a system of rules that uses to ensure that relationships between records in related tables are valid, and that you don't accidentally delete or change related data.
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Rules apply when you use referential integrity
• You can't enter a value in the foreign key field of the related table that doesn't exist in the primary key of the primary table.
• You can't delete a record from a primary table if matching records exist in a related table.
• You can't change a primary key value in the primary table, if that record has related records.
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Primary key
Foreign key
Parent tableChild table
Package Table
Customer Table Table
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Cascade Update Related Fields
• If you select the Cascade Update Related Fields check box when defining a relationship, any time you change the primary key of a record in the primary table Microsoft Access automatically updates the primary key to the new value in all related records.
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Cascade Delete Related Records
• If you select the Cascade Delete Related Records check box when defining a relationship, any time you delete records in the primary table, Microsoft Access automatically deletes related records in the related table.
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
What is a Query?
• A query permits you to select records from your database tables that meet certain conditions or criteria.
• Queries can make lists from one or more tables
• You use queries to view, change, and analyze data in different ways.
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Create a simple select query with a wizard
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Query Design Grid
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
operators used in MS Access
Option Description Example
+ Addition [Basic Salary]+2200
- Subtraction
* Multiplication
/ Division
Arithmetic Operators
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Comparison OperatorsOption Description Example
= Equal = Sarath
< Less than
<= Less than or equal to
> Grater than > 1000
>= Grater than or equal
<> Not Equal <> Matara
Between Between two Values Between 100 and 200
In Within a List of values In (Hambantota Matara, Galle)
* Any Character 041-22*
Like Match a Pattern Like Ruw*
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Option Description Example
Logical Operators
AND Both elements of an expression must be true
NOT The expression must evaluate as false
OR At least one element of an expression must be true
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Creating a Query by Hand (Design View)
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Structured Query Language (SQL)
Structured Query Language (SQL) is a standard query language for managing relational databases. SQL allows users to define the data in a database and manipulate that data.
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Data Definition Language (DDL)
• The data definition language is that (DDL) part of a query language responsible for defining and modifying the structure of the database.
• The principal DDL statements in SQL are:
• CREATE TABLE• ALTER TABLE• DROP TABLE• Etc…
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Data manipulation Language (DML)
• Part of a query language responsible for manipulating database objects.
• Some DML commands are:• SELECT• INSERT• UPDATE• DELETE
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Creating a Table Using SQL
CREATE TABLE students( Name text(20),
Address text(20),Marks number );
Name Address Marks
Students
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Inserting data using SQL
INSERT INTO studentsVALUES ('Bimal S.K.','Matara',68);
Name Address MarksBimal S.K.
Matara 68
Students
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
What is a Form?
Forms provide a convenient way to • enter data• view data
in a database.
• Forms also restrict access to the specific fields in the table
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Creating Forms
• Creating Auto Forms • Creating Forms with a wizard • Creating Forms in Design View
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Creating Auto Forms
• Either columnar, tabular, or datasheet layout
• With AutoForm, you select one record source and
• AutoForm creates a form that uses all the fields from the record source
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Creating Auto Forms
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Creating Forms with a wizard
The wizard asks you detailed questions about the • record sources• Fields• Layout• format
you want and creates a form based on your answers
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Creating Forms with a wizard
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Creating Forms in Design View
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Create a Command Button with a Wizard
You use a command button on a form to start an action or a set of actions
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Create a Command Button with a Wizard
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Working with Reports
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Reports
A Report is an effective way to present your data in a printed format
Most of the information in a Report comes from an underlying table, query, or SQL statement which is the source of the Report’s data.
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Auto Reports
AutoReport creates a report that displays all fields and records in the underlying table or query.
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Create following Interface for your ABC Company database using a form Design View
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
• Import data from the Employee_Personal_Division Excel file and create a table called Personal Division in your database.
• Publish data in Designation table with MS Word.
• Publish data in Employee table with MS Excel.
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Importing Data to Your Database
Import data from the Employee_Personal_Division Excel file and create a table called Personal Division in your database.
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
Link Data from another data Source
Link your database with the Employee_Personal_Division Excel file and name it as “Linked table”
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
• Open your Linked table and enter following data to end of your table
Emp_No
Name Date_Joined Pay_tax
OT_Hours
Des_Code
E044 Gunasekara K. 02/05/2000 No MGR
• Open your Employee_Personal_Division Excel file and observe.
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
• Data Analyze• Forms with Command buttons• Reports• Link Tables• Import Data tables• Publish Tables with MS Word/Excel• Mail Merge with MS access• Using Help
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/
CCCT – Dept. of Computer Science University of Ruhuna http://www.ruh.ac.lk/dcs/