Database Management/Introduction

From Wikiversity
Jump to navigation Jump to search

This lesson introduces relational database concepts.

Objectives and Skills[edit | edit source]

Objectives and skills for this lesson include:

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

Readings[edit | edit source]

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

Multimedia[edit | edit source]

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

Activities[edit | edit source]

Northwind[edit | edit source]

  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 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.

Lesson Summary[edit | edit source]

  • A database is an organized collection of data.[1]
  • 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.[1]
  • 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.[1]
  • 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.[2]
  • Rows are also called records or tuples. Columns are also called fields or attributes.[3]
  • 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).[4]
  • DBMS examples include IBM DB2, Microsoft Access, Microsoft SQL Server, MySQL, Oracle Database, and PostgreSQL.[1]
  • 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.[5]
  • A relationship captures how entities are related to one another. Relationships can be thought of as verbs, linking two or more nouns.[6]
  • Entities must have a minimal set of uniquely identifying attributes which may be used as a unique/primary key.[7]

Key Terms[edit | edit source]

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.[8]
database
A database is an organized collection of data.[1]
database administrator (DBA)
Uses specialized software to store and organize data.[9]
database management system (DBMS)
A software system that enables users to define, create, maintain and control access to the database.[1]
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.[10]
entity
A thing capable of an independent existence that can be uniquely identified.[11]
entity-relationship diagram (E-R diagram)
Shows the associations and dependencies between different tables in a relational database.[12]
field
One specific piece of information about a given item (record) in a relational database table (entity).[13]
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.[14]
primary key
A specific choice of a minimal set of attributes (columns) that uniquely specify a tuple (row) in a relation (table).[15]
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.[16]
relational database management system
A type of database system in which data is stored in tables related by common fields.[17]
relationship
The associations and dependencies between entities in a relational database.[18]
row (also called a record or tuple)
Represents a single, implicitly structured data item in a table.[19]
table
A database table is a collection of related data held in a table format within a database. It consists of columns and rows.[20]
tuple
Also called record or row.[21]

See Also[edit | edit source]

References[edit | edit source]