This preview shows page 1. Sign up to view the full content.
Unformatted text preview: common Database Operations: Create
Delete Operations occur at all levels: Tables, Records,
Columns Database Tables Tables represent entities
Tables are always named in the singular, such as:
Vehicle, Order, Grade, etc.
Tables in database jargon are “flat files”, dBase or
Spreadsheet like.. Attributes Characteristics of an entity
Examples: Vehicle (VIN, color, make, model, mileage)
Student (SSN, Fname, Lname, Address)
Fishing License (Type, Start_date, End_date) Database Table Example Figure 1: A simple – and flawed – table design. Figure 2: An improved database table.. Database Views A View is an individual’s picture of a database. It can
be composed of many tables, unbeknownst to the
user. It’s a simplification of a complex data model
It provides a measure of database security
Views are useful, primarily for READ-only users and are
not always safe for CREATE, UPDATE, and DELETE. Table Indexing An Index is a means of expediting the retrieval of
Indexes are “built” on a column(s).
Indexes occupy disk space; occasionally a lot.
Indexes aren’t technically necessary for operation
and must be maintained by the database
administrator. BTree Index Example Commonly used with
“attribute” tables as well
tables (CAD data
Binary coding reduces
the search list by
streaming down the
A “balanced” tree is
best. Primary Key #
3 37 12
3 19 49
Low High 59 Database Relationships How is one entity related to another entity?
Real-world sources: Ownership
Regulation Database Table Keys
A key of a relation is a subset of attributes with the
• Unique identification
• Non-redundancy Types of Keys
PRIMARY KEY Serves as the row level addressing mechanism in the relational
It can be formed through the combination of several items. FOREIGN KEY A column or set of columns within a table that are required to
match those of a primary key of a second table. These keys are used to form a RELATIONAL JOIN thereby connecting row to row across the individual
tables. Relational Database Management System
Name Address Parcel # John Smith
T. Brown 18 Lawyers Dr.
14 Summers Tr. 756554
887419 Table B
Parcel # Assessed Value 887419152,000
446397100,000 Database Keys Primary Key - Indicates uniqueness within records or
rows in a table.
Foreign Key - the primary key from another table,
this is the only way join relationships can be
There may also be alternate or secondary keys
within a table. Constructing Join Relationships One-to-many relationships include the Primary Key
of the ‘one’ table and a Foreign Key (FK) in the
‘many’ table. Other common terms Cardinality: one-to-one, one-to-many, many-to-many
Optionality: the relationship is either mandatory or
optional. Ensuring Database Integrity Database integrity involves the maintenance of the
logical and business rules of the database.
There are two kinds of “DB Integri...
View Full Document
- Spring '11