Topic 3: Database

1
The Edge of IT ITEC-200 Fall 2006

• Topic 3 Database
• Professor J. Alberto Espinosa

2
Roadmap
Decision SupportDistributed CollaborationEnterpr
ise CollaborationFinancial Managementetc.
Information
BusinessApplications
Transaction Processing
Client Appl
ServerAppl
ITInfrastructure
DB
DB
Database
IT Infrastrucure - HW SW - Database -
Telecom
IT Business
Business Applications
IT Business
3
Agenda

• Introduction to database and database management
  systems (DBMS) concepts
• Learn how to query databases to extract the
  information you need
• Learn how to design and implement databases

4
Database Database Management System (DBMS)
Concepts
Follow up courseITEC-470 Databases, Data
Mining Knowledge Management
5
Definitions

• Database
• An organized collection of logically related
  datathat can be retrieved on demand
• Database Management System (DBMS)
• Software that manages databases (i.e., define,
  create, update, and query databases)
• Acts as intermediary between business
  applications and physical data files
• Most powerful, scalable, flexible and effective
  business applications rely on a well designed
  database and a powerful underlying DBMS

6
The Old WayData files
Data and program files were separate. You had to
write individual programs to define the data
upload it update it manipulate it and or
retrieve it
Examples
Accounting, Human Resources
Data Files
Application Programs
API1
WindowsUnix, Linux
System Software
INSTRUCTION SET
PC, Mainframe
HARDWARE
7
A Better Way Using a DBMS
A business application passes high level
instructions to the DBMS. The DBMS has
capabilities to do all the necessary data
management data definition, manipulation, and
retrieval. So, the business application does not
have to worry about low level data management
functions
Examples
Accounting, Human Resources,ERP, CRM
Database Application
API2
Oracle, Access, MS SQL Server
DBMS
Database
API1
WindowsUnix, Linux
System Software
INSTRUCTION SET
PC, Mainframe
HARDWARE
8
Advantages of Using Databases DBMSs

• Programs independent of data structure
• Less data redundancy
• Better consistency in the data
• More flexibility scalability
• Easier to integrate share data
• Easier to develop business applications
• Easier to enforce business rules/constraints
• Easier access to data by users (e.g., queries,
  reports, forms, etc.)

9
DBMS in a Client/Server Environment

• Database Server runs back-end part of the DBMS
  to process queries and perform database
  management tasks
• Database Client runs front-end part of the
  DBMS that provides the user interface (e.g., data
  entry, screen displays or presentation, report
  formatting, query building tools)

CLIENT SERVER
Database
Request (ex. query)
Execute query
Front-EndDBMS
Back-EndDBMS
Response(ex. query result)
10
DBMS in a Web Server Environment
Request (ex. get a price quote, place an order)
Response (ex. query results with HTML-formatted
product price or order confirmation notice)
11
Data WarehouseA database that stores and
consolidates current and historical data from
various systems (internal and external) with
tools for management reporting and sophisticated
analysisi.e., Datamining
12
Most Common Database Models

• Hierarchical (of historical interest only)
• Network (of historical interest only)
• Relational
• Object Oriented databases (new)

13
Relational Database

• A database with structured data consisting of
• Tables or entities
• Every table has a unique name
• Ex. Students, Courses
• Fields or columns, attributes
• Every field has a unique name within the table
• Ex. Students (StudentID, StudentName, Major,
  Address)
• Ex. Courses (CourseNo, CouseName, CreditPoints,
  Description)
• Records or rows, tuples, instances
• Every record is unique (has a unique field that
  identifies it)
• Ex. jdoe, John Doe, CS, 5000 Forbes Ave.)
• Ex. MGMT-352-001, MIS, Fall 2002, A great
  course

14
Terminology Equivalence
ERD or Data Model OO Database RelationalDatabase OtherTerms Used
Entity Class Table
Instances Objects Records Rows, Tuples
Relationship Relationship Relationship
Attributes Properties Fields Columns
15
DBMS Functions and Tools

• Performs 3 main functions
• Data definition (define, create databases)
• Data manipulation (data entry, updates)
• Data retrieval (extraction, reports, displays)
• Plus additional database tools
• Data dictionary data about the database
• Visual tools report form design
• Data modeling database design tools
• Macros and programming languages
• Internet/web features, etc.
• Examples
• Oracle, DB2, Visual FoxPro, MS Access MS SQL

