Database Design and Application Development Options

farrightΛογισμικό & κατασκευή λογ/κού

15 Αυγ 2012 (πριν από 5 χρόνια και 2 μήνες)

580 εμφανίσεις

IS4401

Project Technology Issues

Introduction

This seminar covers


Databases


When to use a Database


What Database to use


Development Tools


Visual Studio 2005

Databases


When to use a database


What database to use

When to use a database

Reasons for using a database


Large amount of data


Complex data


Customer requires it


Data must be shared with other applications


Data integrity is an issue


Data are easily organised into tabular form


Saves you the bother of designing your own data
storage mechanism

When to use a database

Reasons not to use a database


Application not dependent on data storage


Very small amount of data


Unstructured or unusually structured data


Proof of concept with limited data storage
requirements


But see slide on hosted databases if it’s a website

Alternatives to databases


Text files


Structured (CSV) text files


Serialised object files


Can be implemented in Java, C# and VB.NET


You can define complex and unusual data structures (eg video)

I need a database:

Now what?

Database Management Systems
(DBMSs)


Provide environment to create, operate and manage
databases


Choices typically open to 4
th

years are:


Oracle 10g


SQL Server 2000


SQL Server 2005 Express (
NEW this year
)


MS Access


Also consider:


MySQL 5.0


Each DBMS has its own advantages and
disadvantages


You need to consider these before making your choice

Database Management

Systems (DBMSs)

How to choose a DBMS


Depends on


Project Type


Customer’s Environment


Proof of Concept projects


Main decision here is if you need a DBMS at all


Depends entirely on project


If you decide to use a DBMS, then
Oracle 10g, SQL Server
2000

or
SQL Server 2005

are the preferred choices


Available on BIS network


Industry standard


Plenty of documentation


Provide better integrity and robustness than MS Access

Database Management

Systems (DBMSs)

How to choose a DBMS


Full System Implementations


Application installed at customer site


Does the customer already have a DBMS?


YES:


Then use the customer’s DBMS if possible, especially if it’s Oracle or
SQL Server.


Use MS Access if the customer insists and if you are satisfied that MS
Access will fulfil the data integrity and performance requirements of
your project.


NO:


Customer is unlikely to want to spend money on a DBMS.


In this case, MySQL or SQL Server 2005 Express may be your best
options because they’re free.


MS Access may also be an option if the customer is willing to buy it and
they want a clear upgrade path to SQL Server.


Do NOT assume customer has MS Access if they have MS Office!
Check!

Database Management

Systems (DBMSs)

How to choose a DBMS


Full System Implementations


Web sites


Where is the website to be hosted?


It should not be on Corvus when the system goes live!


Hosting services provide database as well as web hosting


Find out what your intended hosting service provides


Likely to be Oracle, SQL Server or MySQL


Use the same DBMS for your development system


Note!


Need to identify hosting requirements early in project


Need to plan for migration from development system to hosted
system

DBMS Choices:

The Pros and Cons

Oracle 10g and SQL Server 2000


Pros:


Industry standard DBMSs with lots of advanced features


Either will deliver excellent performance and data integrity


Choice really depends on


Personal preference


Programming knowledge


Customer environment


Hosting environment


Cons:


Expensive


Time
-
consuming to install


Customer unlikely to buy one of these DBMSs for your
project if they don’t have one already

DBMS Choices:

The Pros and Cons

MS Access


Pros:


It’s almost everywhere


bundled with some versions of MS
Office


Reasonably easy to use


Familiar API


Well integrated with MS Office


Clear upgrade path to SQL Server


Cons:


Not suitable for large projects


Not suitable for web applications


Limits on database size and number of users


Prone to error if performance limits approached


DBMS Choices:

Access or SQL Server?

Microsoft issues with Access


2Gb maximum size


File sharing architecture will not handle very large
queries


Unsuitable for web applications


Large databases may become corrupt


Can’t recover/rollback from network problems


Theoretically max 255 users, in practice 20


Imposes heavy load on client computer


Does not scale well on multi
-
CPU platforms


Databases frequently badly designed

DBMS Choices:

Access or SQL Server?


Access

SQL Server

Description

Database development
environment that
supports tables, queries,
forms, reports, and
programming logic
.

Scalable, reliable, and
more
secure client/server
database engine
.

Maximum database size

2 gigabytes (GB)

1 teraby
te

Maximum concurrent
users

Up to 20 concurrent
editors.

Up to 100 concurrent
reports being run.

Unlimited

Security

File access
-
b
as
ed

security
.

Enterprise
-
level security
.

Performance

Limited by file share
model
.

Limited only by hardware

and application
design.

Reliability

Adequate for individuals
and small team usage.

Recovery from network
failures cannot be rolled
back.

High reliability. SQL
Server is a mission
-
critical database.

Backup and
administration tools
available.


Table taken from:


When to Migrate
from Microsoft
Access to Microsoft
SQL Server


SQL Server Technical
Article

Writers: Luke Chung and
Dan Haught

Published: February 2005

DBMS Choices:

Pros and Cons

MySQL 5.0


Pros


Enterprise
-
level DBMS


Very fast


Free


Well documented


Easy to install


Cons


Not currently installed on BIS network


Installation of appropriate drivers and patches can be
confusing and must be carefully considered


Need to be careful with security setup

DBMS Choices:

MySQL 5.0

Why consider MySQL?


Free


Fast


Scaleable


Enterprise
-
grade


Runs on all platforms


Much more robust than MS Access


Very comprehensively documented

So what’s the catch?


Does things a little differently from Oracle and SQL
Server


But these differences are also well documented

DBMS Choices:

MySQL 5.0

What is MySQL?


Originally designed to work with medium
size databases (10
-
100 million rows, or
about 100MB per table) on small
computer systems.


Today MySQL Server handles terabyte
-
size databases


The code can also be compiled in a
reduced version suitable for hand
-
held
and embedded devices.

DBMS Choices:

MySQL 5.0

Differences with other DBMSs


Lots of small differences


SQL implementation is slightly different


Philosophy is to standardise MySQL as
much as possible without sacrificing
performance


MySQL has extensions to SQL that
enhance performance


Differences not likely to matter in a 4
th

year project context

DBMS Choices:

SQL Server 2005 Express


Bundled with Visual Studio 2005


Can also be downloaded separately


Advantages


Alternative to Access


Almost the same as SQL Server 2005


Scaleable


Clear upgrade path


Disadvantages


Size limited


this is not likely to be an issue
with smaller businesses

Free database downloads


The following DBMSes can be
downloaded FREE:


Oracle 10g Express Edition

http://www.oracle.com/technology/software/products/database/xe/index.html


MySQL 5.0 Community Edition

http://dev.mysql.com/downloads/mysql/5.0.html


SQL Server 2005 Express

http://msdn.microsoft.com/vstudio/express/sql/download/


Free database downloads


A final caveat on licenses


As a rule, these databases are free for non
-
commercial use


Commercial use may require purchase of a
license


Check the license terms carefully!

Development Tools


Visual Studio 2005


NetBeans 5.5


Visual Studio 2005


Languages Supported


VB.NET


C#


J#


Technologies


.NET 2.0, ASP.NET 2.0, ADO.NET 2.0


.NET for mobile devices

Features of Visual Studio
2005


Integrated Development Server


Deployment Tools


Partial Classes


Designer Code


For Windows Forms


For Web Pages


And lots more

Visual Studio 2005 Layout

Practical Intro to Visual
Studio 2005


Getting started


Configuring for use with C#


Saving to the H: drive


Create and Build a Web Page


Deploy Web page to an external
hosting service