Database Terminology and Components

Database Terminology

• Database constitutes a logical structure designed for data storage
• Database management system (DBMS) represents the software used for database creation and interaction

Database Components

• The components comprise characters, fields, records, and files

Character

• Represents the fundamental unit of data
• It could manifest as a letter, number, or symbol

Field

• The group constitutes related characters
• Representing an attribute or characteristic of an entity
• Corresponding to a column in the physical database

Record

• The field represents the collection for a specific entity
• Corresponds to a row in the subject database

File

• Represents a group of similar records

Database Management System

• It manages the physical structure through data storage
• It controls access and privileges through security measures
• Multiuser access allows the management of concurrent data access
• Data access is provied through a language that facilitates interactions
• Backup procedures enable recovery options after failures
• Data integrity enables checks and constraints
• A data dictionary maintains information about database structure

Database Design

• It uses the Systems Development Life Cycle (SDLC)
• It uses the Entity-relationship model (ER model)
• It uses Normalization

Systems Development Life Cycle (SDLC)

• Systems investigation involves understanding the problem
• Systems analysis involves understanding the solution
• Systems design focuses on logical and physical component creation
• Systems implementation focuses on placing completed systems into operation
• Systems maintenance and review involves implemented system evaluation

Entity-Relationship Model (ER Model)

• Used to show the relationships among the entities
• It can include one-to-one, one-to-many, and many-to-many relationships

One-to-One Relationship

• Each data occurrence in one entity is represented by one occurrence in the other
• Example: An individual has one Social Security number, and each SSN belongs to one person

One-to-Many Relationship

• Data occurrence in one entity is represented by many occurrences in the other
• Example: One instructor may teach many classes, but a class has one instructor

Many-to-Many Relationship

• Data can have multiple occurrences in both entities
• Example: Students can take multiple classes, and each class consists of many students
• Cannot be included in the physical database

Database Normalization

• Determines required tables and columns for each table
• Involves a multi-step process
• Reduces or controls data redundancy

Database Normalization (continued)

• Data redundancy duplicates data in a database
• Data anomalies refer to inconsistencies

Unnormalized Data

• Contains repeating items in the Author column in the data BOOKS table

First-Normal Form (1NF)

• The Primary key is identified
• Repeating items are eliminated

First-Normal Form (1NF) (continued)

• ISBN (International Standard Book Number) and Author columns create a composite primary key

Composite Primary Key

• Multiple columns identify a row
• Can lead to partial dependency, where a column depends on a portion of the primary key

Second-Normal Form (2NF)

• Partial dependency must be eliminated
• Break the composite primary key into two parts, each representing a separate table

Second-Normal Form (2NF) (continued)

• An example is the data BOOKS table

Third-Normal Form (3NF)

• Publisher contact name has been removed

Summary of Normalization Steps

• 1NF: eliminate repeating items, identify the primary key
• 2NF: table exists in 1NF, and partial dependencies are eliminated
• 3NF: table exists in 2NF, and transitive dependencies are eliminated

Relating Tables within the Database

• Normalized tables are linked
• Tables connect through a common field
• A typical common field uses a primary key and foreign key

Lookup Table

• Common reference for descriptive data tables referenced via foreign keys

Structured Query Language (SQL)

• Represents a data sublanguage
• Used to create or modify, add, edit, and retrieve data
• ANSI and ISO standards

Basic SQL SELECT statements

• SELECT retrieves data from the database
• Referred to as a query

SELECT Clause

• SELECT and FROM clauses are required
• SELECT clause identifies column(s)
• FROM clause identifies table(s)
• Each clause begins with a keyword

Operations within the SELECT statement

• The select statement includes column aliases, arithmetic operations, suppressing duplicates & concatenating data

Column Aliases

• Column headings are used to name columns
• Optional AS keyword allows aliases after column headings
• Enclose in double quotation marks to contain blanks

Using DISTINCT & UNIQUE

• A DISTINCT or UNIQUE keyword removes duplicates

Using Concatenation

• Data combined with a string literal
• SQL uses a concatenation operator

Using Arithmetic Operations

• Multiplication & division solved before addition & subtraction
• Operations override order within parentheses

NULL Values

• Representing a condition where the data is missing

Database Table

• A table stores data in the database
• A table consists of columns and rows
• Tables are created using data definition language commands

Table Design

