DATABASE TUTORIAL

DATABASE CONCEPTS
 This tutorial will introduce you to the following things:

 The general idea behind a database that includes:-

 Database concepts
 Advantages & Disadvantages of DB.
 Planning a new database
 Creating a database template

 Adding records to a new or existing database

 Viewing the data in a database

 Clearing entries and records from a database

 Making a backup copy of a database, etc.

Database Concepts
 A database is an electronic filing cabinet
which makes it easy for you to quickly work
with the data
Sort them,
Create reports,

Merge the data with other documents, and

so forth.

 It is a collection of data & set of rules that

organize the data.
Database - Advantages & Disadvantages

 Advantages
 Reduced data redundancy.
 Reduced updating errors and increased consistency.
 Greater data integrity and independence from applications
programs.
 Improved data access to users through use of host and query
languages.
 Improved data security.

 Reduced data entry, storage, and retrieval costs.

 Facilitated development of new application programs.

Etc…….
DISADVANTAGES

 Database systems are complex, difficult, and

time-consuming to design.
 Substantial hardware and software start-up

costs.
 Damage to database affects virtually all

application programs.
 Extensive conversion costs in moving form a

file-based system to a database system.

 Initial training required for all programmers

and users.
HOW IS A DATABASE ORGANIZED BY MS OFFICE ACCESS?

 Access is an object-oriented relational database

management system.

 It is designed to help an organization such as a

company or an institution, even an individuals.

 To collect, organize, manage, and access data in

such a way that it can be easily accessed and
made available to users in a variety of useful
ways.
KEY WORDS IN MS ACCESS DATABASE

 Table:- is a collection of data about a specific

topic, such as employee information, products or
customers.
 Record:-the row in a table that contain information
about a given person, product, or event.

 Field:- the columns in a table that contain a

specific piece of information like the city, state, or
phone number.
CONT.…
 Query:- the ability to access the data by asking
questions.
 Form:- attractive layouts used for entering or
looking up records in a table.
 Primary key:- a unique identifier for a record.
 Data type:- specifying the type of data a certain
field will hold.
 Design View:- a view that allows you to
manipulate how data will be created, stored, and
display on the screen and in reports.
CONT.…
 Datasheet view:- a view of a table that is much like
a spreadsheet . It allows you to enter in your
information quickly.
 Report view:-once you create a report, you need to
turn it to report view to preview it on the screen.
 Form view:- once you create a form, you need to
turn it to form view to enter data, or use if for
lookup.
 Report wizard:- a tool that allows you to create a
report by simply choosing what table and fields in
the table you want to display
UNDERSTANDING FIELDS AND THEIR DATA TYPES

Format Use to display

Field An element of a table that contains a specific item of information, such as a last name. Field’s Data Type -
determines what kind of data the field can store.
Text Short Alphanumeric values, such as a last name or a street address.
Memo Long blocks of text. A typical use of a Memo field would be a detailed product description.

Number Numeric values, such as distances. Note that there is a separate data type for currency.

Date and time values for the years.

Date/Time
Currency Monetary values.
AutoNumber Unique value generated by Access for each new record.

Yes/No Yes and No values and fields that contain only one of two values.
OLE Object Pictures, graphs, or other ActiveX objects from another Windows-based application. Hyperlink
Text or combinations of text and numbers stored as text and used as a hyperlink address.

Attachment Images, Spreadsheet files, documents, charts, and other types of supported files attached to the records in your
database, similar to attaching files to e-mail messages.

Calculated Results of a calculation. The calculation must refer to other fields in the same table. You would use the
Expression Builder to create the calculation.
Lookup Wizard Displays either a list of values that is retrieved from a table or query, or a set of values that you specified when
you created the field. The Lookup Wizard starts and you can create a Lookup field. The data type of a Lookup
field is either text or number, depending on the choices that you make in the wizard.
DATABASE RELATIONSHIPS

 A relationship, in the context of databases, is a

situation that exists between two
relational database tables when one table has a
foreign key that references the primary key of
the other table.

 Relationships allow relational databases to split

and store data in different tables, while linking
disparate data items.
CONT.…
In the relational model, data is
organized as a collection of
RELATIONS or tables.

Relations is a set of ATTRIBUTES or

columns.

Each row (or record) of a relation is

called a TUPLE.
CONT.…
 Within every relation, need to uniquely
identify every tuple:-

 A primary key:- of a relation is a unique and

minimal identifier for that relation.
 Can be a single attribute - or may be a choice of
attributes to use.

 When primary key of one relation used as

attribute in another relation it is a foreign key
in that relation.
CONT.…
 There are 3 types of
relationships in relational
database design. They are:

1) One-to-One
2) One-to-Many (or Many-to-One)
3) Many-to-Many
ONE-TO-ONE

 A row in table A can have only one matching row in

table B, and vice versa.

 This is not a common relationship type, as the data

