Transcript Database Introduction and Conceptual Modeling Database Outline:          Types of Databases and Database Applications Basic Definitions. Typical DBMS Functionality Example of a Database Main Characteristics of the Database Approach Database.

Database
Introduction and
Conceptual Modeling
Database
Outline:









Types of Databases and Database Applications
Basic Definitions.
Typical DBMS Functionality
Example of a Database
Main Characteristics of the Database Approach
Database Users
Categories of End-users
Advantages of Using the Database Approach
Additional Implications of Using the Database Approach
Slide 1-2
Types of Databases and
Database Applications
Numeric and Textual Databases
Multimedia Databases
Geographic Information Systems (GIS)
Data Warehouses
Slide 1-3
Basic Definitions
 Database: A collection of related data.
 Data: Known facts that can be recorded and have an
implicit meaning.
 Mini-world: Some part of the real world about which
data is stored in a database. For example, student
grades and transcripts at a university.
 A software package/ system to facilitate the creation
and maintenance of a computerized database.
 Database System: The DBMS software together with
the data itself. Sometimes, the applications are also
included.
Slide 1-4
Example of a Database
(with a Conceptual Data Model)
 Mini-world for the example: Part of a School
environment.
 Some mini-world entities:
–
–
–
–
–
STUDENTs
COURSEs
SECTIONs (of COURSEs)
(academic) DEPARTMENTs
INSTRUCTORs
Slide 1-5
Example of a Database
(with a Conceptual Data Model)
Some mini-world relationships:
–
–
–
–
–
–
SECTIONs are of specific COURSEs
STUDENTs take SECTIONs
COURSEs have prerequisite COURSEs
INSTRUCTORs teach SECTIONs
COURSEs are offered by DEPARTMENTs
STUDENTs major in DEPARTMENTs
Slide 1-6
Typical DBMS Functionality
 Define a database : in terms of data types,
structures and constraints
 Construct or Load the Database on a
secondary storage medium
 Manipulating the database : querying,
generating reports, insertions, deletions and
modifications to its content
 Concurrent Processing and Sharing by a set
of users and programs – yet, keeping all data
valid and consistent
Slide 1-7
Typical DBMS Functionality
Other features:
– Protection or Security measures to
prevent unauthorized access
– “Active” processing to take internal
actions on data
– Presentation and Visualization of data
Slide 1-8
Typical DBMS Functionality
A Database program lets you:
 Store Information
A database stores lists of information that are
related to a particular subject or purpose. A
database stores personal information or business
information, a database is also makes it easy to
update, organize, and delete information.
 Find Information
You can easily and instantly locate information
stored in a database.
Slide 1-9
Typical DBMS Functionality
 Analyze and Print Information
you can perform calculates on information in a
database. For example, you could calculate what
percent of your total sales comes from the state of
Texas. You can also present information in a
professional-looking printed report.
 Manage Information
Databases make it easy to work with and manage
huge amounts of information.
Slide 1-10
Typical DBMS Functionality
 Share Information
most database programs (include Microsoft
Access) allow more than one user to view and
work with the same information at once. Such
databases are called Multi-user databases.
Slide 1-11
Extending Database
Capabilities
 New functionality is being added to
DBMSs in the following areas:
–
–
–
–
–
Scientific Applications
Image Storage and Management
Audio and Video data management
Data Mining
Time Series and Historical Data Management
The above gives rise to new research and development in
incorporating new data types, complex data structures, new
operations and storage and indexing schemes in database
systems.
Slide 1-12
Microsoft Access 2000
Databases usually consist of several parts. A
Microsoft access database may contain up
to six different database object types as
follows:
Slide 1-13
Microsoft Access 2000
Tables
Tables store a database’s data in rows
(records) and columns (Fields).
Queries
Queries ask a question of data stores in
a table. E.g. query might only display
customers who are from Texas.
Forms
Forms are custom screen that provide
an easy way to enter and view data in a
table or query.
Slide 1-14
Microsoft Access 2000
Report
Reports present data in a table or query
in a printable format.
Pages
A special type of webpage designed for
viewing and working with Microsoft
access data from an intranet or over the
internet.
Macros
Macros help you perform routine tasks
by automating them into a single
command.
Slide 1-15
Microsoft Access 2000
Loading the Microsoft Access Program:
Start
Programs
Microsoft Access.
Slide 1-16
Microsoft Access 2000
Components of a Table
Slide 1-17
Microsoft Access 2000
Components of a Table :
 A field: a specific type of information, such as a
last name of your employee.
 A Field Name: A name that identifies the
information in a field.
 A record: A collection of information about one
person or thing.
Slide 1-18
Microsoft Access 2000
To open a table in design view:
Select the table and click design view.
Data Types:
Numeric: which means it contains only numbers.
Character text: numbers, letters and other symbols
can be entered.
Date/Time: Date and time is entered.
Boolean / logical: which allows Y/N or T/F
Slide 1-19
Microsoft Access 2000
 Data types in access:
1. Text: Stores text, numbers, or a combination of both, up to
255 characters long. Text fields are the most common of
all data types.
2. Memo: stores long text entries – up to 64,000 characters
long (equivalent to 18 pages of text). Use memo fields to
store notes or anything else that requires lots of space.
3. Number: stores numbers that can be used for calculation.
4. Date/Time: stores dates/times or both.
5. Currency: stores numbers and symbols that represent
money.
Slide 1-20
Microsoft Access 2000
6. Auto number: Automatically fills in a unique number for
each record. Many tables often contain an auto-number
field that is also used as their primary key.
7. Yes/No: stores only one of two values, such as yes or no,
true of false.
8. OLE object: stores objects created in other programs such
as a graphic, excel sheet, or work documents.
9. Hyperlink: stores clickable links to files on your computer
or on the network of to WebPages on the internet.
10. Lookup wizard: a wizard that helps you create a field
whose values are selected from a table, query or a preset
list of values.
Slide 1-21
Microsoft Access 2000
Entering data to a table:
There are two ways of entering data into a
table:
1- Key in data directly into the table after
opening it.
2- Using a form.
Make sure to proof-read the data entered for
errors.
Slide 1-22
Microsoft Access 2000
Working with Query:
A query is to ask a question of the information in a table
and then retrieve and display the results.
There are two ways to create a query:
1.Create a query in Design View.
2.Create a query using wizard.
Slide 1-23
Microsoft Access 2000
 Creating a query in Design View:
–
–
–
–
–
–
Double click “Create a query in design view”.
The “Show Table” dialog box appears, select the table
you want to query. Then close the box.
Double click on the fields you want to appear on
your query.
Set the criteria by inserting a criteria operator.
Save the query.
Click on it to display the results.
Slide 1-24
Microsoft Access 2000
Slide 1-25
Microsoft Access 2000
 Here is a smattering of criteria operators and
examples to get you started:
Slide 1-26
Operator
Example
Description
=
=”MN”
Finds records equal to MN
<>
<>”MN”
Finds records not equal to MN.
<
<10
Finds records less than 10
<=
<=10
Finds records less than or equal to 10
>
>10
Finds records greater than 10
>=
>=10
Finds records greater than or equal to 10
BETWEEN
Between
1/1/99
AND
12/31/99
Finds records between 1/1/99 and
12/31/99
LIKE
Like “S*”
Finds text beginning with the letter S. you
can use LIKE with wildcards such as
*.
Slide 1-27
Microsoft Access 2000
 A criteria is used to display a certain type of
information on your query taken from the table.
E.g. display only USA countries.
Criteria And OR rows defines one or set of limiting
conditions.
-To define condition with AND insert the criteria on
the same row.
-To define the condition with OR insert the criteria
on the first and second row.
Slide 1-28
Microsoft Access 2000
Query to retrieve record which contain “EU” or “England”
to
country you must insert the criteria on the first and second row.
.
Slide 1-29
Microsoft Access 2000
To make Query to retrieve record which contain “EU”
and “England” country you must insert criteria in the same row.
Slide 1-30
Microsoft Access 2000
 Sorting a Query:
Tables normally display records in the order they
were entered. Instead of working with table’s
jumbles record order, you can create a simple
query that sorts the table information and presents
it in an ordered, easy-to-read display. You can sort
records alphabetically, numerically, or chronically
(by date) in ascending (A to Z) or descending (Z to
A) order. You can also sort by multiple fields.
Slide 1-31
Microsoft Access 2000
To sort the query click the Sort row for
the field you want to use to sort the query
and select either ascending or descending.
Slide 1-32
Report
 Report is printable format that represent data
in a table or query.
 Every report most contains :
 Title : page header
 Field name.
 Field data.
Slide 1-33
Report
 To create the report
 In the Database window, click Reports, and then
double-click Create report by using wizard.
 In the Report Wizard, go to the Tables/Queries
list, and then select Table: Products.
 Click >> to move all of the available fields from
the Available Fields list to the Selected Fields
list, and then click Next.
Slide 1-34
Report
 To accept the default grouping levels, click Next.
 Select Product Name from the first drop-down
list, and then click Next.
 Select the Columnar layout option, ensure that
the Adjust the field width so all fields fit on a
page option is selected, and then click Next.
 Select the Soft Gray style, and then click Next.
 In the What title do you want for your report
box, type the required title, and then click
Finish.
Slide 1-35
Alter the data and update the
report
To alter your report :
Right click on your report name then select
design view.
In design view screen you can modify the
size and location any field and you can add
new field name and his data field which is
calculated at run time
Slide 1-36
Adding new field at run time
For example:
If I ask you to add cost as new field to your
report for calculate the
Price * item
1.From toolbar select text box and drop it in
the specific location, this text box already
has Label and box
Slide 1-37
Adding new field at run time
2. Change the label to Cost
3. Write
=item*price in the box
4. Press Enter ,the function will be change automatically to
=[item]*[price]
5. Save your report.
If u see small green triangle at the left side of the text box
that is means there is error.
Note :your table already has item and price as fields but its
doesn't has cost field.
Slide 1-38