• Table and column names are a maximum of 30 characters with no blank spaces
• Table and column names must begin with a letter and be unique
• Table and column names can contain numbers and underscores
• Table and columns cannot use reserved words

Table Creation

• Datatype must be specified for each column
• Table holds a maximum of 1,000 rows

User Tables

• Data dictionary maintains information about database objects
• Query data dictionary to examine schema tables
• The USER_TABLES data dictionary contains table information

Describe

• DESCRIBE displays the structure of a table

Table Creation through Subqueries

• Use subqueries to retrieve data from a table
• This requires the AS keyword

Modifying Existing Tables

• Use the ALTER TABLE command to update existing tables

Modification Guidelines

• A column must be as wide as the data
• A decrease in size isn't allowed if a NUMBER column contains data
• Default data won't affect existing data when changed

Drop Column

• The ALTER TABLE command can drop only one column per execution, and deletion is permanent
• One cannot delete the last remaining column

Purge and Drop

• Storage space is deleted at a later time
• Storage space is freed by ALTER TABLE...DROP UNUSED Command

Renaming Tables

• Use RENAME...TO to rename a table

Truncating Tables

• TRUNCATE TABLE command removes all rows, but an empty table structure remains

Deleting Tables

• DROP TABLE command removes the table structure, but the data remains
• Oracle 10g introduced recyclying bins
• Dropped tables can be recovered from recyclying bin

Constraints

• Rules enforce business rules, practices, and policies
• Constraints ensure precision and integrity

Constraint Types

The keys are:

• Primary
• Foreign
• Unique
• Check
• Not Null

Creating Constraints

• Assign names with the keyword CONSTRAINT
• The server names the constraint, and you can use informative names to debug
• When to create -During table creations -After table creation, when you have modiifed an existing table -Column level approach -Table level approach

Creating Constraints at the Column Level

• If a column is being created at the column level: columnname [CONSTRAINT constraintname] constrainttype,

Creating Constraints at the Table Level

• Any constraing type except NOT NULL [CONSTRAINT constraintname] constrainttype(columnname, ...)

Enforcement of Constraints

• If data values violate a constraint, the entire row is rejected

Adding Constraints to Existing Tables

• Add constraints to tables using the ALTER TABLE command ALTER TABLE tablename ADD [CONSTRAINT constraintname] PRIMARY KEY (columnname);

Foreign Keys

• Values exist in the referenced column of a table
• NULL values are allowed
• Enforce referential integrity

Creating Foreign Keys

ALTER TABLE orders ADD CONSTRAINT orders_customer#_fk FOREIGN KEY (customer#) REFERENCES customers (customer#);

Unique Keys

• Updates must meet a specified condition

Using Check

ALTER TABLE books ADD CONSTRAINT books_title_uk UNIQUE (title);

Using Not Null

• Updates and additions must meet specified condition ALTER TABLE orders MODIFY (customer# CONSTRAINT orders_customer#_nn NOT NULL);

Constraints During Table Creation

A column may include multiple constraints: CREATE TABLE ORDERITEMS Multiple tables

• The order# column is included in a primary key and a foreign key constraint

User Constraints

• Users can display contraint listing

Using Disable/Enable

• DISABLE means enable the clause of the ALTER TABLE Command

Dropping Constraints

• Constraints must be dropped and recreated because they cannot be modified -PRIMARY KEY, just list type of constraint -UNIQUE, include column name -All others, reference constraint name
• ALTER TABLE tablename DROP PRIMARY KEY, or UNIQUE ALTER TABLE tablename DROP CONSTRAINT constraintname

Summary

• Constraints serve business rules, policies, and procedures that ensure data integrity in tables
• PRIMARY KEY constraints ensure that there won't be dulicate NULL values
• FOREIGN KEY constraints state that the referenced column entry must match the referenced table
• A unique constraint is similar to a PRIMARY KEY constraint except it allows NULL values to be stored in the specified column
• A CHECK constraint ensures a value meets a specified condition and a NOT NULL constraints ensures a value is provided for a coulmn
• USER_CONSTRAINTS and USER_CONS_COLUMNS data dictionary views provide information regarding constraints

Explore database fundamentals, including logical structures for data storage and DBMS software for interaction. Understand key components: characters (basic data units), fields (related characters as attributes), records (field collections for entities), and files (groups of similar records). Learn how DBMS manages physical structure, security, and multiuser access.