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.

What is a Database?

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 should be saved.

Why are databases important?

Databases allow us to store and retrieve large amounts of data. The data can be sorted and can be interpreted into useful information. 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 divisions of the University.

Databases reduce data redundancy and data inconsistency, problems that are usually faced by those who store data on spreadsheets.

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.

Types of DBMS and Products

A business will need a Database Management System (DBMS) to store, manage, organize 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 dept. 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 sublanguages 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
• Design Data Warehousing and Business Intelligence platform
• Build database scheme, tables, procedures and permissions
• Set up data sharing and disk partitioning
• Develop database utilities and automated reporting
• Create shell scripts for task automation
• Create, test and execute data management languages
• Analyze and sustain capacity and performance requirements
• Analyze, consolidate and tune database for optimal efficiency
• Monitor systems and platforms for availability.
• Oversee backup, clustering, mirroring, replication and failover
• Restore and recover corrupted databases
• Install and test upgrades and patches
• Implement security and encryption
• Provide 24/7 support for critical situations
• Evaluate and recommend new database technologies

A person pursuing a career as 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. COMP 3380 - Database Concepts and Usage
2. COMP 4380 - Database Implementation
3. And one of: COMP 4710 - Introduction to Data Mining or COMP 4740 - Advanced Databases