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)