Transcript Database Fundamentals - School of Computing Homepage

ITE 370: Database Fundamentals
Database Fundamentals
ITE 370
University of South Alabama School of CIS
Last Modified: 7/17/2015
1
ITE 370: Database Fundamentals
Databases
• A database is a collection of related data which is
organized to meet user needs
• We assume a relational database model
• Relational database design objectives:
– Eliminate redundant data
– Be able to locate individual records quickly
– Make enhancements to the database easy to implement
– Keep the database easy to maintain
University of South Alabama School of CIS
Last Modified: 7/17/2015
2
ITE 370: Database Fundamentals
Entities
• An entity is a class of objects, such as people, products,
or services, about which we collect data. For example,
– Students at the university.
– Employees at a business.
– Sales transactions in a retail business.
– Inventory of parts in a warehouse.
University of South Alabama School of CIS
Last Modified: 7/17/2015
3
ITE 370: Database Fundamentals
Relations and Tables
• A relation is a set of columns and rows collected in a
table-like structure that represents a single entity made
up of related data
• A table is a physical implementation of a relation in a
DBMS
University of South Alabama School of CIS
Last Modified: 7/17/2015
4
ITE 370: Database Fundamentals
Attributes and Columns
• An attribute is a characteristic of an entity
• Examples of attributes
– Names, addresses, phone numbers of people.
– Titles, salaries, job descriptions of employees.
– Time, date, customer id, sales person id in a sales
transaction.
– Part number, Cost, In-stock-quantity of inventory of
parts.
• A column, or field, is a physical implementation of an
attribute
University of South Alabama School of CIS
Last Modified: 7/17/2015
5
ITE 370: Database Fundamentals
Tuples and Rows
• A tuple is set of data whose values make up an instance
of each attribute defined for that relation
• A tuple is implemented physically as a row in a table, or
a record
University of South Alabama School of CIS
Last Modified: 7/17/2015
6
ITE 370: Database Fundamentals
Summary of basic terms
• In relational model, or the logical model
– Relations consist of a set of tuples that define a set of
specific attributes for one instance of the entity
• In the physical implementation of a database
– Relations are implemented as tables
– Attributes are columns or fields
– Tuples are rows or records
University of South Alabama School of CIS
Last Modified: 7/17/2015
7
ITE 370: Database Fundamentals
Keys
• Keys are the attributes that are used to uniquely
identify each instance of an entity, i.e. each row in a
table
• The uniqueness can naturally occur like Social Security
numbers or can be created by the database
management system. For example,
– Student Id
– Employee number
– Part number
– Social Security Number
University of South Alabama School of CIS
Last Modified: 7/17/2015
8
ITE 370: Database Fundamentals
Relationships
• Relationships are established between entities. They
provide the link between tables. These relationships
are usually connected by keys (sometimes called
secondary keys). In both entities, the keys contain the
unique data that relates the two tables together.
Usually, the (primary) key field in one table is linked to
a foreign key in another table.
• Relationships may be
– one-to-one
– one-to-many
– many-to-many (requires intersection table in physical
implementation)
University of South Alabama School of CIS
Last Modified: 7/17/2015
9
ITE 370: Database Fundamentals
University of South Alabama School of CIS
Last Modified: 7/17/2015
10
ITE 370: Database Fundamentals
Normalization
• Rules of normalization are established to organize a
relational database so that
– Data integrity is preserved
– Data redundancy is minimized
• Practical limitations sometimes require that data in a
database be non-normalized
University of South Alabama School of CIS
Last Modified: 7/17/2015
11
ITE 370: Database Fundamentals
University of South Alabama School of CIS
Last Modified: 7/17/2015
12
ITE 370: Database Fundamentals
Brief Introduction to SQL
• SQL, sometimes pronounced “sequel” stands for
Structured Query Language, a widely-supported
database language that has established standards
• SQL is a 4GL? Specifies the “what” rather than the
“how”
• In SQL, we will write queries. A query is a formalized
instruction to a database to either return a set of
records or perform a specific action on a set of records
as specified in the query
University of South Alabama School of CIS
Last Modified: 7/17/2015
13
ITE 370: Database Fundamentals
Types of SQL statements
• There are 3 types of SQL statements
– Data Definition Language (DDL)
– Data Control Language (DCL)
– Data Manipulation Language (DML)
• We will almost exclusively focus on the DML—
statements used to view, add, modify, or delete data
stored in a relational database
University of South Alabama School of CIS
Last Modified: 7/17/2015
14
ITE 370: Database Fundamentals
How are SQL statements executed?
• Four ways. We will focus on two:
• Direct invocation
– From SQL-Server Query Analyzer
– From MS-Access in Query Design
• Call Level Interface (CLI)
– Applications invoke SQL statements at run-time
– SQL statements not precompiled
– Executed directly by RDMBS
University of South Alabama School of CIS
Last Modified: 7/17/2015
15
ITE 370: Database Fundamentals
Direct Invocation via SQL-Server Query Analyzer
University of South Alabama School of CIS
Last Modified: 7/17/2015
16
ITE 370: Database Fundamentals
A CLI-style call in Visual Basic 6.0
• In VB 6.0:
SQL$ = “Select LName, FName From Emp Where Title = ‘Clerk’;”
adoEmp.RecordSource = SQL$
adoEmp.CommandType = adCmdText
adoEmp.Refresh
• Or, another example
adoEmp.Open “SELECT * From Emp “ _
& “Where Title = ‘” & cboTitle.Text & “’”
University of South Alabama School of CIS
Last Modified: 7/17/2015
17