Chapter 5 Notes

Crud refers to the most common database operations

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: common Database Operations: Create Read Update 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 data. 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. B­Tree Index Example Commonly used with “attribute” tables as well as “graphic-attribute” tables (CAD data structures) Binary coding reduces the search list by streaming down the “tree”. A “balanced” tree is best. Primary Key # 37 12 49 59 19 44 3 37 12 3 19 49 44 Number Low High 59 Database Relationships How is one entity related to another entity? Real-world sources: Ownership Parentage Assignment Regulation Database Table Keys Definition: A key of a relation is a subset of attributes with the following attributes: • Unique identification • Non-redundancy Types of Keys PRIMARY KEY Serves as the row level addressing mechanism in the relational database model. 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 (RDBMS) Table A 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 established. 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 relationships 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

Ask a homework question - tutors are online