Database Management/Introduction

This lesson introduces relational database concepts.

Objectives and Skills

Objectives and skills for this lesson include:

• Understand database origins and purpose
• Understand database terminology
• Apply database concepts to given data

Readings

1. Wikipedia: Database
2. Wikipedia: Entity–relationship model
3. TutorialsPoint: Introduction to Databases

Multimedia

1. YouTube: What are Database & SQL?
2. YouTube: Introduction to Databases
3. YouTube: Getting Started With Databases
4. YouTube: The Relational Model

Activities

Northwind

1. Review the Database Examples/Northwind database. Answer the following questions based on the information found on the database wiki pages. Do not use a database application. Database applications will be used in future lessons.
2. What fields are contained in the Categories table?
3. What is the primary key for the Customers table?
4. How are the Orders table and the OrderDetails table related?
5. List the CustomerName for all customers in the country 'Italy'.
6. List the ProductName for all products in Category 1 with a price less than $5.00.
7. List the SupplierName for all suppliers from English-speaking countries (Australia, Canada, UK, USA)
8. List the first and last names of all employees having a birthday in September.
9. List the CustomerName and OrderID for all orders placed on 9 September 1996.
10. List the ProductName for all seafood products with a supplier from 'Boston'.
11. List the CustomerName and OrderID for all customers who ordered 'Aniseed Syrup', sorted in alphabetical order.
12. List the ProductName, Quantity, Price, and ExtendedPrice (Quantity * Price) for orders 10344 and 10345.
13. List the first and last names and current age of all employees having a birthday in September.
14. List the OrderID, count of products ordered, and total order cost for orders 10344 and 10345.
15. List the total number of orders and total cost of orders shipped by 'Speedy Express' in February 1997.

Pubs

1. Review the Database Examples/Pubs database. Answer the following questions based on the information found on the database wiki pages. Do not use a database application. Database applications will be used in future lessons.
2. What fields are contained in the Authors table?
3. What is the primary key for the Employees table?
4. How are the Sales table and the Stores table related?
5. List the first and last author names for all authors in the state 'UT'.
6. List the orderID for all orders in storeID 7131 with a quantity greater than 20.
7. List the publishername for all publishers in the European-speaking countries (Germany, France).
8. List the first and last names of all employees having a hire date in June.
9. List the orderid, ord_detail, and title for all orders placed on September 13, 2024.
10. List the first and last employee name for all job descriptions with a publisher name from ‘NY‘.
11. List the storename and orderid for all stores that ordered ‘The Busy Executive's Database Guide‘, sorted in alphabetical order.
12. List the title, quantity, price, and extendedprice (quantity * price) for sales orders P2121 and P723
13. List the first and last names and current number of years for all employees having a hire date in November.
14. List the order number, sum of quantity sales ordered, and total extended cost for orders P2121 and P723.
15. List the total number of orders and total cost of orders shipped by 'Bookbeat' between January - May 2023.

Lesson Summary

• A database is an organized collection of data.
• A database-management system (DBMS) is a computer-software application that interacts with end-users, other applications, and the database itself to capture and analyze data.
• Because of the close relationship between them, the term "database" is often used casually to refer to both a database and the DBMS used to manipulate it.
• A relational database is a database based on the relational model proposed by E.F. Codd in 1970.
• The relational model organizes data into one or more tables (or "relations") of columns and rows, with a unique key identifying each row.
• Rows are also called records or tuples. Columns are also called fields or attributes.
• Generally, each table/relation represents one "entity type" (such as customer or product). The rows represent instances of that type of entity (such as "Lee" or "chair") and the columns represent values attributed to that instance (such as address or price).
• DBMS examples include IBM DB2, Microsoft Access, Microsoft SQL Server, MySQL, Oracle Database, and PostgreSQL.
• An entity may be defined as a thing capable of an independent existence that can be uniquely identified. Entities can be thought of as nouns. Examples: a computer, an employee, a song, a mathematical theorem, etc.
• A relationship captures how entities are related to one another. Relationships can be thought of as verbs, linking two or more nouns.
• Entities must have a minimal set of uniquely identifying attributes which may be used as a unique/primary key.

Key Terms

attribute

Also called a column or field.

column

A set of data values of a particular type, one value for each row of the database.

data integrity

The maintenance and assurance of data accuracy and consistency over its entire life-cycle.

database

A database is an organized collection of data.

database administrator (DBA)

Uses specialized software to store and organize data.

database management system (DBMS)

A software system that enables users to define, create, maintain and control access to the database.

E. F. Codd (Edgar Frank "Ted" Codd)

An English computer scientist who, while working for IBM, invented the relational model for database management, the theoretical basis for relational databases and relational database management systems.

entity

A thing capable of an independent existence that can be uniquely identified.

entity-relationship diagram (E-R diagram)

Shows the associations and dependencies between different tables in a relational database.

field

One specific piece of information about a given item (record) in a relational database table (entity).

index

A data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure.

primary key

A specific choice of a minimal set of attributes (columns) that uniquely specify a tuple (row) in a relation (table).

record

Also called row or tuple.

redundancy

Values repeated unnecessarily in one or more records or fields, within a table, or where the field is replicated/repeated in two or more tables.

relational database management system

A type of database system in which data is stored in tables related by common fields.

relationship

The associations and dependencies between entities in a relational database.

row (also called a record or tuple)

Represents a single, implicitly structured data item in a table.

table

A database table is a collection of related data held in a table format within a database. It consists of columns and rows.

tuple

Also called record or row.

See Also

• Databases
• Wikibooks: Database Design
• Harper College: Relational Databases and Microsoft Access

References

Database Management

Query by Example →