Databases
Week 7
LBSC 690
Information Technology
![](3D"slide0001_image001.jpg")
Agenda
&#=
8226;Questions
&#=
8226;Relational
database design
&#=
8226;Microsoft
Access
Databases
&#=
8226;Database
&=
#8211;Collection
of data, organized to support access
&=
#8211;Models
some aspects of reality
&#=
8226;DataBase
Management System (DBMS)
&=
#8211;Software
to create and access databases
&#=
8226;Relational
Algebra
&=
#8211;Special-purpose
programming language
Structured Information
------=_NextPart_01C5CF5B.5D6BDA10
Content-Location: file:///C:/F533C6D7/690f057_files/slide0054.htm
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html; charset="us-ascii"
•Field An “atomic=
221;
unit of data
–number, string,
true/false, …
•Record A
collection of related fields
•Table A
collection of related records
–Each record is =
one
row in the table
–Each field is o=
ne
column in the table
•Primary Key T=
he
field that identifies a record
–Values of a pri=
mary
key must be unique
•Database A collection of tab=
les
A Simple Example
![](3D"slide0054_image002.gif")
![](3D"slide0054_image003.gif")
primary key
Another Example
&#=
8226;Which
students are in which courses?
=
226;What
do we need to know about the students?
&=
#8211;first
name, last name, email, department
&#=
8226;What
do we need to know about the courses?
&=
#8211;course
ID, description, enrolled students, grades
A “Flat File” Solutio=
n
Discussion Topic
Why is this a bad approach?
![](3D"slide0062_image005.gif")
Goals of “Normalization”
&#=
8226;Save
space
&=
#8211;Save
each fact only once
&#=
8226;More
rapid updates
&=
#8211;Every
fact only needs to be updated once
&#=
8226;More
rapid search
&=
#8211;Finding
something once is good enough
&#=
8226;Avoid
inconsistency
&=
#8211;Changing
data once changes it everywhere
Relational Algebra
&#=
8226;Tables
represent “relations”
&=
#8211;Course,
course description
&=
#8211;Name,
email address, department
&#=
8226;Named
fields represent “attributes”
=
226;Each
row in the table is called a “tuple”
&=
#8211;The
order of the rows is not important
&#=
8226;Queries
specify desired conditions
&=
#8211;The
DBMS then finds data that satisfies them
A Normalized Relational Database<=
/span>
![](3D"slide0065_image007.gif")
![](3D"slide0065_image009.gif")
![](3D"slide0065_image011.gif")
![](3D"slide0065_image013.gif")
Student Table
Department Table
Course Table
Enrollment Table
Approaches to Normalization
=
226;For
simple problems (like the homework)
&=
#8211;Start
with “=
;binary
relationships
&=
#8226;Pairs
of fields that are related
&=
#8211;Group
together wherever possible
&=
#8211;Add
keys where necessary
&#=
8226;For
more complicated problems
&=
#8211;Entity
relationship modeling (LBSC 670)
Example of Join
![](3D"slide0070_image014.gif")
Student Table
![](3D"slide0070_image015.gif")
Department Table
![](3D"slide0070_image017.gif")
“Joined”
Table
Problems with Join
&#=
8226;Data
modeling for join is complex
&=
#8211;Taught
in LBSC 670
&#=
8226;Join
are expensive to compute
&=
#8211;Both
in time and storage space
&#=
8226;But
it is joins that make databases relational
&=
#8211;Projection
and restriction also used in flat files
Some Lingo
•“Primary Key” uniquely identi=
fies
a record
–e.g. student ID in =
the
student table
•“Compound” primary key
–Synthesize a primary
key with a combination of fields
–e.g., Student ID +
Course ID in the enrollment table
•“Foreign Key” is primary key =
in
the other
table
–Note: it need not be
unique in this
table
Referential Integrity
=
226;Foreign
key values must exist in other table
&#=
8211;If
not, those records cannot be joined
&#=
8226;Can
be enforced when data is added
&=
#8211;Associate
a primary key with each foreign key
&#=
8226;Helps
avoid erroneous data
&=
#8211;Only
need to ensure data quality for primary keys
Project
![](3D"slide0075_image018.gif")
New Table
![](3D"slide0075_image020.gif")
![](3D"slide0075_image021.gif")
SELECT Student ID, Department
Restrict
![](3D"slide0076_image023.gif")
![](3D"slide0076_image024.gif")
![](3D"slide0076_image025.gif")
New Table
WHERE Department ID =3D “<=
/span>HIST”
The SELECT Command
&#=
8226;Project
chooses columns
&#=
8211;Based
on their label
&#=
8226;Restrict
chooses rows
&#=
8211;Based
on their contents
&=
#8226;e.g.
department ID =3D “HIST”
&#=
8226;These
can be specified together
&=
#8211;SELECT
Student ID, Dept
WHERE Dept =3D “History”
Restrict Operators
•Each SELECT
contains a single WHERE
–<=
/span>
•Numeric compari=
son
–<, >, =3D, <>, …
•e.g.,
grade<80
–<=
/span>
•Boolean operati=
ons
–e.g., Name =3D =
“John”
AND Dept <> “HIST”
FlightFinder Exercise
=
226;Design
a database to match passengers with
available flights on corporate
jets
&=
#8211;Companies
phone in available seats
&#=
8226;They
want to know about interested passengers
&=
#8211;Passengers
call up looking for flights
&=
#8226;They
want to know about available flights
&=
#8211;These
things happen in no particular order
Exercise Goals
&#=
8226;Identify
the tables you will need
&=
#8211;First
decide what data you will save
&=
#8226;What
questions will be asked?
&=
#8211;Then
decide how to group/split it into tables
&=
#8226;Start
with binary relations if that helps
&#=
8226;Design
the queries
&=
#8211;Using
join, project and restrict
&#=
8226;Add
primary and foreign keys where needed
Exercise Logistics
&#=
8226;Work
in groups of 3 or 4
=
226;Brainstorm
data requirements for 5 minutes
&=
#8211;Do
customers care about the price?
&=
#8211;Do
companies care what passengers weigh?
&#=
8226;Develop
tables and queries for 15 minutes
&=
#8211;Don’t
get hung up on one thing too long
&#=
8226;Compare
you answers with another group
&=
#8211;Should
take about 5 minutes
One Possible Answer
•Surely you didn’t expect th=
is
in the notes :)
Database “Programming”
•<=
/span>Natural language
–<=
/span>Goal is ease of use
•<=
/span>e.g., Show me the last names of students in CLIS
–<=
/span>Ambiguity sometimes results in errors
------=_NextPart_01C5CF5B.5D6BDA10
Content-Location: file:///C:/F533C6D7/690f057_files/slide0084.htm
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html; charset="us-ascii"
•&=
#13;
•<=
/span>Structured Query Language (SQL)
–<=
/span>Consistent, unambiguous interface to any DBMS
–<=
/span>Simple command structure:
•<=
/span>e.g., SELECT Last name FROM Students WHERE Dept=3DCLIS
=
–<=
/span>Useful standard for inter-process communications
•&=
#13;
•<=
/span>Visual programming (e.g., Microsoft Access)
–<=
/span>Unambiguous, and easier to learn than SQL
Using Microsoft Access
------=_NextPart_01C5CF5B.5D6BDA10
Content-Location: file:///C:/F533C6D7/690f057_files/slide0085.htm
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html; charset="us-ascii"
&#=
8226;Create
a database called M:\planes.mdb
&=
#8211;File->New->Blank
Database
&#=
8226;Specify
the fields (columns)
&=
#8211;“Create
a Table in Design View”
&#=
8226;Fill
in the records (rows)
&=
#8211; Double-click on the icon for the t=
able
Creating Fields
&#=
8226;Enter
field name
&=
#8211;Must
be unique, but only within the same table
&#=
8226;Select
field type from a menu
&=
#8211;Use
date/time for times
&=
#8211;Use
text for phone numbers
&#=
8226;Designate
primary key (right mouse button)
&#=
8226;Save
the table
&=
#8211;That’s
when you get to assign a table name
Entering Data
&#=
8226;Open
the table
&=
#8211;Double-click
on the icon
&#=
8226;Enter
new data in the bottom row
&=
#8211;A
new (blank) bottom row will appear
&#=
8226;Close
the table
&=
#8211;No
need to “save” – data is stored automatically
Building Queries
•Copy
N:\share\notes\plane.* to M:\
•“Create Query in Des=
ign
View”
=
–In “Queries”&=
#13;
•Choose two tables,
Flight and Company
•Pick each field you
need using the menus
–Unclick
“show” to not project
–Enter a criterion t=
o “restrict”&=
#13;
•Save, exit, and
reselect to run the query
Fun Facts about Queries
------=_NextPart_01C5CF5B.5D6BDA10
Content-Location: file:///C:/F533C6D7/690f057_files/slide0089.htm
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html; charset="us-ascii"
&#=
8226;Joins
are automatic if field names are same
&=
#8211;Otherwise,
drag a line between the fields
&#=
8226;Sort
order is easy to specify
&=
#8211;Use
the menu
&#=
8226;Queries
form the basis for reports
&=
#8211;Reports
give good control over layout
&=
#8211;Use
the report wizard - the formats are complex
Other Things to Know
=
226;Forms
manage input better than raw tables
&=
#8211;Invalid
data can be identified when input
&=
#8211;Graphics
can be incorporated
Key Ideas
&#=
8226;Databases
are a good choice when you have
&=
#8211;Lots
of data
&=
#8211;A
problem that contains inherent relationships
<=
/span>
&#=
8226;Design
before you implement
&=
#8211;This
is just another type of programming
&=
#8211;The
mythical person-month applies!
&#=
8226;Join
is the most important concept
&=
#8211;Project
and restrict just remove undesired stuff
Discussion Point:
Mythical Person-Month
Mythical Person-Month
•Why is software
development different from manufacturing car?
•If
it would take one person three months, why
does it take four people SIX months?
Trading People and Months is Hard=
Estimating Completion Time=
•Rules of thumb&=
#13;
–1/3
specification
–1/6 coding
=
–1/2 test planni=
ng,
testing, and fixing!
•<=
/span>
•Add time for co=
ding
to learn as you go, but don’t take time away from t=
he
other parts!
–Reread the sect=
ion
on “gutless estimating” if you
are tempted
Click to edit Master text styles
Second level
Third level
Fourth level
Fifth level
' + ENDSHOW_MESG + '
![](3D"notes_flag.gif")
No=
tes![](3D"buttons.gif")
