CS 325 - Week 3 Lecture 2 - 2016-09-08

*   review:
    we could consider a database to contain
    4 main elements:  
    *   user data
    *   metadata
    *   indexes
    *   application metadata

*   in an RDBMS - relational database management system --
    the RDBMS supports the abstraction/illusion
    for the users/application programs that
    the user data is in the form of relations/tables

*   ASIDE - an example of poorly-structured vs.
    better-structured relations...

    *   the structure of the relations in a database
        for a given scenario CAN affect how well
	that database will work in that scenario --
	impacts on data integrity, usability,
	applications, etc.

    *   example:  

        Student1(STU_NUM, stu_lname, stu_phone,
                 advisor_num, advisor_lname,
                 advisor_phone)

        vs.

        Student2(STU_NUM, stu_lname, stu_phone,
                 advisor_num)
        foreign key (advisor_num) references Advisor

        Advisor(ADVISOR_NUM, advisor_lname,
                 advisor_phone)

    *   in the first option,
        if an advisor has 20 students,
	their last name and phone are in the table
	20 times --

	if their phone changes, all 20 copies need
	to be updated;

        and why have the last name and phone
	repeated 20 times in this case?

	...really, there are two significant
	kinds of things -- entity classes --
	in this example, and we'll see that
	in an operational database, it typically'
	works better if each entity class in
	the scenario maps to one or more tables;

    *   the 2nd option, if an advisor phone
        changes, you change it in one row,
	the advisor table; 

	...and there's less unnecessary
	redundancy -- you NEED something to
	indicate who a student's advisor is,
	so the advisor_num foreign key
	is necessary redundancy in option 2
	(but you don't need to repeat the
	phone and last name of the advisor
	for every student)

    *   now, there's a cost to option 2 --
        to look up the phone number of
	a given student's advisor is easier
	in option 1 (it's in the same row),

	while in option 2, you do have to
	find the advisor number in Student2
	and then look up that advisor's
	phone in Advisor; (or, some additional operation
	or operations is needed)
	
*   metadata -- data about the data,
    the parts stored to make the database self-describing

    can be fairly extensive!
    try:

    describe user_tables

    describe user_tab_columns

    ...to get a taste;

*   indexes - here, we mean overhead data
    SUCH AS indexes, to improve performance
    and accessibility; sometimes called
    physical indexes;

    ...the DBMS may support the ability to
    specify additional indexes, that will
    under-the-hood make additions/changes
    to improve performance of that info;

    *   add them thoughtfully, not willy-nilly,
        since there IS a cost to these when
	making updates, for example;

*   application metadata -- a DBMS might also store
    data about application "pieces" of various
    types;
    (not all DBMSs support this, and those that
    do support it to different levels...)

*   DBMSs vary WIDELY in the features and functions
    they support;

    they always need to provide what is needed to
    serve as an interface between users/applications
    and the data;

    and sometimes considerably more;

    *   let's drill down a bit more about
        features that are typically required;

    *   to provide this desired interface,
        a DBMS must, at the very least, provide:

	*   some kind of a DDL - data definition
	    language
        *   some kind of a DML - data manipulation
	    language
        *   some kind of a DCL - data control
	    language

	(note that some may provide MULTIPLE
	languages for one or more of these...)

   *   a DDL lets the user define the database,
       to define its STRUCTURE

       *   SQL can be a DDL -- it has statements
           such as:

	   create
	   drop
	   alter
	   ...

    *   a DML lets the user insert, update, delete,
        and retrieve data from the database

	*   SQL can be a DML -- it has statements 
	    such as:

	    insert
	    update
	    delete
	    select
            ...
    
    *   DCL - data control language - intended
        to provide controlled access to the database

	*   security system
	*   integrity system
	*   concurrency control system
	*   recovery control system
        ...

    *   SQL can be a bit of a DCL --
        you can control access to a table using

	*   grant
	*   revoke
        ...

	*   you can grant insert, delete, update,
	    and/or select access to a database
	    object using SQL GRANT command

	    (and revoke insert, delete, update,
	    and/or select access to a database
	    object using SQL REVOKE command)