Transcript Document

Database Design and
Management
CPTG 424
Functions of a Database

Store data




School: student records, class schedules,
enrollment information.
Manufacturer: customer orders, employee data,
sales data, revenue data, profit data, supply
sources, product inventory.
Query the data
Categorize and sort data
7/20/2015
Chapter 1
2 of 38
Functions of a Database


Track processes
Analyze performance



7/20/2015
Analyze customer behavior
Predict future sales and business patterns
Help make decisions
Chapter 1
3 of 38
Information Center


The database is used as the information center
of business.
One of the most important parts of a business
process.
7/20/2015
Chapter 1
4 of 38

To successfully set up a business, we need to
have a thorough understanding of:



7/20/2015
Your business process.
The technologies involved in database
development and management.
Database theories and technologies
Chapter 1
5 of 38
Database Management System


Even small amount of data is very hard to
manipulate by hand.
Database Management System (DBMS)

7/20/2015
Software to store, manage, and analyze data.
Chapter 1
6 of 38
DBMS Managing Tools








Database configuration
Database administration
Executing query operations
Creating database components
Defining relationships among database components.
Data transformation
Debugging
Networking utilities for a client/server environment.
7/20/2015
Chapter 1
7 of 38
Popular Relational Databases




Microsoft Access
Microsoft SQL Server
Oracle
MySQL (www.mysql.com)
7/20/2015
Chapter 1
8 of 38
MySQL






We will use MySQL for our DBMS.
The world's most popular open source
database.
Easy to use.
Complex enough to handle most of the
enterprise-level database tasks.
Free.
Easy installation.
7/20/2015
Chapter 1
9 of 38
SQL




Structured Query Language (SQL)
A standard interactive and programming language for
querying, modifying data and managing databases.
The core of SQL is formed by a command language
that allows the retrieval, insertion, updating, and
deletion of data, and performing management and
administrative functions.
Both an ANSI and ISO standard, but many database
products often has proprietary extensions.
7/20/2015
Chapter 1
10 of 38
SQL Query Language

To effectively retrieve data, a DBMS uses a
query language to select only those data
requested by the user.

7/20/2015
SELECT * FROM AUTHORS
Chapter 1
11 of 38
XML

Integrated programming language to extend
the DBMS’ data management ability.
7/20/2015
Chapter 1
12 of 38
Data Analysis

Tools for analyzing data.
7/20/2015
Chapter 1
13 of 38
Data Mining


Data-mining tools are used to analyze patterns
of business data.
The patterns can be used to model the behavior
of business processes.
7/20/2015
Chapter 1
14 of 38
Metadata




Metadata services to manage the structure and
the meaning of data.
Metadata are also called the data about data.
Metadata are normally stored in a specific
table called a system table.
You can use metadata to find all the tables that
contain specific data types, such as Date, or all
the columns that have specific names.
7/20/2015
Chapter 1
15 of 38
Security



A database must have a dependable security
system.
User authentication.
Information encryption.
7/20/2015
Chapter 1
16 of 38
Data Transformation Services


Once a database table is created, it will be
populated with data.
Tools for copying data between data sources
such as databases, spreadsheets and text files.
7/20/2015
Chapter 1
17 of 38
Backup and Restore

Tools for backup and restore of database


7/20/2015
Either partial or full.
User interface, ease of data access.
Chapter 1
18 of 38
User Interface





7/20/2015
Clean user interface
User friendly
Ease of data access
Logical positioning of information
Who can best provide information about the user
interface?
Chapter 1
19 of 38
Database Components
Tables.
 Diagrams.
 Views.
 Stored procedures.
 Triggers
 Indexes.
 Users.
 Roles.
 Rules.
7/20/2015

Chapter 1
20 of 38
Tables




Data are stored in tables.
Each table is used to store data related to the
same object such as student.
A table is constructed with columns and rows.
Each column represents an attribute that is
used to describe the object.
7/20/2015
Chapter 1
21 of 38
Diagrams