16
Define, Create and Manipulate Data in Databases
17
MS Access (structured data)
18
MS Access Data Definition
19
MS Access Data Manipulation
20
Visual FoxPro Data Manipulation
21
Lotus Notes
22
Displaying and Printing Data From
DatabasesForms and ReportsThe idea (1)
organize and store the data in the most efficient
way, without data redundancy(2) retrieve the
data in the way you want to see it (not
necessarily how it is organized internally) using
queries(3) present the results to users and
managers using forms (for data entry) and report
(for printouts)
23
FormsTypically used for data entry screen
displays
24
ReportsTypically used for printouts
25
Database Queries
26
Queries are DBMS Commands for

• Data definition (define, create databases)
• Data manipulation (data entry, updates)
• Data retrieval (extraction, reports, displays)

• Queries are often thought of as a method to
  retrieve data, but queries can also be used to
  define and manipulate data
• Databases can be queried in many ways
• Proprietary DBMS commands and languages, or
• Standard query methods/languages (QBE, SQL, etc.)

27
Standard Query Methods

• Query by Example (QBE)
• Called Design View in MS Access
• Visual interface using examples of data requested
• Similar to how you do searches in the library
• Structured Query Language (SQL)
• Popular with power users
• Works in most DBMS
• Can embed SQL commands in programs, web scripts,
  etc.
• English-like commands (4GL), practical
• Exact, mathematical relational algebra matrix
  math

28
Query by Example (QBE)

• Called Query Design View in MS Access
• Column labels are the fields we want to retrieve
• In table cells we enter examples of the info we
  want

29
8 SQL Commands Only

• Data Definition CREATE TABLE, DROP TABLE
• Data ManipulationINSERT, UPDATE, DELETE, UNION,
  JOIN
• Data RetrievalSELECT (need to learn this only)

30
SQL Commands Data Definition Example Create
Delete Table called Friends

• CREATE TABLE Friends
• (FriendID integer,
• LastName char(24),
• FirstName char(24),
• Birthday date,
• Phone char(10),
• Notes memo)

One SQL Command
End ofCommand
DROP TABLE Friends
31
SQL Commands Data Manipulation

• INSERT Add new records
• UPDATE Modify existing records
• DELETE Delete records
• UNION Combine records from two tables
• JOIN Combine columns from two tables

32
SQL Commands Data ManipulationAdd Update
Records

• Insert (add) a complete record (values in all
  fields)
• INSERT INTO Friends
• VALUES (ae, Espinosa, Alberto,
  12/12/2002, 885-1958, Looks
  tired, needs a vacation)

Insert (add) partial record (values in some
fields only) INSERT INTO Friends (FriendID,
LastName, FirstName) VALUES (ae, Espinosa,
Alberto)
Update (modify) record with new values UPDATE
Friends SET LastNameEspinosa
33
Data Retrieval Queries The Idea

• Organize database (design, create)
• In the most efficient consistent way
  (internally)
• Not based on how you want the data to look
• Produce the virtual tables as you want them to
  look using queries

How we store the data
How we display the data
34
Data Retrieval in SQLThe SELECT Command

• SELECT ltfield list function listgt columns to
  retrieve
• FROM lttable listgt tables that contain the data
• WHERE condition1 which records to retrieve
• AND condition2 .
• ORDER BY field1, .. to sort the query result
• SELECT can be followed by
• DISTINCT (eliminates duplicate rows from
  result)TOP (lists only the top rows of
  result) (lists all fields in the
  table)

35
Complexity of SELECT Queries

• Simple Queries
• Involve a single table
• Complex (Join) Queries
• Involve more than one table

36
Simple SELECT Queries

• SELECT ProdID, ProdName, Type, Price
• FROM Products
• WHERE Pricegt300
• SELECT Avg(Price) AS AvgPrice
• FROM Products
• WHERE Pricegt120
• AND TypePercussion
• SELECT Count() as TotOrders
• FROM Orders
• Click here to download this database local copy

37
Complex SELECT (Join) Queries

• Tables Orders (OrderNo, ClientID, OrderDate,
  OrderStatus)
• LineItems (OrderNo, LineItem,
  ProdID, Qty)