stored in table B could just have easily been stored
in table A.
 However, there are some valid reasons for using this
relationship type.
 A one-to-one relationship can be used for security
purposes, to divide a large table, and various other
ONE-TO-MANY (OR MANY-TO-ONE)

 This is the most common relationship

type.
 In this type of relationship, a row in
table A can have many matching rows
in table B, but a row in table B can
have only one matching row in table A.
MANY-TO-MANY

 In a many-to-many relationship, a row in table A

can have many matching rows in table B, and vice
versa.
 A many-to-many relationship could be thought of
as two one-to-many relationships, linked by an
intermediary table.

 The intermediary table is typically referred to as a

“junction table” (also as a “cross-reference table”).
 This table is used to link the other two tables
together.
 It have two fields that reference the primary key of
each of the other two tables.
WHAT IS SQL?

 SQL (Structured Query Language) dealing with relational

database, such as MySQL, Oracle, MS SQL server, Sybase
etc.

 SQL is not case sensitive

 For a query language to be SQL at least it has to support

SELECT, UPDATE, DELETE, INSERT and WHERE.

 SQL is currently the standard query method of all major

DBMS.
BASIC SQL COMMANDS

 Basic SQL commands can be grouped into two:

1. Data Definition Languages (DDL)

 DDL commands

 Allow you to create, alter and delete objects (e.g

tables, views) and also to grant and revoke

privileges.
 CREATE, ALTER AND DROP.
2. DATA MANIPULATION LANGUAGES (DML)

 DML commands
 Manipulate and query data in existing
tables.

 The DDL part of SQL permits database

tables to be created or deleted.

 INSERT, SELECT, UPDATE, DELETE

CREAT ETABLE

 CREATE TABLE table_name(column_name1 data_type,

column_name2 data_type, ...)

 Example
1. CREATE TABLE student(sid NUMBER PRIMARY KEY NOT
NULL,
snameVARCHAR2(20),
sex CHAR(1),
bdate DATE);
2. CREATE TABLE department(did NUMBER,
dnameVARCHAR(20),
PRIMARY KEY(did));
MODIFY TABLE STRUCTURE

 To add an attribute/ column

 ALTER TABLE table_name ADD column_name

datatype
 Example.
 ALTER TABLE department ADD faculty

varchar(20);
TO REMOVE AN ATTRIBUTE/ COLUMN

 ALTER TABLE table_nameDROP COLUMN column_name

 Example:-
 ALTER TABLE department DROP COLUMN faculty;

 To specify a primary key to a table

 ALTER TABLE table_name ADD PRIMARY
KEY(column_name);
 Example:-
 ALTER TABLE student ADD PRIMARY KEY (id);

 Use DESC command to view the structure of the tables

INSERT RECORD

 INSERT INTO table_name VALUES (value1,

value2, value3,....)
 Example:-

 INSERT INTO department VALUES( 001, ‘Comp’);

 INSERT INTO student VALUES( 1, ‘Abebe’, ‘M’,

‘01-aug-2000’,001);

 Don’t forget
 To insert data first to the referenced table and

according to the attributes order

UPDATE RECORD

 UPDATE table_name SET

column_name1=value,
column2=value,...WHERE
column_name=some_value
 Example:-
 UPDATE student SET name=‘Abeba’,

sex=‘f’ where sid=1;

RETRIEVE RECORD

 Selecting All Data

 SELECT* FROM student;
 Selecting Particular Column
 SELECT name, dept FROM student;

 Sorting Data
 SELECT* FROM student ORDER BY name;

 For descending order use the keyword DESC

 SELECT* FROM student ORDER BY name DESC;
RETRIEVE RECORD

 Selecting Particular Rows

 Select * from student where sex=‘f’;
 Select * from student where bdate>’20-
aug-2000’;
 Select * from student where
…………….
OR/AND

 Select * from student where bdate=‘10-jan-1990’

or ‘sid=1’;
 LIKE (Pattern matching)

 Special Characters:
 _ Used to match any single character.
 % Used to match an arbitrary number of
characters.
 Select * from student where sname like ‘%j’;
 Select * from student where sname like ’Abeb_’;
DELETE RECORD

 To delete a single or number of records

 DELETE FROM table_name WHERE

some_column=some_value;

 To delete entire table record

 DELETE FROM table_name;

 Ex.
 DELETE FROM student WHERE name =
‘Abebe’;
DROP TABLE

 DROP TABLE table_name;

 Ex.
 DROP TABLE department;

 Logout SQL
 SQL> quit;
AGGREGATE FUNCTIONS
 Very top-level
 Min:- finds the smallest value in a group
 Max:- finds the largest value in a group
 Avg:- returns the average value for the specified
column of a group
 Sum:- totals a specific column for a group
 Count:- counts all the item in a group

 Ex.
 Select min(sal) From emp;
 Select max(sal)-min(sal) From emp;
LET’S GO TO PRACTICE!