Terms in this set (78)
Standard language for relational data manipulation
SQL Objective 1
Create the database and relation structures
SQL Objective 2
Perform database updates, deletions and insertions
SQL Objective 3
Perform both simple and complex queries to transform the raw data into meaningful information
SQL Characteristic 1
SQL Characteristic 2
SQL Characteristic 3
consists of standard english words
SQL Characteristic 4
SQL Characteristic 5
can be used by a variety of users including DBA's, application programmers, and other types of end users
Specifies which columns are to appear in the output
specifies the table or tables to be used
filters the rows subject to some condition
specifies the sorting condition
forms groups of rows with the same column value
filters the groups subject to some condition
one table, one select
Groups data by a column value and produces a summary row
the use of a select statement embedded with in another select statement
combines columns from several tables(ie the join operation)
SELECT SID, Name, Major
SELECT SID, Name, Major, GradeLevel, Age
WHERE Major = 'MATH'
SELECT Name, Major, Age
WHERE Major = 'ACCOUNTING'
ORDER BY Name
Subquery with Equality
SELECT sno, fname, lname, position
WHERE bno =
WHERE street = '163 Main St.')
Nested Subqueries; use of IN
SELECT pno, street, area, city, type, rooms, rent
WHERE sno IN
WHERE bno =
WHERE street = '163 Main St.') )
Using a Subquery with an aggregate function
SELECT sno, fname, lname, position
WHERE salary >
Using ALL with a Subquery
SELECT sno, fname, lname, position
WHERE salary > ALL
WHERE bno = 'B3')
Using EXISTS with a Subquery
SELECT EmployeeID, firstname, lastname, salary
WHERE EmployeeID=[Enter Employee ID:] AND Exists
WHERE Password.EmployeeID =
[Enter Employee ID:] AND
Password = [Enter Password:]);
SELECT student.sid, student.name, enrollment.classname
FROM student, enrollment
WHERE student.sid = enrollment.studentnumber
Three Table Join
SELECT S.sid, S.name, E.classname, C.room
FROM student S, enrollment E, course C
WHERE S.sid = E.studentnumber AND
E.classname = C.name
This type of query goes beyond simple searches: it makes changes to the data.
Insert: Insert data into a table
Update: Update data in a table
Delete: Delete data from a table
______ INTO ENROLLMENT
VALUES (400, 'BD445', 44)
________ FROM STUDENT
WHERE STUDENT.SID = 100
SET PositionNumber = 44
WHERE SID = 400
Only compatible if they have the same number of attributes and each corresponding attribute has the same domain
Adding tuples from one relation A to those of a second B. Denoted A+B or A U B. A and B must be DC
Includes tuples that occur in the first relation A but no the second B. Denoted by A-B. A and B must be DC
Includes tuples that are in both the first relation A and the second relation B. Denoted by A ∩ B. and A and B must be DC
Projection (Relational Algebra)
Selects specified attributes (columns) from a relation
Selection (Relational Algebra)
Selects tuples Rows from a relation
the product of two relations is the conatenation of every tuple of one relation A with every tuple of a second relation B. Denoted AXB.
the join operator allows us to combine information from two or more tables.
Find those values in the first relation A that are related to all of the values in the second relation B. Denoted A/B
A structured list of data items from the entities defined in the ER Model. A view can be materialized or formatted as a form or report.
A screen display used for data entry and edit
What Forms should do
reflect the view structure
make data associations graphically clear
encourage appropriate action
information should be designed so that it is easy to navigate through
data on teh screen should be grouped and ordered into meaningful parts
Design Case 1
Selection of foreign key in a 1:1 relationship
Design Case 2
maintaining History of Time - Variant Data
Normally, existing attribute values are replaced with new value without regard to previous value.
Values change over time
Must keep a history of data changes
Must create a M:N relationship between the two entities.
Intersection table contains date of change.
Design Case 3
A Fan trap occurs when one entity is the parent in two or more 1:M relationships to other entities
There is no way to join the related entities together.
Design Case 4
Redundancy is seldom a good thing in database environment....
Occurs when there are multiple relationship paths between related entities....
Some designs use redundant relationships to simplify the design....
Integrity of a Database
It is important that the data in a database is correct and consistent
Integrity problems can occur from
incorrect data entry
poor database design
Ways to maintain data integrity
Enforcing domain constraints
Enforcing relationship constraints
Enforcing business rule constraints
Field types (text, numeric, date, memo, etc.)
Field validation rules
Lookup fields (i.e., drop-down lists)
Input masks (e.g., SSN, phone#)
Required field? (are nulls allowed?)
This constraint enforces uniqueness (duplicates not allowed) on an attribute.
Specify a primary key to enforce Entity Integrity (A primary key must have a unique value and null values are not allowed).
Set Indexed property to Yes (No duplicates) on fields that are not primary keys.
Enforce referential integrity (foreign key must either have a null or matching entry to related table)
For 1:1 relationships, foreign key must be unique
Enforce cardinality constraints through the application code
(1,1) cardinalities on the child side of 1:M relationships are enforced by making the foreign key a required field.
Business Rule Constraints
These constraints are particular to the logic and requirements of a given application.
They arise from procedures and policies that exist in the organization.
Add a check digit to validate a clerk's data entry.
The check digit is determined by some mathematical algorithm
A transaction is a logical unity of work that must be entirely completed or aborted; no intermediate states are accepted
: When this statement is issued, the changes are permanently recorded in the database.
When this statement is reached, the changes are aborted, and the database is "rolled back" to its previous consistent state.
The process of restoring the database to a correct state in the event of a failure.
Via Rollback/Rollforward (Undo/Redo)
Backup Facilities: make periodic backup copies of the database
Logging Facilities: keep track of all updates made to the database
Checkpoint Facilities: maintain a point of synchronization between the database and the transaction log
make periodic backup copies of the database
keep track of all updates made to the database
maintain a point of synchronization between the database and the transaction log
Type of transaction (transaction start, insert, update, delete, abort, commit)
Identifier of data item affected
Before-image of the data item
After-image of the data item
Log management information (such as pointers to previous and next log records for the transaction)
The point of synchronization between the database and the transaction log file. All buffers are force-written to secondary storage.
Fault-Tolerant Computer Systems
are systems that contain extra hardware, software, and power supply components that can back up a system and keep it running continuously to prevent system failure.
distributes large numbers of access requests across multiple servers
uses a backup server that duplicates all the processes and transactions of the primary server
links two computers together so that the second computer can act as a back up to the primary computer
Redundant Array of Independent Disks
What raid is
technology that provides enhanced performance and reliability through redundancy
Distributes data across multiple drives for faster read/write speeds, but does not duplicate information (faster performance only)
Writes the same data to two drives(mirroring)
offers both speed and mirroring
Features of Multiuser DBMS Products
Support for SQL and other open standards
Support for transaction processing (commits and rollbacks)
Transaction logging for quick recovery
Programmability (the ability to create customized applications)
Support for DBA functions (e.g., performance tuning and query optimization)