Table Join (2 ways) SELECT Orders.OrderNo,
ClientID, LineItem, ProdID, Qty FROM Orders,
LineItems WHERE Orders.OrderNo
LineItems.OrderNo
Join Condition
Table Product (Don't do this!! Dont forget the
join condition) SELECT Orders.OrderNo,
ClientID, LineItem, ProdID, Qty FROM Orders,
LineItems
38
Database Design
39
Database Design Issue 1Data Integrity in
Databases

• Two main types of data integrity
• Entity Integrity
• Ensuring that every record in each table in the
  database can be addressed (i.e., found) -- this
  means that there each record has to have a unique
  identifier that is not duplicate or null (i.e.,
  not blank)
• Examples every student has an AU ID every
  purchase order has a unique number every
  customer has an ID
• Referential Integrity
• Ensuring that the data that is entered in one
  table is consistent with data in other tables
• Examples purchase orders can only be placed by
  valid customers accounting transactions can only
  be posted to valid company accounts

40
Database Design Issue 2Special Fields (Keys)

• How do you find the record (info) you need?
• Primary key (PK)
• Field(s) that uniquely identifies a
  record(CourseNo, InstructorID)
• Entity integrity PK is not duplicate not
  blank
• PK can be
• A single field (e.g., UserID), or
• Multi-fieldi.e., composite (OrderNo, LineItem)
• Dual key a composite PK with only 2 fields
• How do you link related tables in a database?
• Foreign key (FK)
• A field in a table that is a PK in another table
• That is, a field that must exist in another
  table
• This is how referential integrity is maintained

41
Illustration Primary and Foreign Keys
PK
FK
PK
42
Design Issue 3Steps in Database Design

• Build a Data Model or Entity-Relationship
  Diagram (ERD) for the business application
  this model describes each data element in the
  application and how different data elements
  relate to each other
• Review the data model to ensure entity and
  referential integrity
• Convert (i.e., map) the data model into a
  relational database design
• Convert the data model into a database
  relationship diagram
• Implement Important Data Rules, there are 3 of
  them
• Add update rules (1) dont allow or (2) cascade
  updates
• Add delete rules (1) dont allow or (2) cascade
  updates
• Add business rules e.g., PayDate gt OrderDate
  OrderQty gt 100
• Normalize the database design as necessary and
  update the data model and design if necessary
  i.e., re-organize the tables to avoid recording
  redundant data

43
Data Model Example (Entity Relationship
Diagram--ERD) Course Registration System
Instructors
Course
InstructorID
CourseNo
Teaches
LastName
CourseDescription
1
Many
FirstName
InstructorID
Entities
Telephone
CreditPoints
EMailAddr
PreRequisites
ClassroomNo
1
Relationships
Students
Includes
StudentID
Many
Enrollment
LastName
FirstName
Enrolls
StudentID
SSN
CourseNo
Department
Many
1
College
Comments
Major
EMailAddr
44
Entity-Relationship Diagrams (ERDs) Conceptual
Data Modeling

• Data-oriented modeling method that describes the
  data and relationships among data entities
• Goal capture meaning of the data
• 2 main ERD constructs
• Entities and its attributes
• Relationships between entities

45
Entity

• An object, person, place, event or thing or
  which we want to record data
• Equivalent to a table in a database
• Examples instructors, students, classrooms,
  invoices, registration, machines, countries,
  states, etc.
• Entity instance a single occurrence of an entity
• Example Espinosa, Kogod 39, MGMT-352, 001
• Entities can be identified in a requirements
  analysis description by following the use of
  NOUNS

46
Entity Representation
Entity Name
Peter Chens notation
47
Relationships

• Relationships describe how two entities relate to
  each other
• Relationships in a database application can be
  identified following the VERBS that describe how
  entities are associated with one another
• Examples students enroll in courses
  countries have cities, etc.

48
Cardinality

• Is an important database concept that helps
  understand how two entities are related
• Cardinality describes how many instances of one
  entity can be associated with another entity
• The cardinality of a relationship between two
  entities has two components
• Maximum Cardinality is the maximum number of
  instances that can be associated with the other
  entity usually either 1 or many (the exact
  number is rarely used)
• Minimum Cardinality is the minimum number of
  instances that can be associated with the other
  entity usually either 0 or 1
• Symbols 0 1 Many
• Examples1 student can only park 1 (or 0) cars
  1 to (0 or) 11 client can place (0 or ) many
  orders 1 to (0 or) many1 student can enroll in
  (at least 1 or) many courses and a course can
  have (0 or) many students (0 or) many to (1 or)
  many