Most relational databases have multiple tables.
To make the tables work together,
relationships must be correctly established
among these tables.
This is a task of database design.
A diagram is a convenient tool for creating the
relationships.
7/20/2015
Chapter 1
22 of 38
Views


Sometimes an application (such as a form or a
report) needs data from selected columns from
multiple tables.
A view is a database component that is
constructed to contain the data from a set of
selected columns from one or more tables.
7/20/2015
Chapter 1
23 of 38
Stored Procedures



Sometimes you may want to let a DBMS run a
set of SQL statements to accomplish a task.
A stored procedure is a precompiled set of
SQL statements stored in a database server.
The processing of a database can be faster if
the often-used tasks are programmed as stored
procedures.
7/20/2015
Chapter 1
24 of 38
Triggers



A trigger is a specific kind of stored
procedure.
It is executed automatically when a specific
database action occurs.
For example, when a table is modified by
inserting a new record.
7/20/2015
Chapter 1
25 of 38
Indexes


To speed up the search for specified data, a
DBMS provides a component called an index.
By using an index, the search will look only at
an appropriate column to match a given index
key.
7/20/2015
Chapter 1
26 of 38
Users



The user component defines users in a
database.
Many users may share the same database
object, such as a table.
Each user account can be configured to have
ownerships and permissions on the objects.
7/20/2015
Chapter 1
27 of 38
Roles


When a group of users performs the same kind
of job, you can create a role and grant
appropriate permissions to that role.
The role component allows you to group users
with the same set of permissions.
7/20/2015
Chapter 1
28 of 38
Constraints



Constraints are used to specify characteristics
of data in a column. There are integrity
constraints and value constraints.
Integrity constraints are used to define
relationships among tables.
Value constraints allow a user to specify, for
example, if a column can take a NULL value,
or if the value should be unique.
7/20/2015
Chapter 1
29 of 38
Rules


Rule components are used to restrict values
entered in a column.
Rules specify the range of valid values that can
be entered into a column.
7/20/2015
Chapter 1
30 of 38
Defaults

Defaults specify the default value in a column.
7/20/2015
Chapter 1
31 of 38
User-Defined

Can create user-defined database objects such
as user-defined data types.
7/20/2015
Chapter 1
32 of 38
Database Development Process




Before a database can be built…
Need to understand how the database will
achieve its goals.
Whether it will solve your specified problems.
A well-designed database is based on a good
understanding of the business process.
7/20/2015
Chapter 1
33 of 38
Database Development Process


Step 1 – Identify business requirements and
goals to be achieved.
Gather information from users.
7/20/2015
Chapter 1
34 of 38
Step 1: Gather Information






What are the business requirements for the
future database?
What information needs to be stored in the
database?
How can the data be stored?
How can the data be presented to the end
users?
Need to prepare the agreements and contracts.
The timeline to fulfill those agreements.
7/20/2015
Chapter 1
35 of 38
Step 2: Data Model




Once you have figured out what information
you want to store and keep track of, the next
step is to understand the relationships among
the data.
Use the data modeling process.
Translate the data model to database tables
with integrity constraints.
Specify the tables and columns to organize the
business data into a well-defined structure.
7/20/2015
Chapter 1
36 of 38
Step 2: Data Model


Need to make sure that the database design
team and their business partners are all
satisfied.
Usually several modifications are needed.
7/20/2015
Chapter 1
37 of 38
Step 3: Implementation






Implement the database with a DBMS
package.
Choose a proper DBMS package.
Create the tables.
Populate them with data.
Test the newly created database
Create user interfaces.
7/20/2015
Chapter 1
38 of 38
Step 3: Implementation







Different ways of accessing the database:
One computer.
Local network.
Via the internet. Network database or Web database.
Simultaneous access for millions of users.
Database may not fit into a single computer.
Database must be partitioned into multiple parts, and
the partitioned database is distributed to multiple
computers – distributed database.
7/20/2015
Chapter 1
39 of 38