Databases

COMP 1010 Home > More About Computer Science

Introduction Databases are used by everyone, everyday without most people actually thinking about it. Every time a person uses their bank card they are accessing a Database. The current research into databases systems is to increase the usefulness of database systems to users by creating, extending, and applying database technology.

By Students

If you already have Microsoft Office installed, Microsoft Access is a good starting point to learn the basics of database design. Also check out the Program a day page to get started on programming fundamentals and basics.

"I worked as a rep for the Auto-Trader and Buy & Sell publications and one of the employees created a "database" of new potential clients. The "database" was created using Microsoft Excel and the file consisted of approximately 10 spreadsheets and over 10,000 rows of clients and their advertisements from our competitors. It was difficult to use because there were many duplicate customers, the information was not up-to-date, among many other problems. If a real database had been used then it would have been less frustrating for the customer service reps that used it and for the employee that maintained it."

What is a Database?

A sample database capturing students currently enrolled in a course and grade.

A database is a structured collection of related data that is usually stored on a computer. The data can be sorted, organized and queried. A database can be seen as a digital filing system; where each folder is a record and each drawer is a table.

Fields, records and tables make up a database. When storing basic information for a person a field would contain their name, street address, city and postal code. Related fields make up a record. For each record there is a primary key, a unique attribute that a record can be identified by, like a student number or a social insurance number. A table is a collection of all related records.

How are databases used?

Databases are usually used to store complex data. Here at the University of Manitoba, at least one database would be used to store student information, such as: student ID number, address, phone number, email, registration, and other important information.

Databases are also used at banks to store account information, airports to store flight information, car rental companies to store client information, as well as other settings where records of data would be saved.

Databases can be queried to show only those records that fulfill certain conditions. For example, you can query the database to show all passengers leaving Winnipeg for Las Vegas during the last week of December. Data obtained from a query can be interpreted into useful information.

Why are databases important?

When data is stored using multiple spreadsheets two problems that would arise would include data redundancy and data inconsistency. Data can become inconsistent if records are not properly maintained on spreadsheets. If an address is changed for a client on one spreadsheet then all other spreadsheets containing that client and their address must be updated. It is also redundant to store a client's address on more than one spreadsheet. Using a database would eliminate both problems, tables can be linked so all data stays consistent and redundancy can controlled.

Databases would also allow information sharing. Using the University example the Registrar's office would share some information from their database with Financial Aid, Libraries, UMSU and other departments in the University. All departments would need a student's first and last name, student number and email. If the departments shared the information, then data redundancy is reduced and the student's information remains consistent throughout the departments.

Database disadvantages

Databases are complex structures and take a lot of time to set up. A database administrator or a database designer is usually hired to design, build and maintain a database. Databases also require special software, which can be costly if you are needing to create a large and complex database.

The X: HP/Oracle Database Machine

Types of DBMS and Products

A business will need a Database Management System (DBMS) to store, retrieve, manage, and secure its data. The two main kinds of DBMSs are desktop DBMS and server DBMS. A business will need to define its requirements before selecting which DBMS to use.

Desktop DBMS are inexpensive, simple and easy to use. Desktop DBMS are designed to run on desktop computers and supports only a few users to access the database. A few examples of desktop databases programs are Microsoft Access, FileMaker Pro and FoxPro. Desktop DBMS are suitable for small businesses that don’t have large amounts of data to manipulate and are on a small budget.

Server DBMS are expensive and more complex to use compared to desktop DBMS. Server DBMS are designed to run on servers and can support many users to access the database. Microsoft SQL Server, Oracle and IBM DB2 are a few examples of Server DBMS programs. Server DBMS are suitable for large corporations that have large data warehouses, have a large budget and have an IT department to support the DBMS.

SQL

Standard Query Language (SQL) is a major component used in relational databases used today. Third-party applications use SQL to access different database platforms (ex. Oracle and Microsoft SQL server). SQL is an ANSI (American National Standards Institute) standard used to access and manipulate databases. Although SQL is an ANSI standard, there are different versions such as Oracle’s proprietary PL/SQL and Microsoft SQL Server’s proprietary Transact-SQL. Some of the major commands (include SELECT, UPDATE, DELETE, INSERT, WHERE) must be supported by the different SQL versions to comply with the ANSI standard.

SQL commands are divided into two sub-languages which are the Data Definition Language (DDL) and the Data Manipulation Language (DML). Database administrators use DDL to create and destroy databases and database objects. The DML is used by database administrators to insert, modify and retrieve data within a database.

Database Administrators and Their Roles

Large companies employ database administrators (DBA) to help manage their server DBMS. DBAs have a large responsibility to ensure that data is accessible to users in an efficient and timely manner. Other responsibilities of a DBA include:

• Design and document database architecture
• Develop database utilities and shell scripts for automating tasks
• Evaluate and configure database to meet performance and capacity requirements
• Manage backup, clustering, mirroring, replication and failover
• Create and maintain access permissions and privileges
• Restore and recover corrupted databases
• Install and test new versions of the DBMS
• Provide security and encryption to the database system
• Document database standards and procedures
• Provide 24/7 database support
• Evaluate and recommend new database technologies
• Maintaining database and ensuring its availability to users

• Train users and answer questions

A person pursuing a career as a DBA must have a computer science degree or IT diploma. In addition, the person must also have extensive knowledge in SQL and relational database design.

Databases at U of M

A database specialization can be added to your degree by completing the following courses:

1. Databases Concepts and Usage (COMP 3380)
2. Database Implementation (COMP 4380)
3. And one of: Introduction to Data Mining (COMP 4710) or Advanced Databases (COMP 4740)

Previous Page: Computer Graphics

Next Page: Human-Computer Interaction