49
ERD SYMBOLS (contd.)
Student
EmailAccount
Has
1 to 1
MaximumCardinality(OUTER symbol)
Student
Car
Parks
Mandatory
Optional
MinimumCardinality (INNER symbol)
Peter Chens notationusing Systems Architect
software
50
ERD SYMBOLS (contd.)
? Advises? Have
Advisor
Student
1 to Many
MaximumCardinality
1 to Many (or None)
Faculty
Course
Teaches
Mandatory
Optional
MinimumCardinality
Peter Chens (crows feet) notationusing
Systems Architect software
51
Many to Many Relationships?
(0 or) Many to Many (at least 1)
Orders
Products
Not good for database ? Need to convert
everyMany-to-Many relationship into 2
One-to-Many relationships with a third table
called Intersection Table
Products
Orders
LineItems
1 to Many (at least 1)
1 to Many (or None)
Intersection Table
52
Adding Attributes to ERD
Instructors
Course
PK
InstructorID
PK
CourseNo
Teaches
LastName
CourseDescription
FirstName
FK1
InstructorID
Telephone
CreditPoints
EMail
PreRequisites
Peter Chens notation MS Visio software
53
Steps in ERD Modeling

1. Identify and diagram all ENTITIES
2. Add PK attributes i.e., implement entity
   integrityEnsure PKs are non-null
   non-duplicates
3. Identify and diagram all RELATIONSHIPSand their
   respective CARDINALITIES
4. Add FK attributes i.e., implement referential
   integrity (this is automatic in some toolsMS
   Access)
5. Add remaining attributes

