Database Fundamentals - Introduction to Computer Science - Lecture Slides, Slides of Computer Applications

Download Database Fundamentals - Introduction to Computer Science - Lecture Slides and more Slides Computer Applications in PDF only on Docsity!

Connecting with Computer Science

Database Fundamentals

Objectives

• In this chapter you will:
  • Consider the widespread use of databases
  • Take a brief tour of database development history
  • Learn basic database concepts
  • Be introduced to popular database management software
  • See how normalization makes your data more organized

Objectives (cont’d.)

• In this chapter you will (cont’d.):
  • Explore the database design process
  • Understand data relationships
  • Gain an understanding of Structured Query Language (SQL)
  • Learn some common SQL commands

Why You Need to Know About...

Databases

• Data must be organized
• Effective computer professionals know correct

database design

• Normalization
  • Ensures an accurate and reliable database
• Structured Query Language (SQL)
  • Describes how information is retrieved from relational database

Database Applications

• Database
  • Data logically related and organized into a file or set of files to allow access and use
• Database applications
  • Student grading and library inventory
  • Genealogy studies and Social Security payments
  • Real estate sales, video store rentals, and retail sales
  • Space shuttle missions
• Database development
  • Essential part of computer professional’s daily life

Brief History of Database Management

Systems

• 1970 to 1975
  • Work of IBM employees E. F. Codd and C. J. Date
    • Created theoretical model for designing data structures
    • Model became foundation for database design
  • Software for organizing and sorting data
    • System R by IBM and Ingres by UC-Berkeley
    • Structured Query Language (SQL)
    • SQL: database standard
    • Database management system (DBMS) for PCs
• 1978
  • C. Wayne Ratliff of Martin Marietta develops Vulcan

Brief History of Database Management

Systems (cont’d.)

• 1980 to the present
  • Vulcan renamed dBASE II (no dBase I)
  • Popularity of dBASE II inspires other companies
    • Paradox, Microsoft Access, and FoxPro
  • Databases become essential for business
    • Corporate decision making
    • Systems: inventory management to customer support

Table 6-1, Popular database management systems

Brief History of Database Management

Systems (cont’d.)

Database Management System

Fundamentals

• Six main DBMS functions
  • Manage database security
  • Manage multiple users’ access to the database
  • Manage database backup and recovery
  • Ensure data integrity
  • Provide an end-user interface to the database
  • Provide a query language allowing users to modify and view database information easily

Database Concepts

• Basic database elements
  • Database: collection of one or more tables (entities)
  • Table or entity: divided into rows and columns
  • Row (record or tuple): collection of columns
  • Column (field or attribute): represents specific information
  • Domain: set of possible column values

Figure 6-1, A database table consists of rows and columns

Database Concepts (cont’d.)

Indexes

• Special files occupying their own space
  • Specify columns determining how information stored in a table can be accessed more efficiently - Examples: music database and the telephone book
• Advantages
  • Flexibility: many different columns to sort against
  • Searching and retrieval speeds up
• Disadvantages
  • Extra storage space
  • Updating takes longer

An Example of Indexing

Figure 6-2, You use database concepts in your everyday life

An Example of Indexing (cont’d.)

• Each database row has similar attributes
• Sort key: one or more columns used to determine

the data’s sort order

• One key or a combination of keys determines sort order
• Database information is stored in natural or

sequential order

• Order of records displayed equals the order records are entered

Figure 6-3, Database records sorted by using the UPC column as a key

An Example of Indexing (cont’d.)

Figure 6-4, Database records sorted by Brand_Name and Description

An Example of Indexing (cont’d.)

Normalization

• Set of rules dictating database design
  • Eliminates duplication and inconsistencies
  • Process: sequence of stages called normal forms
    • Five normal forms
    • Third normal form provides sufficient structure
• Three database design problems solved
  • Representation of certain real-world items
  • Redundancies (repetitions) in data
  • Excluded and inconsistent information

Preparing for Normalization: Gathering

Columns

• Make a list of all pertinent fields (columns or

attributes)

• Source of fields: end-user reports or song inventory
• Write fields on the column list
• Review user-specified input forms
• Convert each field from the report to column in table

Figure 6-5, End-user report with table columns highlighted

Preparing for Normalization: Gathering

Columns (cont’d.)

Figure 6-6, Additional table columns can be gleaned from input forms

Preparing for Normalization: Gathering

Columns (cont’d.)

Preparing for Normalization: Gathering

Columns (cont’d.)

• Reconcile fields in report to column list
• Create tables of columns by combining associated

fields

• Logically group related information
  • Example: information on artist and song files
• Gather data to create physical music database

First Normal Form

• Unnormalized table
  • Row-column intersection with two or more values
• First normal form (1NF) eliminates redundancies
  • Create new record for duplicated column
  • Fill in blanks so all columns in record have a value
  • Columns with duplications: Album_Num, Album_Name, Artist_Code, Artist_Name, Media_Type, and Genre_Code
• Remaining redundancies are addressed later

Second Normal Form

• Next steps:
  • Assign a primary key to the table
  • Identify functional dependencies within the table
• Primary key (PK)
  • Column or combination of columns (composite) uniquely identifying a row within a table - Examples: Student ID or Artist_Code

Second Normal Form (cont’d.)

• Determinant: column(s) used to determine value

assigned to another column in the same row

• Example: Artist_Code determinant for Artist_Name
• Functional dependency: column’s value dependent

on another column’s value

• Each value of first column is matched to single value in second - Example: Artist_Name functionally dependent on Artist_Code
• Composite key: primary key made up of more than

one column

Second Normal Form (cont’d.)

• Second normal form (2NF)
  • First normal form and non-PK columns functionally dependent on PK
• Creating 2NF
  • Determine columns not dependent on PK
    • Remove columns and place in new table
  • Default 2NF: table without composite PK
• Primary 2NF benefit: save disk space