Database Management Systems and Concepts
undefined
Database1
Database Creation and Management
Microsoft Access Lectures…
Not about learning Microsoft Access
Just a small easy to use DB tool
About learning very basic concepts &
theories of Relational Database System”
A lot easier to understand using a tool
Help understand advanced topics such as
data modeling (ERD)
3
Basic DB Terms
Data
: Meaningful facts, text, graphics, images,
sound, video segments
A collection of individual responses from a marketing
research
Information
: Data processed to be useful in decision
making
Pattern of geographical buying habit based on analysis of a
marketing research
Metadata
: Data that describes data
4
Data in Context
Large volume of facts, difficult to interpret & make decisions
5
Information
Useful for decision making / interpretation
6
Metadata
Descriptions of the properties or characteristics of the data, including data types,
field sizes, allowable values, and documentation (
Data Dictionary
)
•
ideal employee for developing
Mktg metadata
for the Mktg table?
Purpose of Database
The purpose of the database system is to
keep track of things digitally such as orders
The database system store data that is more
complicated than a simple list in a
spreadsheet SW tool (i.e., Excel)
Excel: As data increases, complexity of data
organization increases…...not linearly.
8
Database
Organized collection of electronic data
Data Stored in a standardized form
Allow data to be easily accessed, manipulated
and updated
Organizational Database
Systems
Database Management System
(DBMS)
System software for creating and managing
databases.
The DBMS provides users and programmers
with a systematic way to create, retrieve,
update and manage data.
It also serves as
an intermediary (bridge)
between database applications and the
database.
Overview of Access
One of the Relational DBMS.
Access, SQL Server, Oracle, DB 2
MS Access is a RDBMS and designed for
managing and controlling
RDS (relational
database system)
.
“Relation” means “Table.”
“Field” means “Column.”
Relational Database System
A collection of tables that are related to one
another based on a common field
.
Primary key and foreign key
RDS theory
Developed based on
predicate
logic
and
set
theory
from mathematics.
Relational Database System
Property of RDS
Based on the set theory
1.
There are no duplicate rows.
The body of the relation is a mathematical set
(i.e., a set of rows), and sets in mathematics by
definition do not include duplicate elements.
If a "relation" contains duplicate rows, then it is
not a relation
.
Property of RDS
2.
Rows are unordered (top to bottom) and
columns are unordered (left to right).
Sets in mathematics are not ordered. So, even if
a relation (table) A's rows are
reversely ordered
,
it is still the
same relation
.
Thus,
there is no such thing as "the 5th
row/column" or the last row/column
. In other
words, there is no concept of positional
addressing.
Property of RDS
3.
No two or more columns of the same table
can have the same name
The relational model requires every attribute to
be referenceable.
Otherwise, it cannot be referenced because of
the obvious (duplication) ambiguity.
Property of RDS
4.
Every value is
atomic
.
A
t
e
v
e
r
y
r
o
w
-
a
n
d
-
c
o
l
u
m
n
p
o
s
i
t
i
o
n
w
i
t
h
i
n
t
h
e
t
a
b
l
e
,
t
h
e
r
e
a
l
w
a
y
s
e
x
i
s
t
s
p
r
e
c
i
s
e
l
y
o
n
e
v
a
l
u
e
,
n
e
v
e
r
a
l
i
s
t
o
f
v
a
l
u
e
s
.
O
r
e
q
u
i
v
a
l
e
n
t
l
y
,
r
e
l
a
t
i
o
n
s
d
o
n
o
t
c
o
n
t
a
i
n
r
e
p
e
a
t
i
n
g
g
r
o
u
p
s
.
Example RDS Table (Relation)
No Order and atomic value in each cell
Not a RDS Table Example 1
Cells of the table hold multiple values
Not a RDS Table Example 2
No two rows can be identical
Open an existing database
To open an existing database, you must
first start Access.
Download and save “
Restaurant 1”
database.
DO NOT JUST OPEN!
And then, simply
double-click
the existing
database (Restaurant 1) to open.
How to create
and
save a new
Access database
Create a
new
database
A
s
s
i
g
n
y
o
u
r
d
a
t
a
b
a
s
e
n
a
m
e
a
s
y
o
u
c
r
e
a
t
e
S
p
e
c
i
f
y
s
a
v
i
n
g
l
o
c
a
t
i
o
n
Database name: your last name + initial of your first name
W
h
e
n
y
o
u
p
r
e
s
s
t
h
e
S
a
v
e
b
u
t
t
o
n
i
n
A
c
c
e
s
s
,
y
o
u
a
r
e
s
a
v
i
n
g
t
h
e
d
e
s
i
g
n
o
f
t
h
e
A
c
c
e
s
s
o
b
j
e
c
t
s
a
n
d
N
O
T
t
h
e
d
a
t
a
b
a
s
e
i
t
s
e
l
f
!
The Save function in Access differs from the Save function in
other Windows programs.
Valle Coffee’s Restaurant DB
V
a
l
l
e
c
o
m
p
a
n
y
s
e
l
l
s
i
n
e
x
p
e
n
s
i
v
e
c
o
f
f
e
e
b
e
a
n
s
t
o
v
a
r
i
o
u
s
r
e
s
t
a
u
r
a
n
t
s
.
B
a
r
b
a
r
a
H
e
n
n
e
s
s
e
y
,
t
h
e
D
i
r
e
c
t
o
r
o
f
C
R
M
,
a
n
d
h
e
r
s
t
a
f
f
u
s
e
A
c
c
e
s
s
t
o
m
a
i
n
t
a
i
n
c
o
m
p
a
n
y
d
a
t
a
s
u
c
h
a
s
c
u
s
t
o
m
e
r
o
r
d
e
r
s
a
n
d
b
i
l
l
i
n
g
s
.
B
a
r
b
a
r
a
h
a
s
r
e
c
e
n
t
l
y
d
e
v
e
l
o
p
e
d
R
e
s
t
a
u
r
a
n
t
1
d
a
t
a
b
a
s
e
t
o
t
r
a
c
k
o
r
d
e
r
s
a
n
d
b
i
l
l
i
n
g
s
.
H
o
w
e
v
e
r
,
s
h
e
h
a
s
n
o
t
b
e
e
n
a
b
l
e
t
o
d
e
v
e
l
o
p
t
h
e
d
a
t
a
b
a
s
e
f
u
l
l
y
t
o
t
r
a
c
k
a
n
d
m
a
i
n
t
a
i
n
o
t
h
e
r
i
m
p
o
r
t
a
n
t
c
o
m
p
a
n
y
d
a
t
a
.
S
o
,
s
h
e
i
s
a
s
k
i
n
g
f
o
r
y
o
u
r
h
e
l
p
i
n
c
o
m
p
l
e
t
i
n
g
a
n
d
m
a
i
n
t
a
i
n
i
n
g
t
h
e
V
a
l
l
e
d
a
t
a
b
a
s
e
.
Descriptions of Restaurant DB
Valle coffee’s Restaurant 1 database will
contain five tables:
C
u
s
t
o
m
e
r
t
a
b
l
e
,
w
h
i
c
h
B
a
r
b
a
r
a
a
l
r
e
a
d
y
h
a
s
.
O
r
d
e
r
t
a
b
l
e
,
w
h
i
c
h
y
o
u
w
i
l
l
c
r
e
a
t
e
s
o
o
n
.
P
r
o
d
u
c
t
a
n
d
O
r
d
e
r
D
e
t
a
i
l
t
a
b
l
e
s
,
w
h
i
c
h
y
o
u
w
i
l
l
i
m
p
o
r
t
f
r
o
m
F
i
n
e
F
o
o
d
d
a
t
a
b
a
s
e
.
B
i
l
l
i
n
g
A
d
d
r
e
s
s
t
a
b
l
e
t
h
a
t
i
s
i
n
E
x
c
e
l
f
o
r
m
a
t
a
n
d
y
o
u
w
i
l
l
i
m
p
o
r
t
i
t
,
a
n
d
t
h
e
n
c
o
n
v
e
r
t
t
o
A
c
c
e
s
s
t
a
b
l
e
.
B
i
l
l
i
n
g
A
d
d
r
e
s
s
T
a
b
l
e
C
u
s
t
o
m
e
r
T
a
b
l
e
O
r
d
e
r
T
a
b
l
e
O
r
d
e
r
D
e
t
a
i
l
T
a
b
l
e
P
r
o
d
u
c
t
T
a
b
l
e
Explore the world of databases with topics ranging from Database Creation and Management to Basic Database Terms. Learn about the significance of data, information, and metadata in decision-making processes. Discover the purpose of database systems and the role of Database Management Systems (DBMS) in organizing and managing electronic data efficiently.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
Database1 Database Creation and Management
Microsoft Access Lectures Not about learning Microsoft Access Just a small easy to use DB tool About learning very basic concepts & theories of Relational Database System A lot easier to understand using a tool Help understand advanced topics such as data modeling (ERD)
Basic DB Terms Data: Meaningful facts, text, graphics, images, sound, video segments A collection of individual responses from a marketing research Information: Data processed to be useful in decision making Pattern of geographical buying habit based on analysis of a marketing research Metadata: Data that describes data 3
Data in Context Large volume of facts, difficult to interpret & make decisions 4
Information Useful for decision making / interpretation 5
Metadata Descriptions of the properties or characteristics of the data, including data types, field sizes, allowable values, and documentation (Data Dictionary) ideal employee for developing Mktg metadata for the Mktg table? 6
Purpose of Database The purpose of the database system is to keep track of things digitally such as orders The database system store data that is more complicated than a simple list in a spreadsheet SW tool (i.e., Excel) Excel: As data increases, complexity of data organization increases ...not linearly.
Database Organized collection of electronic data Data Stored in a standardized form Allow data to be easily accessed, manipulated and updated 8
Organizational Database Systems
Database Management System (DBMS) System software for creating and managing databases. The DBMS provides users and programmers with a systematic way to create, retrieve, update and manage data. It also serves as an intermediary (bridge) between database applications and the database.
Overview of Access One of the Relational DBMS. Access, SQL Server, Oracle, DB 2 MS Access is a RDBMS and designed for managing and controlling RDS (relational database system). Relation means Table. Field means Column.
Relational Database System A collection of tables that are related to one another based on a common field. Primary key and foreign key RDS theory Developed based on predicate logic and set theory from mathematics.
Relational Database System A schematic diagram of a relational database (a) and a sample part of a relational database showing different tables (b)
Property of RDS Based on the set theory 1. There are no duplicate rows. The body of the relation is a mathematical set (i.e., a set of rows), and sets in mathematics by definition do not include duplicate elements. If a "relation" contains duplicate rows, then it is not a relation.
Property of RDS 2. Rows are unordered (top to bottom) and columns are unordered (left to right). Sets in mathematics are not ordered. So, even if a relation (table) A's rows are reversely ordered, it is still the same relation. Thus, there is no such thing as "the 5th row/column" or the last row/column. In other words, there is no concept of positional addressing.
Property of RDS 3. No two or more columns of the same table can have the same name The relational model requires every attribute to be referenceable. Otherwise, it cannot be referenced because of the obvious (duplication) ambiguity.
Property of RDS 4. Every value is atomic. At every row-and-column position within the table, there always exists precisely one value, never a list of values. Or equivalently, relations do not contain repeating groups.
Example RDS Table (Relation) EmployeeNumber FirstName 104 101 100 107 LastName Abernathy Cadley Copley Jackson Mary Jerry Alex Megan No Order and atomic value in each cell
Not a RDS Table Example 1 Cells of the table hold multiple values EmployeeNumber 100 Phone 335-6421, 454-9744 215-7789 610-9850 299-9090 LastName Abernathy 101 104 107 Cadley Copley Jackson
Not a RDS Table Example 2 No two rows can be identical EmployeeNumber 100 101 104 100 107 Phone 335-6421 215-7789 610-9850 335-6421 299-9090 LastName Abernathy Cadley Copley Abernathy Jackson
Open an existing database To open an existing database, you must first start Access. Download and save Restaurant 1 database. DO NOT JUST OPEN! And then, simply double-click the existing database (Restaurant 1) to open.
How to create and save a new Access database Create a new database Assign your database name as you create Specify saving location Database name: your last name + initial of your first name When you press the Save button in Access, you are saving the design of the Access objects and NOT the database itself! The Save function in Access differs from the Save function in other Windows programs.
Valle Coffees Restaurant DB Valle company sells inexpensive coffee beans to various restaurants. Barbara Hennessey, the Director of CRM, and her staff use Access to maintain company data such as customer orders and billings. Barbara has recently developed Restaurant 1 database to track orders and billings. However, she has not been able to develop the database fully to track and maintain other important company data. So, she is asking for your help in completing and maintaining the Valle database.
Descriptions of Restaurant DB Valle coffee s Restaurant 1 database will contain five tables: Customer table, which Barbara already has. Order table, which you will create soon. Product and Order Detail tables, which you will import from FineFood database. Billing Address table that is in Excel format and you will import it, and then convert to Access table.
Billing Address Table CustomerNum BillingName Street City State Zip 129 PO Box 2800 Grandville MI 49468 Sandy Lookout Restaurant Customer Table CustomerNum CustomerName Street City State ZipCode OwnerName Phone FirstContact 000 Choi COB 105 CSUB CA 93311 Scott Choi 5348 09/20/2001 Order Table OrderNum 201 CustomerNum 107 Paid InvoiceAmt BillingDate 01/15/2001 No 854.00 Order Detail Table OrderNum 201 ProductCode 2834 Qty 11 Product Table ProductCode 2301 CoffeeName Colombian Aged Crop Weight/Size 1 lb pkg Price Decaf 7.99