54
ERD ExampleCourse Registration System
Courses (CourseNo (PK), CourseDescripition,
InstructorID, CreditPoints,
ClassroomNo) PreRequisites (CourseNo (PK),
PreRequisiteNo (PK),
Comments) Students (StudentID (PK), LastName,
FirstName, SSN, Department,
College, Major, EMail) Enrollment (StudentID
(PK), CourseNo (PK), Comments) Instructors
(InstructorID (PK), LastName, FirstName,
Telephone, EMail) Classrooms
(ClassroomNo (PK), ClassroomName, Building,
BuildingRoomNo, Equipment,
Capacity) Note PK denotes a primary key
55
Example Course Registration SystemStep 1. Draw
Entities
56
Example Course Registration SystemStep 2. Add
PKs (undeline/separate with a line)
57
Example Course Registration SystemStep 3. Add
Relationships (w/Cardinalities)
PreRequisites
Course
has
Instructors
Teaches
PK,FK1
CourseNo
PK
CourseNo
PK
InstructorID
PK
PreRequisiteNo
Includes
Assigned
Enrollment
ClassRooms
Students
PK,FK1
StudentID
Enrolls
PK
ClassroomNo
PK,FK2
CourseNo
PK
StudentID
58
Example Course Registration SystemStep 4. Add
FKs
Course
PreRequisites
has
Instructors
Teaches
PK
CourseNo
PK,FK1
CourseNo
PK
InstructorID
PK
PreRequisiteNo
FK1
InstructorID
FK2
ClassroomNo
Includes
Assigned
Enrollment
ClassRooms
Students
PK,FK1
StudentID
Enrolls
PK
ClassroomNo
PK,FK2
CourseNo
PK
StudentID
59
Example Course Registration SystemStep 5. Add
Remaining Attributes
Instructors
Course
PreRequisites
Has
PK
InstructorID
PK
CourseNo
PK,FK1
CourseNo
Teaches
PK
PreRequisiteNo
LastName
CourseDescription
FirstName
FK1
InstructorID
Comments
Telephone
CreditPoints
EMail
FK2
ClassroomNo
Assigned
Students
Includes
ClassRooms
PK
StudentID
PK
ClassroomNo
LastName
Enrollment
FirstName
ClassroomName
SSN
Enrolls
Building
PK,FK1
StudentID
Department
BuildingRoomNo
PK,FK2
CourseNo
College
Equipment
Major
Capacity
Comments
EMail
60
EXAMPLEPackage Delivery Tracking System
61
EXAMPLEAirline Reservation System
62
EXAMPLEHW2 Accounting Database(Data Model
diagram using MS Access)
63
ERD ExampleA Music Collection Database
You have such a great music collection that your
friends keep borrowing your music. Things are
getting out of control. You no longer know who
has what, so you decide to create a database to
inventory your music items and keep track of who
has borrowed which items. You analyze the
requirements and come up with the following
entities Artists A list of individual or group
artists in your collection MediaTypes A list of
the different media you possess (CD, VHS, DVD,
etc.) MusicItems A list of the individual CD's,
VHS tapes, etc. you own Suspects A list of
friends and acquaintances who borrowed music
items from you CheckOut A log where you keep
track of who borrowed what You then decide to
collect these attributes for each entity (PK
denotes a primary key) Artists (ArtistID (PK),
ArtistName, Nationality, ArtistRating) MediaTypes
(MediaCode (PK), MediaName, Description) MusicItem
s (ItemNo (PK), Title, ArtistID, MediaCode,
YearRelease, DatePurchased,
MusicType, ItemRating) Suspects (SuspectID (PK),
SuspectName, Address, Telephone) CheckOut (ItemNo
(PK), SuspectID (PK), DateBorrowed (PK),
ReturnExpectedDate, Comments)
64
ERD Example (contd)A Music Collection Database
Your requirements for this application also need
to comply with the following (entities in bold
italics and relationships underlined, note
respective use of nouns and verbs) A music item
(CD, tape, etc.) are recorded by one artist, but
an artist may appear in more than one music item
in your collection (e.g., you have several albums
from Dave Mathews). A music item is associated
with one media type, but a you may have several
music items in a given media type (e.g., you have
several CDs). Over time, suspects can check out
many items (e.g. Alberto has borrowed several
CDs from you), but each check out entry is
associated with only one suspect. Music items
may also be checked out many times over time
(e.g. one of you Dave Mathews albums has been
borrowed several times), but every check out
entry is associated with only one music
item. Please draw an ERD with all entities,
relationships, attributes (please underline PK's)
and cardinalities. If you feel the information
above is incomplete, please make any business
assumptions you need and clearly state
them. Also, describe in words (no need to
diagram) how your design would change if music
items can contain more than one artist.
65
ERD Illustration Music Collection
Artists
MediaTypes
Key Data
Key Data
Associated
By
ArtistID PK1
MediaCode PK1
Non-Key Data
Non-Key Data
ArtistName
MediaName
Nationality
Description
ArtistRating
MusicItems
Key Data
ItemNo PK1
Non-Key Data
Title
ArtistID
MediaCode
YearRelease
DataPurchased
MusicType
ItemRating
Checked Out
CheckOut
Suspects
Key Data
Key Data
ItemNo PK1
Borrows
SuspectID PK1
SuspectID PK2
Non-Key Data
DataBorrowed PK3
SuspectName
Non-Key Data
Address
ReturnExpectedDate
Telephone
Comments
66
What is a good database design?

• Sound data model (ERD)
• Implement Entity Integrity and Referential
  Integrity
• Implement other important rules(note MS Access
  has features to enforce these rules)
• Update Rules what can be updated and when
• Delete Rules what can be deleted and when
• Business Rules needed to conduct business
• The database is Normalized no redundancy

67
Update Rules

• What can be updated/modified in the database and
  when?
• It is OK to update values in any non-PK fields,
  provided that referential integrity and business
  rules are respected
• It is OK to update values in the PK in one table
  if it is not linked to a FK in another table,
  provided that entity integrity, referential
  integrity and business rules are respected
• If a PK is linked to a FK in another table, we
  need to ensure that referential integrity is
  maintained. Depending on what makes sense for
  business and the data itself, either
• Disallow updates of values in the PK, or
• Allow updates, but cascade changes to all
  related FKs in other tables

68
Delete Rules

• What can be deleted in the database and when?
• It is OK to delete records in a table only if
  its PK is not linked to a FK in another table
• If its PK is linked to a FK in another table, we
  need to ensure that referential integrity is
  maintained. Depending on what makes sense for
  business and the data itself, either
• Disallow deletions, or
• Allow deletions, but cascade deletions in all
  related tables that contain a FK linked to this
  table

69
Business Rules

• Most DBMS have features that allow you to impose
  constraints in the data to meet rules imposed by
  a company when conducting business i.e.,
  business rules examples
• CustomerAge gt 18
• OrderQty gt 100
• ProductPrice lt 1000
• PaymentDate lt PurchaseDate 90