The Relational Model: Advanced Topics

clustered

Can only have one way to "cluster" the data together "sorted" on a disk - physically one per table -you can do more, but then it would have a different name changes may impact performance ex.) clustered index on MAJOR, STUDENTID (primary key...better idea to cluster it from that)

materialized view

Copy or replication of data Data actually stored Must be refreshed periodically to match corresponding base tables table and data saved as permanent object refreshes (complete, incremental...on-demand, intervals) remote sites

data security

Method of ensuring that data is correct, safe and cannot be read or changed by those who have no right to access it. grant or revoke -data insertions, selections, updates, deletions (CRUD) (limits and allows things on CRUD) -foreign key references -run a specified function or procedure -maintains data integrity -prevents errors and sabotage

stored procedure

a file containing a collection of compiled and optimized SQL statements that are available for future use scripts and benefits

form

a screen object you use to maintain, view, and print data from a database -prepackaged..all info included query and reporting interfaces -embedded actions with buttons -more user-friendly than SQL -increased data integrity and security

view

an application program's or an individual user's picture of a database persistent query -saved as a permanent object -usually complex...we want to save it because we use it a lot non-persistent data presentation -results are not a permanent object -we don't use it as often, so there is no need to save it and make it an object

scripts

another name for a database SQL file used in MySQL WHAT YOU DO may or may not return data (could be because there isn't any) may be vendor-specific (SQL implementation) BENEFITS (CPI) -controlling data access -preserving data integrity -improving productivity (reuse)

composite

clustered except multi-columns to group it (can group by major 1st, then alphabetically by last name, then...) multi-column, combined, concatenated ex.) who are my [major] seniors?

query execution plan

declarative and imperative

business rules

defines or constraints a process generally true or false -has the condition been met? results in action a statement that defines or constrains some aspect of a business . validating data before you have it

Entity Integrity

each key has to be unique...we can trust that it will be unique protects integrity of entity instance (ensure it is unique!) the rule that no column (attribute) that is part of the primary key may accept null values constraints on primary keys -unique -not null (can't pull anything from that entity) (null = "unknown" "not applicable")

indexes

files that relate key values to records that contain those key values taking a part of a table you need and saving it separately database object -part of the db structure (indexed table or full/partial copy of table) increase performance -full table scan vs. range/location three types -nonclustered, clustered, composite

heap vs. indexed

heap is unorganized, all over the place, it's there but you have to go out of your way to look for it (NOT INDEXED) indexed is organized by specifics, you know exactly where to look

solid state

like a battery -it flows right through...it's a solid unlike a CD

non-clustered index

not copies of the table but a sorting of the columns you specify that "point" back to the data pages in the clustered index pointers to the data physically addresses where the data is located

referential integrity

protect the integrity of primary key reference -make sure there are consistent relationships the rule that if a table A contains a foreign key that matches the primary key of table B, then the value of this foreign key must either match the value of the primary key for some row in table B or be null constraints on foreign keys -doesn't have to be unique, null values are permitted, and all values must exist in the pk field

selectivity

query/index/data number of distinct values/number of rows -from 1 (ideal) to close to 0 (never 0 tho) on slide 18 -selectivity: StudentID = 1 (5/5), LastName = 0.80 (4/5), FirstName = 0.60 (3/5)

Legal values

restrictions on attribute values only those permitted or specific -ex.) LastName may not contain numbers, Password restrictions, Date formats the property that no record can exist in the database with a value in a field other than a legal value

benefits of views

storage performance security hides DB complexity

reports

structured presentation of data using sorting, grouping, filtering, and other operations data visualizations -table or graphical -static or dynamic (reacts to something you do or doesn't) -actionable -drill down

cons of views

table-dependent inflexible performance

imperative

tells us HOW to complete the plan

declarative

tells us WHAT the plan is

query optimization

to minimize response times for large, complex queries best query plan based on selectivity -can be performed by DBMS (not always optimal) -tuned (improves or harms performance)